My database has 1000 records. Each record has 8 number fields. My aim is to find all records with 3 equal numbers.

My database has 1000 records. Each record has 8 number fields. My aim is to find all records with 3 equal numbers.

For clarification please be very specific in your needs.

Your table has 8 Number fields

numA

numB

numC

numD

numE

numF

numG

Record 1 has

numA =

**7.2**numB = 6.5

numC =

**7.2**numD = 3.8

numE = 9.67

numF =

**7.2**numG = -16

Record 2 has

numA = 0

numB = 12

numC = 31.22

numD = 47

numE = 16

numF = 7.2

numG = 9

Record 3 has

numA =

**8**numB =

**8**numC = 49

numD =

**8**numE =

**8**numF = 7.2

numG = 9

You want to do a search and bring up Record 1 and Record 3 as a Found set containing these 2 records.

Is this correct?

-Tom

You want to locate records that have the same value in the field numA on at least 3 different records.

You want to do the same for each of the 8 fields.

In my example records #2 and #3 share the value "9" in field numG, but since there are only two records and not three, I didn't expect that they would fit your criteria.

If you do want to find two or more records with the same value in the same field, you can search for "!" in that field. It is a special character meaning to find duplicates. After searching, sort by that field to see the matches as neighboring records.

I expect locating 3 or more duplicates and omitting simple 2-record duplicates will require adding additional calculation fields, related tables, and/or scripting.

-Tom

Ok. I think we're getting somewhere.

All of my interpretations so far were misunderstandings.

You have 8 number fields. You want to treat them as a set of numbers. You don't care where the values are in any of the 8 fields.

(Note that this data structure is usually represented as a set of related records in another table instead of distinct fields on a single record.)

You want to locate any record that has in common with another record at least 3 number values.

That's a bit tricky. But, as you have seen, defining the problem is a significant part of the work.

-Tom

I think I would create an additional calculation field and and search on it.

GetAsBoolean ( position (

substitute ( sortValues (

list ( field1 ; field2 ; field3 ; field4 ; field5 ; field6 ; field7 ; field8 ) ; 2 ) ;

[ ¶ ; "" ] ;

[ "000" ; "BINGO" ] ;

[ "111" ; "BINGO" ] ;

[ "222" ; "BINGO" ] ;

[ "333" ; "BINGO" ] ;

[ "444" ; "BINGO" ] ;

[ "555" ; "BINGO" ] ;

[ "666" ; "BINGO" ] ;

[ "777" ; "BINGO" ] ;

[ "888" ; "BINGO" ] ;

[ "999" ; "BINGO" ]

) ; "BINGO" ; 0 ; 1 ))

Sometimes a solution can take advantage of a feature of the data.

Are these number fields restricted to positive integers, i.e. counting numbers?

Are these numbers within a specific range?

Are all 8 number fields guaranteed to be filled in with a valid number, i.e. not empty?

What are the technical options for the solution?

Can you add new fields to the table?

Can you add scripts?

Are you using FileMaker Pro Advanced which allows for custom functions?

Can you use a FileMaker plug-in?

Do you want the end result in a Found Set or do you want a list of unique record IDs?

-Tom

A brute search using a looping script (two nested loops) would be one way.

(outer loop) Go to the first record and memorize its 8 values into script variables.

(inner loop) loop over all 1000 records and compare the values on that record to the 8 memorized values.

(resume outer loop) Go to the next record and memorize its 8 values

...

OR

You can create a new, calculated field to use as a relationship key that contains all combinations of the 3 from 8 situation with one combination per line and with each combination in numeric order.

This should be 56 lines = 8!/(3!*(8-3)!)

It will be a tedious calculation.

Then make a self-join relationship to match that key.

The related records should be these 3 of a kind matches.

List(RelatedTable::id)

will give you a list of the IDs.

Here's a brute-force calculation that will produce the value 1 if you get

**at least**3 values in any one record to match each other, in case that's all you need. Note, however, that it won't exclude any case where 4 or more values match.Case (

Num A = Num B and Num A = Num C; 1;

Num A = Num B and Num A = Num D; 1;

Num A = Num B and Num A = Num E; 1;

Num A = Num B and Num A = Num F; 1;

Num A = Num B and Num A = Num G; 1;

Num A = Num B and Num A = Num H; 1;

Num B = Num C and Num B = Num D; 1;

Num B = Num C and Num B = Num E; 1;

Num B = Num C and Num B = Num F; 1;

Num B = Num C and Num B = Num G; 1;

Num B = Num C and Num B = Num H; 1;

Num C = Num D and Num C = Num E; 1;

Num C = Num D and Num C = Num F; 1;

Num C = Num D and Num C = Num G; 1;

Num C = Num D and Num C = Num H; 1;

Num D = Num E and Num D = Num F; 1;

Num D = Num E and Num D = Num G; 1;

Num D = Num E and Num D = Num H; 1;

Num E = Num F and Num E = Num G; 1;

Num E = Num F and Num E = Num H; 1;

Num F = Num G and Num F = Num H; 1

)

Edit: You clarified the situation in the post above as I was writing this one.

Original post:

Again, I must be misunderstanding the problem you are solving.

Do you require a single record to have 3 of the 8 fields with the same value? Record #10 = (1 2

**3**5**3**7**3**4)I didn't think that was needed.

