Managing Duplicates, create Item and Update Item

[CHQLDHealth]

Hi Community, 

I need assistance with importing daily data from excel to SharePoint list.

Each morning I run a flow to list Rows Present in a Table and add them to SharePoint list, this much I can do, however where the trouble starts is there are some records with same Name, Date of Birth, i.e. Duplicate people but has multiple records because the procedure column has different procedures, 

e.g.

Name        Date or Birth          Procedure                            Date to Complete          UR

Me             15/06/1987            Take care to car wash           03/03/2024                   1

Me             15/06/1987            Pick up groceries                                                       1

I want to add the items if they are new and not in the list, or update them if there is a change, for instance the Date to Complete column is updated.

flow is as below:

Get Items is obvious

List rows: no filter query but listing the excel rows

Filter Query is filtering the 3800 list row items down to 540 for selected column.

For each is: body('Filter_array')

Condition is: This is where I need assistance, currently comparing if: items('For_each')?['UR'] = items('For_each')?['field_0'], UR is the column from the List Rows present in a Table. and field_0 is the SharePoint column for UR,

If True I will add an update items , if false Create Items.

Apply to each is: outputs('Filter_array')?['body']

The compose before create items is to convert serial date in excel:  if(equals(items('Apply_to_each')?['Procedure Date'],''),null, addDays('1899-12-30',int(items('Apply_to_each')?['Procedure Date']),'dd/MM/yyyy')).

The flow mostly works but is creating duplicates of any record so I end up cancelling the flow to prevent running an infinite loop.

Any assistance would be great.

Thanks

[ivan_apps · Accepted solution]

So you are close and your issue is actually with “Get Items”. It’s done too early in the flow and should be replacing the “Condition” after the ForEach.

basically you are iterating through the Excel table, and for each row you are reviewing, make a call to the SharePoint list to see if and see if there is a match - that means use the filter criteria in GetItems to query for an existing match:

Name eq [excel value] and Date of Birth eq [excel value] and field_0 eq [excel value]

The there should be a “Condition” after this query, but not like you have it. At this point you are only checking if you got a return value or not (should only be 1 at most). So your condition just needs to check the length of the response array - 0 results means you should create a row, 1 result means you should update.

Condition:

length(GetItems[‘body/value’]) eq 1 — update else create

Hope this helps!

[CHQLDHealth]

Thank you Ivan_Apps, 

I will give this a try and let you know how it goes.

On another note though my Sharepoint list has a field Procedure Date (field_11) set to date format, the Excel data from List rows for this column is serial date  ( e.g. 45789) so the Compose step is to convert this as it will not accept  items('For_each')?['Procedure Date'] doesn't matter what I try so I did this:

if(equals(items('For_each')?['Procedure Date'],''),null,addDays('1899-12-30',int(items('For_each')?['Procedure Date']),'dd/MM/yyyy'))

It still errors and tells me the expected format "''" is not in the correct format String and stops the flow.

Any ideas would be great thank you.

[CHQLDHealth · Accepted solution]

y I should Add the Create Items Procedure date references 

if(equals(outputs('Compose'),''),'null',outputs('Compose'))

Ta

[ivan_apps · Accepted solution]

Yes you’ll want to change the excel connector’s date format to ISO 8601. It should be under ‘show advanced options’

[CHQLDHealth]

Thanks again for the help, all is going well not but for one item not working.

Update works, Create works but in the get items I used the following as you suggested:

field_0 eq' items('Apply_to_each')?['UR'] '

where UR is a unique medical record number

I wanted to also use a second filter for example

field_5 ne ' items('Apply_to_each')?['Procedure'] ' 

The reason is the excel data might contain the same patient with the same UR but having a different procedure at a different date, so naturally I am looking for a point of difference to get both records but it is not working. Is this  filter query on the get items correct:

field_0 eq' items('Apply_to_each')?['UR'] ' and field_5 ne ' items('Apply_to_each')?['Procedure'] '

What I want to achieve is;

UR                Name           Procedure                   Procedure Date

12345           Mr Name      Operation 1               12/03/2024

12345           Mr Name      Operation 23              24/04/2024

Currently the flow works to update an item or create an item but does not create the second or third record if the person is having multiple procedures.

Thank again for your assistance: