Flow Error

[Anonymous]

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! 

[Anonymous]

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]

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

[ccc333ab]

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]

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

[ccc333ab]

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...

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]

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]

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 ''

 

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. 

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]

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]

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]

Yes people will go back and update previous rows. 

[ccc333ab]

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

[Anonymous]

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

[ccc333ab]

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. 

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).

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.

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

 Expression here is:    

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

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

Note: ID here is from the Apply_to_each_2 loop. 

5. If NO then create a new record. 

[Anonymous]

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]

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]

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

[ccc333ab]

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]

[ccc333ab]

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. 

[Anonymous]

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.