Salesforce and other SMB Solutions are coming soon. ×

# How to match 3 numbers in any 15 numbers from 50? ## Recommended Posts  My database have 5 fields - A,B,C,D, E

These fields contain numbers from 1 to 50 in combinations of 5 numbers in each record,

Example; First Record: 2-21-22-45-47

Record 1100; 14-19-32-34-40

and so on

My aim: In Find Mode, enter 15 numbers to get all records with 3 numbers ( from those 15 numbers)  that match

Thank you very much for your help

##### Share on other sites  I have this calculation:

Let([
~N=List(
Primeira Posição; ( Field A)
Segunda Posição; (Field B)
Terceira Posição; ..........
Quarta Posição;
Quinta Posição;
);
~S="ABCDE";
];
Let([~i=1;
~j=GetValue(~P;~i);
~A=GetValue(~N; Position(~S;Middle(~j;1;1);1;1));
~B=GetValue(~N; Position(~S;Middle(~j;2;1);1;1));
~C=GetValue(~N; Position(~S;Middle(~j;3;1);1;1));
~mx=Max(~A;~B;~C);
~mn=Min(~A;~B;~C)];
~mn&" "&Substitute("|"&~A&"|"&~B&"|"&~C&"|";["|"&~mx&"|";"|"];["|"&~mn&"|";"|"];["|";""])&" "&~mx)
&"¶"&
Let([~i=2;
~j=GetValue(~P;~i);
~A=GetValue(~N; Position(~S;Middle(~j;1;1);1;1));
~B=GetValue(~N; Position(~S;Middle(~j;2;1);1;1));
~C=GetValue(~N; Position(~S;Middle(~j;3;1);1;1));
~mx=Max(~A;~B;~C);
~mn=Min(~A;~B;~C)];
~mn&" "&Substitute("|"&~A&"|"&~B&"|"&~C&"|";["|"&~mx&"|";"|"];["|"&~mn&"|";"|"];["|";""])&" "&~mx)
&"¶"&
Let([~i=3;
~j=GetValue(~P;~i);
~A=GetValue(~N; Position(~S;Middle(~j;1;1);1;1));
~B=GetValue(~N; Position(~S;Middle(~j;2;1);1;1));
~C=GetValue(~N; Position(~S;Middle(~j;3;1);1;1));
~mx=Max(~A;~B;~C);
~mn=Min(~A;~B;~C)];
~mn&" "&Substitute("|"&~A&"|"&~B&"|"&~C&"|";["|"&~mx&"|";"|"];["|"&~mn&"|";"|"];["|";""])&" "&~mx)
&"¶"&
Let([~i=4;
~j=GetValue(~P;~i);
~A=GetValue(~N; Position(~S;Middle(~j;1;1);1;1));
~B=GetValue(~N; Position(~S;Middle(~j;2;1);1;1));
~C=GetValue(~N; Position(~S;Middle(~j;3;1);1;1));
~mx=Max(~A;~B;~C);
~mn=Min(~A;~B;~C)];
~mn&" "&Substitute("|"&~A&"|"&~B&"|"&~C&"|";["|"&~mx&"|";"|"];["|"&~mn&"|";"|"];["|";""])&" "&~mx)
&"¶"&
Let([~i=5;
~j=GetValue(~P;~i);
~A=GetValue(~N; Position(~S;Middle(~j;1;1);1;1));
~B=GetValue(~N; Position(~S;Middle(~j;2;1);1;1));
~C=GetValue(~N; Position(~S;Middle(~j;3;1);1;1));
~mx=Max(~A;~B;~C);
~mn=Min(~A;~B;~C)];
~mn&" "&Substitute("|"&~A&"|"&~B&"|"&~C&"|";["|"&~mx&"|";"|"];["|"&~mn&"|";"|"];["|";""])&" "&~mx)
&"¶"&
Let([~i=6;
~j=GetValue(~P;~i);
~A=GetValue(~N; Position(~S;Middle(~j;1;1);1;1));
~B=GetValue(~N; Position(~S;Middle(~j;2;1);1;1));
~C=GetValue(~N; Position(~S;Middle(~j;3;1);1;1));
~mx=Max(~A;~B;~C);
~mn=Min(~A;~B;~C)];
~mn&" "&Substitute("|"&~A&"|"&~B&"|"&~C&"|";["|"&~mx&"|";"|"];["|"&~mn&"|";"|"];["|";""])&" "&~mx)
&"¶"&
Let([~i=7;
~j=GetValue(~P;~i);
~A=GetValue(~N; Position(~S;Middle(~j;1;1);1;1));
~B=GetValue(~N; Position(~S;Middle(~j;2;1);1;1));
~C=GetValue(~N; Position(~S;Middle(~j;3;1);1;1));
~mx=Max(~A;~B;~C);
~mn=Min(~A;~B;~C)];
~mn&" "&Substitute("|"&~A&"|"&~B&"|"&~C&"|";["|"&~mx&"|";"|"];["|"&~mn&"|";"|"];["|";""])&" "&~mx)
&"¶"&
Let([~i=8;
~j=GetValue(~P;~i);
~A=GetValue(~N; Position(~S;Middle(~j;1;1);1;1));
~B=GetValue(~N; Position(~S;Middle(~j;2;1);1;1));
~C=GetValue(~N; Position(~S;Middle(~j;3;1);1;1));
~mx=Max(~A;~B;~C);
~mn=Min(~A;~B;~C)];
~mn&" "&Substitute("|"&~A&"|"&~B&"|"&~C&"|";["|"&~mx&"|";"|"];["|"&~mn&"|";"|"];["|";""])&" "&~mx)
&"¶"&
Let([~i=9;
~j=GetValue(~P;~i);
~A=GetValue(~N; Position(~S;Middle(~j;1;1);1;1));
~B=GetValue(~N; Position(~S;Middle(~j;2;1);1;1));
~C=GetValue(~N; Position(~S;Middle(~j;3;1);1;1));
~mx=Max(~A;~B;~C);
~mn=Min(~A;~B;~C)];
~mn&" "&Substitute("|"&~A&"|"&~B&"|"&~C&"|";["|"&~mx&"|";"|"];["|"&~mn&"|";"|"];["|";""])&" "&~mx)
&"¶"&
Let([~i=10;
~j=GetValue(~P;~i);
~A=GetValue(~N; Position(~S;Middle(~j;1;1);1;1));
~B=GetValue(~N; Position(~S;Middle(~j;2;1);1;1));
~C=GetValue(~N; Position(~S;Middle(~j;3;1);1;1));
~mx=Max(~A;~B;~C);
~mn=Min(~A;~B;~C)];
~mn&" "&Substitute("|"&~A&"|"&~B&"|"&~C&"|";["|"&~mx&"|";"|"];["|"&~mn&"|";"|"];["|";""])&" "&~mx)
)

