Concatenated formula bringing in duplicate entries. Is there a clever way to solve for this?

[BrandonNW]

Hey guys!

Long time - no posting....

I have a good one here dealing with calculated fields and a concatenated formula. The field is a simple concatenated formula to give us one answer from several different fields. Easy, right? What is happening is, the field is populating correctly if we only use it once. If we have to make an update or change to a selection that sits within that concat formula, then it displays both selections rather than replacing the old selection. I've tried some IF(ISBLANK) and some REPLACE statements, but with no success. Even if I clear out all of the previous selections, save it, and then open it back up, the original calc stays. Anybody else run into this one before?

CONCAT({field A}, {field B}, {field C})

[Doug_Den_Hoed__AtAppStore]

Hi Brandon,

I can offer a couple of thoughts for your consideration on this one:

• Add a checkbox called "Calcs Active" (default true, single value) to the custom form, and modify your formula slightly so that you can "reset" it by toggling Active "off" and then "on": IF({DE:Calcs Active}=true,CONCAT({field A},{field B},{field C},"")
• Suspecting that the CONCAT is confused on when and whether to fire, this heavy-handed approach will force a fresh recalc every time by tricking the formula to be inspected due to the presence of $$NOW...noting that both halves of the IF statement then have the same formula: IF($$NOW>=$$TODAY,CONCAT({field A},{field B},{field C},""),CONCAT({field A},{field B},{field C},""))

Regards,

Doug

[BrandonNW · Accepted solution]

Appreciate the suggestions Doug. I tried the second option with the $$NOW qualifier, but that failed as well. I finally found the root cause yesterday, and it was so simple. The calc was retaining the old selections b/c they were technically never cleared. There is some logic here that I failed to disclose. Field 1 drives the options for Field A, B, C etc... We were changing the selection in Field 1, which then hid the Field A or B selection, but it technically never went away. It was just hidden. The fix here was to remove the first selections one by one and save the form. Then, go back in and make the updated selections. This way there is only one selection showing rather than all previous selections. I hope that makes sense. SO SIMPLE... I cannot believe I missed it. This was bugging me for weeks. 😆

[Doug_Den_Hoed__AtAppStore]

Glad you got it Brandon,

Alternatively — as you could verify this in your preview environment if it is in the same state as prod was yesterday — another simple technique I use in such cases is to:

• edit the custom form
• remove the formula on the parameter
• check the “update existing values” option
• save the form
• wait a minute to confirm existing data is null
• reinstate the correct formula
• re-save the form
• confirm data is now correct

Regards,

Doug

[BrandonNW]

Ahh yes... I like the trickery! There is really only a few of these that need updated every couple of weeks, so one by one is not too bad. But, if I ever have more than 10+, I will definitely use this approach for a bulk update. Thanks again!