cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lmheimendinger
Resolver IV
Resolver IV

Compare rows in two arrays when not all columns are identical

Here's the problem set:  A SQL database is maintained by a LOB application.  For one table, whenever a new row is added, I have a flow that is triggered which creates a corresponding row in a SharePoint list; that list has more columns than the SQL table for additional processing.  To add a bit, there is a corresponding SQL table containing rows that are related to the table above, linked by a key value.  Each of these records is also copied to another SharePoint list.

 

When the LOB application updates, as opposed to creating new records, it merely updates the row in the first SQL table but completely replaces all the rows in the second table.  Several conditions can result:

  • some of the rows in the second SQL table may match those in SP based on three column values; these would be considered as unchanged
  • some of the rows in the second SQL table may have no matches in SP based on the three columns; these would be considered to be new rows in SP.
  • some of the rows in the SP list may have no matches in the new SQL rows, based on the three fields; these would be considered as rows to be deleted

My approach was to use Filter Array between the two actions Get rows (for SQL) and Get items (for SharePoint).  Because there are column differences, contains or does not contain won't work as they will never match completely.

 

Here's what I hoped would work:  Use Filter Array following the Get rows and Get Items (the Get rows has a filter that only gets the rows associated with the changed row in the first table, and the Get items does something similar so the two arrays have just records that correspond the way the bullet points above describe; they are specific and related groupings).

 

I manually entered in advanced mode

 

@equals(outputs('Get_Line_Items_from_original_SO')?[colA'],),outputs('Get rows (V2) from SQL for all SO Line items')?['colA'])

 

and then tried to extend it by prefacing equals with @And, then adding a similar expression for equals using colB then a third for colC.

 

I keep getting formatting errors: Condition is too complex or invalid. Unable to switch to basic mode.

 

Am I on the right path?  Is this too much for Filter Array, and even if there is a format error, would the result of the Filter Array be the rows that match on the three columns?

 

Pulling my hair out over this so any help keeps me from going bald.  Thanks.

5 REPLIES 5
lmheimendinger
Resolver IV
Resolver IV

I have a hint on how to do this from some experimental testing.  For each array, use a Select action and define the three elements to compare.  Then filter on the two outputs of the select using contains (or does not contain).  The result of the Filter array will be an array that would be the result of comparing two identical arrays but now is based on the columns in the Select action.

 

Great, but I still need the many, many fields in the two almost identical column arrays, so I now need to figure out how to go from the Select reduced data back to the original arrays.  Maybe Apply to each?  I will see.

mahoneypat
Solution Sage
Solution Sage

I think I worked out one way to do this. 

  • Created two simple arrays - one with three columns (vArray1) and another (vArray2) with the same first two columns (ID and Category).
  • Initialize an array called vFieldConcat to hold the concatenated values of the fields to be used for comparison (ID and Category in this case)
  • Used an ATE to populate that array with the concatenated values from vArray2
  • Filtered vArray1 with this expression - 
    contains(variables('vFieldConcat'), concat(item()?['ID'], item()?['Category'])) is equal to true()
     
    mahoneypat_0-1635597961709.png

     

     

    Hopefully you can adapt this approach.

     

    Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about the Power Platform, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


 That may have effect as the two select actions I outlined in the reply to myself: it delivers a condensed array that can be compared, but then it seems I have no way to get back to the original array elements.  I need all those columns to take additional actions (i.e., create new items in SP).

 

What I am going to test today is to use the select and a filter array combination to get a new matched up array (or unmatched array) then use a ATE on that to get the rows/items that  I would need to update or delete.  It would be lovely if the Filter array would allow something like 

 

@And(equals('array1')['col1'],('array2')['col1]), equals('array1')['col2'],('array2')[col2']), equals('array1')['col3'],('array2')['col3'}))

 

because the execute time would be much faster than one or more ATE loops.  If this could be figured out, it would be a major find for me.

Note that in my suggestion it is the original array that is being filtered.  The concatenation allows you to compare 2+ columns to filter to where they match on those columns, and you can do what you need from there.

 

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about the Power Platform, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Finally getting back to working on this after some travels and other tasks.

 

Interestingly, I have gone down a similar path but have stumbled on getting farther.  Here's what I did:

 

  • Created an array variable with [] as it's initial value
  • I then walked through my SP list items and added those to the array where (in my case) the data value indicates that I want to keep the item data
  • I then removed all of the SP list items whether or not they were added to the array variable
  • I then walked through the SQL array of the new/changed/deleted items, creating a new SP list item for each one

What I want to do in that Apply loop is then look at the array and see if there is a matching value so I can, after creating a new SP list item, update the columns I want to carry  forward.  What I have not figured out is the syntax to do that.

 

First, the dynamic content only exposes the array variable name, not the column names in the array.  Do I reference those as varArray["colName"]?  Even more so, how do I "walk through the array rows inside an ATE loop walking through the SQL rows, meaning refer to the current SQL item column names inside the ATE?

I kinda ask the same questions if a Filter Array action is inside the ATE.  What is the syntax to refer to current row in ATE and the array variable?  Worse, I am uncertain if the Filter Array just returns an array of the compared values or the entire array?  I need all those other fields for a selected array row.

 

Hope this makes sense.

 

Helpful resources

Announcements

Super User of the Month | Drew Poggemann

