Tip #1321: Formatting currency values in Power Apps portals

Dollars are dollars are dollars. Until they’re not. I’ve been working on the portal implementation where pricing can look like this:

(Those are UAE Dirhams, if you must know)

Forms seem to be fine but any custom layout, uhm, is suddenly very challenging. I can get values without any issues but can’t format them for display. Before long, an SOS is sent:

Folks, can’t figure out how to get formatted values from fetchxml in liquid. Is it possible at all? 

— some random dude who knows bugger all about portals, let’s call him “George”, for example.

Mighty portallers like Nick Doelman, Colin Vermander, and Andrew Butenko chimed in. Redacted list of their suggestions:

  • Do a join to currency and bring currency sign (g.d. – that would only work half way as the currency sign doesn’t give me a proper localized currency formatting. Where do I put the sign, front or back? Do I use dot or comma as a decimal separator? )
  • Maybe it’s a limitation in the Liquid implementation (g.d. – duh!)
  • Write a plugin to output to another field (g.d. – ugh, did someone said #lowcode?)

It’s been a while since I wrote a half-decent plugin so I decided to dig into the portal source code instead (and you can too). Lo and behold, there are undocumented filters that do exactly what’s needed.

format: format, [culture]

Takes a format string and an optional culture, making it possible to write something like:

<blockquote>
My income is {{ 12345.005 | format: "C", "en-US" }} 
before tax. When I grow up and move to south of France, 
I will earn {{ 67890.049 | format: "C", "fr-FR" }} 
per week.
</blockquote>

Which produces a nice but totally fictitious statement:

My income is $12,345.00 before tax. When I grow up and move to south of France, I will earn 67 890,05 € per week.

currency: entity, attribute, [format]

Format is great but still leaves us with the task of figuring out the currency of the record, etc. Enter currency filter that “formats a given numeric value according to the CRM currency settings for a given record and entity attribute”. Easier to demonstrate:

{% assign q = entities.quote[params['id']] %}
<blockquote>The quote total value is 
{{ q['totalamount'] | currency: q, "totalamount" }}
</blockquote>

That produces something like

The quote total value is £1,234,567.89

The other useful currency and decimal filters include:

basecurrency: [format]

Formats a given numeric value as representing a value in the organization base currency.

invariantculturedecimalvalue: decimals

Returns the unformatted/culture invariant value for a decimal number up to a specified number of decimal places

maxdecimals: decimals, [culture]

Formats a decimal number using specified number of decimals and culture. Strips out trailing zeros and the decimal separator if possible.

decimals: decimals, [culture]

Formats a decimal number using specified number of decimals and culture.

Cover photo by Disha Sheta from Pexels

Tip #1320: Keep in touch

&tl;dr

We are back, tons of tips in the pipeline, promise to keep in touch, new stuff coming, first BAD Masterclass of 2020 is just over two weeks.

Tell me more

We are back after month and something hiatus.

Not that we did nothing in these few week, just the opposite. But we should’ve posted an update or a notice but we didn’t and for that, we are sorry.

When kids are travelling these days we rarely ask them to write or call on a daily basis. All we want is for them to keep in touch. “We are here and we are OK”. CRM Tip of the Day, our beloved child, is here and it’s ok. It’s just grew up a few inches/centimetres over the break.

Touch in today’s world is everywhere. Touch-screens, touch-optimized, touch-only, do not touch. For some people touch is the main sense. Keeping in touch as important as keeping others informed of a progress.

Sometimes the progress you report could be as simple as “still working on it” and it’s just as important as reporting something “done”. You keep others informed.

When you commit to something, it’s your responsibility to account what are you up to. You may account to your project manager, or yourself – all depends on that “something”.

You become responsible forever for what you’ve tamed. You’re responsible for your rose.

Antoine de Saint-Exupéry, The Little Prince

What’s cooking

We have grand plans for 2020. Some rebranding, revitalization of our YouTube channel, forays into new technologies and topics.

BAD Masterclass 2020 Promotion

And then, of course, there is BAD Masterclass. People gave us the feedback that at times masterclass was a bit too much. We decided to get rid of “a bit” and condensed everything we want to talk about, into a single day. Enter BAD Masterclass Ramp-up Edition. Save yourself both time and money and get up to speed on Power Platform in under 9 hours.

London and Glasgow are in just over two weeks, go ahead and register!

Good to be back and stay tuned!

Cover photo by unsplash-logoPhix Nguyen

Tip #1319: Double the single quotes

tl;dr

If you have email addresses coming from somewhere and you’re using them in Power Automate to filter and find the records, make sure to double the quotes.

Full story

One of the observations made by our lifetime tipster Jerry Weinstock was that citizens tend to create automations that follow happy path. I have to say this turned out to be valid for yours truly as well.

Am I becoming a citizen?! o__O

The Enabler

I was processing some records that came from a spreadsheet and needed to look up CDS records by email. That part of my flow looked something like this:

Happy – 3000, unhappy – 2. Diving in.

That explains it. According to RFC5322, the recipient part may use any of these ASCII characters:

  • Uppercase and lowercase English letters (a–z, A–Z)
  • Digits 0 to 9
  • Characters ! # $ % & ‘ * + – / = ? ^ _ ` { | } ~
  • Character . (dot, period, full stop) provided that it is not the first or last character, and provided also that it does not appear two or more times consecutively (e.g. John..Doe@example.com)

All of these characters are probably fine except the quote that breaks the fragile syntax of JSON land. So the expression catering for unhappy path has become:

And everyone is happy now, including the unhappy path with 6 quotes in a row.

Photo by Dominika Gregušová from Pexels

Tip #1318: Why don’t I see the “associated records” link in subgrids?

In unified interface, when you click on the more option button, you will sometimes see the the option to see the related records in the subgrid.

This is handy, especially when you want to see all of the view options, like filters.

But sometimes you might see the “see all records” option instead. This is not very handy, as it shows you all of the records in the related entity, and you don’t have all of the view options.

The reason why you don’t see the “see associated records” option in your subgrid is because you do not have the entity in the subgrid displayed on the navigation area of the form. Add the entity in the subgrid to the form navigation menu/related records are of the form on which the subgrid lives, and you can start viewing your associated records.

Tip #1317: Make lead qualification better with Power Automate

You work on a lead in Dynamics 365–you have several conversations and enter some notes.

After you qualify the lead, the activities show up on the account or contact record that was created in the qualification process, but the notes are left behind on the lead.

Power Automate to the rescue!

Create a Flow that runs on create of a contact (or account if you are qualifying leads to accounts).

Action 1: Get lead record (to get all details)

Action 2: Condition: If originating lead equals null, terminate

Action 3: List records — get notes where objectID equals contact originating lead.

Action 4: condition: length(outputs(‘List_records’)?[‘body/value’]) is greater than 0 (does #3 return any notes)?

If yes, Apply to each and update the note, setting the regarding to the contact.

End result? Your lead notes are now linked to the newly created contact.

Cover photo by Min An from Pexels

Tip #1316: Do form embedded charts work in unified interface?

I heard that you can’t put charts on form in Dynamics 365 unified interface. Is that correct?

Tip Jar

We’ve received this question from several readers, so I decided to check it out.

I did this in Dynamics 365 Online v 9.1. I don’t know if this works in on premise 9.0.

  1. Add a subgrid to your form
  2. View subgrid properties
  3. In the chart options area, check the box for show chart only

VOILA! CHART!

This tip has been Chart Guy Verified™. It doesn’t show the chart if you don’t check the show chart only box. sorry no chart/view combo subgrids.

Cover photo by unsplash-logoDan Meyers

Tip #1315: How to remove Field Service from Dynamics 365

Disclaimer for Ben Vollmer: We love field service. If it was food, we would eat it at every meal.

That out of the way, sometimes you find yourself with solutions in your environment that you don’t need. Maybe you set up a trial of Dynamics 365 and you accidentally chose the “I want it all” option and wound up with a bunch of managed solutions in your environment. This can be bad, as any solution you develop in this environment will have a dependency on these solutions, requiring you to install them before you can move your solution.

If you find yourself in this situation, you will want to remove these solutions from your environment. But that can be challenging, as many of these apps install multiple solutions in your environment.

In this video tip, I show you how to determine the order in which these solutions should be removed and then successfully uninstall field service from a Dynamics 365 environment.

YouTube player
I removed Field Service and lived to tell the tale

I did see the Skyline Consulting blog on this topic, but it is outdated as the solutions are now different.

Tips for uninstalling first-party apps:

  1. Sort solutions in descending order–that generally tells you which solutions to remove first
  2. Remove patch solutions before removing the main solutions
  3. When in doubt, go to the components list in classic solution manager, select one of the entities in the app and click “solution layers.” This gives you information about the layers, remove the higher layers first.
  4. When you hit a dependency notice when deleting the solution, remove the dependency, then delete the solution.

The following is the order of operations from the video:

  • msdyn_FieldServiceTrial
  • msdyn_FieldServiceGeofencing
  • Remove Geofencing SDK step
  • msdyn_Geofencing_Patch_1
  • msdyn_IoTHealth
  • msdyn_IoTProviders_Patch_1
  • IoTProviders
  • msdyn_CFS_Patch_1
  • ConnectedFieldService
  • msdyn_IoT_Patch_1
  • IoTConnector
  • msdynce_FieldServiceHealth
  • msdyn_FieldService_Patch_6
  • remove msdyn_/Utils/head.js from user form
  • remove msdyn_/Utils/head1.0.3.js from contact form
  • FieldService

Cover photo by unsplash-logoOlga Guryanova

Tip #1314: Sharing in Forms Pro

You create an amazing Forms Pro survey, and you want to share it with your colleagues so they can edit it. So you hit the Share button.

You are given three options: Share and collect responses, share as a template, and share to collaborate. To share editing privileges, you should select Share to collaborate.

This will give you a URL that you can give to your friends, from which they will be able to collaborate on the survey.

So why don’t they see it in the Shared with me area?

The reason your colleague may not see it in the shared with me area is that they haven’t accepted the invitation yet. Once they click the share link once, the next time they log in the survey should appear in the shared with me area.

Cover photo by Stephanie Pombo from Pexels

Tip #1313: Blocked by conditional access

We are back from the travel bursts, some reorganization, and “hold my beer I’m too busy to do it myself” spurts. The first post after this short hiatus is not going to be about Power Automate, however tempting it might be. We’ll get to that subject later on this week. In the meantime, something a bit more pressing.

Imagine you have working code, something innocuous, something perhaps like this fragment:

using (var client = new CrmServiceClient(connStr))
{
  if (client.IsReady)
  {
    Trace.WriteLine(
      $"User={client.GetMyCrmUserId()}");
    Trace.WriteLine(
      $"Org={client.ConnectedOrgFriendlyName}");
  }
  else
  {
    Trace.WriteLine("NOT READY");
  }
}

You admire this bastion of reliability for a second then go get a refill for a drink of your choice. When you come back 5 minutes later, your defenses are crushed and the code stopped working (a.k.a NOT READY message). No, this is not a joke, yes, participants of the Toronto BAD Masterclass witnessed it in real time and yes, it did happen in real life though perhaps less dramatic, just replace going for a drink with going home and coming back the next morning.

The worst part is that the logs won’t give you anything. Even if you enable logging as per Use Trace Not Console nugget of wisdom, you’d get an infamous fault exception at best:

Error: 
An unsecured or incorrectly secured fault
was received from the other party.
See the inner FaultException
for the fault code
and detail.

It does read like a mediocre haiku when you format it like that, doesn’t it?

If you have your own authentication implementation that goes a bit deeper (as we’ve done in our PHP Toolkit), you may get a less cryptic message

AADSTS53003: Blocked by conditional access.

Which is still a mystery – what conditional access? As with any murder story, there are two parts to whodunnit.

Part 1: The diligent

Unbeknownst to you, a curious but, unfortunately, overzealous and not very thorough Azure AD administrator discovered baseline protection policies and, among those, ability to block legacy authentication. “We don’t use POP, IMAP, SMTP, do we” – reasonably argued the admin and enabled the policy.

Turns out, “legacy authentication protocol” is a very broad and not very well defined scope that includes any user-level protocol where MFA cannot be applied. That unfortunately, includes WS-Trust authentication that is widely used by our beloved SOAP client that, as of the time of writing, is still a foundation of the SDK client including CrmServiceClient from XrmTooling.

Part 2: The oblivious

You guessed it – the connection string in the example above was using username/password combination. Perhaps, it was a historical oversight. Perhaps, despite the server-to-server authentication being available for a long time, the coder was clinging to the non-interactive user logins. Whatever is the reason, combined with the shooting in the dark admin, the code fell apart.

It was a hunting accident. Admin was aiming for a sitting duck (or so they thought), the code just happened to be in the line of sight.

Part 3: The solution

Review your code for any authentication that still uses username/password and replace it with the OAuth equivalent. If the process is interactive, use ADAL libraries. If the process is in a dungeon, use server-to-server authentication variation of those. Easiest way to deal with these scenario is to use XrmTooling with its plethora of authentication methods, both interactive and not so.

But whatever you do

STOP USING named Office 365 account for authentication

Tîpp Jäår

If you have Azure AD Premium, you should be able to buy some extra time by adding a policy exception that would let WS-Trust through. I wouldn’t recommend it though – could get messy and it’s better to fix the code, anyway.

Cover photo by kat wilcox from Pexels