Need Help w/ Calulation with date fields

[AWS]

HI,

I have created a customer database for my company, and I have 4 date fields for next service date, example 2 Months, 6 Months, 9 Months, and 12 Months service, sometimes only one of these field may have data and the rest might be blank, sometimes some or all could be filled in, or even none at all. What I would like to do is to somehow compare all of these fields and return the date that will need service next for my report. example the 2 month field might have 2/1/2002 and the 12 month might have 12/1/2001, so the 12 month service should be next for service, and should show up on my report. And if all the fields are empty no data, would like it just not print anything at all for the next service date, not all customers need service. I have looked at some of the calulation functions, I pretty sure I need to use the If Statement, and the ISEmpty function. and maybe others. I think this would be easier for me if, the If Function was more like it is for the scripting part of this program which would make test each date field easier, but I don't think I have that option.

Any help or calations that would do this would be great.

Thanks In Advance.

Greg

[danjacoby]

My first though is that nested "If" statements are best, since you have to compare each field to the other three to determine which is "next".

It gets easier if you create an open script that clears any field in any record where the date in the field has passed.

It's still pretty complex, though, especially since you have to account for empty fields.

[AWS]

I can't clear any date field that has passed,cause If I haven't serviced them, I still want to know that date, so I can in the future, I am doing is after I service a customer, I would then advance the customers service date ahead, example, next service date is 4/1/2003, After I have serviced there units, I would then change the 4/1/03 to 4/1/04 if this was for the 12 month service.

Infact, I have just entered about 600 customers, about 30% done, many have not been serviced because everything was on paper, now that I am converting everything into filemaker, i'm gonna have lot of customers that will need service for past dates, because, i'm sure you'll agree it's very hard to keep track of paper records for about 2000 or more customers.

Thanks for the info, I've been and will still be testing with If statements, it just seems to be harder cause the if syntax is very different than the if syntax of the scripting section also like other programming languages.

Any more Ideas would be great.

[Lee Smith]

The enclosed file is shows a couple of ways that you could handle it.

HTH

Lee

AnniversaryTickler.fp5.zip

[AWS]

I checked out your file, looks pretty good, but it won't fix my problem but may need to use a case statement somewhere, someway, I really need to compare whats in each of the four date fields, (if any) and have it store the value of the date that is less than the rest. Of course, if there is only one date field is filled in, then it would return that date. If no fields are filled in it would return nothing. If I compare a empty field with another field that does have a date, would it come back with garbage? I'm thinking it might, so think I might need to first test field ISEMPTY, then procede to try to compare with another date, if present.

Example:

Next Service Date

2 month 1/16/03

6 month

9 month

12 month 12/16/03

This would return 1/16/03

Thanks Again, and anyother suggestions would be helpful.

Greg

[cmartin]

Hey-

I attached something that may be a good solution. I separated the service date info out into another related file and then simply set NextServiceDate for the customer to min(ServiceDate). Have a look. I believe this is the best and easiest solution. The only thing I would recommend is upon creation of a new customer, script the creation of their four related records (one each for two month, six month, nine month, twelve month). Let me know what you think!

Good luck-

Courtney

NextService.zip

[cmartin]

Also I think I should add that you would need to do some checking to ensure that any given customer only has one record for each of the specified intervals. I think limiting the portal to four rows with no vertical scroll bar is a good start, as in my example. But rather than have the user pick the interval as in my example, you would want to create all the line items through scripts. For customers you already have in the db you could write a one-time looping script to accomplish this. Then going forward, as I mentioned in my last post, you would want to create these four records each time a new customer is entered.

[Ugo DI LUCA]

Hi Courtney,

It may be hurts you AWS, but I totally agree with Courtney about the separate file for related records.

You may look at it with attention as I am quite sure that you're not selling only one service....

A line item is all what you need. Then the calcs would be easier and your relation solution...will look relational.

In addition to the last answer made by Courtney, I'd say you can smoothly control what you are pulling into the line item with some "controlled by calcs" settings in the fields definition within the Related File. You may be not need any script for this setting....

[AWS]

Thanks Courtney,

That seems to make alot of since to me, and I have already put it into use, and have created a new record script to fill in the 4 values for the interval type, like in your example. I have 2 questions.

First, Concerning date fields or even other types of fields, can you create a input mask, i've done this in MS Access, so if I clicked into or tabed to a date field, it would display " / / ", and allow me just to type in the numbers of the date, and not have to type in the slashes, same thing would be useful for a telephone field too.

And Second and most important, Could use some help getting started in writing the one time script to convert all of my next service dates to the service date relalationship that I created like your example. I figure the first thing needed to be done is to create all 4 interval values like I did when creating a new record. then to check and copy any data that might be in each of the 4 date fields to the proper service date location. I've already got about 600 records entered into this system, and don't want to mess it up, i'll back up though before I run the script.

