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:

In-App Notification Builder for XrmToolBox
User experience Microsoft Docs
Developer experience Microsoft Docs
Last but not least: create notifications using Power Automate from ever awesome Sara Lagerquist

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

Tip #1415: Call Excel functions from Power Automate

If you know Excel you’ll be very comfortable with Power Automate, they said. Except most of the Excel functions do not even have an equivalent in Power Automate.

But what if we could access all Excel functions in Power Automate? Say no mo

In the Episode 13 of Citizen Can Amey and George talk about broken giraffe, browsing Graph Explorer on Monday night, what is a holiday, and the magic of calling Excel functions from Power Automate.

watch the video

Tip #1414: Enforce unique email but ignore inactive records

Alternate keys are great way to enforce uniqueness of the column values. For example, if you want lead emails to be truly unique, just add an alternate key and no user will be able to bypass that (unlike the duplicate detection which is a fairly timid mechanism). The challenge is when you want that uniqueness to apply only to active records. Bear with me.

You thought that lead was lost forever and deactivated the record (you do not ever delete important records, right?). Now they are coming back, rejuvenated and refreshed and ready to buy not one but seven of your wonderful Jigamagigs. New lead is created but with the same email and that’s OK – we don’t hold grudges. What you don’t want is two active leads with the identical emails.

Alternate key using email are not going to work for you in this scenario, but you can enforce the uniqueness using a faux column that contains email for active records and null for inactive.

It works because alternate keys ignore null values.

The walkthrough below uses the contact table.

Create a column large enough to hold email addresses. No need to make it searchable or enable audit.
Create new real-time workflow (and you can do that from the new solution explorer)
Automation > Process > Workflow”>
Set the workflow properties
- Check As an on-demand process if you have existing records in your Dataverse instance so that you can run it once to populate the values.
- Start after Record is created, Record status changes, and Record fields change.
- Select emailaddress1 as the field to trigger.
- Set the workflow logic: If contact status is Active, set faux column created in Step 1 to emailaddress1 value. Otherwise clear the column.
Create alternate key over the faux column
Run the workflow over all existing contact records to populate. Note: it may fail if your table contains active duplicates. Check for failed workflows, resolve the duplicates, run again.

Now, when a user tries to create a record with a duplicate email address they will see the error.

A screenshot of a sample error message with a caption "Business Process Error" and the message "A record that has the attribute values I Am Unique already exists. The entity key Unique Email requires that this set of attributes contains unique values. Select unique values and try again."

But users will be able to create the duplicates if only one of the records is active.

List of contacts where two of them have the same email address but one of them is inactive.

The good thing about this method is that it’s bulletproof against clever users importing via Excel online, sneaky developers updating using SDK, and even almighty administrators activating the existing records that contain the duplicates.

This tip wouldn’t be possible without my family, my parents, my sponsors, and David Yack who provided a sanity check where I needed it most.

Cover photo by Rupert Britton on Unsplash

Tip #1413: Components Required in the Solution for Relationship Mapping Deployment

Today’s winner is Linn Zaw “I always” Win. And you can be one too if you email your tip to jar@crmtipoftheday.com!

Have you ever added the 1:N or N:1 relationship into the solution, created new mappings in the solution and deployment the solution to another environment only to find out that the mappings are not deployed? Upon opening the customizations.xml of the solution, you might notice that the newly created mappings are not included under the <EntityMaps /> tag.

These are the components required in the solution to include the relationship mapping.

Relationship which contains the mapping (obviously)
Both Primary and Related tables of the relationship
Target column of the mapping

Relationship

Mapping

Tables and target column

Screenshot of the classic interface for the solution components. Components > Entities node is expanded in the tree, Fields node under the target entity name is selected.
The following screen elements are highlighted and numbered:
number 2 - Primary Entity and Related entities under Entities node in the tree.
number 3 - mapped target field selected in the list view in the right part of the screen.

Cover photo by Davies Designs Studio on Unsplash

Tip #1411: Power Apps portal vulnerability checks using PowerShell

I’ve created couple scripts that quickly check if your Power Apps portal has a potential vulnerability in the portals I look after.

OData Endpoint Probe

This one is quite trivial and simply probes the OData endpoints exposed by the portal. Takes portal url as a parameter and lists all OData endpoints. Endpoint is marked as a suspect if it can be accessed.

Dataverse Probe

This one is a tad smarter and longer. It prompts to connect to Dataverse, grabs the first portal, and checks lists and forms to see if any of them have table permissions switched off. If permissions are on and endpoint is not accessible, we are good. If permissions are on and endpoint is open it’s a potential leak. If permissions are off and there is an open OData endpoint, this is bad.

For forms it’s black and white: script only lists the forms where table permissions are off.

Consider the chapter about Power Apps portals data leak closed.

Tip #1410: 50 Shades of Regex

I usually don’t drool over code but this one is just way too elegant for me not to. StackEdit – In-browser Markdown editor is an awesome app for markdown editing. One of the standout features is ability to comment and review – something sorely missing from the standard markdown. StackEdit very cleverly serializes reviews and notes as a base64 string and inserts it at the end of the document using HTML comment <!– –>. As part of the exercise this comment is saved very tidily in a 50 char wide column. So how does one wrap a string at a fixed width? This is the genius line:

const serializedData = 
  utils.encodeBase64(JSON.stringify(data))
    .replace(/(.{50})/g, '$1\n');

What does it do?

utils.encodeBase64(JSON.stringify(data)) – take our data object, serialize it to string, then convert to base64 to make sure it’s printable
replace – that’s just replace one thing with another
(.{50}) – a group of 50 characters
/g – global, i.e. do not stop after the match
'$1\n' – replace those groups of 50 characters with themselves plus newlines

Is that a brilliant piece of code or what? 🤤

Cover photo by Ono Kosuki from Pexels

Tip #1409: Count records for all tables in Dataverse

Want to count all records in all Dataverse tables like a boss? Say no mo.

Make sure TDS endpoint is enabled using Manage feature settings.
Connect to your Dataverse as described in Use SQL to query data (Microsoft Dataverse)
We cannot use some of the features including loops (see How Dataverse SQL Differs from Transact-SQL) so let’s build that SQL:

SELECT 
 CONCAT(
 'SELECT ''', name, ''' AS TableName, 
 COUNT_BIG(1) AS CountOfRows FROM ', 
 name,
 ' UNION')
FROM 
	sys.tables
WHERE
	TYPE = 'U'
AND SCHEMA_ID = 1

Copy all results (Ctrl-A), open New Query window (Ctrl-N), paste (Ctrl-V)
Scroll to the end, remove the last word UNION and add ORDER BY 2 DESC line
Execute
You are welcome

Screenshot of T-SQL table results with the partial list of table names and row count for each displayed.

Note: the execution may timeout if your Dataverse is of any decent size. Use trial/error approach to figure out executable chunks – you probably know your largest tables anyway.

« Previous
1
…
3
4
5
6
7
…
147
Next »

Power Platform
Dynamics CRM

Tip of the Day