My problem with this calculation: it dont allow me to search more than 5 numbers, My aim is to search same 3 match entering 15 numbers in the Find Mode

Edited by Plinio
##### Share on other sites  Something like this should work. Feel free to ask if you have questions. The key concept to this approach, is that a list of values will match on a list of values across a relationship as though they were separate records, each individual value is compared.

##### Share on other sites  The short version of where the "magic" happens is in the FilterValues function. You feed the function 2 lists. FilterValues will return all of the values that match between the 2 lists. You then just need to use ValueCount to verify there are more than three matches. The calculation would look something like:

`ValueCount ( FilterValues ( RuleOfThree::listNumbers ; \$listOfNumbersToMatchAgainst ) ) ≥ 3`

Here RuleOfThree:listNumbers is a calculation that transforms your 5 field values into a single list. There are a lot of ways to do this. I used a field just to demonstrate what you need, which is a list to use with FilterValues.

\$listOfNumbersToMatchAgainst would be your list of 15 numbers to compare each record against. It could be a global field, to take advantage of a relationship to reduce the list of matches, or a variable, or whatever. As long as it's a return-delimited list to use for FilterValues.

Edit: based on a back channel conversation, this should be compatible with an older version of FileMaker ( in this case, it may be version 8 ).

##### Share on other sites  I guess my issue is I didnt create a Value List

##### Share on other sites  59 minutes ago, Plinio said:

I guess my issue is I didnt create a Value List

You don't need a Value List ( as in FileMaker's Value List )... You just need a list of values, 2 of them, for the FilterValues function.

Make sure your Global table has a record in it. ( zero record tables and globals can have unintended behavior ).

Post screen shots of your tables, and Relationship graph ( just the portion for this table ). That will make it easier to point you in the right direction.

##### Share on other sites  ##### Share on other sites  Print Screen of RuleofThree Layout

##### Share on other sites  Print Screen of RukeofThree Relationship

##### Share on other sites  Print Screen of Globals Relatioship

##### Share on other sites  ##### Share on other sites  P Screen of List Numbers Calculation

##### Share on other sites  Print Screen of IsThreeMatch Calculation

##### Share on other sites  Print Screen of RuleofThree Browse

##### Share on other sites  ##### Share on other sites  It looks like you have the setup in place. Put in data. If you have an existing table of data, make the listNumber calculation field in that table and use those fields. Make the relationship between the globals table and your actual data table.

Then when you enter your list of 15 numbers to compare, the isThreeMatch field will tell you if three of the numbers are match.

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account. ×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.