Tip #1425: Dataverse CSV import using alternate keys

Today’s tip is from Alex McLachlan (@alex_mcla), saving the day (no kidding – I used it the very next day after receiving the tip). You can be a hero too by emailing your tip to jar@crmtipoftheday.com!

With the classic import experience, you couldn’t use a CSV import containing an Alternative Key column to update existing records. See (2) Update existing records on import (using alternate keys – account number)??? – Microsoft Dynamics CRM Forum Community Forum

Now with Import from Excel > Import from CSV gives:

Screenshot of the standard Import from CSV settings dialog. New setting allowing selection of the alternate key is highlighted.

Selecting the Alternative Key then allows update of existing records in the CSV file. The Alternative Key needs to be included in the Data Mapping.

Tîpp Jäår $0.02 + GST

If you’d like to consume a video, Power CAT team has one just for you:  Easier Imports With Dataverse Alternate Keys – Power CAT Live – YouTube.

Cover image by hudsoncrafted | Pixabay

Tip #1424: The user does not have sufficient access right to run flow with custom connector

I’ve tried all the right things:

  1. Created, deployed, and configured custom connector into the target environment before importing the solution that uses the connector. All tests pass.
  2. Created a manual Power Automate flow as part of the solution.
  3. Shared the flow with the user (should not need to for the solution flows but just in case)
  4. Ensured that the flow runtime permissions are set correctly (correct use of run-only permissions)
  5. Shared custom connector with the user (should not need to but just in case)

And yet, when the said user tried to run the flow they kept getting this error message

Screenshot of an error message that reads:

The user '39b162da-9c30-eb11-a813-000d3a7953be' does not have sufficient access rights to perform this operation on entity with id c4bd1890-3b2f-43d5-8153-31b9e8487241 in instance with URL 'https://redacted.dynamics.com/'. Required access right 'ReadAccess' is missing.

This flow uses the following apps. A green check means you're ready to go.

This information seems to be helpful but then it generates more questions than answers:

  • Operation on entity with id XXX. Is that a record with that id or id of the actual entity/table?
  • What entity/table are we talking about here?
  • The flow uses unspecified list of apps – what is the relevance of this?

Long story short: user was missing read privileges on Connector table (and id mentioned is the id of the entity/table not of the record in the table). There are two Connector tables on the most instances I’ve seen and, just like the USB, you are going to get the first one wrong. There is an easier solution to the problem: assign Basic User role to the user. One of the permissions it includes is that very permission to read custom connectors (what did you think is in the Connector table?!).

Cover image by ds_30

Tip #1423: Search for solution that does not exist

You are about to record or present a demo but your environment contains all the demos you performed previously and looks like this (zoomed out for brevity):

What you are about to do next is what I call the “sweeping under the rug”:

Search for the solution that does not exist yet

Thee Nabler

If you are about to create a demo solution Unicorns and Gluten, search for it first then your screen will be clean as a whistle:

And once created, solution will be the only one present. Nice and clean demo ahead.

Congratulations, you just successfully performed the “sweeping under the rug” manoeuvre.

Cover photo by Bob van Aubel on Unsplash

Tip #1422: Leave old stuff behind

Let me start 2022 slowly, with a tip how to avoid annual cleanup. It all started with this email:

Screenshot of the email that reads:

Hey,
 
Hope you are well and are going to get a break over Christmas!
 
I got a notification of you making changes in your Dropbox – feel free to remove me if you like (see attached)
 
Cheers
Richard
IT Engineering Team

Darn, I completely forgot about those folders and big thumbs up to Richard for reminding me. Richard is one in a million though, most would simply ignore the shared folders. Until such time when you drop some sensitive files in there.

How to avoid this unnecessary stress and reliance on Richards of this world to do the right thing? Simples:

Always set the expiration date when sharing folders or files

Thee Nabler

I don’t care what it is. Could be tomorrow, could be 5 years from now. But do set it and spare yourself potential embarrassment at best and the jail time at worst (I’ve seen it in a movie, totally plausible scenario). Almost like in the song:

Go ahead and share but trust no one. (So much for faith in humanity in 2022)

Cover image by Gerd Altmann

Tip #1421: Hide form selector in model-driven apps

Everybody take a seat. Tanguy WiTouzard has the floor. (You can grab everyone’s attention too by sending your useful tip to jar@crmtipoftheday.com)

Wizardry starts here

I don’t know if it was published before but I just found out that we were able to remove “forms” from “form selector” using Javascript. It’s really useful when you have many forms because you are creating forms for different purpose but don’t want users to be able to switch themselves to those forms.

Screenshot of a form in a model-driven app. Form selector is visible and shows multiple forms available to the user to select from.

So let’s say, I have so many forms for account entity (see screenshot), I can remove the form selector by using the following script.

function disableFormSelector(){
	formContext.ui.formSelector.items.forEach(
    function(f){
		  f.setVisible(false);
	  }
  );
}

Voila! Sans sélecteur de formulaire!

(Tanguy actually didn’t say anything here but I’m not missing the opportunity to flex my Google-inspired French skills – t.j.)

Screenshot of a the same form as before in a model-driven app. Form selector is no longer visible after the  javascript fragment execution.

(And I have no idea why Tanguy gets involved with piscines this time of the year. And why would swimming pools need a résumé? I guess he’s looking for a new job trying to finance his new swimming pool. 😈 – t.j.)

