We have two tables - one for assets and another a check out log
To get the current location with we have two formulas for calculated text
Check out log
if(isblank({Checked In Date}), {LOCATION}, "")
Assets
if(sum({Checkout Log}.{Asset (Checkout)}.{Currently Checked Out})>"0",childconcatenate({Checkout Log}.{Asset (Checkout)}.{CURRENT LOCATION} , ","),{HOME LOCATION})
They work fine except a comma is appearing the box before the location. How do we get rid?
Ceri MacLure shared this idea
Comments
2 comments
Hello Ceri,
It appears that the childconcatenate function is also concatenating a blank value(if there is one). This means that we need to adjust the Assets formula slightly in order to account for this possibility. Below is a formula that should accomplish this. Essentially this checks to see if the first character is a comma. If it is, it will be removed.
if(mid(childconcatenate({text calcs}.{Link to Sheet1}.{Snip from Notes}, ","), 1, 1)=",", mid(childconcatenate({text calcs}.{Link to Sheet1}.{Snip from Notes}, ","), 2, 9999999), childconcatenate({text calcs}.{Link to Sheet1}.{Snip from Notes}, ",")
Hope this helps!
Derrick
Hi Derrick
I'm taking over from Ceri on this Track Via application and have tried your fix. When saving the table I get this error message:
The field 'CURRENT LOCATION' did not pass validation
Parse error near char 249 " In specified formula 'if(mid(childconcatenate({text calcs}.{Link to Sheet1}.{Snip from Notes}, ","), 1, 1)=",", mid(childconcatenate({text calcs}.{Link to Sheet1}.{Snip from Notes}, ","), 2, 9999999), childconcatenate({text calcs}.{Link to Sheet1}.{Snip from Notes}, ",")'. Expected ',' or ')'.
Please sign in to leave a comment.