Plinio Posted August 6, 2021 Share Posted August 6, 2021 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 Quote Link to comment Share on other sites More sharing options...
Plinio Posted August 6, 2021 Author Share Posted August 6, 2021 (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 August 6, 2021 by Plinio Quote Link to comment Share on other sites More sharing options...
Josh Ormond Posted August 8, 2021 Share Posted August 8, 2021 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 Quote Link to comment Share on other sites More sharing options...
Josh Ormond Posted August 9, 2021 Share Posted August 9, 2021 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 ). Quote Link to comment Share on other sites More sharing options...
Plinio Posted August 10, 2021 Author Share Posted August 10, 2021 I guess my issue is I didnt create a Value List Quote Link to comment Share on other sites More sharing options...
Josh Ormond Posted August 10, 2021 Share Posted August 10, 2021 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. Quote Link to comment Share on other sites More sharing options...
Plinio Posted August 10, 2021 Author Share Posted August 10, 2021 Print Screen of Globals Layout: Quote Link to comment Share on other sites More sharing options...
Plinio Posted August 10, 2021 Author Share Posted August 10, 2021 Print Screen of RuleofThree Layout Quote Link to comment Share on other sites More sharing options...
Plinio Posted August 10, 2021 Author Share Posted August 10, 2021 Print Screen of RukeofThree Relationship Quote Link to comment Share on other sites More sharing options...
Plinio Posted August 10, 2021 Author Share Posted August 10, 2021 Print Screen of Globals Relatioship Quote Link to comment Share on other sites More sharing options...
Plinio Posted August 10, 2021 Author Share Posted August 10, 2021 RuleofThree Fields Quote Link to comment Share on other sites More sharing options...
Plinio Posted August 10, 2021 Author Share Posted August 10, 2021 P Screen of List Numbers Calculation Quote Link to comment Share on other sites More sharing options...
Plinio Posted August 10, 2021 Author Share Posted August 10, 2021 Print Screen of IsThreeMatch Calculation Quote Link to comment Share on other sites More sharing options...
Plinio Posted August 10, 2021 Author Share Posted August 10, 2021 Print Screen of RuleofThree Browse Quote Link to comment Share on other sites More sharing options...
Plinio Posted August 10, 2021 Author Share Posted August 10, 2021 Print Screen of Globals Browse Quote Link to comment Share on other sites More sharing options...
Josh Ormond Posted August 11, 2021 Share Posted August 11, 2021 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.