Here is a link to a dummy page I created to try and solve this problem(s): Sticky Problem
Okay, I’m going to try and make this sound as easy as possible. I’m looking for a solution for the following problem(s). I’ll list these out in Levels.
I have one data set of Protocols which lists out different prescriptions I may use, and within that data set it will list out the different Medications I will be using in that specific protocol. The Medications are pulled from a table, and there is the possibility of multiple Medications in a given protocol (so multi-select is used). If I want to order a particular Prescription, I will use a toggle. When that toggle is triggered, it will list the Medications in a new column (so I have a data set to pull from).
Level 1
Is there a way to remove duplicates from the Lookup column? This way when I am adding new Medications to a protocol I won’t see duplicates?
Level 2
Next, what I am wanting to do is to have another table that will pull in all prescribed medication from the Protocols list (skipping blanks and removing duplicates), but I would like each row to just have one value associated with, NOT the multiselect like I have shown in table Attempt 1, where I used this formula: Prescription.[Medication to Rx].Unique().Filter(CurrentValue!=“”).
Level 3
If we are able to accomplish Level 2, then I would like to be able to add new information into a column directly beside Medication Orders (in table Attempt 1). I would like this to be a select list where I am choosing from the list of possible Medication Sig from the Medication table, but only showing me the options that correspond to the medication that is in Medication Orders. Meaning, if Medication Orders lists Medication 3, I only want to see the options that are associated with Medication 3 (as opposed to all options). Currently in Attempt 1 all I can see is the Display Column (Medications), but I’m wanting the select list to be the adjacent column (Medication Sig)
Well, it’s always better to see the sample doc for tailored advice. But let me just imagine what you have.
Level 1: as easy as doing .Unique() on the collection. If Unique() doesn’t work for you, chances are you’re taking multiple Protocols, reading multi-selects of Medications from those protocols, end up with a list of lists, and don’t flatten it with ListCombine(). Hence the solution would be .ListCombine().Unique() on your column. To skip blanks, make it .ListCombine().Filter(CurrentValue.IsNotBlank()).Unique()
Level 2: You can generate such table with a button that will iterate first over the rows and then within rows over each medication:
Protocols.FormulaMap(CurrentValue.WithName(CurrentProtocol,
CurrentProtocol.Medications.FormulaMap(CurrentValue.WithName(CurrentMedication,
If(
{there's no such row yet},
MyTable.AddRow(
Protocol, CurrentProtocol,
Medication, CurrentMedication,
{other columns}
),
_Noop()
)
))
))
Level 3: If you want to filter a lookup list based on some criteria, you can set up an item filter in the column’s settings. You can set that filter to a custom formula to only include items that are listed for thisRow’s Medication entry or something. The filter supports full formula language so you can be pretty flexible with that.
If you need any help with that, I’m happy to do a gig — PM me.
P.S. Sorry, didn’t see the sample link. Looking now.
@Paul_Danyliuk Thank you for your response! Sorry for putting the link at the bottom of the text, I moved it to the top and will make sure to do that in the future!
So, for level 1, I tried adding a custom filter onto the multi select column Medication in the Prescription table, but the drop down is still giving me the duplicates. Did I do something incorrect? I’ve attached screenshots.
Level 2
I’m relatively new for formula map and more complicated formulas such as that, so I’ll have to spend some more time learning that. I got great at excel over the years but need to continue to learn the Coda language better.
Out of curiosity, what would I be putting in for {there’s no such row yet}?
Level 3
I guess the first issue I’m having with this is how do I have the select list dropdown be something other than the ‘display column’ in the reference table?
If you (or anyone else) has Coda videos to send me to in order for me to learn better that would also be appreciated!
