Updating SharePoint List from Excel Workbook

[Phineas]

I have an Excel workbook with a column called 'EmployeeNumber'.

The workbook is updated in OneDrive everyday.

I have a SharePoint List with a column 'EmployeeNumber'.

The SharePoint list needs to match the most recent Excel workbook.

I have a recurring flow.

Which is the best practice, to compare the Excel content to the SharePoint content and delete items from SharePoint that don't match Excel?

Or, to overwrite/delete the SharePoint list with the last version of the Excel workbook every day?

[lbendlin]

If you don't care about the version history for the list items and the recycle bin filling up then the flush and fill approach is better (it is certainly faster). Figuring out if an item has changed or not is cumbersome, especially when more than one column can change.

[Phineas]

Is there a template for such? Or can you quickly outline the construction?

I've been knocking around in PA all day trying to get it right; swing and a miss every time.

I've got -
Recurrence - 24 hour
List rows present in a table (Excel is the dataset)  - Point to my table
Get items (SharePoint List)

...then things get fishy. 

Both Excel and SharePoint have 'EmployeeNumber' as UniqueID

I've tried a condition after the Get items that says if 'EmployeeNumber' (Excel) ne 'EmployeeNumber' (SP)

Delete else Update

I've tried Update else Create

I just can't get the configuration right.

Preference is to limit recycle bin clutter; Maybe overwrite with delete if not in Excel, create if not in SP (with the overwrite eliminating what would have needed to be deleted (maybe🙄).

[lbendlin]

Both Excel and SharePoint have 'EmployeeNumber' as UniqueID

I've tried a condition after the Get items that says if 'EmployeeNumber' (Excel) ne 'EmployeeNumber' (SP)

These statements are mutually exclusive.

The idea to use the Employee ID as the primary key is correct.  But then you have to consider a lot of cases

- new employee id in Excel - needs to be added to Sharepoint list

- employee id no longer in Excel - needs to be removed from SharePoint list

- some other field is different between the two tables - field needs to be updated in sharepoint

This makes the brute force flush and fill sound like a better idea... if you can stomach the cost (history, recycle bin).  Trying to do it "the right way"  as listed above involves a lot (A LOT) more work, and likely will take longer.

[Phineas]

Okay, I'll table my preference.

How do I structure the dump and rebuild flow?

My attempts have mass produced the wrong items, replicated the right items unnecessarily, or do nothing at all.

All I have in the flow at this point is -
Recurrence - 24 hour
List of rows present in table (Excel dataset)
Get items (contents - at this point, only headers that match the Excel)

[lbendlin]

1. get items from SP list

2. apply to each - delete item (set parallelism to 50)

3. get rows from Excel

4. apply to each - add SP item from Excel row.

[Phineas]

I apologize, visual I cannot understand what you recommend.

Have you an example from flow?

I have -
Recurrence
Get items (SharePoint)
List rows present in a table

When I get to Appy I use the Excel Value as the 'output', I place a 'Create item', but I can't see the Excel columns.

[grantjenkins]

Quick question - will the Excel file you receive via email contain a Table with the data, or just data and no Table defined?

[Phineas]

The 'Proof-of-concept' dataset was provide as a table. My assumption is future overwrites to the Excel file will also be formatted as table, though I will need to make that clear to the client.

That said, I'd like to hear your thoughts regarding the issue in either scenario, if possible.