As part of a new monthly feature in the Community, we are excited to share that Drew Poggemann is our featured Super User for the month of February 2024. If you've been in the Community for a while, we're sure Drew's name is familiar to you, as he is one of our most active contributors--he's been a Super User for five consecutive seasons!   Since authoring his first reply 5 years ago to his 514th solution authored, Drew has helped countless Community members with his insights and expertise. In addition to being a Super User, Drew is also a User Group leader and a Microsoft MVP. His contributions to our Super User sessions and to the new SUIT program are always welcome--as well as his sense of humor and fun-loving way of sharing what he knows with others.   When Drew is not solving problems and authoring solutions, he's busy overseeing the Solution Architecture team at HBS, specializing in application architecture and business solution strategy--something he's been doing for over 30 years. We are grateful for Drew and the amazing way he has used his talent and skills to help so many others in the Community. If you are part of the SUIT program, you got to hear some great tips from Drew at the first SUIT session--and we know he still has much more to share!You can find him in the Community and on LinkedIn. Thank you for all you do, Drew!

Announcing Power Apps Copilot Cookbook Gallery

We are excited to share that the all-new Copilot Cookbook Gallery for Power Apps is now available in the Power Apps Community, full of tips and tricks on how to best use Microsoft Copilot as you develop and create in Power Apps. The new Copilot Cookbook is your go-to resource when you need inspiration--or when you're stuck--and aren't sure how to best partner with Copilot while creating apps.   Whether you're looking for the best prompts or just want to know about responsible AI use, visit Copilot Cookbook for regular updates you can rely on--while also serving up some of your greatest tips and tricks for the Community. Our team will be reviewing posts using the new "Copilot Studio" label to ensure we highlight and amplify the most relevant and recent content, so you're assured of high-quality content every time you visit. If you share a post that gets featured in the curated gallery, you'll get a PM in the Community to let you know!The curated gallery is ready for you to experience now, so visit the new Copilot Cookbook for Power Apps today: Copilot Cookbook - Power Platform Community. We can't wait to see what you "cook" up!    

Celebrating a New Season of Super Users with Charles Lamanna, CVP Microsoft Business Applications

February 8 was the kickoff to the 2024 Season One Super User program for Power Platform Communities, and we are thrilled to welcome back so many returning Super Users--as well as so many brand new Super Users who started their journey last fall. Our Community Super Users are the true heroes, answering questions, providing solutions, filtering spam, and so much more. The impact they make on the Communities each day is significant, and we wanted to do something special to welcome them at our first kickoff meeting of the year.   Charles Lamanna, Microsoft CVP of Business Applications, has stressed frequently how valuable our Community is to the growth and potential of Power Platform, and we are honored to share this message from him to our 2024 Season One Super Users--as well as anyone who might be interested in joining this elite group of Community members.     If you want to know more about Super Users, check out these posts for more information today:    Power Apps: What is A Super User? - Power Platform CommunityPower Automate: What is A Super User? - Power Platform Community Copilot Studio: What is A Super User? - Power Platform Community Power Pages: What is A Super User? - Power Platform Community

Super Users 2024 Season One is Here!

   We are excited to announce the first season of our 2024 Super Users is here! Our kickoff to the new year welcomes many returning Super Users and several new faces, and it's always exciting to see the impact these incredible individuals will have on the Community in 2024! We are so grateful for the daily difference they make in the Community already and know they will keep staying engaged and excited for all that will happen this year.   How to Spot a Super User in the Community:Have you ever written a post or asked for help in the Community and had it answered by a user with the Super User icon next to their name? It means you have found the actual, real-life superheroes of the Power Platform Community! Super Users are our heroes because of the way they consistently make a difference in the Community. Our amazing Super Users help keep the Community a safe place by flagging spam and letting the Community Managers know about issues. They also make the Community a great place to find answers, because they are often the first to offer solutions and get clarity on questions. Finally, Super Users share valuable insights on ways to keep the Community growing, engaging, and looking ahead!We are honored to reveal the new badges for this season of Super Users! Congratulations to all the new and returning Super Users!     To better answer the question "What is a Super User?" please check out this article: Power Apps: What is A Super User? - Power Platform CommunityPower Automate: What is A Super User? - Power Platform Community Copilot Studio: What is A Super User? - Power Platform Community Power Pages: What is A Super User? - Power Platform Community

Did You Attend the Microsoft Power Platform Conference in 2022 or 2023? Claim Your Badge Today!

If you were one of the thousands of people who joined us at the first #MPPC Microsoft Power Platform Conference in 2022 in Orlando--or attended the second-annual conference in Las Vegas in 2023--we are excited to honor you with a special community badge! Show your support for #MPPC Microsoft Power Platform Conference this year by claiming your badge!           Just follow this link to claim your badge for attending #MPPC in 2022 and/or 2023: MPPCBadgeRequest    Want to earn your badge for 2024? Just keep watching our News & Announcements for the latest updates on #MPPC24.

Microsoft Power Platform | 2024 Release Wave 1 Plan

Check out the latest Microsoft Power Platform release plans for 2024!   We have a whole host of exciting new features to help you be more productive, enhance delegation, run automated testing, build responsive pages, and so much more.    Click the links below to see not only our forthcoming releases, but to also try out some of the new features that have recently been released to market across:     Power Apps  Power Automate  Copilot Studio   We can’t wait to share with you all the upcoming releases that will help take your Power Platform experience to the next level!    Check out the entire Release Wave: Power Platform Complete Release Planner 

Users online (2,967)