Create Linked Assignments from Multi-Select Field

Question

We are creating an app that is intended to track various types of presentations given by our employees. Each presentation can have multiple presenters and multiple reviewers, but also the same person may present/review multiple other presentations. It's a classic many-to-many relationship scenario, but I am not sure the usual intermediate join table is the correct approach for our current challenge.

We have an Employee List table and a Presentations table. For any given presentation, we would like to have a multi-select field fed from the Employee List to select all applicable presenters, and a similar multi-select field for reviewers. Each person should then have the presentation "assigned" to them and be visible on the Employee List record, preferably as a child table or similar report link. On the Presentations table, we'd then like to have each name link to the applicable Employee record as if it's the reference field, but with multiple names in the same field. I know I could create a Rich Text field to parse out the names and create the links, but this does not assign the presentation to the employee.

Is there a straightforward way to accomplish this, or do we need to abandon the multi-select approach and go with a conventional many-to-many join table? We'd like to keep the multi-select if possible as it is much quicker to enter information. Also note the listed Employees are not necessarily QB users.
------------------------------Oana Whalen------------------------------

ChayceDuncan · Answer

For what you're looking for you'll need to find a way to either 1) convert over to a many to many relationship and use the table structure to support what you're doing or 2) keep what you're doing and using Pipelines or some other method to convert the multi select over to a many to many.

What you're trying to do and any potential updates you want to do for reporting and tracking will need the relationship to join the employee to a presentation no matter how you slice it.

If you keep it pretty simple - I would suggest replacing the multi select with an embedded grid-edit report. So if you're on a presentation record for example - you can create the intermediary join table and have an embedded grid edit where the only column is 'Employee' and you pick their name in a dropdown. You can replicate that for Reviewers. Or if you're on an employee record an embedded grid edit of presentations.

If you REALLY want to keep the traditional multi-select, you could evaluate using a Pipeline and loop through the list of employees that are included the the multiselect and create a new record in your intermediary table for each employee. This allows you to keep the UI of the multi select but gain the benefit of the proper structure. I would caution here though that if you add/remove people you need to be checking to see if that person already exists or should be removed so this gets hairy quickly, hence the recommendation for the embedded grid edit above.

In your comment you mention a rich-text to parse out the names and make links which is doable - but that link is purely going to be navigation and anytime you need to do something different such as set up a report, make a notification/reminder or anything specific to one employee you'll hit a wall which is why I wouldn't recommend it.

------------------------------
Chayce Duncan
------------------------------

Sign up

Login to the community