How to I bring a candidate score from one table to anothe table in coda

I have a table called Candidate Decision Matrix and another table called Research Paper Evaluation matrix (images of sub tables attached) . The tables are linked by the name of the candidate. I want to bring the Overall Score on Research Ability for each candidate from the table Research Paper Evaluation Matrix to the Candidat


e Decision Matrix table. I have tried the formula ResearchPaperEvaluationMatrix.Filter.IThisRow.Name= ResearchPaperEvaluationMatrix.CandidateName).Overall Score on Research Ability but this yields not result, how can I fix this

Hey there @Jimmy_Psillos ! Welcome to the Coda Community!!

Coda has “chip cards” that help in formula writing. The chips have to identifying pieces of information for you:

  1. Color: tells you what table the information is coming from. Same color means same table.
  2. Type: at the end of the chip there is a little icon telling you whether the data toy are pulling is text, row, number, etc

Your filter formula is attempting to compare via = a text value and a list of rows. For that filter formula to work you will need to compare like values (text to text or Row row to row)

Send a copy of your doc and I can help out!

Or try something like:
ResearchPaper.filter(CandidateName.totext()=thisRow.name).overallscore.

While something like that should work, it will be a slower formula in the long run because of the use of the .totext() inside the filter.

Best practice would be to reference something from both tables that are already the same value-type.

1 Like

Hi @Jimmy_Psillos :blush: ! And Welcome to the Community :tada: :grin: !

The problem here, as said by @Scott_Collier-Weir , is that you’re trying to compare in your filter 2 different things : a row (which seems to be a reference coming from somewhere else) and a Text value, which will never work (but without having a sample doc to look at, it’s hard to give you a precise answer)

Could you try something like :

ResearchPaperEvaluationMatrix.Filter.(CandidateName.[ColumnContainingThe Name] = thisRow.Name).[Overall Score on Research Ability]

Note that this might not work as it depends on your actual setup

Hi Scott thanks for the quick feedback, am new to Coda and enjoying it and looking forward to learning more! This is my doc and would appreciate your help ! https://coda.io/d/Decision-Matrix-for-Chief-Economist-Shortlist_dK44bQuKlp_

Thanks Pch - appreciate the quick feedback , this is a copy of the doc, would appreciate your help ! https://coda.io/d/Copy-of-Decision-Matrix-for-Chief-Economist-Shortlist_dzZgacK1IO_

Hey there @Jimmy_Psillos !

Would love to help out but you have the permissions to the doc set as private - I requested permission which you can grant via email or you can set the sharing permissions of the doc to “Anyone who has link can edit”

Hi Scott apologies, I have set the doc to anyone with the link can edit. Let me know if that has come through OK on the document

Here you go @Jimmy_Psillos :blush: !

I’ve corrected your formula directly in the doc you shared :innocent: !

[Research Paper Evaluation Matrix].Filter(thisRow.Name = [Candidate Name].Name).[Overall Score on Research Ability]

1 Like

Hey @Pch thanks for the quick help!

@Jimmy_Psillos - Just also wanted to add that in the long run (especially if your documents are growing large) you will need to be thoughtful about each formula you write and optimizing them for speed.

How you could do this is on your Research Paper table create a column with the formula [candidate Name].Name, call it “Name” and then reference that column inside your filter formula like this:

* [Research Paper Evaluation Matrix].Filter(thisRow.Name = Name).[Overall Score on Research Ability]

That will prevent your filter formula from un-necessarily calculating values and keep it fast in the long run!

I added another column next to PCH’s with that formula on it as an example on your original doc! Hopefully that helps. Also, see this article about optimizing formulas, its super helpful! → Optimizing Formulas

1 Like

Thanks @Pch , much appreciated

1 Like

Thanks Scott for the quick help and the article on optimising formulas!