Tip #1438: Turn on auto search for lookups

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	Shows nothing or Shows a bunch of ‘recent records’ (do not get me started on the number of times I have turned this redacted (t.j.) feature OFF! (no tip ref but here -> Disable Recent Records from Dynamics 365 Lookup Control \| Model-Driven Apps [Quick Tip] \| D365 Demystified)
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.

How to make this go away

Go to https://admin.powerplatform.microsoft.com/
Select the environment you want to update
Select the Settings cog in the command ribbon
Expand the Product menu
Select Behaviour
Update the Lookup Behaviour to the minimum amount possible for each of the options
- Minimum number of characters to trigger typeahead search: 1
- Delay between character inputs that will trigger a search: 250ms

Screenshot of a lookup displaying list of matching records after typing just 1 letter

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

Tip #1437: Duplicate property names in JSON objects in Power Automate

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

Tip #1436: Change the Default Duration Value of the Time Entry in Dynamics 365 Project Operations

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:

Click on the Events tab
Expand the On Load section
Click on “setDefaultDurationOnCreate” handler
Change the parameter value from 60 to the preferred default duration (in minutes). (e.g. 480 for 8 hours default value)

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

Tip #1435: Update Dataverse user photo from Office 365 profile

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

Tip #1434: Can you count OR how to add index to array in Power Automate

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.).

Initial Array (returned from a connector):
[
   {“Name”: “Foo”},
   {“Name”: “Bar”}
]

Output Array:
[
   {“Order”: 1, “Name”: “Foo”},
   {“Order”: 2, “Name”: “Bar”}
]
Mike!!!

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']

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:

From: range(1, length(outputs(‘Compose’)))
(that creates an array- remember the looping exercise? – t.j.)
Map > Order: item()
(array index! – t.j.)
Map > Order: outputs(‘Compose’)?[item()]?[‘Name’]
(just repeating the property value – t.j.)

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

Tip #1433: How to list all Dataverse tables in style

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.

Open https://make.powerapps.com, ensure the correct environment.
Expand Data, select Tables.
Locate and select table Entity. If you can’t find it, make sure to switch the view from Default to All.
Select Data tab, optionally change the view to All columns.

Screenshot illustrating the steps just described

Two: You can also use our OData endpoint (/api/data/v9.2/entities).

Sign in into https://adgaf.crm.dynamics.com
Copypasta this URL: https://adgaf.crm.dynamics.com/api/data/v9.2/entities
Optionally do sort – something you can’t do in the first method: https://adgaf.crm.dynamics.com/api/data/v9.2/entities?$orderby=versionnumber
If the solutionid is fd140aae-4df4-11dd-bd17-0019b9312238, this is the Active layer and represents an unmanaged customization: https://adgaf.crm.dynamics.com/api/data/v9.2/entities?$filter=solutionid%20eq%20fd140aae-4df4-11dd-bd17-0019b9312238&$orderby=logicalname

Three: Otherwise, you can join to the solutions table and see what solutions each of these entities/tables are brought in from.

I wish we could just $expand=solutionid in OData but solutionid is not a foreign key, i.e. not a navigation property. Joining in FetchXml works just fine though.
You can use usual FetchXml manoeuvres like filtering and grouping to work out what table is in what solution.

<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

Tip #1432: Create records in Power Automate without mandatory fields

I really like mini truck stops when two or more MVPs brainstorm a random problem, usually because they are bored 😈.

Help me understand scenarios where one would use a guid() expression in flow when creating new rows.
Antti Pajunen

What Antti left out was (reasonably) common wisdom that explicitly setting identifiers for new records is possible but frowned upon due to the performance implications. (Cue long discussion about sequential guids.)

This section was intentionally left blank because it would have been otherwise filled with the intense discussion where do guids sit in the order of the universe, are they truly unique, and whether it’s OK to use hexadecimal “beef” in guids when creating a Power Platform solution for Beyond Meat, Inc (NASDAQ: BYND).

Solution looking for a problem

It didn’t take long for the kumbaya to climax at this brilliant suggestion:

You can create records in flow and bypass required fields by using an Update ~~statement~~ action and generating a new ID using the guid() expression.
Aiden Kaskela

Problem looking for a solution

Topic is a required column in the lead table. Who would put a Topic: input textbox in a contact us online form? That’s right, nobody. But if we trigger Power Automate with the form submission, how do we create a lead record?

Technically, we can put some default text in like “Created from online enquiry form”. But that would simply cement this meaningless text across the entire lead table. What if you could create a lead record without a Topic? The first user to open the lead in a model-driven app would be forced to put something in the Topic column before saving the record – more chances of having something relevant recorded.

Solution ❤️🫶🫀 Problem

Use Update a row action and set Row ID to guid() expression. This action does not have any required columns and behaves like an upsert operation: if ID is found then it’s an update otherwise it’s an insert. Using a freshly baked guid guarantees nothing will be found and forces a new record.

The end result? Lead with the name and perhaps some other information but without a topic!

Cover image by jette55 | Pixabay.

Tip #1431: Power Automate loops without variables

EDIT: Added range method. Thanks Natraj Yegnaraman for fixing my brain fart!

