cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User
Super User

Diff two dynamic arrays?

Hello,

Hoping someone can assist with this one. 

Scenario:

  • Table A is in a database; it is the source-of-truth
    • Contains two columns: SupervisorNumber and EmployeeNumber
    • Each SupervisorNumber can have 1 or more EmployeeNumbers
  • Table B is a Sharepoint list and must be updated once a day
    • Table B has more than just SupervisorNumber and EmployeeNumber columns
  • There is no common ID column or Primary/Foreign key relationship
  • Concatenating SupNum and EmpNum would always be a unique value

Goal:

  • If a record in Table A is created, the same record must be created in Table B
  • If a record in Table A is deleted, the same record in Table B must be deleted
  • If a record in Table A is edited, the same record in Table B must be edited

I've tried all kinds of things to no avail. Any ideas?
Thank you

4 REPLIES 4
Community Support Team
Community Support Team

Re: Diff two dynamic arrays?

 

Hi @ericonline ,

 

Whether there is a column in Table A and Table B can establish a relationship. Preferably, the values of the two fields are equal and non-null have unique values. For example, a specified field in Table A can be used to filter out the specified rows in Table B.

If there is such a field, then you can use Filter Query to filter out whether there is a corresponding field in another table. If there is, you can choose to update or delete the corresponding row; if not, create a new row.

Hope it makes sense.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: Diff two dynamic arrays?

Hi @v-bacao-msft ,

Which action are you referring to ("Filter query")? I don't see it in Flow.

image.png

 

Super User
Super User

Re: Diff two dynamic arrays?

Banging my head against this one still! 

Here is what I have so far:

image.png

  • "SELECT_" statements:
    • Are used to created a unique key by concatenating the two columns in question
    • Example:
      • image.png
      • image.png

@RezaDorrani , @yashag2255 , @Pstork1  any insights?

seadude
Level 10

Re: Diff two dynamic arrays?

Whew! Finally figured a method out. I don't think it will scale beyond a few thousand records but...

Its pretty tough to explain. I'd like to attach a sample Flow that shows the process in action, but I don't see a way to attach a file here. I spent some time reviewing Set Theory to break things down to the basics (sometimes I make things too complicated!) before approaching this again.

In a nutshell:

- Create common keys that can be used to compare records in the database and the Sharepoint list

- Use intersection to find the records in common between the two tables

- Use union to get all records from the intersection and the database ("source of truth")

- Use two loops:

  - For each "source of truth" record, if the sharepoint record is not present, append to "Add to Sharepoint" array

  - For each sharepoint record, if the "source of truth" is not present, append to "Remove from Sharepoint" array

- From there, it'll be up to you to determine how to create and delete records in the sharepoint list.

Hope to hear from some of you on how you would/could handle this as well.

Helpful resources

Announcements
firstImage

Power Platform Online Conference

Speakers, submit your sessions now! Call for speakers ends Feb. 10!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (5,153)