Referencing a Checkbox field in another table

Showcase
1

I have Doc with several tables of Student information. Each student is identified with a unique “Student Number”. The table “Student Master” has all the key information about a student. In another table, I would like to pull in some of the Student Master data, including a field called “SPED” which is a checkbox that indicates whether or not a student receives special education services. I have added a formula as follows:

Filter([Student Master],[Student Number]=thisRow.[Student Number]).SPED

The formula returns “true” or “false” depending on the Checkbox status of the referenced field, but when I try to display the field as an actual Checkbox, I get an error message stating “Cannot convert value to the specified format.” How can I get this to display as a Checkbox properly?

Thanks!

2

Hey David!

Im a public high school teacher (SPED actually) and have used Coda extensively both in my own work and for my district. I’ve built tools that have saved out district 100’s of hours across employees and teams (including an entire IEP feedback automation system - never spend time collecting feedback again!)

I see you are an assistant principal - If you need any further help with ideas/solutions reach out and let me know! I have extensive use using Coda in conjunction with things like Aeries, Canvas, Powerschool, gmail and more.

For this specific problem - any chance you can share a dummy doc (with fake student names) Would be much easier to help in that way!

1 Like
3

I suspect the issue is the good old “list-of-one-item vs one-item” confusion. Try appending a .First() onto the end of your formula.

Filter([Student Master],[Student Number]=thisRow.[Student Number]).SPED.First()

The reason this works is because your Filter formula actually returns a list of rows, and the .SPED on that will of course return a list of true/false values. The fact that the list only contains one value is besides the point. So when you take the First() of that list, it will give you the one and only item in the list, which is normal true or false value that can be displayed as a checkmark.

5 Likes
4

That did it, Ryan. Thank you for the solution AND the explanation!

1 Like
5

Hi Scott! This particular problem is now solved (see Ryan’s response) but I would LOVE to pick your brain about something I’ve been struggling with. I’ll send you a PM (if I can figure out how!) with details. Thank you!

-David

6

Thank you @Ryan_Martens2 for this answer–just saved me a ton of time today!