Robert Parker wrote:
I have two lists of numbers, which are both the primary keys/serial numbers of contacts from the same contact DB. These lists can be placed into globals or even made to be unique fields in unique records if need be, but the REAL "thing" I'm wanting to do is figure out fastest way to spot any SNs that are on BOTH lists. This would then become a THIRD list of primary keys/SNs.
Without knowing more details about your solutions, you can likely use modified versions of either of these techniques to do what you are looking for.
Well THAT was fast - Thanks Steve!
I'll check them out and let you know what happens.
Both the articles discuss how to find duplicate records in the SAME table, which isn't what I'm looking for.
I'm taking two lists of serial numbers and seeing if any of the numbers from one list are on the other. I then take various actions depending on whether they are are are not on both lists.
I suspect I'll be looping through the smaller of the two lists, parsing out each list-item and then looking to see if it exists on the OTHER list. There are several ways to do this but I was hoping for a quick and dirty trick, function, script or some other way to compare two lists faster.
Create a relationship where A::ID = B::ID
In either table, search for a non-empty value ("*") in the related ID field.
This works when there are TWO tables, but as I said, I'm in the SAME table with two lists that, for the sake of argument here, are both in GLOBAL fields.
Anyone else have any ideas?
FilterValues ( list1 ; list 2 )
FilterValues ( gField1 ; gField2 )
returns the values that are on both lists.
If your lists of IDs are relatively short, Ray Cologon's excellent XORValues ( ListA ; ListB ) function , which can be found at https://www.briandunning.com/cf/39 may be useful to you.
At first glance, you might think that the UNIQUE list of values between your two lists is exactly the opposite of what you are looking for. But, if you know what is unique between the two lists, you can also derive what is in common.
Let ( [
ListA= List ( "apples" ; "bananas" ; "cherries" ; "dragon fruit" ; "etrog" ) //
; ListB= List ( "figs" ; "grapes" ; "honeydew melon" ; "bananas" ; "etrog" ) //
; combinedUniqueIDs= XORValues ( ListA ; ListB ) // Gives a,c,d,f,g,h
; Remove_fromA = FilterValues ( ListA ; combinedUniqueIDs ) // find everything in A that is in the combined unique set: a,c,d
; Keep_fromA= XORValues ( ListA ; Remove_FromA ) // b,e
/* included for example purposes, but the work is actually already done
; Remove_fromB = FilterValues ( ListB ; combinedUniqueIDs ) // find everything in B that is in the combined unique set: f,g,h
; Keep_fromB= XORValues ( ListB ; Remove_FromB ) // b,e
Just another way to fo this without using any relationships. The limits on custom functions (around 10,000 iterations) would mean that you can only work with a combined data set of less than that many items.
Also, Ray set up a ZapValues function http://www.briandunning.com/cf/193 which can probably substitute for my remove_from and keep_from above.
-- Drew Tenenholz
Or, yes, the much simpler FilterValues (ListA; ListB ) ....over thinking again.....
XORValues is still really useful, just not here.
The FilterValues did the trick!