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.

10 thoughts on “Tip #1432: Create records in Power Automate without mandatory fields

  1. JCobelens says:

    Interesting solution.

    However you can still use the add a new row action and enter the expression null for any required column that can/should remain empty. Works for me.

    • Excellent solution if it works! I’d still prefer Upsert in some cases because I’ve seen an implementation with 27 😖 mandatory fields. Don’t fancy entering null into each one.

    • It works but it gives you different results in some cases, like a a required drop down or boolean column with a default value. It’ll be required in Power Automate but the default value isn’t prefilled on the UI (at least not on my instance at this moment). If you use the null input on those fields the result will be an empty field; if you use an upsert then the result will have the default value.

    • Timothy Bohte says:

      I totally agree with you. There is no need to for the extra update afterwards.
      And experience is that if you leave the field blank it will just not update that field to empty. Really, you should use the expression null.
      But before you do that, you should ask yourself why this field is mandatory at all.

  2. MrE says:

    No topic no lead? The end user is certainly happy to have a tons of leads she wants to work off with no topic but a required field she does maybe even not know what to fill in :-). Maybe you will then see leads with topic = ‘.’ or ‘?’…
    Especially if sorted A-Z … by topic… Why not to make the field optional? … Not nice for lookup fields i guess… Firstname Lastname?

  3. Magnus says:

    Is it just me, or does this not work here om june 21st 2022?
    Tried both “Update row” with guid() in Row ID
    and
    “Create row” with null in required fields.

    None of above works for me :-/

    • Magnus,

      when you say “does not work” what exactly does not work? I’ve just tried it again and it still works like a charm. Update with guid() creates a new record without any of the mandatory fields filled in.

  4. Juan Simon says:

    Hi, used it before and it worked like a charm.
    This week though, one of my Flows started to fail when using the Dataverse connector when trying to create a Project inside D365 PO, with this message: Object reference not set to an instance of an object.

    And also, if you try to use null() in an update action it does not let you and says wrong syntax.

    Let me know if you found this issue as well and any workarounds if possible.

    Thanks,

Leave a Reply to Juan Simon Cancel reply

Your email address will not be published.