Thanks again for your help!

Greg

[cmartin]

As to the first question, I am not aware of input masks being built-in to FileMaker. Although I do believe there may be plug-ins, which you could purchase, that could accomplish this. Best bet is either search the forums or start a new post related to this.

As for your script, gimme a few and I can post something. Of course, anyone else is more than welcome to have a go.

-Courtney

[Ugo DI LUCA]

Hi Greg,

1. There are some input mask exampleshere

2. After downloading Courtney's sample, I'm not so sure it would help to auto-create 4 records in the related file, as long as some of these records would be empty.

It's hard to say what I would do instead but your count(related records) could become easier....

3. I don't get what exactly your looking for about this starting script....

then to check and copy any data that might be in each of the 4 date fields to the proper service date location.

Could you be more specific on what you want to "copy" ?

[AWS]

ok, before I created the related file that courtney suggested, I had and still have for right now, 4 other date fields, NextServiceDate2, NextServiceDate6, NextServiceDate9, and NextServiceDate12. Most of these have data in them for about 600 customers that I have entered so far. Some customers may not have any dates in any of these fields most have one in 12 month, others may have more than one field filled in. Now that i'm switching to what Courtney explained to me, I need a script that will go thru my entire database, and copy these dates to the proper spot in the related file for each customer. So that my Reports including the Next Service date will show up. As far as the auto-create, This makes it easy for me to enter the data, so that it shows up correct spot, and it creates 4 date fields to, so I can just tab to the 4th if I want and type in a 12month service date, also I in the future if I need to also set that same customer up on a 2month service as well, I can just tab to the first location in the portal, and not have to move the 12 month down. Hope this makes since to you. Thanks

[Ugo DI LUCA]

Hi Greg,

and not have to move the 12 month down

IMHO, it doesn't matter as this is a related record. You can sort it out by date later on. Having empty records is not a real concern anyway, there are still some workaround to get your count(related records) work properly. Keep it if you wish...

BTW, you didn't answer to my assumption that you may have more that one service (or some categories,...) ...

About your script, as you said you backed-up your file, you could try this.

Create 2 globals and a indexed calc in the Customer file.

g_date

g_customer_ID

c_constant ( indexed = 1)

Create a global field in the Related file

g_constant = 1

Create a relationship from Customer to the Related in the Related file.

Call it constant and use the g_constant for left side and c_constant for right side.

Then these scripts in both files

Customer file :

"Start script Main"

Show All records

Sort by Customer_Id

Go to record (first)

"Set globals for date 1"

Set Field (g_date, d_date1)

Set Field (g_customer_Id, customer_Id)

"Set globals for date 2"

Set Field (g_date, d_date2)

Set Field (g_customer_Id, customer_Id)

"Set globals for date 3"

Set Field (g_date, d_date3)

Set Field (g_customer_Id, customer_Id)

"Set globals for date 4"

Set Field (g_date, d_date4)

Set Field (g_customer_Id, customer_Id)

"Go to Next record"

Go to record (next, end script after last)

In the related file :

Start script Related :

Show All records

Go to first record (you need to have one created or add a step "create new record" in this script)

Set new record :

New Record

Set field (customer_Id, ::Constant:g_customer_Id)

Set Field (date, ::Constant:g_date)

Finally the script "Import from Main" will look like

Perform script "Start Script Related"

Perform External script "Start Script Main"

Loop

Perform External script "Set globals for date 1"

Perform script "Set new record"

Perform External script "Set globals for date 2"

Perform script "Set new record"

Perform External script "Set globals for date 3"

Perform script "Set new record"

Perform External script "Set globals for date 4"

Perform script "Set new record"

Perform External script "Go to Next record"

End Loop

Tell if it doesn't work.

[AWS]

I'm gonna give what you gave me a try, thanks.

And yes and no, as far as different services, I work for a Well pump company, sometimes for certain customers depends what type of units (water filteration systems to get rid of iron or to adjust the PH reading, also need to maintance the customers holding tanks and other things to do with the well system) they have installed, we need to service the units maybe every 2months, and so on, the 12 month is used for our annual service, full maintance on the entire system.

Thanks again.

[AWS]

Thanks Ugo, and Courtney!

Ugo, I created all the scripts and even added other scripts to add the Inveral data in the fields to for all my customers, I know, I don't have to use them, but like how it looks.

Thanks Again, I was able to copy all of the data to the related file correctly.

You where a GIANT help!

[cmartin]

Sorry I never posted a script. Got a little bogged down with work here yesterday. I had a feeling UGO would give it a go. Glad it's all working out for you.

Enjoy!

Courtney

[Ugo DI LUCA]

Hi Courtney,

I see you stars , .....finally