Skip to main content

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?

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.

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

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.

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

 

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.

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.

Reply


Terms & Conditions