How to match 3 numbers in any 15 numbers from 50?

[Plinio]

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

 

[Plinio]

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";
~P ="ABC¶ABD¶ABE¶ACD¶ACE¶ADE¶BCD¶BCE¶BDE¶CDE"
];
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 August 6, 2021 by Plinio

[Josh Ormond]

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.

RuleOfThree.zip

[Josh Ormond]

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

[Plinio]

I guess my issue is I didnt create a Value List

[Josh Ormond]

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.

[Plinio]

Print Screen of Globals Layout:

[Plinio]

Print Screen of RuleofThree Layout

[Plinio]

Print Screen of RukeofThree Relationship

[Plinio]

Print Screen of Globals Relatioship

[Plinio]

RuleofThree Fields

[Plinio]

P Screen of List Numbers Calculation

[Plinio]

Print Screen of IsThreeMatch Calculation

[Plinio]

Print Screen of RuleofThree Browse 

[Plinio]

Print Screen of Globals Browse

[Josh Ormond]

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.