Limit Multi-select picks to one in linked table

  • 0
  • 1
  • Question
  • Updated 2 months ago
  • In Progress

I have a table with employees and their details, including a multi select for their function(s).

In another table I have a roster where each person can be assigned a roster shift. As some employees have multiple functions assigned to them, I want to be able to specify when they are put on a shift what function they are doing (out of the ones previously assigned to that employee from the multi-select in the employee table).

At this stage if they have multiple functions selected in the employee table they all show in the roster shift record.

Thanks in advance
Harry

Photo of Harry Wright

Harry Wright

  • 70 Points

Posted 4 months ago

  • 0
  • 1
There is no native capability to conditionally filter a multi-select type field. You could do this if the functions were child records.

The only other option would be to add the same multi-select field to the roster allowing you to specify which functions are needed for that shift. Then set up a way to 'check' the functions on the roster against the employee's functions and prevent the record from being saved if there is a mismatch.
Photo of QuickBase Girl

QuickBase Girl

  • 338 Points 250 badge 2x thumb
I keep reading that there is no 'native' capability to filter a multi-select field, so what is the non-native option. Is there one? I'm ready to go rogue. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
@QuickBase Girl
Can you describe you use case.
Photo of QuickBase Girl

QuickBase Girl

  • 338 Points 250 badge 2x thumb
Pretty simple. 

I have Lot #'s (a lot of them). I have hundreds of inventory items (flavor powders) and each one of the flavor powders have many lot numbers. 

Example: Ingredient Inventory of Sherbet Flavor Powder of 60kg. That inventory may have 3 Lot #'s -
(12kg) #123
(30kg) #456
(18kg) #789

If I choose Sherbet Flavor Powder in my ingredient list (for a recipe), and I need 35kg, I will be using 2 lot numbers.

I would like only the Sherbet Flavor Powder Lot #'s show in the multi-select. 
Curious to hear what the pros recommend for this one. My first thought would be, as I think QB Junkie alluded to above, that you'd rather be operating with the lot #s in their own table than as a multi-select field on the ingredients table. Assuming I'm understanding your setup correctly. That way when you go to pick a lot number for a recipe, rather than using a multi select of a million potentially unrelated lot numbers, you use a record picker to the lot number table that's filtered by related ingredient.

If your ingredient/lot number relationship is many to many (ie. a lot number can be associated up to more than one ingredient, as opposed to lot numbers being strictly child records to the records on the ingredients table), it gets a little stickier, but I think you'd still start with a lot number table, do the standard many-to-many technique to a joined ingredient/lot number table and then work from there.
Photo of QuickBase Girl

QuickBase Girl

  • 338 Points 250 badge 2x thumb
It is set up properly for the a basic dropdown to work and show only the related lot numbers. However, it gets complicated as they run several batches, and each batch can have several ingredients, and many lots per ingredient. I would have way too many single drop downs. If I could just have a multi-select for each ingredient, it would solve all my problems.
Photo of Justin Torrence

Justin Torrence

  • 110 Points 100 badge 2x thumb
You could use the Image Onload Technique to hide or disable options through Javascript. That would be a non "Native" option. Problem is that using Javascript on the page is not supported by QuickBase, so they were to change something on the front end, there is a chance it would break your Javascript.
Photo of QuickBase Girl

QuickBase Girl

  • 234 Points 100 badge 2x thumb
Tell me more of this magic...
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
There is probably a native solution but u have wold need to see your app to try to sort it out. Contact me at QuickBaseCoach.com if you like.
Photo of QuickBase Girl

QuickBase Girl

  • 338 Points 250 badge 2x thumb
I sure have been trying to figure it out. Not sure why we don't have conditional multi-select as an option. :(