Expand all | Collapse all

auto flag checkbox fields via tresholds

  • 1.  auto flag checkbox fields via tresholds

    Posted 01-30-2018 23:40
    Any suggestions for the following challange.

    I'm working on a change request application.
    It;s not unlikely that requests that meet certain criteria will need to be 'auto-approved'
    ie cost < $50 & type = software than 'field_approved' --> checked.
    also actions like send email to john when hardwre or send to ann when software are likely.

    A possible option to achieve this is likely 'dynamic form rules' by creating conditions to compare to the hardcoded  thresholds and use the action field to flag the 'field_approved'
    However when these tresholds need to be changed all rules need to be amended. Not a nice solliution as I would like to pass this to the senior users but having the users playing around in the rules is not a good idea.
    I foresee that we will star twith a view tresholds but that it will quickly evolve into a jungle of rules

    Could the application variables be used here? Can application variables been seen by rules at all?  How to overcome the fact that the variables are text by default?
    create some kind of table with one single record and cretae a field for each variable?

    Any experiences or best-practices?


  • 2.  RE: auto flag checkbox fields via tresholds

    Posted 01-30-2018 23:49
    You are on the right track.  Do not hard code threasholds in form rules.

    While you can use Application variables, they are very obscure and only visible to Admins, so i do not recommend that approach..

    I would have a new table called Approval thresholds. Make 1 record and then via access permissions lock everyone, including the admin role  from adding or deleting records.

    The Record ID will be #1.

    Make a formula numeric field in your details table with a formula of 1. Then make a relationship using that field back to the Threasholds table.

    Then add create any new fields you like  for any threshold limits on that Threshold record  and look them up down to your details records.  You might consider if you want to snapshot them. 

  • 3.  RE: auto flag checkbox fields via tresholds

    Posted 01-31-2018 07:57
    Thank you, good to hear I'm doing good stuff here.

    Please allow me to drill down a bit on the relationship and table locking.

    Do I need to make a relationship the 'official way' and than make that single field in the CR-table  containing '1' the connected field?.
    In otherwords there is a hardcoded link this way to record ID#1 in the CR-treshold table.

    You see lock the CR-tresholds table before making the relationship.
    Though can that lock be removed afterwards for modifiing tresholds.?
    That lock is to avoid that QB will add fields or other stuff with the CR-thesholds table during making the relationship?
    And what should the relationship be 1 CR -< many Threshold or 1 Treshold -< many CR's?

    Thanks again

  • 4.  RE: auto flag checkbox fields via tresholds

    Posted 01-31-2018 12:46
    The relationship will be

    1 Treshold -< many CR's

    When I say to lock it just mean no one can add or delete the record which holds all your threshold settings. It will always be Record ID#1

    Of course, authorized Roles can be allowed to edit that recrd and as Admin you will create all your threshold fields there.

    It is like another way to do an application variable, except that any fields that you need in any details records will need to come in via a Relationship. The Record ID which holds the settings is Record ID 1, so in the details side, you need to have a formula that is also 1.