Help

Re: Change a Lookup field to a Formula field?

Solved
Jump to Solution
987 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Tom_David
6 - Interface Innovator
6 - Interface Innovator

Winged Foot Condominium Assn dBase.

Base has 2 Tables named Maint Fees and Winged Foot Assn

  • MAINT FEES TABLE

  • Maint Fees table has 5 fields: Unit Name > [$] 2022 Maint Fees > [$] 2023 Maint Fees > [Linked] Winged Foot Units > [A] Notes

  • Maint Fees table has 6 records (Condos)

  • Maint Fees amounts can change every year for each of the 6 records

  •  

  • WINGED FOOT ASSN TABLE

  • Winged Foot Assn table has 29 fields - these 4 fields should be considered for a formula: [Primary Field] [A] Unit & Week > [CkBx] Maint Week > [CkBx] Assn Owned > [Lookup] 2023 Maint Fee > WF Unit

Is it possible to change the Lookup field 2023 Maint Fee to a Formula field?

If so, the formula needs to Lookup the amount from the Maint Fees table based on the WF Unit field and enter the amount from the Maint Fees Table field [$] 2023 Maint Fees OR IF [CkBx] Maint Week OR [CkBx] Assn Owned is checked, enter blank or zero.

  • An additional consideration for the formula can be to substitute the WF Unit field data with the 1st 4 characters (WF-A??) of the Primary Field. Unit & Week eamples: WF-A5, WF-B37, WF-D52, etc.

If this seems like a big mess I will need to invoke the 'Newbie' excuse, but I'm happy to work on it to learn more about 'how to' in AirTable. It's been an easy transition from my 'spreadsheet-only world' so far 😊.

 

1 Solution

Accepted Solutions
Stephen_Orr1
10 - Mercury
10 - Mercury

If the lookup depends on a condition that's not in the same table, either create a formula field in the source table to extract the necessary values and then look up the formula field in the destination table, or pull in the necessary fields from the source table to be used as conditions in the lookup field.

See Solution in Thread

3 Replies 3
Stephen_Orr1
10 - Mercury
10 - Mercury

Hi @Tom_David!

You can add conditions to your lookup field to grab a field only when certain conditions are met:

lookup.png

but you can't make a single field lookup multiple fields based on conditions. For that, I would pull in the necessary fields exactly as they are using lookups, then create formula field to reference the lookup(s) in.

I hope that helped!
-Stephen

Ok, I'm thinking...

So, question about this statement... For that, I would pull in the necessary fields exactly as they are using lookups, then create formula field to reference the lookup(s) in.

Do you mean the Maint Fees Table fields need to be pulled into the Winged Foot Assn Table?

Stephen_Orr1
10 - Mercury
10 - Mercury

If the lookup depends on a condition that's not in the same table, either create a formula field in the source table to extract the necessary values and then look up the formula field in the destination table, or pull in the necessary fields from the source table to be used as conditions in the lookup field.