MS Power Automate adding a toggle to not include unique number indentifiers in JSON

  • Profile Image
    HEATFREIGHT
    Asked on January 04, 2022 at 01:26 PM
    Parker, yes that's correct.

    If this toggle option were possible, the new functionality would enable robust connections to Power Automate despite removal of items near the beginning of the form or reordering of the fields. The current option is more user friendly for form creation, but completely unreliable once a user begins automating things in Power Automate and they are forced to reorganize or change a complicated form.

    Since there is a case to be made for either situation, a toggle is nice compromise.
  • Profile Image
    Parker
    Answered on January 04, 2022 at 01:33 PM

    I have made the feature request to the developers for you. If anything comes of this, you'll be notified here.

  • Profile Image
    ecologsweden
    Answered on March 03, 2022 at 03:09 AM

    One vote from me too to get this feature - it's absolutely needed for the integration with PA!

  • Profile Image
    Maruel_T
    Answered on March 03, 2022 at 03:16 AM

    Hi there,

    My colleague already escalated a feature request to our developers. We dont have an ETA for you on this request to be implemented. But, we can notify you once we have news from them.

    Let us know if you have further inquiries.

    Thank You.

  • Profile Image
    HEATFREIGHT
    Answered on March 03, 2022 at 09:24 AM

    @ecologsweden you can use a Parse JSON action and some special string logic to remove the "## - " prefix from the key names of the Jotform trigger input fields.

    I don't have time at the moment to find the exact tutorial I used to make this happen, and it's a lot of non-obvious, tedious string operations and substring functions, and it's not particularly high performance (especially for large JotForms with many fields) but it's been working for me for about a year now. It probably adds up to a minute or so to the runtime of a flow with around 40 to 60 fields from a JotForm.

    Later I can provide screenshots and code from my workaround. The workaround allows you to create robust connections to Power Automate flows which don't break when the Jotform fields are rearranged or if fields are removed.

    Basically, instead of using direct dynamic property references to your Jotform fields, you just run a loop of string logic on a string of the JSON body from Jotform, and this string logic simply strips away the JSON punctuation, then removes the first 5 characters, as in "## - ", then uses AddProperty() function to rebuild the JSON. Then you can use Parse JSON to obtain the new keys, or you can use something like 

    outputs('FixedJotFormJSONString')?['KeyName']

    to reference the known Jotform fields.

    This way, as long as your Power Automate flow can expect the same keys (once the prefixes have been stripped), then the flow will always work even if the order (and thus the prefix) of the Jotform inputs has changed.

    The one major drawback is it won't work well if your Jotform has multiple fields sharing the exact same key name (display name). Jotform does not use the "unique name" of the keys when it sends the data to Power Automate. All it sends are the (display) key names prepended with some number like "## - " and the values themselves, but the unique field names aren't used. So, although Jotform itself may be able to deal with multiple fields sharing the exact same key name, your Power Automate flow will throw errors or have unexpected results if you have multiple keys named exactly the same (because stripping the number prefix will make them literally the same), and a JSON object can't have multiple properties of the same name.

    I had thankfully just a few fields like this. Fields with the key "Date" caused problems for me, as well as like "SalesRep" or "Email". But my use case was such that I could just keep the first property of a particular key name and then drop any additional properties sharing the same key name. If your use case has multiple fields sharing the exact same key name, you will have to do something special, like dropping those parameters or adding your own unique prefix or suffix to make all the keys in the resulting JSON unique.

  • Profile Image
    ecologsweden
    Answered on March 14, 2022 at 08:35 AM

    @heatfreight Many thanks for taking the time to explain! I understand the concept but would be very helped to see more details of your solution to get around this!

    Thanks again,
    Mats

  • Profile Image
    HEATFREIGHT
    Answered on March 14, 2022 at 08:55 AM

    See how far this gets you:
    1647262540_622f3b4c5c769_TrimJSON.png

  • Profile Image
    HEATFREIGHT
    Answered on March 14, 2022 at 08:59 AM

    The code hidden in the dynamic content of these flow actions is copied to the note at the top of each action. Let me know if you can't read it. I'm including hopefully more legible images of the actions inside the "Apply to each".1647262706_622f3bf2f090e_TrimJSON2.png

    1647262748_622f3c1ced326_TrimJSON3.png

  • Profile Image
    HEATFREIGHT
    Answered on March 14, 2022 at 09:07 AM

    And here are those same formulas copied in full, because I'm still not certain the images are legible.

    After that "Apply to each" loop finishes, you simply run a "Parse JSON" action on the "JSON" object you just created, and that will give you the same output as the Jotform flow trigger but without the "## - " prefix. Then you can select parameters like @{outputs('ParseJSON')?['CustomerName']} rather than having @{triggerOutputs()?['body']?['04 - CustomerName']} change to @{triggerOutputs()?['body']?['05 - CustomerName']} because you added a field before the CustomerName field on your form, causing every dynamic content reference for the fields below that to fail in your flow!


    varJsonStringBody

    @{triggerOutputs()?['body']}


    varJsonString (initialize a string variable)

    String

    @{outputs('varJsonStringBody')}


    JSON (initialize an object variable)

    Object

    {}


    trimJSON

    @{trim(replace(replace(variables('varJsonString'), '{', ''), '}', ''))}


    outputJSON

    @{split(outputs('trimJSON'), '",')}



    Apply to each prepare JSON

    @{outputs('outputJSON')}


    Condition remove colon space

    @{items('Apply_to_each_prepare_JSON')} contains @{string(': ')}


    Condition valid JSON string

    OR

    @{outputs('itemsApplyToEachPrepareJSON')} contains @{string(',')} 

    @{outputs('itemsApplyToEachPrepareJSON')} @{string('"')}


    RemoveQuotes

    @{trim(replace(substring(outputs('itemsApplyToEachPrepareJSON'), 0, indexOf(outputs('itemsApplyToEachPrepareJSON'), ':')), '"', ''))}


    SubstringRemovePeriod

    @{replace(outputs('RemoveQuotes'), '.', '')}


    RemoveQuotesAgain

    @{trim(replace(substring(outputs('itemsApplyToEachPrepareJSON'), add(indexOf(outputs('itemsApplyToEachPrepareJSON'), ':'), 1)), '"', ''))}


    addProperty

    @{addProperty(variables('JSON'), outputs('SubstringRemovePeriod')?['body'], outputs('RemoveQuotesAgain'))}


    set JSON

    @{outputs('addProperty')}