Forum Discussion

DianeSinawi's avatar
DianeSinawi
Qrew Member
2 years ago

Issues with form rules and notifications when Log Edits is enabled

I have a multiple choice field (Purchasing Review and Approval) with one value "Reviewed and Approved." The field is empty until someone approves. To log the user and date, I enabled Log Entries for this field.

After value "Reviewed and Approved" is selected, I don't want this field to be editable. However, my form rule is not working in this field. If I select a different multiple choice field that does not have Log Entries enabled, the rule is working.

NOTE: originally I had 2 values "Not Reviewed or Approved" and "Reviewed and Approved", but it had the same issue.

Also, I've set a notification (currently to myself for testing) that when the value changes to "Reviewed and Approved", a notification is sent. This is also not working.

Is this a QB limitation when using Log Entries, or am I doing something wrong? Thank you for your help!







------------------------------
Diane Sinawi
------------------------------

6 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew #1 Challenger
    2 years ago

    The issue is that once populated there is other text in that field.

    so create a field for [Approval Status] with this formula


    Trim(Right([Purchasing Review and Approval],"]"))


    Here below are my own cheat notes on parsing fields set to Log Changes where the new entries are at the bottom. 


    Parsing Append Only Fields

    so... if you have a text field set to Log Changes where the new entries act in the default manner which is to be at the bottom of the updates, here are some formuals to use to parse out the values from the most recent entry.  I am also assuming that you are logging the date and not the date and time.

    Most recent value (formula text field type)

    Trim(Right([my update field],"]"))

    Date of most recent update (this needs to be a formula date field)

    ToDate(Left(Right([my update field],"["),9))

    Who did the most recent update (formula text field type)

    Trim(NotLeft(Left(Right([my update field],"["),"]"),9))

    Parse out the date and time of the update

    var text DateAndTime = Left(Right([append only with date /time],"["),18);
    var date TheDate = ToDate(Left($DateAndTime," "));
    var timeofday TheTime = ToTimeOfDay(NotLeft($DateAndTime,9));


    ToTimestamp ($TheDate, $TheTime)



    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
    • MikeTamoush's avatar
      MikeTamoush
      Qrew Commander
      2 years ago
      I think the issue is a combination of problems. The first, Mark already explained. However, if I remember correctly, dynamic form rules and notification triggers look at whatever is in the field. As soon as the record is saved and the selection is logged, QB see's the field as null, as nothing is currently selected in the dropdown.

      The solution is to use Marks formula field, and have notifications trigger when the multiple choice field is changed, but the formula field equals your condition.

      You can use the formula field for the dynamic rule, but the problem you will have is, as soon as the select from the multiple choice field, it will instantly go read only, which means if they selected accidentally, they are stuck. You only want the rule to go into effect after they save.

      Mark might have a better solution for that issue, but I think in the past I have made a checkbox called 'Make Multiple Choice Read Only', then make a dynamic form rule that says 'When the record is saved and the Multiple Choice Field has been Changed to Approved' change Checkbox to Checked'

      Then make a second dynamic form rule based off that checkbox (if checkbox is checked, make multiple choice read only). If you do this, you actually don't really need Marks formula.

      ------------------------------
      Mike Tamoush
      ------------------------------
      • DianeSinawi's avatar
        DianeSinawi
        Qrew Member
        2 years ago
        Thank you Mike. I'm considering your and Mark's advise both. I'll update after I try something out.

        ------------------------------
        Diane Sinawi
        ------------------------------
    • DianeSinawi's avatar
      DianeSinawi
      Qrew Member
      2 years ago
      Thank you Mark. I'm considering your and Mike's advise both. I'll update after I try something out.

      ------------------------------
      Diane Sinawi
      ------------------------------
    • DianeSinawi's avatar
      DianeSinawi
      Qrew Member
      2 years ago
      I ended up finding a simpler solution, as I have limited experience with formulas.

      I removed logging from Review and Approval field. I added 2 new fields: Approved By and Approved Date. They autopopulate with current user and date when value in Review and Approval field is changed to "Reviewed and Approved". They are read-only fields, so the users cannot populate those fields themselves.

      Also, I made the Review and Approval field not editable when it is changed to "Reviewed and Approved".

      If someone accidentally approves, if they don't save they can just close the record and it will remove the value and make the field editable. If they save, we can temporarily suspend the rule, remove the value, then put the rule back.

      This gets me the ultimate result I need - ability to log who actually approved and date in some way, and ability to keep approvals from being edited. There's no conflicts with notifications. But I really appreciate the time you took to help. 



      ------------------------------
      Diane Sinawi
      ------------------------------