When makers need a loop with a counter in Power Automate, say from 1 to 20 (or 0 to 19 for all of you developers out there), the immediate reaction I’ve observed is to reach for a ~~gun~~ variable:

You don’t have to because there are at least two methods that avoid those variables and associated extra actions (that you pay for!).

Method 1: createArray

Use createArray function as the input for the Apply to each loop. This method works for small number of iterations (upper limit is how many comma-separated numbers you can type without getting carpal tunnel syndrome). Big advantage is ability to use custom values for iterations. For example, if you want to loop over the number of days in all months, you can use createArray(31,28,31,30,31,30,31,31,30,31,30,31). Using other data types is also possible, e.g. createArray('One', 'Two', 'Three') or createArray('🤘', '📃', '✂️'). Get the current index value as item() or items('Loop_name') (latter works for nested loops as well).

Method 2: range

If all you want to generate are the sequential integer numbers, easiest way is to use range instead of createArray. For example, to loop from 1 to 20 enter range(1, 20). Use item() or items('Loop_name') to get the current index.

Method 3: endless loop with the limit

Expand Change limits and set the Count to whatever is needed. Brevity is the winner here. The downside is pro-style zero-based indexing. Get the current index value as iterationIndexes('Loop_name') (yes, it’s now documented). In fact, you case use iterationIndexes at any time in Do Until loops giving you a zero-based sequential iteration index.

Tip #1430: Add Power Automate flows into solution like a boss

You didn’t think I was waffling about replacing triggers in Power Automate flows for no reason, did you? Not once but twice, in fact. The technique of painlessly replacing triggers is more like a kata before you master the art of breaking bricks with your head 🧱🤕.

Enter the world of solutions where nine out of ten attempts to import a flow from outside are met with “Where the **** is my flow?!”.

Typical WTF day. I **KNOW** I have like 42 flows outside the solutions!

I hope you are not like me because, for what seems like eternity, I was behaving like an uneducated baboon and was blatantly ignoring the Learn more link because, you know, real men don’t read user manuals. Turns out, apart from the ~~brick crushing~~ trigger replacing kata, this link has everything you need to master the art of import.

Let’s have a look at that non-manly reference Solutions in Power Apps – Power Apps | Known Limitations.

Flows using connectors that are ‘indexed’ cannot be added into solutions. Indexing isn’t supported for solution cloud flows yet. Indexing enables the quick retrieval of those flows to display in a menu or list. Indexed connectors include Power Automate instant (button) flows, Power Apps, Teams, SharePoint, Dynamics 365 Customer Voice, Microsoft Forms, legacy Dataverse connector, Dynamics 365, Excel Online, Microsoft Project, Azure IOT Central V2, and Project Online.

Read the list of connectors and weep because, according to British scientists™, when you work in Power Platform ecosystem that’s 98.4% of all the triggers you use. For example, if you have a ready to go manual flow prototype created in 1987, i.e. outside a solution, and you’re planning to use it as a child labor then you’re simply out of luck because manual is another word for “Power Automate instant I need to be indexed or 7 puppies going to die (button)” flow. Also featured in this hit list is legacy Dataverse connector a.k.a. “The only codeless way known to humanity to run a flow against a selected item (until the fullness of time®)”. And that’s even before you look in the SharePoint General Direction.

But let’s not dwell on the past laden with expletives and read on:

Workarounds for this limitation:
Edit the flow to remove the indexed connector triggers/actions, add it into a solution, and then change it back.

Digital transformation take the wheel! Magic steps, folks, magic steps:

Delete the “indexed” (whatever the heck it means) trigger from the flow. How do you know which step to delete? It’s the first one! Want to do it without breaking your masterpiece? See this and that.
Add a silly “non-indexed” (whatever the heck it means) trigger, e.g. schedule.
Try importing again. Voilà! The flow is now visible. Did I mention magic?
Import that flow.
Recreate the original trigger.

Now of course you can see why we’ve been practicing the art of replacing triggers without breaking anything else.

Cover image courtesy of Heiko Behn | Pixabay.

Tip #1429: Replace Power Automate triggers without breaking too much, Part 2

By now you know not to trust triggers and recapture their output in the step immediately after the trigger. That way you fix only one step when trigger is replaced.

Do not use trigger output beyond the first step in your flow
Sulking Enabler

But do you want more? Do you want to keep the steps completely unbroken? Easy! Use expressions and Power Automate won’t touch any references that are part of an expression – it just does not have IQ to parse and figure out what to remove.

Consider this:

It’s neat and readable but delete the trigger and Compose step will completely lose the Inputs.

But if you use an expression, for example, concat(triggerBody()['text'], '') (which does nothing to the parameter):

then deleting the trigger will not change anything in that expression and, if you followed the yesterday’s advice, will not change anything downstream. Replace the trigger, keep the same parameter name, and you won’t have to touch the rest of the flow.

For the record, using trigger output in Parse Json as demonstrated in Part 1, saves them from destruction so you can have the parameter and eat it too.

« Previous
1
2
3
4
5
…
147
Next »

Power Platform
Dynamics CRM

Tip of the Day