Cover image by PhilippT | Pixabay

Tip #1420: JSON Function and size limits in Power Apps

Power Fx (gotta use the cool keyword!) JSON function in Power Apps is pure magic, it’s like a compactor converting anything you throw into it into a neat text representation to send over the wire, engrave on a golden bullion, or stash it as an alphabet soup. It works great apart from the times when it does not.

Turns out, it’s got a 0.5MB size limit. Give it anything that produces longer string than that and get blank in return. No error, no messages, no trace. Nothing. Blank. Blankinness. Nothingness. Ouch.

Now you know.

Cover photo by Sarah Kilian on Unsplash

Tip #1419: Dual-write 404 error when metadata contains special characters

As Power Platform extends its reach beyond the realms of Dynamics 365 (CRM/CE/etc) and SharePoint, we’re starting to see some interesting integration challenges from Dataverse big brother – Finance and Operations. Today’s tip is from Parth Bhaidani. (Hey, you can have your name published here too – just drop your sizzling tip into jar@crmtipoftheday.com!)

Dual-write provides tightly coupled, bidirectional integration between Finance and Operations apps and Dataverse. However, at times it could be trickier to address the issues. Recently, I was working on a Dual-Write integration and one of the tasks was to add a new Table map. Prima facie the task was fairly straightforward until I stumbled upon the error, “The remote server returned an error: (404) Not Found. Unable to retrieve metadata for AX environment“. Turns out the name of the Table on the F&O side has a forward slash (/) in it (Country/regions) and that’s the reason for the error above.

To fix this issue, we would have to create a new Data Entity but without special characters. (Luckily, in F&O we can create multiple entities for the same underlying table – t.j.)

  1. In the F&O environment go to Data Management Workspace -> Data Entities
    F&O Data management interface with Data entities button highlighted.
  2. Search the Table name for which you are trying to add the Table Mapping (in my case Country/regions) and copy the Target entity name.
    Country/region table selected in the table mappings with Target entity column value highlighted.
  3. Click New and populate Target entity with the copied Target entity from the above step, everything else will be auto populated and leave those columns as is. Click Save.
    New table entry without special characters
  4. Add the Table Mapping using the new name (which is again the one copied in Step 2). Now, you should be able to add mapping without any issues.

Cover photo by Vincent van Zalinge on Unsplash

Tip #1418: In-app notifications in model-driven apps

Welcome to inaugural video shorts where all human knowledge is compressed into the bites of 60 seconds or less, to be consumed while waiting for the paint to dry.

In this episode we help Lisa to enable in-app notifications in model-driven Power Apps without any code using a freshly baked XrmToolBox tool by Ivan Fricko.

Links galore:

Cover photo by Prateek Katyal on Unsplash

Tip #1417: Power Excel in SharePoint

You’ve seen us in Tip #1415 doing some Microsoft Graph abracadabra to call Excel functions directly from Power Automate. To do that we use Excel spreadsheet located on OneDrive for Business. Documentation mentions in passing that “You can use Microsoft Graph to allow web and mobile applications to read and modify Excel workbooks stored in OneDrive for Business, SharePoint site or Group drive” but none of the examples uses SharePoint, everyone is obsessed with /me/drive/. Brian asks a fair question:

Is it possible to do with an excel document stored in a SharePoint document library?

Brian

Lo and behold. The syntax is similar except instead of /me/ you have to point to the site, i.e. /sites/root/lists/{list-id}/ (if your list is on the root site). For example this will calculate the average:

POST https://graph.microsoft.com/v1.0/sites/root/lists/33246232-feed-dead-beef-decaf5388770/drive/root:/your-workbook.xlsx:/workbook/functions/average
content-type: Application/Json 
authorization: Bearer {access-token} 

{
    "values": [1,2,3,4,5]
}

Cover photo by salvatore ventura from Pexels

Tip #1416: Power Platform PowerShell module includes unapproved verbs

tl;dr

No, Power Platform team didn’t sneak in any profanities into PowerShell module. It’s caused by the verb Apply, is by design, and can be safely ignored.

Longer version

I usually oblivious to any warnings produced by the command line tools. My attention is fully reserved for the word FATAL in Ferrari red (#ff2800 if you must know), the rest I make go away with a magic cls command.

Benedikt Bergmann is by far a more diligent developer and, while using the Power Platform Build Tools, got concerned that some steps get a warning:

##[warning]The names of some imported commands from the module 'Microsoft.Xrm.WebApi.PowerShell' include unapproved verbs that might make them less discoverable. To find the commands with unapproved verbs, run the Import-Module command again with the Verbose parameter. For a list of approved verbs, type Get-Verb.

Is the sky falling and we’re all doomed because PowerShell team from 1984 did not approve our choice of verbs? (Here’s the full list: Approved Verbs for PowerShell Commands – PowerShell | Microsoft Docs)

Nah, turns out none of the approved verbs was a good fit for apply solution upgrade action that ended up being Apply-SolutionUpgrade in PowerShell. If you use this action in your script, you might get the warning and it’s OK to ignore it.

Well, if you ask me I would have chosen something random and memorable, for example Mount-SolutionUpgrade, Measure-SolutionUpgrade, or even Resize-SolutionUpgrade. But now you know why I’m not in charge of naming PowerShell cmdlets.

Cover photo agracier – NO VIEWS, CC BY-SA 3.0, via Wikimedia Commons