Show record ID in table A if a checkbox is checked for that record in table B

[Stacey_Cummings]

I love Airtable! My goal is to use it for genealogy research which can be complicated, so I started with something fairly easy to learn different ways of doing things. My first base is a Pet Meds and Vet visits. Below is what I’ve tried, but I’m stuck on #3. If there’s a better way to do what I’m trying to accomplish, let me know! I’ve used SQL before, but it’s been a long time ago.

1 - I have a Prescription table which lists all the meds each of my pets has gotten (vaccinations and meds they take daily (Active meds)). I have a checkbox column for the Active meds in the Prescription table.

#2 - Then I have a Pets table with 5 pets. I linked the prescription table to the Pets table. Now, the Pets table shows all the meds a specific pet has ever been given in the Prescription column. I added a function column #15, which is pulling all the meds, but I want to limit it to Active meds, based on the check box in the Prescription table.

#3 - I don’t know the formula to only list the meds that are checked in the prescription table; this is what I have so far.

Thank you,
Stacey

[Zollie]

This isn’t something a formula can do well. You’d be better off using the Scripting Block. You can re-post with a ‘work offered’ tag if you need someone to write the code for you.

Scripting Block

This is a new Airtable feature that runs a block of code when a user clicks a button. Because it only runs when you click a button, you could either use it to go lookup a value for you, then print it to a window. Or you could have it directly change the cells in the base. Scripting blocks are more difficult to use than formulas, but they’re also a bit more powerful. If formulas are tweets or a haiku, scripting blocks are more like short stories - they’re less constrained and allow coders to describe a great deal more.

Why Formulas Aren’t Ideal Here

The big weakness of Formulas is it’s inability to split up comma separated data for long lists. In an ideal world you’d do something like what’s outlined below, but a formula would stumble on the last part as lists grows beyond a couple items.

---

Pets (table)

Prescriptions (linked record)
[Zimectrin Gold] [Bute] [Potomac Horse Fever]

Active Medication (lookup)
[checked] [unchecked] [checked]

Active Medication (formula)
true, false, true

Active Prescriptions (formula) <- this one is the problem
Zimectrin Gold, Potomac Horse Fever

---

The trouble comes in trying to split up the comma separated true/false values and match them up with prescription names. Airtable formulas don’t currently have a great vehicle to express something like “give me the 4th item in the list.”

[Stacey_Cummings]

Zollie,
Thank you! I figured out what I needed to do based on your first reply about look-up, before adding the script info. I used look-up to bring in the Active Meds column into the Pets Table.

First I realized it did just what I asked, but no real value with only displaying the check box.

Then I went to the Prescription Table and added an IF statement in a new column, to return the Name field if the Active meds column is Not Blank (has a check mark)

Then back to the PETS table and did a look-up on my Formula column, came out perfect! Now I have a column that will just show the daily meds in my Main PETs Table. I’m going to create a new view in my Pets table to just list the name of the Pets and what meds they get every day:

Considering trying to get the doses in there, but that’s for another day!

Thank you again,
Stacey

[Stacey_Cummings]

Well I couldn’t help but try on the doses!

My formula to include the dose and instructions fields
IF({Daily Meds} = BLANK(), BLANK(), CONCATENATE(Name,": “&Dosage,” “& UOM,”, “& Route,”, “& Frequency,”, “& Duration,”. “& Instructions,” "& Condition))