cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Flow Error

I created a flow to import information from an excel spreadsheet to a SharePoint list. There is a column that has yes or no options and I am receiving an error for my expression. 

 

I used if(equals(variables('str'),'Yes'),true,false) but I am still receiving any error message. 

 

Any help will be appreciated! 

46 REPLIES 46
Anonymous
Not applicable

Yes it's creating multiple records in SharePoint.

 

Screen shot of Apply to each

 

 

 

There is only one row in the spreadsheet that I am testing.  Let me know if you need additional information!

Anonymous
Not applicable

To clarify, there are technically 2 rows in the spreadsheet. One being the header.

ccc333ab
Solution Sage
Solution Sage

For testing purposes, can you add a second row of test data in your excel file, run the flow and send me the actual run of the flow. (focusing on the Apply_to_each section).

 

Also, in this test do both rows get duplicated? 

Anonymous
Not applicable

I added another row to the spreadsheet and ran the flow twice. It duplicated both rows.

 

 

 

ccc333ab
Solution Sage
Solution Sage

Few more questions:

1. In your run, how many iterations does the apply_to_each say (Show 1 of ??)? For example, below mine ran 32 times...

ccc333ab_0-1625149923761.png

 

 

2. Why did you run the flow twice? The flow should only need to be run once as the List Rows-->Apply to Each runs through all rows of the spreadsheet...

 

Anonymous
Not applicable

Mine says run 1 of 2. 

 

I ran the flow twice to see if it would duplicate, since my flow reoccurs every hour. Even if I don't run it manually, the auto run duplicates every hour.

ccc333ab
Solution Sage
Solution Sage

OK, I understand now. The duplication is because you run the flow against the same data multiple times, I was assuming the data was getting duplicated on a single run.  

 

So this is a bit more complex...you will need to determine if the row has been processed before or not. There are many approaches you can take but since this seems like a brand new process, this is the approach I would take.  

Note: This solution assumes you aren't going to be "modifying" the data in Excel and expecting it to update your list (and if you do, that'll require different logic)

 

1. Create 2 new columns in EXCEL. One called ID and one called Processed (you can even have this hidden if you don't want people to see this). Leave Processed blank but have ID be soething like a counter, just so each row has a unique number. 

 

2. In your List rows present in a table (call to excel), add in a Filter Query of

Processed eq ''

 

Screenshot 2021-07-01 9.34.56 AM.png

 

3. Now you'll only have those rows that haven't been added to your list. So you can use the same Create Item to add the SharePoint data.  

 

4. WIthin the Apply_to_each loop, right after your Create Item step, update your Processed Row in excel to "yes". Use the ID as Key Column, and use the ID from "List rows in a table" call. 

 

Screenshot 2021-07-01 9.52.35 AM.png

 

5. Once you add this in, all your fields in Excel will appear, just add YES to your Processsed column. 

 

That should be it....hopefully that makes sense...

Anonymous
Not applicable

So I will be modifying the data in excel and expecting it to update in the SharePoint List 😞

 

The excel document will constantly be modified with new rows daily. That is why I have the reoccurrence set to every hour.

ccc333ab
Solution Sage
Solution Sage

Yes, this will check  for "new" rows being added to the spreadsheet. What I meant by modify is, will people go back and update previous rows that have already been processed?

Anonymous
Not applicable

Yes people will go back and update previous rows. 

ccc333ab
Solution Sage
Solution Sage

Ok. I'm nowhere near a computer so will reply in a few hours....

Anonymous
Not applicable

No problem, take your time. You have been a HUGE help!

ccc333ab
Solution Sage
Solution Sage

OK, so here is the approach for this scenario. 

 

Setup

In your Excel file, keep the ID field that will have a unique value (a counter will do). We need something to be able to link records up between your Excel sheet and your SharePoint list. Here is my dummy data....I named my ID field DisciplineID in both the Excel file and in SharePoint. 

ccc333ab_0-1625194561012.png

Logic

The basic of the flow is to: 

  • Loop trhough every row in your excel file
  • For the current row, check to see if the ID matches a record in SharePoint
  • If a row matches, then update that record
  • If not, then create a new record

Flow

1. Use List rows present in table from Excel (with no filter query if you put one in from my last suggestion).

ccc333ab_1-1625194828804.png

2. Now use and Apply to each to loop through each row, and do a get items call against the SharePoint list. This uses a filter query to see if any rows have the same DisciplineID.

ccc333ab_2-1625194967022.png

 

3. Now check to see if the Get Items returned any records. 

ccc333ab_3-1625195022639.png

 Expression here is:    

length(body('Get_items')?['value'])

 

4. If YES then you want to UPDATE  the record. 

ccc333ab_4-1625195201045.png

Note: ID here is from the Apply_to_each_2 loop. 

 

5. If NO then create a new record. 

ccc333ab_5-1625195334741.png

 

Anonymous
Not applicable

Sorry for the delayed response, I was OOO!

 

Would I create a new flow or integrate this into my current flow?

 

If integrate, where would it start from?

Please see my current flow below.

 

 

 

ccc333ab
Solution Sage
Solution Sage

The "List Rows present in a table" would match exactly up with my "List Rows present in a table". 

So then your apply to each matches my apply to each, but everything inside would now change to my suggestion. 

Anonymous
Not applicable

I received the following error message. I believe it has to do with the condition?

 

 

 

 

ccc333ab
Solution Sage
Solution Sage

Can you show me your flow design? Specifically....

  • What you have in your Apply to each
  • What you have in your Get Items 

The error most likely is what you have in your filter query in your Get Items step. 

Anonymous
Not applicable

Screenshot 2021-07-08 081924.pngScreenshot 2021-07-08 082057.pngScreenshot 2021-07-08 082155.png

ccc333ab
Solution Sage
Solution Sage

1. It does not appear you carried out the "Setup" section, where you NEED to create an ID to track this with (i.e., Discipline ID). Make SURE you add the new ID (DisciplineID) to both your SharePoint list and your Excel file. 

 

Note: When creating this column in both SharePoint and Excel, do not name it ID (which is why I suggested using DisciplineID). That is because in SharePoint there already exists and ID field that you don't want to conflict with. 

 

2.  You Filter query is wrong. The filter query is a box that will only bring back the rows that this filter query is true for (so follow what I did in my instructions...it is saying bring back the rows from SharePoint where the DisciplineID is equal to the DisciplineID in Excel. 

 

ccc333ab_2-1625755585616.pngccc333ab_4-1625755706832.png

 

Anonymous
Not applicable

I fixed the setup section with the correct ID formatting.

 

The filter query looks similar to yours but it is still giving an error message. 

 

Screenshot 2021-07-08 144525.png

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Users online (5,468)