Create sharepoint item with dropdown fields

[Anonymous]

Hi,

In a flow, how can I enter a custom value for a dropdown field that is connected to a Sharepoint list? All my attempts at inputting dynamic fields and expressions have all failed so far for this specific type of column.

Context:

I'm building a flow that creates a Sharepoint item from a .csv export. There is an existing PowerApps available to add new items manually. I am building a flow to create multiple items much faster.

The flow I am building works to create the items, but some columns with dropdown choices don't work. I can't find how to enter custom values properly in some dropdown fields.

The dropdown columns that I am struggling with all pull data from another Sharepoint List. For example, in the screenshot for the Account Id column in Sharepoint, the values I can choose are located in a Sharepoint list called Accounts. The dropdown list displays items in the following format: [AccountID] - [Title]  (DisplayName). 

In my flow, when entering a custom value, I would get an error message if I didn't enter an integer. So I tried with entering only the account number (for example  I tried '1010', instead of '1010 - Petty Cash'). With either way the AccountId column remained empty after running my flow. 

Maybe it is something to do with the column properties or with some type of look up in the Account list. I am not sure what to look for however to investigate. ... maybe in PowerApps? But I have never used it so I would need help to know what to look for. 

As a note, not being an admin, I don't have permission to edit the Accounts list. 

[sudharsan1985]

@Anonymous 

If you are trying to give a custom value for the below mentioned column, you have to give the ID of the item. ID of the item that is present in the Accounts list.

Hope it helps, please like it or mark it as a solution if it resolves ur clarification or issue
-Sudharsan K...

[Anonymous]

thanks for your help. The issue was I needed to start with a Get Items action 

[v-bacao-msft]

Hi @Anonymous ,

@sudharsan1985 's suggestion is correct, you need to fill in the Item ID to Account Id fields in the Accounts list.

Please provide more details about your problem so that we can better assist you in solving your problem.

Best Regards,

[Anonymous]

Thank you both for your help, @sudharsan1985 and @v-bacao-msft. This community is great. So good for learning.

As I'm progressing in building this flow, I run into another problem. I quickly reached the nesting limit!

Creating the Sharepoint item in this list requires 6 list queries, since there are 6 lookup fields, and each is mandatory. The associated Create Item action ends up nested in multiple 'Apply to each' loops. As you'll see below, it's not pretty!

Apply to each

Continued:

The below screenshot is an example of a query that runs before the Create item.

Query

In the end, each of my queries return only one value, as they are all unique. So I don't understand the need for Apply to each...

Also, adding to the nesting count I also have to 2 control conditions: one right after the trigger to test if the file created is a .csv file, and another one later on for two different scenarios.

Do you know a way to create my Sharepoint items, which requires 6 query outputs, that will not require so many nesting levels?

[sudharsan1985]

@Anonymous 

Is it possible to get the 'ID' of the lookup value from the .csv file? It would be great if you could retrieve the id from .csv and it would reduce lots of complexity in your flow. If it is not possible, then what I propose is to use the 'Apply to each' for each lookup (I hope the lookup's are multivalue) separately inside the 'Apply to each' for the .csv file values and store it in different variable and then use that variable in the 'Create Item'.

Hope it helps, please like it or mark it as a solution if it resolves ur clarification or issue
-Sudharsan K...

[v-bacao-msft]

Hi @Anonymous ,

You could use Get item action to get the corresponding item in the lookup list instead of using Get items action.

For example, LookupField is a lookup column in Test 2 List, and it looks up from List 1.

If we could get the Lookup column id, we could fill this id in Get item action to get the corresponding item in List 1.

Image reference:

Hope that makes sense.

Best Regards,

[Anonymous]

hi @v-bacao-msft & @sudharsan1985 I looked into both your suggestions and it comes down to knowing the ID of each lookup list item. It's a great idea to remove Apply to each actions. However I can't think of a way to provide the list item ID in the .csv , which would be required to run a Get Item action. That's disappointing because it would have solved my issue! I'll keep the suggestion in mind, in case in a future development my .csv is able to generate list item IDs.

The alternative to working with the ID that @sudharsan1985  is proposing seems to describe my current structure, except I am not storing the Get items outputs as variables. But doing that also creates Apply to each loop for each lookup, so I'd be back to square 1 with my nesting level issue. But maybe I am not understanding your suggestion correctly?

And to answer your comment about multivalues, I apply filters in the queries to make sure to return a single value.

The only alternative I can think of with my limited knowledge is to split my flow into two flows: the first flow would create the Sharepoint item, and end with creating a copy of the .csv file. The second flow would be triggered when the copy is created, then perform another set of action applied to each line in the .csv. Feels clunky when I think about scaling the flow and sharing it for import to colleagues, but it would get the job done.

I also have the option to export my original data into an Excel template rather than .csv . I would need to run a few actions to format into a proper table and remove empty lines before using the values in each row, but I should be able to do that relatively easily. I thought it was worth mentioning in case you think that changes anything to the nesting levels required to create an item in Sharepoint with all the lookup fields.
Curious if you have any thoughts on this?

[Anonymous · Accepted solution]

Hi, update! I have found a solution to avoid multiple levels of nesting with multiple lookup fields.

Here's what I did: after a Get Items, I initialized a variable that fetches for the ID of the value in my lookup fields, using this expression: 

int(body('Get_items')?['value'][0].ID)

And bingo, avoided a bunch of Apply to each. I followed this video that turned out to be quite educational for someone at my level (beginner): https://www.youtube.com/watch?v=bTqWrM1p_WI

Perhaps this is what @sudharsan1985 was referring to the whole time and I couldn't fully grasp. Sorry if that's the case!

What got me confused me was that my list had an AccountID column, but it wasn't the pure ID the of list item! It was just a made up name. Good lesson.

thanks for putting up with me!

[sudharsan1985]

@Anonymous 

That's gud to hear, hope you found the solution. Its better to choose the best solution to help others in the community.