Searching for any reccord that has the same information in 2 firelds

[AJT]

Hello.

I am creating a database that contains peoples birthdays. In each of the reccords there is a field for their Date of Birth, and for the current date.

I have created two more fields that will take the day and month out of the above fields.

so, i am left with

The field "Birthday" with "20/4" in it.

The field "Current Day plus Month" with "30/8" in it.

i am trying to perform a find that will list any reccords that has the same date in the "Birthday" and the "Current Day plus Month" fields.

(This has to be done using a script)

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

so, i currently have:

Perform Find [Restore] (Table::Current Day plus Month: [==Birthday])

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

But this is comming back with nothing (i have set the birthday of a person to be the same as today's date)

-- The reason i have put it into "20/4" insted of the full date is because the year will not match!

Please help me!

Edited August 30, 2006 by Guest

[Lee Smith]

Search for a date range.

01/01/2004..12/31/2004

Lee

[AJT]

I would do that, but i always want it to be today's date - i don't want to type in the date every day to see if it is anyone's birthday.

The field i have will update itself with todays date.

[comment]

In version 8 and above, you can use wildcards in searching for dates. To find all people whose birthday is today, you can search for "8/30/*".

Or, by a script (finds people who share a birthday with current contact)

Set Variable [ $bDay; Value:Substitute ( Contacts::Birthdate ; Year ( Contacts::Birthdate ) ; "*" ) ]

Enter Find Mode [ ]

Set Field [ Contacts::Birthdate; $bDay ]

Perform Find [ ]

No need for any extra calculation fields for this.

(If anyone wonders at the variable calc - this should work no matter what your date settings are.)

I didn't get the bit with the "Current Day plus Month". If by chance you are looking for people whose birthday is in the upcoming month, then you WILL need a calculation field (result is Number) =

Month ( Birthdate ) + Day ( Birthdate ) / 100

then you can search this field for a range, e.g. "8.30 .. 9.29".

Note that Perform Find [Restore] does not work with dynamic criteria. Use a variable and Set Field[], as shown above.

Edited August 30, 2006 by Guest

[Lee Smith]

Sorry, I miss read what you are wanting.

Create a calculation field with the

Case ( Get ( CurrentDate ) = Date ( Month ( Birthday ) ; Day ( Birthday ) ;Year ( Get ( CurrentDate ) ) ); "Happy Birthday")

Lee

p.s.

I got interrupted and then posted this without looking. I see the comment has posted a response, but I'll leave it anyway, so that you can see a second approach.

Edited August 30, 2006 by Guest
p.s.

[AJT]

Okay, thanks for your replies.

but, I don't think that either of your replies have quite got it.

----

Basically, is there a way of performing a find that will give me all the reccords where field "A" has exactally the same value as field "B"

But if Field "A" has a differet value to field "B" , it will ignore it.

----

EG. if "A" has '123' in it and "B" has '123' in it, it will be displayed.

if "A" has '123' in it but "B" has 'HG' in it, it will be ignored.

[both A and B are on the same reccord]

[Lee Smith]

You probably want to use the exact function.

Search for [color:blue] Exact function in the FileMaker Online Help.

Lee

[Ender]

You just need to define a calc that determines if the two source fields are a match:

SameValues (calculation, number result) = field1=field2

Use the Exact() function only if you need the test to be case sensitive:

SameExactValues (calculation, number result) = exact(field1; field2)

Then to find those records that have the same value in the source fields, perform a Find for a 1 in that field.

[Ender]

Note that this is not an efficient way to find birthday for the current date. The problem is that your current date field would either need to be populated for all records every day, or it would be an unstored calc and be kind of slow to evaluate.

A better solution is to use a script to perform a wildcard find (FM8 and later)

Enter Find Mode[]

Set Field [ Date of Birth; month(get(currentdate)) & "/" & day(get(currentdate)) & "/*" ]

Perform Find []

or store the current month/day in a global field and use a self-join to the record's month/day. Then a Go to Related Records[] would jump to any related records with a matching birthday.

Edited August 30, 2006 by Guest

[comment]

The way I understood your question, Field A has the same value for all records. In such case, it is inefficient to look for records where Field A = Field B. It's much simpler and faster to search Field B for the A value. Or, as Ender says, define a relationship based on Field A = Field B. If Field A is the current date (or a derivation of current date), it can be an unstored calculation - so all you need is a GTRR step.

[AJT]

Thanks Lee, Ender & Comment!

I took Lee's advice, and it worked! but, i'm sure Enders or Comments would have done aswell.

Regards,

Edited August 30, 2006 by Guest