I’m building up a form submission URL from pieces of a table using a concatenate, but if one column is empty, the entire concatenate goes blank.
This could often happen with this particular case, so I would like the concatenate to skip over any columns that are blank…
Is there a different formula I should be using?
Thanks!
Not sure why that would happen (I’d imagine Concatenate would just skip those, never had issues with it), but what you can try is append .IfBlank("") to the parameters that may be blank and can cause the formula to fail.
Hi @Michael_Warren,
not currently in front of my computer, so I can’t try it myself: have you tried with the Format() function (I often prefer it over Concatenate() ) and see if it has the same behaviour?
Let us know.
Cheers
Hi @Michael_Warren,
I tried out with a very simple case and to be honest I’m not able to reproduce your issue:
Can you please share a doc with your use-case so that it’s easier to dig into it?
Cheers
Thank you @Paul_Danyliuk and @Federico_Stefanato for your help!
I also have not been able to recreate this in a test doc (!!!) — but I have figured out that the concatenate goes blank when a LOOKUP column is empty, with the following error in the concatenate column:
Pardon the last post — it looks like the issue is with the _DEREF_OPT error in the screenshot above, which is mentioned on this page:
_DEREF_OPT— “Dereferences the given input with the given reference”
When I have a LOOKUP column with nothing selected, the concatenate breaks and gives the above _DEREF_OPT error
anyone have an idea of what to do with blank LOOKUP cells?
I’ve been toying with adding toText(), ifBlank() and different things to my concatenate but no luck so far
apologies for this sloppy thread, I’m pulling my hair out trying to figure this out
Here is a test doc! So sorry for the wait.
In the LOOKUP CONCATENATE table at the bottom you will see that an empty cell in the Email LOOKUP column causes the CONCATENATE to break with the _DEREF_OPT error
Hi Michael, your investigations were on the right track, and your example doc shows what exactly the problem is.
It’s nothing to do with Concatenate itself, but just this bit: thisRow.Email.Email.
Since the thisRow.Email column is blank on the third row, that evaluates to <undefined>.Email, and surely it breaks.
Now one option to fix it is to test if thisRow.Email “exists”, returning "" otherwise:
Concatenate(URL.ID,Name.ID,thisRow.Name,Email.ID,If(thisRow.Email,thisRow.Email.Email,""))
But probably the most concise way (in this specific case that the property you need is also the display column) is to use ToText.
Concatenate(URL.ID,Name.ID,thisRow.Name,Email.ID,thisRow.Email.ToText())
Hi @Michael_Warren,
I think there is an issue with your formula: concatenate(URL.ID,Name.ID,thisRow.Name,Email.ID,thisRow.Email.Email)
if you just remove the last Email (why this?) :
concatenate(URL.ID,Name.ID,thisRow.Name,Email.ID,thisRow.Email) it works.
Let me know.
Cheers
Look, even simpler. 
@Federico_Stefanato thank you!
Sadly, I’d figured out a bit earlier what you just explained, and I’m still having trouble.
I’ve added another column to the example doc showing that when you want to reference an additional column from another table (thisRow.Email.[More Info]) other than the display column (thisRow.Email) it breaks the concatenate
In this particular use case I have a column that will not always have data, and if it is empty I can’t have it breaking the concatenate.
Any and all suggestions welcome!
As @Dalmo_Mendonca said, you get a broken formula when you try to dereference a column from an object that doesn’t exist (i.e. is blank).
So if thisRow.Email is blank, trying to get anything from it e.g. thisRow.Email.Something will result in a dereference error, and formulas dependent on that won’t work.
So best to wrap these things with conditional logic:
If(thisRow.Email.isNotBlank(), thisRow.Email.Something, "")
Hmmm.
I second this one from @Paul_Danyliuk
My post was quite superficial as I didn’t get the Email.Email (table-field).
Is similar cases, you can think of having Display Columns of referenced tables which are self consistent; i.e. an Email.EmailLabel which shows Email.ifBlank("").
This way you only reference tables with default - safe - columns.
There is plenty of counter-examples for this, so it is just a thought, not a rule.
Cheers
Actually @Michael_Warren just took a look at your doc, and the actual issue with your doc is confusing naming.
The lookup column from table People shouldn’t be named Email. It’s not an email, it’s a person record.
The display column of table People shouldn’t be an email as well. Or, it can be if it’s assigned so mindfully and not just so that People.Filter(...).ToText() returned an email somewhere. If I were making a doc with People table, the display column would either be a name, or some dedicated column where I’d concatenate the name and/or the ID and/or the email.
Thirdly, not sure why the separate table for the concatenated output though. It could go just fine on the People table.