Use PowerApps variable to determine to which Dataverse table to add a row to in PowerAutomate

[Christopher5]

Hi guys,

I built a Canvas app, in which upon start I automatically create the following variable based on the logged-in user's information: CompanyName (let's say with values being CompanyA, CompanyB, ... up to 50 different companies all following such a standardized naming convention).

While using the app, the user is able to upload a file and by doing so triggers a PowerAutomate cloud flow.

This flow takes 2 inputs from the App (the CompanyName variable, and the attached file).

My goal:

In the flow, I want to add a new row to an existing Dataverse table, and within this row add the attached file within a File column. The specific Dataverse table which should be added to needs to be determined by the variable CompanyName, as there is 1 table per company (i.e. CompanyA has a table, CompanyB another ...)

My current approach:

There already exists 1 table per company, and these table names follow the same naming convention as the variable CompanyName, i.e. they are named something like '[prefix]_CompanyA' or [prefix]_CompanyB'. In the flow, I start with the PowerApps v2 trigger, taking in both the CompanyName variable and the file. I then use the Step Add a New Row (Dataverse), and choose the Dataverse table name not from the dropdown list, but I use the dynamic content available to me through the CompanyName variable.

My issue:

The step is unable to add the empty row. I suspect an issue with the underlying JSON being created by the step.

Additionally, I will next need to add another step to add the file to the new row... the question will arise: How can I get the RowID of the row I just added in the step before??

Usually when working with Dataverse tables, you don't need to add the 'Row Item' parameter in the Add a New Row Step, and a dynamic content is available to specify the RowID when adding a file that same row. But, given that I am using dynamic content (i.e. the variable) to determine the Dataverse table name, I need to specify both Row Item and Row ID..

Many thanks in advance!!!

Chris

[SamLed · Accepted solution]

Hi,

I guess you try to add an empty row because you don't see fields ? 

