Skip to main content

Lookups, Rollups, and Formulas producing comma after unique values

M
Forum|alt.badge.img+5

Hello,

  1. I'm pulling in purchase order data for my company, and have a "Vendor Management Team Partner" associated with each purchase order record.
  2. I'm also reconfiguring these POs in a separate table, such that the email address of the PO requester is the primary key.
  3. If a requester has multiple POs, they'll have multiple values in the VM Team Partner column, so I'm also adding a Rollup for unique values.
  4. I'm then automating the copying and pasting of the unique values in the Rollup field into a Linked field for the VM Team Partner's email info.
  5. However, in only some of records, the Link field will display a comma at the end of the values, whereas the Rollup field will not.

How is this happening when there is no comma in the Lookup field? I now have to add values in my "VM Email" table for both "John Smith" and "John Smith," which is an unexpected nuisance.

Can someone help me understand how this is happening and how to remedy?

5 replies

TheTimeSavingCo
Forum|alt.badge.img+28

Hm, I attempted to replicate this but everything seemed to work as expected and there was no extra comma


Could you provide a screenshot of your automation that pastes the unique values in the Rollup field into the Linked field's "Update Record" step where the data that's being updated into the Linked field is visible?

Adam, Consultant @ thetimesaving.company
M
Forum|alt.badge.img+5
  • mtardiff
  • Author
  • New Participant
  • 2 replies
  • March 10, 2024
TheTimeSavingCo wrote:

Hm, I attempted to replicate this but everything seemed to work as expected and there was no extra comma


Could you provide a screenshot of your automation that pastes the unique values in the Rollup field into the Linked field's "Update Record" step where the data that's being updated into the Linked field is visible?


It does work on probably 95% of my records, as you have in your test screenshot, but I'm not sure what's wrong with the other 5%.

The Rollup field formula is the simple ARRAYUNIQUE(values).

The Update Record automation step is what you'd likely expect it to be:

This procedure of copying and pasting values from one column to another via automation is something I'm well-acquainted with at this point in my Airtable journey, but this is might be the first time I'm updating a Link field with the contents of a Rollup field. I haven't seen this behavior when trying to copy any other field type into a Link field.

 

 

TheTimeSavingCo
Forum|alt.badge.img+28
mtardiff wrote:

It does work on probably 95% of my records, as you have in your test screenshot, but I'm not sure what's wrong with the other 5%.

The Rollup field formula is the simple ARRAYUNIQUE(values).

The Update Record automation step is what you'd likely expect it to be:

This procedure of copying and pasting values from one column to another via automation is something I'm well-acquainted with at this point in my Airtable journey, but this is might be the first time I'm updating a Link field with the contents of a Rollup field. I haven't seen this behavior when trying to copy any other field type into a Link field.

 

 


That's so interesting and I'd love to poke around your base to see what was happening!  If you could duplicate your base and leave only the records with the problems and DM me an invite that'd be great

No worries if that's not possible and if you ever figure out why this his happening I'd really like to know why as well!

 
Adam, Consultant @ thetimesaving.company
M
Forum|alt.badge.img+5
  • mtardiff
  • Author
  • New Participant
  • 2 replies
  • March 11, 2024
TheTimeSavingCo wrote:

That's so interesting and I'd love to poke around your base to see what was happening!  If you could duplicate your base and leave only the records with the problems and DM me an invite that'd be great

No worries if that's not possible and if you ever figure out why this his happening I'd really like to know why as well!

 

Unfortunately I'm unable to duplicate the workspace/base due to data privacy requirements, but what I can at least say is that the Rollup/Link fields are the only ones that think a comma exists (and the Link field is the only one that displays a comma).

Some other things I've noticed:

  • ARRAYUNIQUE(values) won't display the comma at the end of the name, but it will store it in the text
  • ARRAYJOIN(ARRAYUNIQUE(values),", ") WILL display the comma at the end of the name, but it won't append an unnecessary comma for other values (i.e. "John Smith, Jane Doe")
  • RIGHT({VM Team Emails})="," returns 0/false for the records with a comma stored at the end of the string, and RIGHT({VM Team Emails}) produces NOTHING in that column (as opposed to a comma, or the last letter of the last name as I'd expect)
 
Truly bizarre.
TheTimeSavingCo
1 person likes this
  • TheTimeSavingCo
    TheTimeSavingCo
A
Forum|alt.badge.img+17
  • Andy_Lin1
  • Inspiring
  • 183 replies
  • March 13, 2024

I can't duplicate the error:
https://airtable.com/appvR6hY4ZgTSWVVS/shriIIqar0FGP7sb1

However, you can correct it by using using the following formula in your Rollup field:

REGEX_REPLACE(ARRAYUNIQUE(values)&"", ",$", "")

&"" converts the array to a string, ",$" looks for a comma at the end of the string, and "" replaces it with nothing.

Alternatively, you can try the ARRAYCOMPACT function, which will remove empty/null values in the rollup:

ARRAYCOMPACT(ARRAYUNIQUE(values))

This behaviour has been around for a while and I'm not sure if it was identified as bug, nor what exactly triggers it. I believe it was related to empty data, but the forum search function is failing me at the moment.

TheTimeSavingCo
1 person likes this
  • TheTimeSavingCo
    TheTimeSavingCo

Reply


Most helpful members this week

Terms & Conditions