Skip to main content
Question

Dynamically select related record with Current User

  • April 11, 2019
  • 0 replies
  • 21 views
I have a table for Orders and a table for Employees. There is an Employee record for every User in my application and there is a unique constraint on the "User" field. An Employee can have multiple Orders. I need to know a way, without setting the "User" field in the Employee table as the primary key (I have too many relationships and applications that depend on the Record ID field to do that right now) to have the "Related Employee" field of the Orders table be automatically selected when the user creates a new record.
    This topic has been closed for replies.

    Ya. Been there, done it, bought the T-Shirt.  OK so you are too chicken to change that Key field.  It will probably work OK, but there is a Plan B.

    Make a Sync table of the employee table and bring across the userid and the the Record ID# of the Employees table.  Note that the user id will come across in text format and will appear as the email address or the user's "User Name"  if they entered one. Most users never find that menu.  Set the userid in text format field as the Key field.

    Then on the Orders table make a field called

    [Userid of Current User in text format] 

    with a formula of

    ToText(User())

    Use that in a relationship back to the Employee Sync table and look up the [Record ID#] of the Current Employee.

    Then have a form rule

    When Related Employee is blank

    change [Related Employee] to the value in the field [Record ID# lookup form Syn table]

    Set the checkbox at the very bottom to fire all the time (ie un check it)




    Terms & ConditionsAccessibility statement