Add and subtract non-numeric (and non-date) fields

[A_R]

Hello. I need to add and subtract items that are not numbers nor dates. How can I do it?

To illustrate it, I have changing groups of people working on projects, so I have a first column for people originally assigned, then a second for people who left in the middle of the project and a third one for people assigned also in the middle:

[A] Original composition: “John” “Jack”
[B] Removed: “John”
[C] Added: “Jill”

(All above are linked to the ‘People’ table.)

I would like to have a [D] Current column where I can get “Jack” “Jill” as result of a formula, which should essentially work as D = A - B + C, i.e., D should contain items in either A or C but not B.

How can that be done, please?

[Justin_Barrett · Accepted solution]

The trickiest part of that is the removal of folks who leave. If only one person is removed, that can be done with a formula field using the SUBSTITUTE() function. More than one, though, and a formula can’t handle it because Airtable’s formula functions can’t iterate through an arbitrary collection of items.

Most likely you’ll need to enlist the help of a script. You could manually trigger one with a button, or try the new scripting action beta, which could automatically refresh the final list of participants as you make changes to the other fields. If you’d like help with the script, just holler!

[A_R]

I tried using SUBSTITUTE() and it seems to work fine actually. I will do some more testing to be sure but even when the initial group is A, B and C, then A and C get replaced by D and E, and F is also randomly added, the formula seems to work:

SUBSTITUTE(SUBSTITUTE({Original members},{Replaced members},{Added members}),{Final members},"")

This is the formula for a “Current members” field.

The “Final members” field lists all removed members at some point, either by leaving/being replaced at some point or when a project simply ends (they are all “removed” when it ends). Thus I can see who was at the end, and the “Current members” field shows me who is in it now.

This also allows me to filter current or past groups, by querying whether the “Current members” is empty or not.

So, as I said, so far so good, but I’ll keep testing to make sure we are no unaware of any pitfall ahead.

The only drawback is that “Original members”, “Replaced members”, “Added members” and “Final members” all contain separated/individual shaded items, but the “Current members” contains text-only result of the SUBSTITUTE() formula. Instead of this:

John Jack Jill

it contains plain text like this:

John Jack Jill

I would love to have that first formatting preserved, so I’ll mark @Jason so that the team acknowledges the suggestion.

[Justin_Barrett]

A.R:

I tried using SUBSTITUTE() and it seems to work fine actually. I will do some more testing to be sure but even when the initial group is A, B and C, then A and C get replaced by D and E, and F is also randomly added, the formula seems to work:

SUBSTITUTE(SUBSTITUTE({Original members},{Replaced members},{Added members}),{Final members},"")

That may work for simple cases, but I doubt that’s going to work for everything. Here’s why…

The SUBSTITUTE() function looks for the exact text of the second element ({Replaced members} in your example), and replaces it with the third element. It doesn’t recognize that multiple links means multiple items. The link field sends the formula a single string containing a comma-separated representation of all the links it contains.

If the people in {Replaced members} aren’t in the same order as they are in {Original members}, and also adjacent to each other in the selection order, they won’t be pulled out. Going back to your example, If you start with links to A, B, C, in that order, what will be sent to the innermost SUBSTITUE() function is the string “A, B, C”. The string coming from the {Replaced members} field will be “A, C”, and that string doesn’t exist in “A, B, C”, so nothing will be substituted.

A.R:

The only drawback is that “Original members”, “Replaced members”, “Added members” and “Final members” all contain separated/individual shaded items, but the “Current members” contains text-only result of the SUBSTITUTE() formula.

That’s because those other fields are link fields. Once you pull data from a link field into a formula, you’re no longer working with links. You’re working with the text labels of those links. There’s no way to “fix” the formula output and re-add that formatting. Formulas only process and output text, numbers, dates, and boolean values.

If you want to end up with a field that contains actual links, and which accurately represents the removal and addition of people based on your other link fields, you’ll need to use a script.

[A_R]

You are right. When I properly changed the order, the SUBSTITUTE() formula didn’t work. So here I go learn how to script. Any tips are welcome…

[A_R]

Oh! But there could be a way. Please help me here:

1. Is there a formula that simply removes a string from another, and a similar one that simply adds a string?
2. Is there a formula that allows just “selecting” the 1st, or 2nd, or 3rd… item of another field?

If yes for all questions above, then I could build a multilevel nested formula where I tell it to remove the 1st item in {Removed members} from {Original members}, then remove the 2nd one, and so on, and then add all items in {Added members}.

Can this manual approach be done somehow? (I might need a few IF formulas between them all, but it still worth the shot.) BTW, we never have more than 3 people getting replaced in any group, so I don’t need to build a formula for coping with anything like 10 possible member changes (fortunately).

[Justin_Barrett]

The SUBSTITUTE() function (not formula; the formula is the entire thing you’re writing in a single field, and most formulas contain one or more functions, with each function performing a specific operation) is what replaces one thing with another. As I said above, the problem comes when finding what to replace.

Going back to the ABC example, say that A and C drop out. All we can get via formulas is the literal string “A, C”. Airtable doesn’t currently have any functions in its formula system that allow you to break that string into a collection of items (i.e. an array), and no way to iterate through a collection and perform the same operation on each one. If it did, we’d already be talking about it. :slightly_smiling_face: Trust me, this is beyond Airtable’s current formula operations. A script is the only way to get this done.

If learning scripting feels too daunting, message me and I’ll see what I can do to help. I love writing scripts to solve problems. :slightly_smiling_face:

[A_R]

@Jeremy_Oglesby, does New Formula Field Functions contain anything like what we talked about in here yet?

In short: a way to perform basic operations with items instead of numbers of text? E.g., taking a set of 3 elements like {A, B, C} and adding D and subtracting B to get {A, C, D} as result (as separate items, not as a single concatenated text or anything like that).

[Justin_Barrett]

Those functions that @Jeremy_Oglesby mentioned are functions he asked the Airtable team to add. They don’t exist in Airtable yet.

[A_R]

I know. I am suggesting precisely that: that what we discussed above is considered, so that we have formulas that perform operations with the existing elements in given set.