Formula needed to iterate through an array

[Brian_Schuster]

I wrote a formula to find and replace two text strings, and luckily it worked.
REPLACE(REPLACE(Description,FIND("<p>",Description),LEN("<p>"),""),FIND("</p>",REPLACE(Description,FIND("<p>",Description),LEN("<p>"),"")),4,"")

But I can imagine a situation where I would want to replace more unique strings within another field without using an excessively long formula. Each unique text string makes this formula substantially more complicated.

Is there a possibility of adding iterations to the formula field using a built-in array (multi-select field) or custom array such as [1,2,3,7,13] as the input? This would mimic Javascript’s “for” function.

[Justin_Barrett]

First off, your formula could be greatly simplified by using the SUBSTITUTE() function instead of REPLACE(). The former doesn’t need to know the position of the thing it’s replacing because it searches for it on its own, and replaces all iterations regardless of position. Your function above becomes this:

SUBSTITUTE(SUBSTITUTE(Description, "<p>", ""), "</p>", "")

In some cases, it wouldn’t be that cumbersome to nest a few SUBSTITUTE() functions to replace various things; in others, as you noted, it can be quite a chore. The other day I actually wrote a formula to help me build a rather lengthy series of over 50 nested SUBSTITUTE() functions. :slightly_smiling_face:

There have been requests in the past to add some type of iteration support to Airtable’s formulas (search for “iterate formula” to find them), and I agree that this would be a great addition. For more complex processing in the meantime, you might consider playing with the scripting action beta, which would let you use JavaScript to modify the data in your base when certain triggers occur.

[Brian_Schuster]

The scripting block sounds cool but I haven’t been able to use it effectively. I think it’s too far outside my reach at the moment to be useful for me on an everyday level unless I really focus on it. What I like about Excel and Airtable formulas is that they’re intuitive, so it doesn’t take as much re-entry time to get back into writing with them.

I am also trying to figure out how to use filterByFormula to filter by multiple fields through the API. I tried
AND(({field1}=“My field1 query”),({field2}=“My field2 query”))
but it hasn’t been working quite right.

I noticed that AND() evaluates for “true” and a basic comparison evaluates to 0 or 1.

[Justin_Barrett]

The scripting action beta isn’t the same as the scripting block. They run on nearly the same API, but the scripting action beta runs in the background, acting on triggers that you set up (either a new record being added, or a record entering a specific view), and you write these scripts directly in the action environment, not in a block. I’ve found it to be incredibly helpful for big and small stuff. If you’d like some help with a script, just holler and one of us who has the coding bug will come running to your assistance. :slightly_smiling_face:

Unfortunately, I haven’t used the Airtable API yet, but looking at the basic formula syntax, I notice that you’re wrapping extra parentheses around the individual comparisons, which isn’t necessary. Try this instead:

AND({field1}="My field1 query"),{field2}="My field2 query")

[Brian_Schuster]

Justin_Barrett:

AND({field1}="My field1 query"),{field2}="My field2 query")

I tried it that way, and MANY others. No luck with any combination so far. Could there be an error with the API accepting multiple filterByFormula queries?

[Justin_Barrett]

I don’t think so. After reviewing the API docs, I should probably ask this question: are you URL-encoding the formula before passing it through? If not, that’s likely the problem. It shouldn’t look exactly as laid out above. In the actual API call, the variation I wrote above should look like this:

AND%28%7Bfield1%7D%3D%22My%20field1%20query%22%29%2C%7Bfield2%7D%3D%22My%20field2%20query%22%29