Automatically populate field with value from other table

[Manuel_Kammerma]

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

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?)

[Kevin_Conklin]

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:

Airtable

Airtable: Organize anything you can imagine

Airtable works like a spreadsheet but gives you the power of a database to organize anything. Sign up for free.

[Kevin_Conklin]

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.

[Eamon_OConnor]

I’m trying to almost exactly this and struggling. I have a Zapier zap creates a new Airtable record in Table A any time there is a form submission on my Squarespace site. In Table B I use the timestamp of each record from Table A to populate a bunch of lookup fields.

So I’m trying to make a zap that makes a new record in Table B and populates the link field with the timestamp of the most recent record from Table A (i.e. the one that is created with the other zap). I don’t want to tie this zap to an event from Squarespace because I’m concerned that if there is a lag then the timestamps won’t matchup and my lookup field in Table B won’t find a matching record in Table A.

I’m getting Airtable to create a new record in Table B, but the link field is not getting populated (with anything).

Any thoughts?

[Zachary_King]

late addition to this, but this is exactly what I want to do!

how can i automate the population of lookup fields in a second table?

I have a master table, then a child table which has lookup fields.

when a new record is created in the child table, how can i get the lookup fields automatically populated? If i use zapier just to copy the record from the master table, then it’s less than ideal as those records are no longer linked, just a copy of a point in time.

did you manage to figure this out @eamon??