Concatenated formula bringing in duplicate entries. Is there a clever way to solve for this? | Community
Skip to main content
BrandonNW
BrandonNW
Level 6
June 10, 2020
Solved

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

  • June 10, 2020
  • 1 reply
  • 706 views

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})

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by BrandonNW

    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. 😆

    1 reply

    Doug_Den_Hoed_AtAppStore
    Community Advisor
    Doug_Den_Hoed_AtAppStoreCommunity Advisor
    Community Advisor
    June 11, 2020

    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
      BrandonNWAuthorAccepted solution
      Level 6
      June 11, 2020

      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
      Community Advisor
      Doug_Den_Hoed_AtAppStoreCommunity Advisor
      Community Advisor
      June 11, 2020

      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

        Terms & ConditionsAccessibility statement

        Loading footer...