Compare rows in two arrays when not all columns are identical

[lmheimendinger]

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.

[lmheimendinger]

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]

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()

  

  Hopefully you can adapt this approach.

  Pat

[lmheimendinger]

 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.

[mahoneypat]

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

[lmheimendinger]

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.