Automatically populate field with value from other table


#1

Hey there, I just started using Airtable today and want to go ballistic :smiley:

I have two tables

Availability with a customer, number field (w), number field (max)
[customer: “username”; kw: 6, max: 42]

Tasks with several fields, but specific also customer, number field (kw), number field (value),
[customer: “username”; kw: 6; value: 4]

My Goal:

I want to populate with a complex lookup like - ((<Tasks.value>/<Availability.max>)*100) WHERE (<Availability.customer> = <Tasks.customer> AND <Availability.w> = <Tasks.kw>)

So basically, I want to see how much percent of max Availability for a specific customer is a single Task taking up.

Any Idea how to solve this? (Which plan is needed?)


#2

Good question. Based on my understanding after using Airtable for two days, I would be looking for a way to use a formula field to link to the other table. But I don’t see a way to do that.

I created a sample base to replicate what you are doing and it only worked if I created a formula field concatenating customer&kw, then changing that field to the link field. (then using a lookup in the tasks table to get max visible in there, then another formula field with the percentage calc).

But, changing the original formula field to the link field changes the entries to the linked text. So with any new entries, you need to manually select in that field. Not ideal.

My sample is here:


#3

I think you could do this by using Zapier to copy (upon creation of a new record) the contents of a formula field (concatenation of customer & kw) to a field that is used as a match field. Then have a lookup field bring in the max number, and then you can easily make a formula field do the calculation you are looking for. It’s a little clunky needing to use Zapier, but should work.