Tip #1398: Set calculated field to null

Calculated fields columns are great for quick simple formulas. They are fast, instant, and always up to date. But what if you need to clear the value of the column as part of the calculation?

Customer has sites, sites have assets in various categories (hardware, software, etc). The other day I’ve had a requirement to show the total value for an asset category as $0.00 if the site had the assets but wasn’t live yet and to show — if site didn’t have assets in a specific category. Serving as a visual clue. Great idea and calculated field with a condition on status should do the job. Except that sometimes I need to set it to null.

Create a dummy column Null Decimal. Not searchable, optional, no decimal places, never visible, always null.

Now we can use this column to set the value of a calculated decimal or a currency column.

Cover image by Gerd Altmann from Pixabay

Tip #1397: Reduce extra Power Automate runs and overcome trigger limitations

You know I’m a sucker for some open source goodness. This time the goodness is from Aiden “President” Kaskela. (Got something to share? Send your wares to jar@crmtipoftheday.com).

The Common Data Service (Current Environment) trigger in Power Apps allows you to run your cloud flow when certain fields are updated, but there are two shortcomings:

  1. Your flow can trigger on multiple fields and you don’t know which one changed.
  2. In the Dataverse, fields may register as a change even if the value doesn’t (typically caused by updates through code where it’s including the current field value instead of just the changes).

Since you don’t know what fields changed, or if they’ve even changed at all, your flows may be running unnecessarily and doing a lot more work than they need to – and in a world with API limits, that’s something to be concerned about.

I build Power Automate Assistant to address these challenges, which not only tell you what fields changed but also cut down on the number of flow runs due to ghost updates. This tool is open source and available through the MIT license.

The Dataverse considers a field as having changed even if the value doesn’t actually change. We can’t control that. What we can do though, is create a new field and control when and how that is changed. By ensuring only true changes are tracked, we can bypass unnecessary flow triggers.

Here’s how it works:

In this example, we have a flow that runs when a Contact is created or updated, if the First Name or Email changes.

  1. Create a new text field on your entity (afk_ModifiedFields). In this case I’ll stick with 100 characters, but it should be as long as all the schema names for the fields you’re tracking.
  2. Install the Power Automate Assistant and configure it on the contact so it runs any time the First Name and Email changes.
  3. When those fields are updated, the tool will evaluate the field values to ensure it actually changed. If the fields have changed, it’ll update the new text field (afk_ModifiedFields) with the field names that changed.
  4. Configure your flow to run on afk_ModifiedFields.

What happens now?

With this setup, if you update the contact’s email address and save the record, your new text field will say “emailaddress1”. If the first name changed, it’ll say “firstname”. If both changed, it’ll say “emailaddress1,firstname”.

By setting your flow to run on that text field, we can ensure it only triggers if we have a real change, and on top of that, you can treat that new text field as an array and see what fields triggered the update and take actions accordingly.

Enjoy!

Where do I get it?

Managed and unmanaged solutions and source code is available at https://github.com/akaskela/PowerAutomateAssistant

Step-by-step instructions in the wiki on Github https://github.com/akaskela/PowerAutomateAssistant/wiki/TrueFieldChange

Cover photo by Photoholgic on Unsplash

Tip #1396: Multi-currency aggregates in Dataverse

tl;dr

When using aggregates with multiple currencies make sure the business understands how the totals are calculated, how exchange rate is used, and why it’s a good idea to roll out your own calculations.

Bring it on

Multi-currency support has always been one of the staple features of …. eerrr … Dataverse since version … uhm … 4.0? And it’s a great one out of the box: enter the value, specify the currency, and kaboom, it’s converted on the fly using whatever the current exchange rate is, and saved in both base and record’s currencies.

Where it starts getting complicated when you consider changes over the time and aggregates. Time component affects the calculations because the exchange rate changes. Aggregates throw in another spanner because you can’t simply add Icelandic Krónas and Netherlands Antillean Guilders.

Let’s say we have sites around the globe with some equipment purchased and assigned. Our base currency is USD. Site total are calculated using rollup fields.

Site record for Sydney, Australia (not Sydney, Nova Scotia) as of 1 July 2020:

Date01-Jul-2020
Exchange rate0.6895
NamePriceFXTotal USD
Router$500.6895$34
5m cable$300.6895$21
Totals$800.6895$55

How Dataverse calculates the totals:

  • Take the price values entered by the user ($50 and $30), convert to the base currency ($34 and $21 USD)
  • Calculate the total using base currency ($55)
  • Convert back to $80 in the record’s currency (AUD) using FX rate of 0.6895

That was easy, right? Skip forward to November 2.

Date02-Nov-2020
Exchange rate0.7
NameTotalFXTotal USD
Router$500.6895$34
5m cable$300.6895$21
Totals$790.7000$55

Same data but we just lost $1 because the exchange rate went up to 0.7. Individual items didn’t recalculate as we haven’t change anything.

Let’s keep going. On the same date we added another item:

Date02-Nov-2020
Exchange rate0.7
NameTotalFXTotal USD
Router$500.6895$34
5m cable$300.6895$21
Firewall$750.7000$53
Totals$1540.7000$108

Note that the exchange rate for the last item is different. Roll forward to today:

Date28-Jan-2021
Exchange rate0.7628
NameTotalFXTotal USD
Router$500.6895$34
5m cable$300.6895$21
Firewall$750.7000$53
Totals$1410.7628$108

And we seemingly lost money again because our peso keeps climbing.

