Conditional values matching a text field selection to multi-select options
Hello! I've been working with formula fields to concatenate multiple field values in connected tables to limit dropdown options for users picking a related record. I'll try to write out the example, then put the problem I'm running into below.
Table 1: Customers (Child) | Table 2: Inspections (Parent)
Customers have a date (date) and a type of inspection (multiple choice).
Inspections have a date (date), types of inspections that could be scheduled (multi select text - same options as Customer types of inspections) . Each inspection also has a scheduling status (multiple choice - open, scheduled, capped)
Customers concat field:
[Customer date] &
"Open") &
If(Contains([Inspection Type],"3-day"),"3-day",
Contains([Type], "2-day"),"2-day",
[Type])
Inspections concat field:
[Inspection Date]&
[Scheduling Status]&
If(
Contains(ToText([Inspection Type(s)]),"3-day"),"3-day",
Contains(ToText([Inspection Type(s)]),"2-day"),"2-day",
ToText([Inspection Type(s)]))
Each inspection starts as an open slot on a given day and has some selection of Inspection types that can be accepted for that Inspection record. Each customer can only have one inspection. I am trying to limit the dropdown when choosing the parent inspection in the Customer form where 1. the dates match and 2. the Customer inspection type is LISTED in the multi select type field of the Inspection record. There is also a third criteria, that status of the Inspection record is "open", but this is only a field in Inspections, so the concatenated formula in Customers just has the static text "open". I started to group some of the inspection types in the formula because some of them have the same naming conventions, but this is impossible for all scenarios.
I can't wrap my head around how it would be possible to match based on Customers: [Inspection Type] being contained within Inspections: [Inspection Type(s)] (with the two other exact match fields) when the conditional values settings in Report Links seem to require the fields to be equal in order to use them as a filter...
Appreciate anyone willing to read all the way through and any insights or suggestions!!! Thank you.