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.&nbsp;

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.&nbsp;

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.&nbsp;
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.&nbsp;
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.&nbsp;
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.&nbsp;
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.&nbsp;------------------------------Chayce Duncan------------------------------

prashantmaheshw · Answer

Luckily what you want want is precisely what's been achieved in post below. Don Larson was even kind enough to make it more meaningful .

https://community.quickbase.com/discussion/video-become-multi-select-hero-by-automating-multi-to-multi-relationships

PS : I hate multi select field for precisely the same reasons.------------------------------Prashant Maheshwari------------------------------

oanawhalen · Answer

Thanks, Chayce. I hadn't considered making the reports directly editable through grid edit, but that is a good thought and would serve the purpose here, if not the cleanest looking. Was hoping there might be a cleaner solution that I wasn't seeing but I had many of the same thoughts you mention about the challenges with each of the different options. Good points on the need for additional maintenance/checks if we do pursue the multi-select approach.

------------------------------
Oana Whalen
------------------------------

chayceduncan · Answer

If you're comfortable with playing with your own primary keys, you could try the multi select route but when you create the records you make the primary key something like [Employee Email] - [Related Presentation]. You can then add an 'Applicable' flag that you pull the lookup of the multi-select and bring that down to use a Contains function to see if that persons name still appears in the list of employees presenting. This alleviates having to worry about only creating certain people or deleting them because then you can just filter out the ones that aren't applicable.

I tend to agree the user experience of the multi select is typically better for the everyday user. The above is still the more complicated route than inline grid edit but an option nonetheless.

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

I tend to agree the user experience of the multi select is typically better for the everyday user. The above is still the more complicated route than inline grid edit but an option nonetheless.&nbsp;------------------------------Chayce Duncan------------------------------

oanawhalen · Answer

Prashant, this is a brilliant solution! It may be a little more complex of a setup than we want to implement for this application, but will definitely use it to resolve some similar needs. Thank you for sharing!

One question - is there a way to delete selected options with this approach while you are still adding to the list? Is this the reason for the editable text box? I know you could delete the records created via the Pipeline after saving, but wondering if there's a way to remove items before saving the record.------------------------------Oana Whalen------------------------------

Forum Discussion

Create Linked Assignments from Multi-Select Field

6 Replies