Efficient table comparison processing

More of an apiant best practices question. Have an automation that is syncing user related roles from one system to another. The way it needs to process is retrieve all the roles from one system, then retrieve all the roles from another. if it exists in the first and not the second, add to the second. if in the second and not the first, delete from the second. The question is how to most efficiently pass this through without multiple loops. i could pull the first, then loop through each record and see if it exists in the second but that would be an extra call for each record that probably isnt needed. ideally i would want to pull both lists, pull the unique values and only process those. thoughts?

Hi Brent,

Seems like the compare arrays action is what you need:

Help info:

“Compares the elements in two arrays, a left array and a right array. Emits two outputs: a “missing array” of values contained in the left array but missing in the right array, and an “extra array” of values contained in the right array but not in the left array. Duplicate values are emitted only once.”

Please continue to check with us when you have questions like this. Sometimes we build utility functionality that inadvertently doesn’t end up being installed to your systems.

sounds like exactly what i need. so how does the output of that look? does it give two lists? can it be looped?

It emits 2 arrays, one for missing values and one for extra values. Each array will only contain unique values.

perfect. i will give that a try. thanks

so im having issues with the compare action seeing the values as arrays. example below

i tried using the retrieved fields and trying to convert field to array and both appear to only pass in single values to the compare module. thoughts?

You can’t use the compare action within a loop like that b/c the outer loop controls array access for its scope.

So the arrays used as input have to be sent to the compare action outside of the context of a loop. One solution may be to put the compare logic in a subroutine, but the subroutine must be the one that emits the arrays used as input into the compare.

i assumed it was something related to the loop just wanted to confirm. thanks