IS there a way to create a relationship using a list user field as the reference field in a child table?

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
Setup:

Crew Members (parent) can have many Schedule Entries (child)

Currently, each schedule entry can be related to one and only one Crew Member.  If there are 5 different crew members working the same job, I would have to create 5 separate schedule entries (one for each crew member) repeating work details in all of them.  

Instead, I want to be able to select the 5 crew members from a list user field in a single schedule entries record.

Note: I need to maintain the relationship because there are some look-up fields I'm using from the Crew Members table as well (such as phone number, pay rate, etc.).

Is there a way to accomplish this?

Thanks in advance.
Photo of Juan

Juan

  • 466 Points 250 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,478 Points 20k badge 2x thumb
Short Answer: Not really

Medium Answer:  You could have those child records duplicate / copied and then just change the Crew Member.

Medium Answer 2: YOu could make the child table for work entries 'editable' on the form, and have it only have the dropdown, and the rest of the values are lookups from the project or the crew member.  i.e. The work details would actually be up a level, and just sent down for reporting purposes.

Long Solution / Complicated:
Use the Multi-select field on the parent level, and then run a script that creates child records for each of the users, and relates them appropriately.
Photo of Juan

Juan

  • 466 Points 250 badge 2x thumb
Thank you. I had already implemented your medium answer, but it still cumbersome and less than ideal.  For various reasons none of the other suggested approaches would work but I appreciate the feedback. :)