The point is that this action load table schema and display fields when you select a table name. I guess all company table have same schema, have you thought about selecting one of the company table from the action dropdown list, so it will load schema and show fields. Then, you create a test row (you'll delete later) using the action by populating required fields and run the flow.

Then have a look to row inputs from flow history, here what I get when creating a test contact in table Contacts:

See "Parameters", you've got there the entity name (the plural schema name) to use in a variable to set table name dynamically, not display name which is "Contacts". You've also got the fields name.

So now you can create the JSON input for the field "Row Item", see below, I've created a new contact "Test2" (remove "item/" from previous raw input parameters)  

Result:

As an advice, you should build the table name in a compose or directly in variable initialization, not in action "Table name" field.

About Row ID, have a look to dynamic contents available from "Add a new row", you'll see "OData Id", this is the created row ID.

______________________________________________________________

If I have answered your question, please Accept the post as solution.
If you like my response, please Thumbs Up.

[Christopher5]

Thanks a bunch Sam.

Already great advice to run the 'Add a New Row' step of the process with a test row to see the schema, and then write the JSON correctly. I will also initialize a variable when starting the process, using the variable I took over from PowerApps.

It does not solve my problem though:

The reason I add an empty row first, is because I only want to include a file attachment in the row. To add a file attachment, I need the separate step called 'Upload a file or an Image'. That's why I can't put anything in the row while creating it.

And the issue of then locating this row will also remain, because dynamic content such as "OData Id" will only be available if I am referencing the Dataverse table name without the variable. Or did you try to add a step to "Upload a file or image" after the below?

Thanks again!

[Christopher5]

This is how my process currently looks like (you'll need to zoom in).

To recap, I face 2 issues:

1. The step which adds an empty row ('Add a new row') fails, although it looks like correct JSON to me (I add empty curly brackets under Row Item)

2. I cannot find back the Row ID of the row added in in the 'Upload a file or image step'. The column name I can standardize and find back easily.

The complexity stems from using a variable to reference the dataverse table name.

I cannot hardcode the dataverse table names, or use nested conditioning, because we are talking about over 50 tables.

[SamLed]

Hi Christopher,

Yes, once you switch to dynamic table name, PA doesn't propose the action dynamic contents anymore.

Do same as I did with JSON input schema to give to Row Item, select a table, get OData ID dynamic content, double click it to select it, copy and paste it in dynamic contents expression tab, you'll get this: @{outputs('Add_a_new_row')?['body/@odata.id']}.

Now you can switch back to dynamic table name and paste the above expression directly in the Row ID field of your Upload action (the @{} allow this, if you use the expression tab remove them)

______________________________________________________________

If I have answered your question, please Accept the post as solution.
If you like my response, please Thumbs Up.

[Christopher5]

Clear, thanks Sam.

Regarding the Adding a New Row step, on which my flow is still failing:

- For row item it should be fine to pass an empty dictionary as {}? Or do I need to specify column names and assign them empty values as {"Col1": "", "Col2": ""}.

- For the table name, I should use the logical name or schema name? And I use the prefix assigned (e.g. 'crf85_') AND and a forward slash in front: /crf85_TableName?

I tried different combinations of each but am not getting it to work unfortunately.

[SamLed]

I gave a try with Contacts table and looks like it didn't complained:

However the "Last Name" field seems to be mandatory:

So we can infer, there is no control, try it. But I would wonder why you want to pass an empty JSON, is there no data you should fill in?

About table name, I've read everywhere that it's plural schema name to use. 

Extract from Microsoft Doc: Customize table definitions (Microsoft Dataverse) - Power Apps | Microsoft Learn

LogicalName: Name that is the version of the table name that is set in all lowercase letters.

SchemaName: Name that will be used to create the database tables. This name can be mixed case. The casing that you use sets the name of the object generated for programming with strong types or when you use the REST endpoint.

Note: If the logical name differs from the schema name, the schema name will override the value that you set for the logical name.

______________________________________________________________

If I have answered your question, please Accept the post as solution.
If you like my response, please Thumbs Up.

[SamLed]

About Table name, I have to admit that it is a bit confusing when you read Microsoft docs especially about casing.

for example: To use dynamic table name for "Users" table we need to give "systemusers", for Contacts one it is "contacts". They are the internal plural schema names of these tables.

[SamLed]

I read your replies again and I'm quite sure according to my test that if your action fails it is more due to table name than empty JSON. What is exactly the error thrown ? (Just figure out you didn't post it).

______________________________________________________________

If I have answered your question, please Accept the post as solution.
If you like my response, please Thumbs Up.

[Christopher5]

Below is the flow I am running. Please disregard the changed table name compared to the examples I was providing above (the name now has _lgd_trial suffixed, but it does not change the gist of the issue)

I use value 'banka' (Bank A) for the variable, which results in a table name which is valid.

I tested it by using the table name from the drop down as opposed to using the dynamic content.

This is the resulting error of the flow above:

Thing is, that if I change the expression within RowID, currently being

outputs('Add_a_new_row')?['body/@odata.id']

to

outputs('Add_a_new_row')?['body/crf85_banka_lgd_trialid']

it works.

Of course this is unfortunately not a solution as I lose the dynamic aspect of my flow.

Very likely I am not aware of some fundamental aspect of these expressions?

[SamLed]

Hi Christopher,

Verify if this 'crf85_banka_lgd_trialid' is present in all tables. If you have 50 tables serving same aspect for different customers, I guess they're using same schema else it would be a design fault. schema should be a generic one for every table. If it is present, you won't loose dynamic aspect.

Otherwise let's imagine to illustrate what I'm about to  explain you, if 'crf85_banka_lgd_trialid' is a company name, then you can dynamically build  your expression as follow:

outputs('Add_a_new_row')?[concat('body/crf85_', [CompanyName], '_lgd_trialid')]

where [CompanyName] is your variable.

______________________________________________________________

If I have answered your question, please Accept the post as solution.
If you like my response, please Thumbs Up.

[Christopher5]

The 'crf85_banka_lgd_trialid' is referring to the row ID, and it seems that it references the table name, which is the only aspect which differs across tables.

I used the following in an Expression:
outputs('Add_a_new_row')?[concat('body/crf85_',triggerBody()['text'],'_lgd_trialid')]

It throws the following error:

I really appreciate all this guidance!

[Christopher5]

In the above screenshot, note that it didn't even run the process.

[Christopher5]

I followed your indications, with a little tweak.

The concatenated portion I stored as a variable.

And then used this variable in the outputs() function within row id, instead of adding the concat() function itself:

Tested it with BankA and BankB and it does the trick.

Je pense que ca ce tout?:)

[SamLed]

Hi,

Yup, trigger inputs are not allowed in expressions, use a compose to build your fieldname and replace the trigger outputs by your compose ones:

So if you create a compose called Fieldname your expression should look like:

outputs('Add_a_new_row')?[outputs('Fieldname')]

______________________________________________________________

If I have answered your question, please Accept the post as solution.
If you like my response, please Thumbs Up.

[SamLed]

Oui, c'est tout ! 😉

Glad to have been helpful, don't forget to mark my original answer as solution, it closes a topic and is useful to any other who are facing same issue.

______________________________________________________________

If I have answered your question, please Accept the post as solution.
If you like my response, please Thumbs Up.

[Christopher5]

Wouldn't want to admit how long I've spent on this.

Again many many thanks Sam.

Bon après-midi!!

[SamLed]

You're welcome. Your thanks are my reward 😉