Change a Lookup field to a Formula field?

[Tom_David]

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 😊.

[Stephen_Orr1]

Hi @Tom_David!

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

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

[Tom_David]

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 · Accepted solution]

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.