Jump to content
Salesforce and other SMB Solutions are coming soon. ×

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


Plinio
 Share

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

 

Link to comment
Share on other sites

Posted (edited)

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 by Plinio
Link to comment
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.

RuleOfThree.zip

Link to comment
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 ).

Link to comment
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.

Link to comment
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. 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share



×
×
  • Create New...

Important Information

Terms of Use