How to zero out a Date field?

[Rangoon]

I have two fields "StartDate" and "StopDate". What I would like to have happen is for both fields to zero out at midnight of the "StopDate". How can it be done?

Thanks

[Kurt Knippel]

What do you mean by "Zero Out"? A date cannot be zero. Do you mean you want them empty or blank?

Do you want to preserve the original data and simply display it as "Zero"?

If you want to preserve the original data you could just use calculation to display the data, if before midnight on the end date it will display the data as entered, after midnight is displays "".

If you actually want to change the data to "Zero" then you will need a script do loop through the records and change it.

[Rangoon]

Kurt,

I want to have the fields empty not zero out as mentioned eariler. I could not tell by your response how to do it. Could you elaberate. Also I would like to keep a list of the promotions that corrilate to the dates. How would I go about that?

Thanks Kurt...

[Vaughan]

Set Field [datefield, TextToDate("")]

The trick is the TextToDate() function!

[CobaltSky]

Your question really has two parts - how to return a date field to a 'null' value, and how to schedule this event to occur at a pre-appointed time.

The first question is not so hard. Emptying date fields can be achieved in several ways:

1. Manually, you can select the field contents and press 'Delete'.

2. If you are working on within a script on a layout on which the date field in question is present, it is possible to set the field to a 'null' value using the Clear ["YourDateField"] script step.

3. If you are not on a layout where the date field is located (perhaps not even in the same file) you will need to use the Set Field script step. You've probably already discovered that Set Field ["YourDateField", " "" "] will not work (ie will place invalid rather than null content into the field). What you can do instead, however, is create an empty global date field in the file in which the script is located called 'gDateNull', and then use the formula: Set Field ["YourDateField", "gDateNull"]. That will do it. Vaughan's suggestion of using the TextToDate("") would be equally effective.

The second part of your question is a bit more tricky. FileMaker does not provide very flexible script scheduling options 'out of the box. There are two approaches you might consider.

1. If you do not require unlimited access to the file and are able to set a script running manually at some point, you can create an unstored calculation field which subtracts the time between 'now' Status(CurrentTime) and midnight on the date in question, then place at the start of your 'Set Field ["YourDateField", "gDateNull"]' script, a Pause/Resume Script step defined using the "For duration given by field value" option, based on the value produced by your calculating field. This will pause the script until midnight on the day specified.

The drawback is that your use of FileMaker on the machine/workstation in question (without first cancelling the script) will be limited to the file running the script, and to browsing and editing values.

2. For a more satisfactory script scheduling and triggering solution, you will need to consider using one of a number of third-party plug-ins which provide sophisticated scheduling, triggering and/or remote control capabilities. There are a number of them available, and they use the FileMaker external function interface to offer varying degrees of flexibility and extensibility. Three of the better known of these are Troi Activator from Troi Automatisering, oAzium Events from Waves in motion, and SCRIPTit from CNS. If you're interested, they are available as shareware demo downloades at their respective vendow sites. The URLs are:

http://www.troi.com/software/activatorplugin.html

http://wmotion.com/events.html

http://scriptit.cnsplug-ins.com/

There are others, but any of the above three would be a good place to start, and all of them would be capable of being configured to provide the kind of function you've asked about.

[Rangoon]

Is there a way to do this without the use of a script? I have over 75 Vendors that offer different promotion dates and $ amounts on maybe 10 products a month. Thats an average of 750 promotions per month. I'm afraid that the scrit solution will be very sloooooooow. Especially on older machines.

Entering in all the promations dates is bad enough, I would just like to find a way to clear the field more easily. I'm sorry I didn't give more information with my initial post, I just never thought of the script solution.

What about a hidden field that sets on top of the Start Date and Stop Date fileds? I'm not quite sure how that works but I know I've seen something like it some where along the line.

We are on FMP 5.5. The dates are in the products file and are included on the same layout as the product information.

Thanks for your help and I'm sorry if I didn't give you the information you needed from the start!

[CobaltSky]

I'm afraid that I, and perhaps others, had assumed that you were looking for a script.

From what you've now said, it sounds as though a pair of unstored calculation fields would be preferable.

What you would do is enter the start date and stop date, as you suggest, then create two

calculating fields, with formulae as follows:

Case(StopDate + 1 > Status(CurrentDate), StartDate)

and

Case(StopDate + 1 > Status(CurrentDate), StopDate)

---

...then place these fields over the top of the StartDate and StopDate fields (respectively) on your layout, with "Allow entry into field" turned off for the calculating fields.

That way, you'll still be able to click into the start and stop date fields to enter/edit/view the dates, but after the stop date has passed, they won't ordinarily display on the layout. It's a bit of "smoke and mirrors", but it might get you where you want to go...

Hope this is a bit closer to the mark?!

[Rangoon]

Yep, the overlaying calc fields is the ticket. May I push the light fantastic with one last question. When I enter the dates they do not show untill both date fields and promotion amount (all three) are entered. Is there a way for each field to show it's value as they are entered?

Thanks for your help. Next time I will make sure to spell out, not only what I'm looking for but some background on it's intended use. You guys are good enouth to help and I chase you all over the place before you understand what I need. Sorry about that guys and I'll do better next time...

[Kurt Knippel]

This is most likely due to the way in which the fields are formatted on the layout.

Something that you could do is the use a dialog or special data entry layout where the fields are just normal data entry fields where you always see the values. This "dialog" is only used when you need to add/edit the values. All other times the user sees just the calculated fields.

[CobaltSky]

Try the formulae:

Case(IsEmpty(StopDate) or StopDate + 1 > Status(CurrentDate), StartDate)

and

Case(IsEmpty(StopDate) or StopDate + 1 > Status(CurrentDate), StopDate)

---

That way the calc fields will still display when the stop date is null (ie before you've got to it to enter a value).

Cheers,

[Rangoon]

Mr. Cobalt or should I call you Sky...

That was perfect, exactly what I wanted. Thank you very much sir!