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:

  1. 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.
  2. Add a silly “non-indexed” (whatever the heck it means) trigger, e.g. schedule.
  3. Try importing again. Voilà! The flow is now visible. Did I mention magic?
  4. Import that flow.
  5. Recreate the original trigger.

Like a boss memeNow 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.

Tip #1428: Replace Power Automate triggers without breaking too much, Part 1

Always stash trigger output into variables or compose before continuing with the flow. If your trigger output contains a record from the underlying storage (Dataverse, SharePoint, etc) get that record and use the output of the get step downstream.

Why? Because when you need to replace or recreate a trigger (happens more often than you think), you would have to recreate the reference in one place only. Have you used the trigger output throughout the flow, trigger deletion would mop it all up and break every single step where the output was used. Go fish!

Don’t do this:

Do something like this instead:

Why? Because when you delete the trigger in the first example, both compose steps will be broken. In the second example only Parse Json step will require a fixup but the rest of the flow will be fine.

Getting triggering record has additional advantage. Sometimes the triggers (looking at you, Microsoft Dataverse) tend to overlook certain properties like formatted values, @odata.id, etc. Getting the record as the first step ensures you get up to date values for all columns that you might be interested in.

In other words:

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

Bitter Enabler

Cover image courtesy of Schäferle | Pixabay

Tip #1427: Single letter custom formats for dates in Power Automate cloud flows

Today’s tipster is Elaiza “What The Flow” Benitez. (And you can be a tipster too, just send your gem to jar@crmtipoftheday.com!)

“Froyo Queen” Azure McFarlane mentioned in a birdie chat that she was having trouble with formatting the month value using the formatDateTime function in Power Automate cloud flows. She was attempting to only retrieve the month value without the leading 0.

The expression applied was formatDateTime(utcNow(), 'M') but the output returned is the following:

(Problem is that standalone ‘M’ (and bunch of other letters) is not treated as a custom format at all but as one of the standard formats. – t.j.)

Solution: prepend the % character in the expression like this: formatDateTime(utcNow(), '%M')

Ta da!

Special thanks to The Enabler (that’s me ☺️ – t.j) for pointing to the documentation that specifies this.

Another mystery solved! #ScoobyDoo

Cover image by congerdesign | Pixabay

Tip #1426: Power Automate Gymnastics Reference Guide

Power Platform Tip of The Day presents: Power Automate expressions you didn’t know you needed until you found them. Everyone keeps a stash of useful Power Automate expressions in Notepad++, VS Code, EMacs, vi, or sticky notes if you don’t know what any of the above mean.

Good citizens and developers (Amey “ABC” Holden, Antti Pajunen, Matt Collins-Jones) got around the campfire and put together

Power Automate Gymnastics Reference Guide
(Yes, I am a link to that page, please click me)

The expressions are ready for you to copy and paste without coding.

It doesn’t count as code if you copy and paste

Amey “ABC” Holden

Have a favorite expression of your own? Drop it in the comments or send it straight to jar@crmtipoftheday.com.

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