How to recognize current user as reference field from a related table

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
Hi,
I have a table containing specialists and their data (for example "1st language")
In another table, these specialists can create records (requests). When a specialist creates a request, I would like to have her/him automatically recognized (as well as their data) in order to be able to use their data in another related table that centralizes requests (with requests from other users).
To do so, I have related the specialists table to the specialists requests table, but I can't figure out how to automatically recognize the user. The "Specialist" field (reference proxy) is there as a drop-down, and when you select a name, their other data (like 1st language) is automatically recognized; but I would just like to avoid that the specialists have to select or type their own name in the "Specialist" field when they are the current user. However a form rule to set the Specialist field to be the current user doesn't work. Neither does when I try the same with the reference field (related specialist), it says there are no compatible fields when I want to change the reference field to the current user.
Thanks,
Yoël
Photo of Yoel Kuliasko

Yoel Kuliasko

  • 520 Points 500 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,702 Points 50k badge 2x thumb
The magic is to change the key field of the Specialist Table to be the userid field. Then everything will work beautifully and you will be able to default the specialist field being selected to be the current user.
Photo of Yoel Kuliasko

Yoel Kuliasko

  • 520 Points 500 badge 2x thumb
The Specialist table already is the master table in 2 other relationships.
From your experience, does QB manage change of a key field alright regarding these relationships?
Here's QB's warning message:

Changing the Key Field

When a table is already used as the master table in one or more Relationships, the linkage between detail and master records is through the Key Field in the master table. Changing the Key Field without changes to the details tables would break these linkages.

Quick Base will modify the details tables as follows, in order to maintain the correct linkages:

  • A new reference field will be created in the details table. The value of this new reference field will be set in each record in the details table to the appropriate new key value from the master table in order to maintain the pre-existing linkage. If a lookup field to the new Key Field already exists in the details table, it will be converted to a non-lookup field and used as the new reference field.

  • All fields in the existing Relationship will be migrated to use the new reference field.

  • The new reference field will replace the old one on all forms in the details table.

  • The old reference field will be converted to a lookup field.

Thx
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,702 Points 50k badge 2x thumb
Yes, QuickBase does do a great job in making a Key field change, despite the scary warnings.  What you have to do though after the change is to them go to the child tables and just check the date entry form and how the parent record is being selected to ensure that the data entry fields on the form are correct.
Photo of Yoel Kuliasko

Yoel Kuliasko

  • 520 Points 500 badge 2x thumb
Works great.
I just had to remove a bunch of placeholders in the users list (with one click), and in a related table one formula field (that made the specialist from user to text) I had to save the formula again to display the name instead of "invalid".
Other than that, I didn't run into any changes.
Thanks, Mark!