Forum Discussion

MikeTamoush's avatar
MikeTamoush
Qrew Commander
3 years ago

Need a sneaky workaround for a dynamic form rule issue.

Current Setup as follows:

Employees have Many Audits.

Employee Table with fields: Name (text field), and User Name (User field).
Audit table with lookup fields: Employee Name (Text, Proxy)

Goal is to set the Related Employee to the current user, if the Related Employee is blank (essentially, set the default for Related Employee to be Current User).  I can't just set the default to 'Current User' because related fields are numeric. I could potentially make my key field on my employees table be the user name, but I don't want to do that for some other reasons that will get me in trouble.

Current almost working solution: I have a formula query on my Audit table that goes and finds the record ID in the Employee table, of the Current user (I match up emails from the current user to the email in the employee table). Now that I have the record ID, I have a simple dynamic form rule that says, if the related employee is blank, set the related employee to the number in the formula query. Works perfect.

Except for one thing. In my related field, I have the results limited to about 10 people (auditors). If a non auditor tries to create a report (which can happen, they may create a report and set the auditor), then essentially QB freaks out. The rule says 'If Related Employee is not blank....change field value.' So what happens? It briefly fills in the Related Employee, instantly figures out it isn't a valid selection so it blanks it out....so now it is blank and it tries to autofill again...and again....so you end up with the related field constantly attempting to fill itself, then blanking out, and repeating.

It does not matter if the checkbox on bottom says 'Only perform if it goes from false to true' because it IS going from false to true constantly. Fills in, goes blank. Fills in, goes blank.

Any sneaky ways to set a related field to the current user AND be able to limit the related field options?

------------------------------
Mike Tamoush
------------------------------
  • You say:  I have a formula query on my Audit table that goes and finds the record ID in the Employee table, of the Current user


    Can you change the query to only return a record ID if the employee is an Auditor? 


    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
    • MikeTamoush's avatar
      MikeTamoush
      Qrew Commander
      Ahhh yes I am sure there is a way! That should do it thanks as always!

      ------------------------------
      Mike Tamoush
      ------------------------------