list of unique combinations

[timben]

Trying to determine the way to show the  sequence of numbers in a different order. I have a data base with  UPC numbers that come as 4567890 or 6789405 + any other order but all have the same numbers. I am using find  but it will only identify duplicates, not if they are in a different sequence. Please explain to me the way this can be accomplished.

 

Tim

[eos]

Look at the Custom Function section at briandunning.com; there are several CFs to produce permutations.

[timben]

Thanks.

[LaRetta]

Well it won't take much.  Here is a simple custom function which will add together the Code() from all the characters in the string.  Then create a calculation of Coder ( yourString ) and search the calculation using !

 

CF name:  Coder

Parameters:  string

// adds each individual code value for all characters in the string specified

Let ( num = Code ( Left ( string ; 1 ) ) ;
num +
Case ( Length ( string ) ; Coder ( Right ( string ; Length ( string ) - 1 ) ) ; string )
)

So in your example above, both those strings return 375 so now they can match.  

[LaRetta]

Hey Tim, I also wish to welcome you to FMForums!!  This is the very best forum for FileMaker and all the folks here are really great!  Welcome!

 

And I notice you show only FMPro client ... if you don't have FMPA (the advanced version), you can't add a custom function.  If you need to insert the custom function into your solution, anyone with advanced can do it for you but it is best if you can purchase FMPA because you'll need it if you are performing any developer work at all.  Let me know if you want me to add it for you.

[timben]

Thanks LaRetta!!!

                              I was thinking I could put  a script in the find section to do this. Is it Possible?. If not I will purchase the advance version.

[LaRetta]

If you wish to know the code number of each record in your table then you need to use custom function with recursion to loop the characters and add them together or, if your strings are always exactly the same length then you can use regular calculation translating code of each character separately similar to:

 

Code ( Middle ( string ; 1 ; 1 ) ) +

Code ( Middle ( string ; 2 ; 1 ) ) +

Code ( Middle ( string ; 3 ; 1 ) ) +

 

.... etc.  But this will work ONLY if the number of characters is static.  And the problem is, out of sight out of mind so if your SKUs change length a year from now, you won't remember that this single calculation depends upon set length and it will break; best not to trust the 'it will never change' premise ... it ALWAYS changes.

 

It might also be possible to use ExecuteSQL() instead of the custom function but I would suspect it would be slower in this instance.  Maybe some SQL experts can make a suggestion here.

 

No matter how you achieve the calculation, only a calculation (which evaluates each record individually) can compare one record to all other records in a table and then script can find the duplicates for you.  Overall, I recommend purchasing FMPA; you will not regret it one bit as it will be the most important FileMaker investment you make.

[Raybaudi]

You could also try this calculation, instead of a custom function:

Evaluate(
Substitute ( UPC 
; [ 1 ; "+1" ]
; [ 2 ; "+2" ]
; [ 3 ; "+3" ]
; [ 4 ; "+4" ]
; [ 5 ; "+5" ]
; [ 6 ; "+6" ]
; [ 7 ; "+7" ]
; [ 8 ; "+8" ]
; [ 9 ; "+9" ]
; [ 0 ; "+10" ]
)
) & "-" & Filter ( 1234567890 ;  UPC )

 
So in your example above, both those strings return 49-4567890 so now they can match.

 

Edit: summing the individual numbers or their code, can lead to false positives.

Try for example:

1111222 and 1111114

0001111 and 0000022.

So that LaRetta's solution may give erroneous results.

[LaRetta]

Good catch, Daniele.

[Raybaudi]

Thank you, LaRetta.

 

I realized that even my solution made that error, so I added the Filter( ) part.

[comment]

This is a strange request: under what circumstances would 4567890 be considered a "duplicate" of 6789405? It's certainly not true with genuine UPC numbers.

Anyway, generating all permutations (not combinations) of a given set of digits is hardly the best way to proceed: in your example, you would need to generate 5040 (the factorial of 7) different permutations of the code you are searching for. It would be better to go with LaRetta's suggestion and generate (in advance) a unique code for each given set of digits - regardless of their order.

For example, sorting the digits in ascending order - so that both 4567890 and 6789405 become 0456789 - would do the job. However, this is not quite simple to achieve when digits can be repeated within a set, e.g. 1212120.

A better option, IMHO, is to think of "duplicates" not as "containing the same set of digits" but rather as "containing the same amount of each digit". Then one could do something simple like =

PatternCount ( text ; "0" ) &
PatternCount ( text ; "1" ) &
PatternCount ( text ; "2" ) &
PatternCount ( text ; "3" ) &
PatternCount ( text ; "4" ) &
PatternCount ( text ; "5" ) &
PatternCount ( text ; "6" ) &
PatternCount ( text ; "7" ) &
PatternCount ( text ; "8" ) &
PatternCount ( text ; "9" ) 

to generate the required code.


---
P.S. I'm not back yet - this just caught my eye in passing...

[Raybaudi]

 

P.S. I'm not back yet - this just caught my eye in passing...

 

... but I'm very happy that you came back and visit us.

 

P.S.: why quoted numbers ?

 

P.P.S.: why no more power of ten ? ( 1111111 and 2222222 return the same PatternCount ) Now I see ( & instead of + )

[LaRetta]

.

 

P.P.S.: why no more power of ten ? ( 1111111 and 2222222 return the same PatternCount )

 

not quite, Daniele, 2222222 returns 0070000000 and 1111111 returns 0700000000, at least when I plug them in.    

 

ADDED:  Were you sorting the digits?

[Raybaudi]

Yes, i didn't see that comment changes also + with &.

I do not understand your last question ?

[LaRetta]

I asked if you were sorting the resulting digits in which case they would match; I thought that might be how you achieved them 'matching' since it differed from my results.