Connecting Data from Linked Tables

[Liz_Jeffries]

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.

[JonathanBowen]

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

[Liz_Jeffries]

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?

[JonathanBowen]

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

A “rates” table:

And a “clients” table:

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

[Liz_Jeffries]

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

[JonathanBowen]

Or, if you have this set-up:

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

[Liz_Jeffries]

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.