How to use Power Automate to automatically create a new item in one of the entity table in Model driven app

[rrelopez]

Hi,

We have a canvas app called "Intake Form" in SharePoint online that takes in all Requests being submitted from users. The user is either able to 'Save it' only or 'Submit' the form. Once the form is submitted, we want to move/create the submitted item over to the 'Request Log' entity in Model Driven app. Is it possible to use Power Automate in achieving this and how?

Thanks in advance.

[abm]

Hi @rrelopez 

What's the 'Intake Form' datasource is? CanvasApp saves the information to SharePoint in another list? If the information is stored in SharePoint list then you can create a flow using 'When an Item created (SharePoint)' as trigger. Once the trigger is defined then use Create the item action step using Dynamics flow action where you can map the SharePoint details to D365 entity fields.

Thanks

[rrelopez]

Hi @abm ,

Thank you for your reply and suggestion. So I created a Power Automate Flow that gets triggered when a new item is created in SharePoint list and then uses the Common Data Service action  "Create a new record" to create this item in the custom entity "Request Log".

But the flow is failing on the "Create a new record" action with the below error. I'm new to Power Automate and not sure how to debug this.

{

"status": 400,

"message": "Bad Request - Error in query syntax.",

"error": {

"message": "Bad Request - Error in query syntax."

}

Here is the screenshot of my inputs for creating a new record. 

[abm · Accepted solution]

Hi @rrelopez 

Remove all the mapping under Create new record step except keep only Date Created. Run the flow and see whether that works or not. Then map the next one and so on until it fails. One of the field mapping type is wrong here. 

Thanks

[rrelopez]

That worked @abm ! 🙂 Thank you! I was able to find the field mapping that causes the error. These are the Submitter and Requester fields (both are of type Person or Group from SharePoint list). And the fields that I'm updating in CDS Entity are Lookup (User) as per below.

I've tried all possible inputs that I can think of and that are available (such as Requester Display Name, Requester Email, Requester Claims, and I even hard coded the name e.g. FirstName LastName) to the mapping field but no luck. Any suggestions? Thanks in advance!

[abm]

Hi,

You need to find the GUID (Id) of that user record and assign the GUID. To do this you need to do a List Record by name. Assume user names are unique in CRM. If not you might end up using the wrong user GUID. There are lots of examples here how to set lookup in CDS. 

Thanks

[rrelopez]

Hi @abm,

Thanks again for your reply. In creating a new record from a SharePoint list item to a CDS entity, I'm trying to pass the 'Created By' username to the lookup user field in CDS. If I understand this correctly, does this mean I have to create a new Entity in CDS that contains all users, and then reference this new entity via 'List records' action to get the GUID? Do you have any links to share that discuss how to do this? 

I really appreciate your help on this.

Thanks

[abm]

Hi @rrelopez 

You don't need to create the users in CDS. If both SharePoint and CDS users are same then check the users entity in CDS. Yes you still need to set the GUID by querying the list record by name.

To set the lookup in CDS see this post.

https://crmtipoftheday.com/1311/use-entity-name-in-flow-lookups/

Thanks

[rrelopez]

Thank you @abm. I was able to figure it out with your help. I used the action List records to query the Users entity and then used the Get record to retrieve the value that I need.