Referencing column data which is a pick-from-list value (solution)

Making Packs
1

Hi fellow Codas.

I don’t know if this has been mentioned before. I had a problem and I spent a fair number of hours searching the various libraries and forums without success to find an answer. I was on the verge of asking the community when I happened upon by chance, so thought I’d share.

  1. I have a table of Level 1 categories (Assessment Groups); 10 records
  2. I then have a table of Level 2 categories (Assessment Areas), for which there are several Assessment Areas within an Assessment Group. This table (50 records) has Assessment Group as a drop down single select
  3. I then have a table of Assessment Questions, where each question relates to one (and only one) Assessment Area via a drop down, and each question carries a potential score and an actual score. I have included Assessment Group in this table as a formula. Note that, as a result, the data in the Assessment Group column in this table is bordered with a rounded rectangle

I wanted to create a “Summary” table that pulls in the sum totals for each Assessment Group from the Assessment Questions table, but could not get the filter to work (using the thisRow.Assessment Group as a comparison to the Assessment Group in the Questions table). I could not for the life of me work out why it wouldn’t link. I tried all sorts and eventually, almost by accident, stumbled across the answer.

I summised that the reason it was failing to link was because it say the entry in my “Summary” table as pure text, whereas it say the entry in the “Questions” table as a referenced ID (albeit one displayed as text). However, by converting the value in the “Questions” table to text by using the Trim() function, Coda then treats each as text and compares them, thereby allowing me to sum up for each row in the Summary table.

There is actually a little more to what I am doing (actually summing up weighted answers to calculate a weighted percentage), but the logic of how to connect data across tables using Trim() for selected values is the important information here.

Hope this helps!

Geoff

2

Hello @Geoff_Porritt ,

Having access to your doc (or a data-insensitive copy of it) would be helpful to actually dig into the actual implementation and hopefully sort it out.

Thank you!