Forms field formula, Concatenate syntax not working

asked on April 14, 2021

Can someone help me figure out what is making this calculation in the Advanced Tab on my form invalid? I can make the formula work to collect the parts into the hidden field, but NOT when I add the if/then statement. The IF/THEN instructions are basically saying: If the fields are all blank, then do NOT fill the hidden collection field with anything.

IF(OR(LEN(INDEX(Location_Area.CivicHse,ROW()))=0,LEN(INDEX(Location_Area.CivicSt,ROW()))=0,LEN(INDEX(Location_Area.StAve,ROW()))=0,LEN(INDEX(Location_Area.Hamlet,ROW()))=0),"#",CONCATENATE(INDEX(Location_Area.CivicHse,ROW())," ",(CONCATENATE(INDEX(Location_Area.CivicSt,ROW()))," ",(CONCATENATE(INDEX(Location_Area.StAve,ROW())),", ",(CONCATENATE(INDEX(Location_Area.Hamlet,ROW())),", AB"))

This is a town's civic address, collecting the parts in a table (to force them to give us the parts the way we want them) and then collecting the parts into a hidden field.

I can make the collection work... but I cannot make it work with an if/then statement where the statement reads: If the fields are all blank, then do NOT fill the hidden collection field with anything.

The variables:

(where Civic is the completed but hidden collection field)

The table looks like this:

0 0

SELECTED ANSWER

replied on April 14, 2021 • Show version history

I believe you are missing three closing parentheses at the end of the syntax.

Alternatively, you should be able to combine all the concatenation into one function, I.E.

IF(OR(LEN(INDEX(Location_Area.CivicHse,ROW()))=0,LEN(INDEX(Location_Area.CivicSt,ROW()))=0,LEN(INDEX(Location_Area.StAve,ROW()))=0,LEN(INDEX(Location_Area.Hamlet,ROW()))=0),"#",CONCATENATE(INDEX(Location_Area.CivicHse,ROW())," ", INDEX(Location_Area.CivicSt,ROW())," ",INDEX(Location_Area.StAve,ROW()),", ", INDEX(Location_Area.Hamlet,ROW()),", AB"))

0 0

replied on April 14, 2021 • Show version history

Awesome! Thanks, Brett! Your alternative seems to be working for me. Adding the three closing parentheses didn't, but when I copied your second option it did work! I'm doing more testing for now to see how it plays out in the long run, but thanks!

Do you know any tips to figuring these things out? Or good sites to go to for getting help to troubleshoot these? Prior to posting this Answers post, I did try adding closing parentheses, but maybe not enough (I think I tried adding up to two more).

1 0

replied on April 14, 2021 • Show version history

I use Atom, which is a text browser that has features such as showing which parentheses match up to each other. You can set it to use languages like SQL or Javascript, but I don't believe there is one for the excel-like formulas used in forms calculations.

https://atom.io/

In that case I just use the Formulas help page to see if I messed something up regarding which variables go where.

https://doc.laserfiche.com/laserfiche.documentation/english/docs/Default.htm#../Subsystems/ProcessAutomation/Content/Forms-Current/Formulas.htm

1 0

replied on April 14, 2021

So now I tried to translate your answer on the above calculation into this one and yet, although its not showing as Invalid, it is not stopping the zeros etc from showing up when the target fields are blank:

=IF(OR(LEN(INDEX(Location_Area.TWP1,ROW()))=0,LEN(INDEX(Location_Area.Rge1,ROW()))=0),"TWP ",CONCATENATE(INDEX(Location_Area.TWP1,ROW())," and Rge ", INDEX(Location_Area.Rge1,ROW())))

The whole purpose of this is to get all the "types" from the table to fill one other field that will be the column in reports so we can sort by location easier in future data searches. Currently, what is showing up in that field is:

which is a filledField via Javascript.

0 0

Question

Question

Forms field formula, Concatenate syntax not working

Answer

Replies

Sign in to reply to this post.