PowerFx and weird data = problems

[codes4pizza]

This might be an issue with either BulkDataUpdater, XrmTokensRunner or a limitation in PowerFx itself. We were using BDU to perform a data fix and because there were many thousands of records that needed an update of some sort we found that running multiple BDU's (one for each field that needed updating) was wasteful and time consuming

So we used a query that found all records that needed to be updated, and each field had a calculated PowerFx expression that would return the current field value or the data-fixed value we wanted. This was added with the OC flag. The calculated field expression for each field was a similar pattern and looked something like this

<PowerFx|If (Lower("{address1_postalcode|<value>}") = "x", Blank(), Lower("{address1_postalcode|<value>}") = "xyz", Blank(), "{address1_postalcode|<value>}")>

The idea being that if the field contained "x" or "xyz" then we would null it; otherwise it would be unchanged.

This worked well for field with typical data. However it falls over when the field contain values such as '"()

Values like the above cause an error

The field data is being parsed as part of the PowerFx expression and that just feels totally wrong.

[rappen]

Hi, sorry late response...

I have not reproduce this issue, but I assume there is some missing in my code.
It is trying to handle these logics in plain strings, handling quotes in strings may be hard...

I will see if I can solve it!

[codes4pizza]

Hi Jonas, I have a simple repro here that you can use.

Create a new contact in your D365 system and give it a firstname of '"()
[thats a single quote, a double quote and an open and close parenthesis]
give it a lastname of some random string you can use to find the new contact in a FetchXml query - for example xyzabc

Start bulk updater tool and set fetchxml to

<fetch>
  <entity name="contact">
    <attribute name="firstname" />
    <attribute name="lastname" />
    <filter>
      <condition attribute="lastname" operator="eq" value="xyzabc" />
    </filter>
  </entity>
</fetch>

In BDU, use the Update tab to define an update to firstname field using Calculate option and set the expression as the PowerFx expression below with the only when change is needed option.

<PowerFx|If (Lower("{firstname|<value>}") = "x", Blank(), Lower("{firstname|<value>}") = "xyz", Blank(), "{firstname|<value>}")>

When you run the Update on the contact you just created you will get the error in BDU that I originally posted.

Hope this helps.

[rappen]

Excellent details for reproducing the issue! 😊

(But why is he called '"() - sounds a bit like Mr Bobby Tables... 😉)

[codes4pizza]

Consequence of the client pen-testing their website and it feeding data to the integrations. I guess Bobby Tables got a job! 🤣 Sent from Android