Compare linked fields and output the difference

[Sam_B]

Hello All,
I’m trying to build a checklist. I want to Compare all linked Field “Items Needed” with linked Field “Items Owned”; and then output difference between the two in Field “Missing Items”.

I tried the simple Min() formula for Items Needed - Items Owned = Missing Items, but I don’t the Airtable likes the Min() with linked records. Also, I converted the linked Fields with arrayjoin() and made two more fields, but again Min() does not work on that either. (Not to mention the array join option would run into issues with order of which the linked fields were created).

Is there a function I am missing? How do you guys compare and output the difference with linked fields or list of alpha-numeric/array data? Thanks.

[Xavier_G_Sabate]

Hi Sam,
If I understand correctly, you are substracting “Items needed” (a string pointing to a linked field) and “Items Owned” (another string pointing to a linked field).
If that is the case, it won’t work, you cannot substract strings even if you use arrayjoin() to make a unique long string with the descriptions.

What you can do is to evaluate the length of those arrayjoins and then, compare them using min() or whatever the arithmetic function. This will tell you if both fields are different in length, but of course you cannot know what are the real differences because of the ordering of the links when they were created.
Another option by using arrayjoin and count the number of colons created in both fields by that function, then compare those numbers. Again this is just approx since you don’t know exactly what fields are compared

I don’t think that this has an easy solution, if you want to compare item by item you cannot do it directly with a function, neither for linked or own fields.

I have a similar case in one of my bases, and I could solve it using a masking function (powers of 2 indicating existence or not of an item, quite convoluted), but that works only if you have a few number of different items

Another option would be creating an automation, fired every time the linked fields are modified, that uses a script task and then program a routine in javascript that can compare value by value. This can be the most powerful solution, however you will need to code.

[Sam_B]

Thanks Xavier for the response. Your understanding of what I need to accomplish is correct.

I considered the len() function as you mentioned, and reached the same conclusion - that it doesn’t provide the granular attention to each “Item Needed” vs “Missing Items”(Fields as string) as selected or it’s order, from the linked Table. Similar to the arrayjoin() function.

I was really hoping to have overlooked or missed a function in the Airtable toolbox because this seems like a simple enough function, but I think you are confirming my fears that it will not be that simple. I’m going to wait in hopes that there is someone with an elegant solution for this. More elegant than the mess I’m about to venture into.

In addition to considering your suggestion, my next thought / experiment exercise is leaning towards transposing the linked “Items Needed” in a new Table as individual Records, and then manipulating views. However, I hesitate to give this much thought because it would become overly convoluted for something that feels like it should be subtraction function on two linked Fields.

[OfficeOurs]

if(not(find(

arrayslice(linked_records_1, 1, 1),

linked_records_2),

arrayslice(linked_records_1, 1, 1)))

& if(not(find(

arrayslice(linked_records_1, 2, 2),

linked_records_2),

"," & arrayslice(linked_records_1, 2, 2)))

Repeat as many times as the most possible records in linked_records_1

[Andy_Lin1]

I'm very, very late to the party, but you could use REGEX_REPLACE, something like this for your "Missing Items" field. It'll be text though, and not record links.

REGEXREPLACE(ARRAYJOIN({Items Needed},", "),
ARRAYJOIN("(?:"&{Items Owned}&"(?:, )?)","|"),
"")