Help with a formula

[Philippe_Creve_]

Hello Everyone,

Is there a formula for multiple values I set to equal "Success"? ( put several values like "1234" which would be equal to "Success") please 

thank you in advance,

Best regards,

[Karlstens]

A switch statement might be what you’re after. 

https://support.airtable.com/docs/an-alternative-to-if-statements-using-switch

[Ben_Young1 · Accepted solution]

Hey @Philippe_Creve_! 

One way of accomplishing this would be to use the OR() function.
Here's an example:

IF(
    OR(
        {Numbers} = 1234,
        {Numbers} = 5021,
        {Numbers} = 5960
    ),
    "Success",
    "Wrong Number"
)

This formula will evaluate the Numbers field against the three statements defined inside the OR() function.
If any of those statements returns true, then the success value will be returned from the parent IF() function.
If you're only looking for a reasonable number of possible combinations, then all you have to do is write each statement within the OR() function to fit your needs.

Depending on just how many possible number combinations should return the "Success" string, there's a point where you'll need to work smarter and not harder by using other methods. Let me know if you find yourself having to type out a huge number of statements and I'd be happy to provide you another solution.

[Philippe_Creve_]

Thank you very much it's perfect ! 🙂 

Finally I try to make an automation:
If the "Success" value of Table A is obtained, a box is checked in Table B

if you are good with automations I will not say no for a small solution please

[Ben_Young1]

There's many ways to do this. I'll walk you through two ways to do this.

I couldn't really think of an example where I would use this type of design, so I'll just fill everything in with generic data.

I created two tables: Source Table and Target Table.

In line with the formula I shared previously, I created a new formula field that will return a success value if any of the following numbers are entered: 1234, 5029, and 1927.

IF(
  {Number},
  IF(
    OR(
      {Number} = 1234,
      {Number} = 5029,
      {Number} = 1927
    ),
    "✅ Success",
    "❌ Wrong Number"
  )
)

Now, I'll go ahead and create a linked record field so that we can relate our source records to their related target records.

We can now build our automation.
I'm going to have my automation trigger whenever the "✅" emoji is present in a record's formula field.
From here, I'm going to create a conditional action group. This action group will be set to run only if the record is already linked to a record in the Target Table.

We can specify this condition by targeting a property of the data held in the linked record field.
Specifically, we want to look at the length of the list of linked records. With is selected, we'll specify that we only want to run this action group if there's one record linked.

Next, we need to get the information about the record in the Target Table.
To do this, we'll use the Find records automation action. Specify the table to search, in our case we are searching the Target Table. We also want to search for our record based on a condition.
Next, we'll need to specify how we want to actually search for our record.

To do this, we can tell the automation to look at the Source Table linked record field. For each record, we want to look for a field that contains the name of the original Source Table record.

Now, when we test the step, it should return a the Target Table record that is linked to our Source Table record.

Sweet. Now that we've found our record, we'll create and configure our Update record automation action.
We'll specify that we're updating a record in the Target Table table. We now need to tell Airtable the record id of the record that we want to update.

To do this, we'll select the Airtable record ID option under the Make a new list of... section found in the Find records action step where we actually found the record.

There's something important to note here... you can only update a single found record. If you want to update multiple records, you will need to leverage the scripting action. Attempting to update multiple records found in the Find records step will cause an automation failure.

Finally, we'll pick our desired checkbox field and set the value to true. The final result will look something like this:

I'll go ahead and turn my automation on and see how it all runs!
As expected, the linked record is updated successfully!

That's the first method. I'm not the biggest fan of this method for a variety of reasons, but if it fits your requirements, then it definitely works. A recommended add-on would be to create another conditional action group that fires if the source record isn't linked to any records in the target table.

If there isn't a linked record, then the automation will create a new record in the target table, set the checkbox value, and then link the two records.

Here's another method I would recommend that doesn't rely on any automations.

For this method, you'll create a rollup field on the Target Table table that looks at the value of the linked record's formula field and utilize a formula that returns something based on the field value.

 

Here's the final result: 

Of course, this method is only really applicable to you if you don't actually need a field to update, but it's still worth mentioning.