Perform calculation between two different tables

Hello everyone,

I am new to Coda but not similar products. Currently I am trying to move our budget calculation from Excel/Google Sheets to Coda.

The issue is that I have two tables and I want to reference a value in table1 in table2 for calculation and then the outcome from table2 in table3 for further calculations.

In a nutshell, let’s say I have table1 with the below data:
Person1 = $20/hr
Person2 = $30/hr
Person3 = $50/hr

The second table I set up has a relation on the name:
Person1 = 5 hours
Person2 = 10 hours
Person3 = 1 hour

In a column in the second table I am trying to use this formula:

[Hourly Freelance Expenses].Filter(Name=thisRow).[Hourly rate]

I tried referencing the table and the row directly

[Hourly Freelance Expenses].[Hourly rate]

But that just gives me all the values in the Hourly Rate column rather than the specific row’s value. I tried using thisRow with it but it doesn’t seem to work.

Any ideas how I get the value of a specific row in Hourly Rate column so I can multiply it with the number of hours to get the total?

Thanks in advance.

PS: I did search around here and found a few similar things but every time I try the solution the function builder shows that the part of the function doesn’t exist in this context.

Hi @Mo_Sam,

Why do you have in both tables the same display name? (Person1, Person2, Person3)

You have to use relations to connect the related records and then using the dot annotation in CFL you can bring column data from that table.

I put together a fast example that might be what you need. I built two examples as each has fits different use cases.

See the doc below and let me know if it solves your issue.

First off thank you very much for making this example. I think I got lost in what you did, sorry, I am new to this.
But I can’t access the formulas when I press on “Open in Coda”. And the small view in post gets overshadowed by the message “Not saving, just playing”.

I thought I’d share what I have so you can see what I am trying to do:

All I am trying to do is to multiply Hourly Rate from “Hourly Freelance Expenses” by July Hours and the result would appear in Total in the “Table” table.

As for why I am duplicating the name column, I read somewhere that you need to have a relationship between two tables to be able to use their values in calculations.

Hi @Mo_Sam,

I forgot to say… welcome to Coda. Sorry if I got you scared, do not be as you will get to know very fast :slightly_smiling_face:

You can simply copy the doc (top right button).

Now about your goal, I tend to avoid having exact copies of my data if a specific need doesn’t exist for it.

I your case I don’t see why you would need to have two tables that have the same display value .

Please check your doc and the formula in the Total column. Since you already have a relation Nameyou can use the dot annotation to build a formula such as thisRow.Name.[Hourly rate].Sum() * thisRow.[July Hours] Please ignore the thisRow as it isn’t needed.

IMHO, your use case needs a different doc architecture as I understand that you try to bring a few linked sheets/workbooks into coda and have data segmented by user, pay rate, date (months, year etc).

If that is the case you can reach me out and we can have a look over Zoom and guide you so that your build is scalable, all for free.

1 Like

Thank you very much @Cristian_Nichifor . Your new formula fixed the issue immediately. What I am gleaning is, if I have a relationship between the two tables, I can reference the relationship (in this case Name) and use the . operator to access the other table’s properties. Although for the table I am in, I can just go after the property right away.

I appreciate the offer, really thank you. I will probably take you up on that at one point, the goal of this exercise is to get to know Coda more and understand how its language works. I used to be a developer and also used Notion so trying to wrap my head around how Coda works is taking a bit of time. It is amazing though, nonetheless.

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.