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 the time it is ok for there to be overlapping or conflicting events, but occasionally this will be a problem. 

We currently rely on users to check the calendar before creating a new record to make sure there is no applicable conflict, however I would like to build in some kind of automatic alert to help avoid user error. 

Basically, I need the user to be notified, either during the creation of the the record after the start and end datetime fields are set, OR after the record is saved, if the condition is met that any part of the duration of the event overlaps with the duration of another record.

For example: Record 1234 has a start datetime of 10/12/2022 of 13:30 and end datetime of 10/12/2022 of 16:30. A user goes to create a new record (let's call it 5678) and sets the start datetime of 10/12/2022 at 15:00 and end datetime of 10/12/2022 at 17:00. Since there is an overlap between 15:00 and 16:30 of the two records, I want the user to get an alert or a notification email.

I have not been able to find a way to do this yet. Does anyone have any suggestions?
Thanks,
-Matt

------------------------------
Matthew Thornton
------------------------------
  • 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
      2 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
        2 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
        ------------------------------