The problem with the original formula was that when you turn a lookup field into a string with & "", the resulting string usually does not have commas in it. Thus, it seemed like the original formula was removing commas, but not encoding the spaces, because there were no commas to begin with. The spaces were not encoded because there were no commas and spaces to for the SUBSTITUTE to find.
This issue with lookups seeming to have commas results from the fact that lookups are arrays, and when they are displayed as arrays, they are formatted with commas, but when they are converted to strings, there are no commas. Super confusing? Yes. Likely to change? No. Just use rollups instead whenever possible.
Here’s what’s so bizarre & inconsistent about Airtable’s handling of this:
When you convert a lookup field that looks up a Collaborator field into a string, Airtable retains the commas & you can substitute the commas. :man_shrugging: :man_facepalming:
I haven’t tested this with all field types, but I’m assuming that this weird inconsistency might possibly exist with other field types as well.
@Justin_Barrett might have already documented this strangeness in his Field Reference Database, although I can’t find the link to his thread about his base at the moment.
Yep, seems like rollups are the safest way to go here.
p.s. For any new users who are creating rollup fields for the first time, there is a small bug with rollup fields too… you can’t have any fields in your base called “Values” before creating a rollup field in that table.
First of all, I have to give you a shout out for the Prefill Forms app. Brilliant and mega useful.
Scott’s Array formula is good enough. I placed hyphens and the demarcation is fine now, since I don’t see a way for Prefill Forms to render these hyphens as spaces (%20) in the target field.
You can see it in this example (click on the Inquire link).
The form allows a lead to inquire about the services of a workforce member available for hire. The form submits to an Inquiries table, which is, at the moment, a primitive CRM.
Here’s the URL for the base: http://bit.ly/airtablefieldreference
And here’s what that URL expands into:
I wasn’t able to test multiple collaborators in lookup fields as thoroughly as I’d hoped because I never work in bases where multiple people are collaborating, and I haven’t found a way to “fake” multiple collaborators to run a test (though I’ve never really looked that hard).
One way to have test having multiple collaborators is to create a new Airtable account for yourself using a different email address. Then, add your other account as.a read-only collaborator so that you won’t be charged.
I thought about that, but I don’t like having accounts that are only used for testing and nothing more. I’m probably in the minority there, but that’s just how my brain prefers it.
