6 Replies Latest reply on Jun 17, 2012 6:46 PM by rtgauss

Find all records with identical value in field A and field B; copy this value into field C. – Complex Find, Script, or Calculation?

I've got a database with fields A, B, C, D, E…

Some records have the same value in A and B.

I'd like to find all these records (and then copy these values into C.)

Can I obtain this with a (more or less) simple find, like 'find all records with (value)A = (value)B)'?

If not, is there another approach?

Thanks for help

Jurgen

• 1. Re: Find all records with identical value in field A and field B; copy this value into field C. – Complex Find, Script, or Calculation?

Filemaker does not perform calculations during find, so in order to find records where A = B, you must either add a calculation field (result is Number) =

A = B

and search for 1 in this field, or show all records and loop among them, omitting each record that does not meet the criteria.

jobemay wrote:

I'd like to find all these records (and then copy these values into C.)

This part is not quite clear. Prhaps all you need to do is make C a calculation field =

Case ( A = B ; A )

This way you don't need to find anything, it's already done.

• 2. Re: Find all records with identical value in field A and field B; copy this value into field C. – Complex Find, Script, or Calculation?

Michael Horak wrote:

Filemaker does not perform calculations during find, so in order to find records where A = B, you must either add a calculation field (result is Number) =

A = B

and search for 1 in this field, or show all records and loop among them, omitting each record that does not meet the criteria.

Thanks Michael, I suppose this was the crucial message. I hoped to do it without a calculation field, maybe with a self-joining relationship, but I must admit I don't know much about it. Will give it a try anyway, and post it if there's a postable result.

Jurgen

• 3. Re: Find all records with identical value in field A and field B; copy this value into field C. – Complex Find, Script, or Calculation?

You could find the records with just a simple script:

---------------------------------------------------------------

Show All Records

Go to Record/Request/Page [First]

Loop

If [A ≠ B]

Omit Record

Go to Record/Request/Page [Next; Exit after last]

End If

End Loop

---------------------------------------------------------------

The remaining records left in the current found set are the records that A = B.

• 4. Re: Find all records with identical value in field A and field B; copy this value into field C. – Complex Find, Script, or Calculation?

That script will not work as advertised. There probably needs to be an ELSE after the Omit Record step.

• 5. Re: Find all records with identical value in field A and field B; copy this value into field C. – Complex Find, Script, or Calculation?

Your're, I went too fast.

Try:

----------------------------------------------------------

Show All Records

Go to Record/Request/Page [First]

Loop

If [A=B]

Go to Record/Request/Page [Next; Exit after last]

Else

Omit Record

End If

End Loop

• 6. Re: Find all records with identical value in field A and field B; copy this value into field C. – Complex Find, Script, or Calculation?

Sorry, going to fast.

Typo - should be not equal to:

Show All Records

Go to Record/Request/Page [First]

Loop

If [A≠B]

Go to Record/Request/Page [Next; Exit after last]

Else

Omit Record

End If

End Loop