I thought you wanted three separate records to have the same 3 values present among their 8 fields.

Record#1

**1**3

5

7

9

**11****13**15

Record #2

**1**2

4

6

8

**11****13**17

Record #3

**1****11****13**20

25

26

28

30

I will add only that, instead of the value "1" as the result of each true condition, you could have entered the first value on that line to find out what the triplicated value was. For example, instead of

Num B = Num C and Num B = Num E; 1;

you could have put

Num B = Num C and Num B = Num E; Num B;

Of course, then you'd need a pair of empty quotation marks ("") as the final option of the Case statement if it failed to meet any of the preceding criteria.

But now I see, after reading further downthread, that you really want to compare values

**across**records and not merely**within**one. I regret that the above formula won't do that job for you.If you have only 3 fields then that is fine but there are 8.

Here is a brute force calc that should do it.

let ( _values = "|," & field1 & "|," & field2 & "|," & field3 & "|," & field4 & "|," & field5 & "|," & field6 & "|," & field7 & "|," & field8 & "|," ;

patterncount ( _values ; ",0|" ) >= 3 or

patterncount ( _values ; ",1|" ) >= 3 or

patterncount ( _values ; ",2|" ) >= 3 or

patterncount ( _values ; ",3|" ) >= 3 or

patterncount ( _values ; ",4|" ) >= 3 or

patterncount ( _values ; ",5|" ) >= 3 or

patterncount ( _values ; ",6|" ) >= 3 or

patterncount ( _values ; ",7|" ) >= 3 or

patterncount ( _values ; ",8|" ) >= 3 or

patterncount ( _values ; ",9|" ) >= 3 or

patterncount ( _values ; ",10|" ) >= 3 or

patterncount ( _values ; ",11|" ) >= 3 or

patterncount ( _values ; ",12|" ) >= 3 or

patterncount ( _values ; ",13|" ) >= 3 or

patterncount ( _values ; ",14|" ) >= 3 or

patterncount ( _values ; ",15|" ) >= 3 or

patterncount ( _values ; ",16|" ) >= 3 or

patterncount ( _values ; ",17|" ) >= 3 or

patterncount ( _values ; ",18|" ) >= 3 or

patterncount ( _values ; ",19|" ) >= 3 or

patterncount ( _values ; ",20|" ) >= 3 or

patterncount ( _values ; ",21|" ) >= 3 or

patterncount ( _values ; ",22|" ) >= 3 or

patterncount ( _values ; ",23|" ) >= 3 or

patterncount ( _values ; ",24|" ) >= 3 or

patterncount ( _values ; ",25|" ) >= 3 or

patterncount ( _values ; ",26|" ) >= 3 or

patterncount ( _values ; ",27|" ) >= 3 or

patterncount ( _values ; ",28|" ) >= 3 or

patterncount ( _values ; ",29|" ) >= 3 or

patterncount ( _values ; ",30|" ) >= 3 or

patterncount ( _values ; ",31|" ) >= 3 or

patterncount ( _values ; ",32|" ) >= 3 or

patterncount ( _values ; ",33|" ) >= 3 or

patterncount ( _values ; ",34|" ) >= 3 or

patterncount ( _values ; ",35|" ) >= 3 or

patterncount ( _values ; ",36|" ) >= 3 or

patterncount ( _values ; ",37|" ) >= 3 or

patterncount ( _values ; ",38|" ) >= 3 or

patterncount ( _values ; ",39|" ) >= 3 or

patterncount ( _values ; ",40|" ) >= 3 or

patterncount ( _values ; ",41|" ) >= 3 or

patterncount ( _values ; ",42|" ) >= 3 or

patterncount ( _values ; ",43|" ) >= 3 or

patterncount ( _values ; ",44|" ) >= 3 or

patterncount ( _values ; ",45|" ) >= 3 or

patterncount ( _values ; ",46|" ) >= 3 or

patterncount ( _values ; ",47|" ) >= 3 or

patterncount ( _values ; ",48|" ) >= 3 or

patterncount ( _values ; ",49|" ) >= 3 or

patterncount ( _values ; ",50|" ) >= 3

)

Here's a brute-force calculation that will produce the value 1 if you get

at least3 values in any one record to match each other, in case that's all you need. Note, however, that it won't exclude any case where 4 or more values match.Case (

Num A = Num B and Num A = Num C; 1;

Num A = Num B and Num A = Num D; 1;

Num A = Num B and Num A = Num E; 1;

Num A = Num B and Num A = Num F; 1;

Num A = Num B and Num A = Num G; 1;

Num A = Num B and Num A = Num H; 1;

Num B = Num C and Num B = Num D; 1;

Num B = Num C and Num B = Num E; 1;

Num B = Num C and Num B = Num F; 1;

Num B = Num C and Num B = Num G; 1;

Num B = Num C and Num B = Num H; 1;

Num C = Num D and Num C = Num E; 1;

Num C = Num D and Num C = Num F; 1;

Num C = Num D and Num C = Num G; 1;

Num C = Num D and Num C = Num H; 1;

Num D = Num E and Num D = Num F; 1;

Num D = Num E and Num D = Num G; 1;

Num D = Num E and Num D = Num H; 1;

Num E = Num F and Num E = Num G; 1;

Num E = Num F and Num E = Num H; 1;

Num F = Num G and Num F = Num H; 1

)