Forum Discussion

JENNIFERPASCHAL's avatar
JENNIFERPASCHAL
Qrew Trainee
2 years ago

Look up on Record Owner

Hello -

I have two tables - Projects and Employees. Employees holds employee information like our internal employee ID, email, name, department, etc. I have several relationships built between the tables to allow users to lookup an employee and pull in related information to that employee (One Employee can have many Projects).

I have been asked to create report that pulls in employee information on the Record Owner in projects table. I am not sure how to accomplish this given no one would be "looking up" the value but instead I need to somehow pass the data from the Record Owner field to the reference field in the relationship.

I've created a formula field that returns the internal ID from the Record Owner user field. What is the best way to pass that value to the reference field in the relationship to then lookup related information like department?

------------------------------
Thanks in advance,
Jennifer Paschal
------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    2 years ago
    One choice would be to mark the userid field as being unique. Make a Connected sink table of your employee table but after you make the sink table, set the key for you have to be the user ID field.

    Then you can easily make that the parent of your child records based off n the Record Owner, and look up any fields you need, like dept.  

    That should work fine unless an employee user ID changes, for example, perhaps someone gets married or unmarried and has a name change.  If you set the Sync table to add but don't delete records, then you can simply delete all the records and sync again.

    A completely different approach is just simply use a pipeline that will trigger when a record is being added to then then search the table of employees looking for a match on the user ID and then update the department that way. The only problem is if the employee changes their department, then the records would not automatically be updated and you would have to build another pipeline to detect that situation.


    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------