We talk about security around Dataverse and Power Platform from time to time. We even dabble into platform agnostic security tips. Today is all about vendor-agnostic cybersecurity.
Learn the fundamentals of identity management, zero trust, AppSec, and data security in this new 7-lesson open source course, “Security for Beginners” created by Microsoft Cloud Advocates. Each lesson should take around 30-60 minutes to complete and will help kick-start your security learning.
WARNING: Niche L400 tip ahead.
If you’re expect primary column name to be tablename + “id“, add an exception for systemuser and team tables – both will come back as ownerid in custom API.
If custom API has an output property of type EntityReference then, for example, for account (or any other “normal”) table, the following json is returned as expected:
of the
"Account": {
"@odata.type": "#Microsoft.Dynamics.CRM.account",
"accountid": "87aa5cfb-197c-ea11-a813-000d3abeef18"
}
Save yourself a trip to metadata and use tablename + “id” for the primary column name, right? Not so fast. For either systemuser or team, you get ownerid instead of expected systemuserid or teamid
"User": {
"@odata.type": "#Microsoft.Dynamics.CRM.systemuser",
"ownerid": "889362cfb-22aa-eaea-abba-dead3a6a9942"
}
No one knows dev docs better than Jim Daly, his patience to express RTFM sentiment is fascinating.
In Web API ownerid is the primary key for the systemuser EntityType and the team Entitytype because both inherit this property from the principal EntityType, which enables the capability for either type to be owners of records.
— Jim Daly
The ownerid value is the same as the respective systemuserid or teamid columns.
EDIT: the same applies to any activity table, e.g. task, email, appointment, etc, because any table that is configured as an activity inherits from the activitypointer entity type. The primary key for any activity will be activityid, according to Web API EntityTypes – Power Apps | Microsoft Learn.
We all enjoy flexing our Dataverse or Dynamics 365 skills from time to time. Many of us have meticulously crafted a demo instance from proverbial stone and blood. However, one of the challenges is keeping the data up-to-date. There’s nothing worse than displaying 20 tasks that are all overdue by four months. Fortunately, Dataverse offers a fantastic feature that allows us to modify data during import! Grab yourself a cuppa, blanket, comfortable horizontal surface and dive in: Add transformation mappings for import (Microsoft Dataverse) – Power Apps | Microsoft Learn.
Alternatively, you can simply use an Excel file with formulas while following the instructions: Import or export data from Microsoft Dataverse – Power Apps | Microsoft Learn. Boing!
For fancier, more modern transformations you may want to look into Importing using a connector and Power Query.
#"Added Two Weeks" = Table.ReplaceValue(
#"Changed column type",
each [Due Date],
each DateTime.From([Due Date]) + #duration(14,0,0,0),
Replacer.ReplaceValue, {"Due Date"})
I am back after lengthy hiatus. Any day is good to restart but today feels just right:
Changes are coming but let’s resume the daily ritual first.
George
Tipster in charge, reporting for duty 🫡
Almost forgot! Cover photo by Abi Schreider on Unsplash
Today’s tip is from Amey “Anything But Code” Holden. You can drop your priceless tip into our tipping jar too, thank you very much!
I (Amey, not me – t.j.) often see users struggle with the confusing behaviour of lookups in model-driven apps. Typical sketch from an average week of consulting with model-driven apps looks like this:
| User | Clicks on the lookup column on a form |
| Lookup Column |
or
|
| User | Confused – “there’s nothing there, it’s not working” |
| Expected behaviour | Show me a list of available values to select |
| Actual behaviour | Nothing |
| Me | “Start typing – what are you looking for?” |
| User | Types what they are looking for |
| Lookup Column | Does nothing |
| User | More confused – “there’s still nothing there” |
| Expected behaviour | Filter the list of available values that match what I am typing |
| Actual behaviour | Nothing |
| Me | “Oh yeah ok now press ‘Enter'” |
| User | “Where’s ‘Enter’?” |
| Me | “On your keyboard. Return? Pointy Arrow Button? No not the delete one! Ok ok ok click on the search icon. No not that one. Aghhhh!” |
| **the process continues for a while until desired outcome is achieved or one of us gives up** | |
| User | “ahaaa found it” |
| Me | “I’m sorry, lookups are confusing, **redacted** (t.j.) design, don’t worry it’s not your fault, you are still really smart” etc. etc. |
Rinse and repeat for all your customers and environments – send all thankyou gifts to **redacted personal details for Amey Holden**.
Cover image “Ben to the Rescue” by dafuriousd is licensed under CC BY-NC 2.0
From time to time you may be forced to deal with JSON objects in Power Automate that contain duplicate property names. Consider this object, for example:
{
"Id": 42,
"Name": "Fixing the machine",
"From": "Edison",
"Total": 1000,
"Items": [
{
"Id": 1,
"Name": "Hammering",
"Subtotal": 1
},
{
"Id": 2,
"Name": "Knowing Where to Hammer",
"Subtotal": 999
}
]
}
To make this object maker-friendly, the common approach is to use Parse JSON action and derive schema from the data itself.
When done, maker can use properties as dynamic values. In our data, unfortunately, some properties have identical names (Id and Name) even though their path is different.
As it turns out, object schema can be enriched with “title” and “description” annotations. For example, let’s describe Ids and Names
{
"type": "object",
"properties": {
"Id": {
"type": "integer",
"title": "Invoice ID",
"description": "Unique invoice identifier"
},
"Name": {
"type": "string",
"title": "Invoice name"
},
"From": {
"type": "string"
},
"Total": {
"type": "integer"
},
"Items": {
"type": "array",
"items": {
"type": "object",
"properties": {
"Id": {
"type": "integer",
"title": "Invoice line item ID",
"description": "Line item number unique within the invoice"
},
"Name": {
"type": "string",
"description": "Line item name, not to be confused with invoice name"
},
"Subtotal": {
"type": "integer"
}
},
"required": [
"Id",
"Name",
"Subtotal"
]
}
}
}
}
The result is the game changer:
Now you can have your JSON and eat it too.
Cover photo by Natalia Yakovleva on Unsplash
Love the tips from Linn because they are short and precise. Unlike me, he does not beat around the bush and goes for jugular: “if you need X do Y or go home”. Today’s tip is no exception even though I know bupkis about Project Ops.
When the new Time Entry is created in Dynamics 365 Project Operations, the default value is set to “1 hour”.
But unlike the default Duration value in the Appointment activity, the default Duration value in Time Entry can be configured.
To change the default Duration value of Time Entry, edit the Quick Create form of the Time Entry table with the name “Create Time Entry”. In the form properties:
No idea what just happened. I think I just billed a client 8 hours instead of 1 just by following Linn’s tip.
Cover image by anncapictures from Pixabay
If you think that the title looks suspiciously close to Tip #1378: Update Common Data Service user photo from Office 365 profile, you’d be absolutely right. Why? Because historically Power Platform is renowned for two traits: renaming things and changing behaviors of those things.
I’ve taken care of the former by replacing Dataflex Common Data Service with Dataverse in the title. Robert Hawke takes care of the latter because something changed, and our simply brilliant code no longer works.
I (Robert – t.j.) tried this (Tip #1378) and it didn’t work (got an error that the content was not in Base64), but I was able to fix it after some tinkering. Instead of referencing Image File Content directly I needed to reference only the body portion. Solution is to put the code below into Entity Image (when updating Dataverse user record – t.j.):
body(‘Get_user_photo_(V2)’)?[‘$content’]
In short, when you get an error that the content is not Base64, try using $content property. All the other steps from Tip #1378 are fine.
Cover photo is still Female Avatar by Tucia / CC BY
When developers like Mike! start their sentence with “this is probably really simple”, you know that it rarely is. (No idea why he’s so excited about his name though, I suspect there is some math flex in here).
This is probably really simple, but anyone know how I would add an index to an array of objects in flow e.g. (– thank you Tîpp Jäår, for nice formatting; -you’re welcome, Mike! – t.j.).
Mike!!!
Initial Array (returned from a connector):
[
{“Name”: “Foo”},
{“Name”: “Bar”}
]
Output Array:
[
{“Order”: 1, “Name”: “Foo”},
{“Order”: 2, “Name”: “Bar”}
]
Honestly, I was waiting for Linn to produce some json > xml > xpath > json magic but the best he could come up with was, and I quote, “🤣 “(true story). Lo and behold as Damien Bird flexes some Scottish voodoo.
Hey Mike (! – t.j.), you can use a select. Create a range from 1 to length of the array, then simply add the new key into the array as follows:
![screenshot of two steps in Power Automate flow:
1. Compose that contains the expression [{"Name": "Foo"}, {"Name": "Bar"}]
2. Select step with the following information:
From: range(1, length(outputs('Compose')))
Map > Order: item()
Map > Order: outputs('Compose')?[item()]?['Name']](https://crmtipoftheday.com/wp-content/uploads/2022/04/image.png)
If we ignore the first step that simply defines the input array, the solution is a one-step magic of Select action with the following parameters:
The output as expected:
[
{“Order”: 0, “Name”: “Foo”},
{“Order”: 1, “Name”: “Bar”}
]
Yes, it is zero-based and Damien has apologized for that and said that “You can still start your index from 1 with sum(item(),1)“. After this unwarranted and unreserved apology I now suspect Damien is actually a Canadian and not a Scot.
Cover image is by Gerd Altmann
Is there any way to export automatically or with a script a list of all the Dataverse tables in an organisation?
From our good friend Shan McArthur came a multilayer answer (narrated and illustrated by The Enabler):
One: We have a virtual entity called entities that will give you a list of all entities in the database.
Two: You can also use our OData endpoint (/api/data/v9.2/entities).
Three: Otherwise, you can join to the solutions table and see what solutions each of these entities/tables are brought in from.
.jpg){kind=link}
<fetch top="50" >
<entity name="entity" >
<attribute name="name" />
<link-entity name="solution"
from="solutionid"
to="solutionid" >
<attribute name="uniquename" />
</link-entity>
</entity>
</fetch>
Cover photo by Dimitra Peppa on Unsplash