How to check for existing ID in Excel and prevent duplicates
I am making a flow that essentially takes an Excel document that is regularly uploaded to a OneDrive for Business folder, get the rows, and insert it into a master list, which is just another excel file. These rows then move on to create specific tasks within Planner.
This part works great. However there is a possibility that the regularly uploaded file to OneDrive will contain a row that already exists in the master list. There's a unique ID (job number) field that will exist that I want to put a condition to check against, but I cannot figure out how to do this.
I want to check if the 'Job Number' from the file already exists in the master list. If it does, no task should be created, if it does not then proceed to the 'Create a task' step.
I've tried with Filter Array and Conditions, but I do not seem to be able to use those to properly compare the 'Get Rows' to the actual file I'm inserting the rows into.
I've done something similar. Here are some picture to show what I successfully implemented.
Method is to
Initialize a boolean Check variable
Get the rows of the new file
Get the rows of the Master File
Compare each potentially new file record to every record in the Master file
If there is no match, then add new record to the master, if there is a match, do nothing
(Mine is linked to a SQL server and planner, so ignore the icons, but it would be GetRows1 and GetRows2 for you)
Be sure to set the boolean Check variable to false at the start of each New File record iteration
Then the Job IDs between the two files would be compare, if there is a match, the CheckExists variable is set to TRUE
Finally, after each Master row is checked, there is a final Condition. If the CheckExist is false, add it to the Master
This solution for keeping my Team's Planner is sync with a SQL Server DB/PowerBI dashboard has worked great for me. I would like to not that we don't need real-time updates for my team, I just have it running four times a day and we have about ~1000 tasks so far.
If you're doing 100,000s of rows between two excel files and need something like this updated hourly there are probably better solutions.