Search Calculated dates - Newbe Question

[HawkLA]

I am new to Filemaker. I am setting up a Customer contact list, that has a calculated date field. I have the customer's birthday, and then I calculate the date 3 months, 6 months, 9 months, and year from that date.

How can I set up a report that will go through all my contacts and pull out just the calulated dates that happen this month? Can I set this up to automatically run and print a report on the first of every month for example?

HawkLA

[Osman]

you can use fallowing calculations;

for three months

= Date(month(Birth_Date) + 3; day(Birth_Date); year(Birth_Date))

for six months

= Date(month(Birth_Date) + 6 ; day(Birth_Date); year(Birth_Date))

for nine months

= Date(month(Birth_Date) + 9 ; day(Birth_Date); year(Birth_Date))

for a year

= Date(month(Birth_Date); day(Birth_Date); year(Birth_Date)+ 1)

[HawkLA]

That is what I did. The calulated dates show up on my form, but How do I do a search for the dates that come up this month, and then make a report?

HawkLA

[John Mark Osborne]

There is a much easier way to do this without adding any fields to your database. All you need is a simple script. Download this file from my web site.

http://www.filemakerpros.com/BDAY.zip

[HawkLA]

Maybe I am not explaining it well. I am a photographer who photographs babies every 3 months. I created a database that includes the field for birthday, then I made calulated fields that show me the date every 3 months that they should make additional appointments. All this works well and displays in my database.

My question is, How can I run a report each month that will show me who is due for a photo session that month? How can I automate this? I am fairly new to Filemaker, and I just modified one of the sample databases. I don't know anything about scripts or how to use Automator on my Mac. I just need to email or write my clients a couple of weeks before they should schedule a photo session.

HawkLA

[John Mark Osborne]

After reading your post several times I think you might be able to accomplish this manually. Try entering find mode, typing in the following find criteria into your calculated date fields (this is the same thing that is in the file I referred you to but without a script):

1/*/2007

This will locate all of the records with a date falling in January, 2007. The asterisk indicates the day can be any day of the month. So, with this find, you should be able to locate the customer records with dates that fall into a particular month. You could also try a date range in find mode:

1/1/2007...1/14/2007

This will find all the records within the date range.

However, if you really want this to be a one button process such that you can find people and send a form email, you'll need to learn scripting. Here are several good resources.

Professional Foundation Training Book and CD

http://www.filemaker.com/developers/professionaltraining/bookcd.html

My Video Training Bundle:

http://www.databasepros.com/videos.html

[HawkLA]

GREAT! Thank You! I will start learning the Scripting next.

My next question is this. How can I make a "Next Appointment" field? Can Filemaker look over the 4 calculated dates that I have for each 3 month session, and then determine what the NEXT appointment date is? If I knew how to do that, then I could make a report that told me when everyone's next appointment is. How can I do that?

[John Mark Osborne]

Yes, you could use a Case function to determine which was closest to the current date.

[HawkLA]

Can you give me an example of how to use the CASE function? Can I make a new field called "Next Appointment Date" and then use the CASE function in a calculation to find the next date? Am I on the right track?

HawkLA

[HawkLA]

I have a series of appointment dates that are calculated in my database in 3 month intervals from a starting date.

I would like to have a calculation field that will return the results of the date of the "Next Appointment". In other words the database would search my calcuated fields and return the next accuring date.

From this I want to create a report that Shows the "Next Appointment" Date for each of my clients.

HELP! How do I do this calculation?

HawkLA

[aldipalo]

Calculated field:

"Next_Appointment_Date"

Calculation = :Current_Appointment_Date + 91

of course that's assuming 364 days per year rather than 365. If that matters there are many complex but elegant calculations that can be done. Try searching Date + Calculation and you will get 100's of threads.

HTH

[HawkLA]

I think I need to give you some more info. I photograph baby portraits. I input the baby's birthday, then Filemaker through a calculation field calculates the date 3 months from the birthday, then 6 months, then 9 months, then 12 months. So all these dates are listed on my data entry screen.

What I want to do is to run a report for each client that shows when they are do to come in for portraits next. Something Like this:

Customer Child's Name Next_Appointment

Does this help? How do I do this?

HawkLA

[John Mark Osborne]

Isn't this the same question you asked already:

http://fmforums.com/forum/showtopic.php?tid/180936/post/224377/hl//

I can understand it may not have been answered to your satisfaction but you should let others know this is the second time you are asking and refer them to the thread.

[Lee Smith]

The two topic have been merged, and a Private Topic sent.

Lee

[comment]

Try something like:

Case (

Appointment1 > Get (CurrentDate) ; Appointment1 ;

Appointment2 > Get (CurrentDate) ; Appointment2 ;

Appointment3 > Get (CurrentDate) ; Appointment3 ;

Appointment4 > Get (CurrentDate) ; Appointment4

)

[HawkLA]

Thanks! I will try this! I think this will work!

HawkLA

[HawkLA]

I put in the Case Calulation as you have above. I am getting an error though. The Appointment1 - Appointment4 fields are "Calculated" fields! I calculate the dates from the Birthday and Add 3 months to that date to get the Appointment fields.

The error that I get says Filemaker says "The Specified Field cannot be found." And it highlights the Calculated Appointment1 Field.

What can I do? I hope I am not bothering everyone. I just can't get everything to work like I would like.

HawkLA

[comment]

Your field name is probably not EXACTLY "Appointment1". Use your real field names instead of the names I made up for the example.

[HawkLA]

I did use my EXACT Field names. My Field names are 3Month, 6Month, 9Month, 12Month.

So I can use the Case funtion on a calculated field?

HawkLA

[HawkLA]

I got it to work! WOW!!! I had to click on my Fields instead of typing them in the formula. It put a $ and {} around the fields and it now works!

Thank you so much for the help everybody! Now I just need to design my reports!!!

THANKS!!!

HawkLA

[mr_vodka]

Taken from the help section of FileMaker in regards to naming fields...

It is not good practice to name fields leading off with a number. You should considering revising it to months_3, months_6, etc.

[HawkLA]

Thank you for the tip!

How Do I change the Field names so that they do not start with a number? Is there a way to do a find and replace that will rename my fields and change my formulas that contain those fields?

HawkLA

[Lee Smith]

Just select the Field in [color:blue]Defind Fields, change the Name to what you want, and then click on [color:blue]Change button.

[HawkLA]

GREAT! It worked! What a cool thing! Thanks again everyone!

HawkLA

[mr_vodka]

Yes you can change the field names in Define Fields as Lee said. When you change the fields names, FileMaker automatically changes them for you in the scripts as well. Lucky You! :P