Sep 06, 2023 02:56 AM - edited Sep 06, 2023 04:13 AM
Dear community,
I have two tables, one called Supply and one called Demand, full of engineering information. There could be several Supply records that match the properties of Demand records.
I am looking to create a formula in the Demand table that returns the cell 'Specific Material' from the Supply table records, for each Demand record, via IF AND OR conditions.
I don't know whether this is possible, and below is my attempt referring to the Supply table via 'Supply.' then the Supply field name. The Demand fields referenced all show as purple, indicating that they are correctly identified, but the Supply fields are all still black.
The conditions are all AND except for Supply.X material colour, which could be a match to either Translucent Colour or X Material colour in the Demand table. One of these should match as the last AND condition being met.
Could anyone help me solve this via formula? I'd prefer a formula solution to a suggestion to use Lookup fields.
I had this working nicely in Excel.
Thank you in advance!
```python
IF(
AND(
{Supply.Highest Target viscosity} >= {Lowest Target viscosity}, {Supply.Lowest Target viscosity} <= {Highest Target viscosity}, {Market} = {Supply.Market}, {X material} = {Supply.X material},
OR(
{Supply.PCR material colour} = {Translucent Colour}, {Supply.X material colour} = {X material colour}
)), 'Supply.Specific Material'
)
```
Solved! Go to Solution.
Sep 06, 2023 03:38 AM - edited Sep 06, 2023 03:39 AM
Unfortunately, that is not possible in Airtable. Remember that Excel is more powerful than Airtable in many ways.
You might be able to create an automation that finds the right match for you, but there is no way to do that with formulas unless you create a bunch of lookup fields.
For advanced automations, you may need to use scripting or Make.
Sep 06, 2023 03:38 AM - edited Sep 06, 2023 03:39 AM
Unfortunately, that is not possible in Airtable. Remember that Excel is more powerful than Airtable in many ways.
You might be able to create an automation that finds the right match for you, but there is no way to do that with formulas unless you create a bunch of lookup fields.
For advanced automations, you may need to use scripting or Make.
Sep 06, 2023 04:10 AM
Thank you for your answer @ScottWorld
I was worried as much that it wouldn't be possible that way via formula. I will have to think about re-structuring with a bunch of lookup fields or an automated script solution.