While math is correct, it does not feel right. We purchased items in AUD so the total shouldn’t change. Right? The answer is “it depends” on how your business records the value of the assets, how the depreciation is calculated, and if the moon is in the house of Saturn. We’re not even taking into account when the individual lines will recalculate – it’s a separate topic.

The most common localized style of recording is to calculate the aggregate value in the local currency and then convert to USD. Total line should look like this:

Totals$1550.7628$118

Things can get really heavy if individual items can indeed be in the different currencies. You’d need to decide if you want to convert the items to the base or local currency when doing the calculations.

That makes more sense but it’s not possible for Dataverse to perform this mental gymnastics. Solution? Do it yourself. It wouldn’t be too difficult to write a cloud flow that would iterate over the assets and calculate the total value the way the business wants. Needs to be real-time? Summon a developer and get them to write a plugin.

Bottom line

Multi-currency aggregates rarely work the way the business wants out-of-the-box, handle them with care.

  • Remember that any OOB aggregates are done in the base currency.
  • Understand the individual records recalculate only when they are saved and they will use the current exchange rate (stored in the currency settings). That may or may not be what the business wants or expects.
  • Explain the calculations to the business and confirm that is (or is NOT) expected and/or correct.
  • Decide if the exchange rate should be updated on a regular basis using some external FX service OR if the business would prefer to have a fixed exchange rate and adjust it as required on an infrequent but regular basis (not an uncommon accounting approach).
  • Apply UCS™ methodology[1] to figure out if custom calculations make sense. Most of the time they do.

[1] Use Common Sense

Tip #1393: If TDS endpoint does not work

Have you tried turning it off and on again?

IT Support

Dataverse connector in Power BI is all the rage: fast, efficient, understands metadata well, supports both Import and DirectQuery, makes sushi upon request, you name it. Except that sometimes it does not work. Try selecting account table, for example:

DataSource.Error: Microsoft SQL: Invalid object name 'dbo.FilteredAccountTDS'.
RequestId: TDS;a2c61dc8-b400-4d34-b66f-3c30157f0a42;2
Time: 2021-01-20T05:24:22.7689085Z
Details:
DataSourceKind=CommonDataService
DataSourcePath=foobar.crm6.dynamics.com
Message=Invalid object name 'dbo.FilteredAccountTDS'.
RequestId: TDS;a2c61dc8-b400-4d34-b66f-3c30157f0a42;2
Time: 2021-01-20T05:24:22.7689085Z
ErrorCode=-2146232060
Number=40000
Class=16

If you try accessing it using SQL Server Management Studio, the result is pretty much the same:

What is this Filtered nonsense you’re talking about? I have no knowledge of this FilteredAccountTDS view, my dude.

SQL Server on behalf of Dataverse

Support request raised, screens shared, emails exchanged. The solution, believe it or not, is in the best traditions of The IT Crowd. Seriously, go to Manage Feature Settings, turn TDS off then turn it back on. Rumor has it that there was an update applied recently and, if you had TDS enabled during that update, few things may have gone south. Flipping the switch fixes that.

Tip #1392: Missing entities in Power Automate

The classic “give me 5 minutes and I’ll whip a flow for you” did not start well. Users entity table was not there.

Options Choices:

  • It’s something trivial and I’m simply no longer worthy.
  • ‘u’ is not an actual ‘u’ but Russian ‘и’ typed accidentally but in a weird font.
  • It’s a UI bug
  • It’s singular not plural
  • I missed an announcement and it’s a new feature where only selected tables appear in the dropdown and I need to enable some obscure setting. “It’s for your own good” kind of flag because it’s a coveted systemusers entity @&^! table.

At this point the only thing I was happy about I was not running a screenshare. I went for a copout. “Something is not right, give me 20 I’ll call you back”.

18 minutes in, table (the physical one) dented. Take this:

Save, close, reopen. @#%*&^%!

I forgot 3 years ago the client insisted on Employees rather than Users despite my plea not to rename. Nostradamus is my middle name except that my gloomy predictions only apply to my own doomed future.

Lesson: if you can’t find a table in the connector dropdown, check if it hasn’t been renamed. As far the blame is concerned, I’ll go with Occam’s razor – sometimes I’m simply not worthy.

Tip #1391: Content management in Power Apps portals

They said Power Apps portals are only good for building functionality. They said Power Apps portals can’t do content management. They said Power Apps portals do not support publishing cycle without taking pages offline.

They. Were. Wrong.

The Enabler

Watch this video to learn how to use language feature in Power Apps portals to create a true publishing cycle with support for staging environment and one button publishing.

WATCH VIDEO

Tip #1390: Bring back the green bar

In Tip 1386 we bemoaned the death of the green bar indicator of the installation status of solutions in Power Apps and Dynamics 365.

Chart Guy to the rescue! Ulrik Carlsson has created a Power BI report leveraging the FetchXML in Tip 1386 to create a report with green bars that you can use to view the installation status of your solutions.

Download the .PBIT here.

Cover photo by Johann Trasch on Unsplash

Tip #1389: Better screenshots through browser sizing

If you are working on documentation and you ever need to take a screenshot of something that appears in a side panel, such as a Power Automate approval or a model-driven app quick create form, and there is a lot of white space between the content and the bottom button, it can look unsightly in your documentation.

Instead of this screenshot, resize your browser so the button appears closer to the content.

this #simplebutuseful tip will make your documentation better.

Cover photo by Khaled Reese from Pexels