Skip to main content
Solved

Last OR argument is ignored

  • November 6, 2024
  • 6 replies
  • 61 views
A
Forum|alt.badge.img+3

I am trying to fix a formula for hours, but I can't find the error. For some reason, the last argument in an OR Formula is ignored.

The base has 2 tables (Table 1 and Table 2)

Table 1 has one record with the following values:

Field 1 (single line text) = A

Field 2 (single line text) = B

Field 3 (single line text) = C

 

Table 2 also has one record with a multi-select field. The values are

Field 1 = C, D, E, F

 

My OR formula should check if any of the 3 values from the record (Table 1) is included in the multi-select field (array) of the record in Table 2.

 

 

OR( FIND(Field 1 (Table 1), Field 1 (Table 2)), FIND(Field 2 (Table 1), Field 1 (Table 2)), FIND(Field 3 (Table 1), Field 1 (Table 2)) )

 

 

When I add "C" to Field 2 (Table 1), then it's correctly counted.

If I add C in Field 3 (Table 1) I don't get an error, and the formula just goes through. It just ignores the last argument, "FIND(Field 3 (Table 1), Field 1 (Table 2))".

 

Does anyone have an idea how to solve this?

Best answer by automator

I solved it. The REGEX lookup formula added a "space" before the lookup value, and therefore, the value in {field_3_table_1} was not equal to the value in {array_table_2}. Thanks to everyone who looked into this.

Learning: The main formula was correct, but the formulas that created the values that were delivered to the main formula were incorrect.

6 replies

Alexey_Gusev
Forum|alt.badge.img+25

Hi,
it's not clear at all. Do you have a sort of Table 2 lookup in first table? Formula cannot read data from other table.
If you have, for example 5 records in first table and 10 records in other table, How should it know which record  of Table2 related to a record in Table 1. I'm surprised you can even save a formula with {Field1 (Table 2)}. Please provide a screenshot.

TheTimeSavingCo
Forum|alt.badge.img+31

Not entirely sure what you're trying to do, but try this:

OR( AND( {Field 1}, FIND( {Field 1}, {Field 1 (from Table 2)} & "" ) ), AND( {Field 2}, FIND( {Field 2}, {Field 1 (from Table 2)} & "" ) ), AND( {Field 3}, FIND( {Field 3}, {Field 1 (from Table 2)} & "" ) ) )

 Link to base

Adam, Airtable Consultant - thetimesaving.company
kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • November 7, 2024

Screen shots please.

  • Including column headings that show the field types.
  • Include the exact formula with the actual field names that match the screen shot of the data
  • Include sample data that shows the issue

Also check for any hidden spaces in your data entry and select choices.

A
Forum|alt.badge.img+3
  • automator
  • Author
  • Inspiring
  • November 8, 2024

We do the lookup of each table through Make.com. Yes, the whole formula is more extensive, but I thought it was too much to ask anyone here to read through the whole thing. Please find below the whole formula:

 

 

AND( FIND('{{58.stage}}',{stage}), FIND('{{58.country[]}}',{countries}), NOT(FIND({contact_id},'{{58.contacts}}')), NOT(FIND('{{58.name}}',{names})), OR( FIND('{{58.field_1_table_1}}',{array_table_2}), FIND('{{58.field_2_table_1}}',{array_table_2}), FIND('{{58.field_3_table_1}}',{array_table_2}) ), NOT( OR( FIND('{{58.field_1_table_1}}',{array2_table_2}), FIND('{{58.field_2_table_1}}',{array2_table_2}), FIND('{{58.field_3_table_1}}',{array2_table_2}) ) ), FIND('{{58.bus_mod}}',{bus}) )

 

 

I do two OR lookups, and both of them ignore, for some reason, the last condition.

Below is a screenshot of the Make automation. The formula above is in Module 65 (second in the screenshot).

 

A
Forum|alt.badge.img+3
  • automator
  • Author
  • Inspiring
  • November 9, 2024

We do the lookup of each table through Make.com. Yes, the whole formula is more extensive, but I thought it was too much to ask anyone here to read through the whole thing. Please find below the whole formula:

 

 

AND( FIND('{{58.stage}}',{stage}), FIND('{{58.country[]}}',{countries}), NOT(FIND({contact_id},'{{58.contacts}}')), NOT(FIND('{{58.name}}',{names})), OR( FIND('{{58.field_1_table_1}}',{array_table_2}), FIND('{{58.field_2_table_1}}',{array_table_2}), FIND('{{58.field_3_table_1}}',{array_table_2}) ), NOT( OR( FIND('{{58.field_1_table_1}}',{array2_table_2}), FIND('{{58.field_2_table_1}}',{array2_table_2}), FIND('{{58.field_3_table_1}}',{array2_table_2}) ) ), FIND('{{58.bus_mod}}',{bus}) )

 

 

I do two OR lookups, and both of them ignore, for some reason, the last condition.

Below is a screenshot of the Make automation. The formula above is in Module 65 (second in the screenshot).

 


Update:

Adding to the confusion, I ran several tests and checked each condition. The formula works perfectly except when:

The correct value (e.g. C) is in

{field_3_table_1} (single line text)

and in the array {array_table_2} at the first position. If the value is at the second position in the array, then the formula works again.

I also checked different array lengths, and there is no correlation.

A
Forum|alt.badge.img+3
  • automator
  • Author
  • Inspiring
  • Answer
  • November 10, 2024

I solved it. The REGEX lookup formula added a "space" before the lookup value, and therefore, the value in {field_3_table_1} was not equal to the value in {array_table_2}. Thanks to everyone who looked into this.

Learning: The main formula was correct, but the formulas that created the values that were delivered to the main formula were incorrect.

Terms & ConditionsAccessibility statement