Using Coalesce Function to handle null value in Power Automate – Dataverse


Recently in one of our flows, we were getting the below error –

InvalidTemplate. Unable to process template language expressions in action ‘List_rows_:_Region’ inputs at line ‘0’ and column ‘0’: ‘The template language function ‘replace’ expects its first parameter ‘string’ to be a string. The provided value is of type ‘Null’. Please see https://aka.ms/logicexpressions#replace for usage details.’.

One or more fields provided is of type ‘Null’, a different type is expected.

This was because the replace function that we were using for the Filter rows property of List Rows (Dataverse) had the variable value as null.

Below was our dynamics expression where we replaced apostrophe with 2 single quotes – https://nishantrana.me/2023/02/27/how-to-handle-single-quote-apostrophe-in-filter-rows-in-power-automate-dataverse/

replace(replace(variables(‘varFieldData’)?[‘myVariable’],‘_’,’ ‘),””,”””)

The solution was to use coalesce function, to replace null with a blank string as shown below.

replace(replace(coalesce(variables(‘varFieldData’)?[‘myVariable’],”),‘_’,’ ‘),””,”””)

Similarly, we can use coalesce function to provide a default value.

The coalesce function will evaluate its arguments in order and will return the first non-blank or non-empty value. If all the arguments are blank or empty strings, the function will return blank.

After using the coalesce function, our flow was successful.

More on COALESCE

A similar example in SQL –

Hope it helps..

Advertisements
Advertisement

Binding Choice / OptionSet (multiselect) with Combo box in Canvas Apps / Power Apps (DataVerse)


Instead of hardcoding Combo box , we can bind it to the Choice / Option Set field of Dynamics 365 / Dataverse.

Format – Choices(DataSource[@columnname])

Check out the post that explains it – https://debajmecrm.com/how-to-bind-a-multiselect-choice-of-dataverse-to-a-combobox-in-canvas-apps/

Also check out – how to bind and do filter on multiselect combo box – https://youtu.be/5dSk5iOgT68?t=286

To learn about all the properties of Combo box refer – https://www.spguides.com/powerapps-combobox-control/

Hope it helps..

Advertisements
%d bloggers like this: