Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Connecting Data from Linked Tables

Topic Labels: Formulas
776 6
cancel
Showing results for 
Search instead for 
Did you mean: 

I want to calculate a value from one table multiplying it with a record value from another table. Can this be done please?

Specifically, a number of consultations provided in one table, multiplied by a rate given in a different table.

6 Replies 6

Hi @Liz_Jeffries - you’ll have to make some connection between the two tables to do this. Maybe each consultation is linked to a rate record in the other table? Can you share your base structure as it might be easier to advise if we can see what set-up you have already?

JB

Thanks for your reply. I have created a table which is the rates table. I have also created a clients table. I have created a link between the clients table and the rates table. The link shows up in the rates table with the client being linked to the rates field. However, when I try to create a formula between a field in the clients table in which I enter the number of sessions attended and the rate in the rates table I get an error in the formula field. It is a simple formula {field A}*{field b}. I am not sure what else has to be done. Can you clarify?

Hi @Liz_Jeffries - I’m not sure if I’ve got your base structure right, but here’s my mock-up:

A “rates” table:

Screenshot 2019-07-03 at 18.19.11.png

And a “clients” table:

Screenshot 2019-07-03 at 18.19.18.png

On the “clients” table, the Rate is a linked field (to the “rates” table), the Rate Price is a lookup on the “rates” table. The # of sessions is a number you enter and the total value is a formula:

{Rate Price} * {# of sessions}

Does this work for you?

JB

Thanks @JonathanBowen I am going to explore this. It may be the Lookup that I don’t understand or haven’t got to grips with yet (just starting out today with Airtable). Everything else seems as I have it setup. Thanks for your help.

Liz

Or, if you have this set-up:

Screenshot 2019-07-03 at 18.24.08.png

where the link field is the value, then the formula needs to get the numeric value out of the linked field, so the formula would be:

VALUE({Rate}) * {# of sessions}

VALUE() converts the text string to a number (and the linked field is a string - even though it looks like a number)

JB

Thanks again - I have managed to get it to work by adding in both a rate and rate price and having a link and a look up in the clients table to the rates table - I hope I have explained that correctly. I thought that I only needed to create a link to the rates table and it would pick up the rate value. Anyway it works now! Clearly this will be a bit of steep learning curve for me but I have taken the first step! Thanks again.