Forum Discussion

MatthewThornton's avatar
MatthewThornton
Qrew Member
3 years ago

Alert or notification based on datetime field of another record

Hi Quickbase community, I have an app that is principally used for posting events, which has a start and end datetime fields. My organization uses a calendar view to see the these events. Most of t...
RaziD_'s avatar
RaziD_
Qrew Cadet
3 years ago
HI Matthew;
You can create formula check box in the table to see if there is any record conflicting:
This formula will show if it is okay to create record for those time:

var text recs=GetRecords("{xx.OAF.'"&[Start Time]&"'}AND{'xx'.OBF.'"&[End Time]&"'}", "aaaaaa")
if($recs="", true, false)

In this formula:
xx= Field id of Start Time
aaaaaa=Table Dbid()

You can add more conditions to query above.
After you create this field you can create form rule to prevent user to save record if this checkbox field is true. Or you can create notification to be triggered when record is added and this checkbox field is equal to checked.

I hope this helps.

------------------------------
Razi D.
WEP Forms
http://www.destatechs.com
------------------------------
MatthewThornton's avatar
MatthewThornton
Qrew Member
3 years ago

Hi Razi,

Thanks for the assistance! This formula didn't quite work for me. After inputting my DBid and field ids, I was given an error message saying that the variable declaration was expecting text but found recordlist. I did some digging around and couldn't seem to find a way to adjust the recordlist to text so it could then be used in the supplemental if statement. Any ideas what to try next?

var text recs=GetRecords("{'8'.OAF.'"&[Start Date and Time of Activity]&"'}AND{'8'.OBF.'"&[End Date and Time of Activity]&"'}", "aaaaaa");
if($recs="", true, false)

Thanks!
-Matt



------------------------------
Matthew Thornton
------------------------------
  • RaziD_'s avatar
    RaziD_
    Qrew Cadet
    3 years ago
    Hi Matthew;
    You can add ToText() function to formula like  below:

    var text recs=ToText(GetRecords("{'8'.OAF.'"&[Start Date and Time of Activity]&"'}AND{'8'.OBF.'"&[End Date and Time of Activity]&"'}", "aaaaaa"));
    if($recs="", true, false)

    Or you can do:

    var number recs=Size(GetRecords("{'8'.OAF.'"&[Start Date and Time of Activity]&"'}AND{'8'.OBF.'"&[End Date and Time of Activity]&"'}", "aaaaaa"));
    if($recs>0, false, true)


    ------------------------------
    Razi D.
    WEP Forms
    http://www.destatechs.com
    ------------------------------