Jump to content
Sign in to follow this  
Rangoon

How to zero out a Date field?

Recommended Posts

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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...

Share this post


Link to post
Share on other sites

Set Field [datefield, TextToDate("")]

The trick is the TextToDate() function!

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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?!

Share this post


Link to post
Share on other sites

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...

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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,

Share this post


Link to post
Share on other sites

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

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Similar Content

    • By Tumma K
      Hello, All!

      I am an aspiring developer for Filemaker. The company I work with is stuck in the past working off of Filemaker Pro 4.1

      I was given the task of bringing us up to Filemaker Pro/Server 13. So far my conversion prototypes are successful but we recently had a layout issue that can only be fixed in versions 3-6 (as the file is an .fp3) I work off of a macbook while our network is all Windows 7. In order for me to repair the layouts without tampering our active database, I decided the best option is to repair a copy of our solutions off the network. Unfortunately, when I go to download the trial version of Filemaker Pro 6 off of the respected website, the file is corrupt! I've tried multiple times, with different extraction apps and in different directories.

      My question is;

      Does anyone know a place where I could obtain version 6 (or better yet, 4.0) for an OSX computer? I've looked everywhere!
       
      Thank you for your time,
      Tumma K.
    • By MrEddByrnes
      I'm hoping my question can have a happy ending. In the mid-90's, I purchased Filemaker 3. When Filemaker 5.5 Pro was released, I bought the update CD, which requires the user to either have FM 3 installed or to have the installation CD for FM 3. I've used it all these years, most recently with Windows XP Pro, and it has worked just fine. The databases I began with were long ago converted to FM Pro 5.5 databases.
       
      I'm still using FM Pro 5.5 on a laptop with WinXP Pro, but in 2013, I purchased a PC with Windows 8. I haven't been able to install FM 3 on it, therefore can't install FM Pro 5.5. I am retired and rarely use Filemaker, but I have a few Filemaker databases I'd like to add to my Win 8 machine. I don't feel it's worth upgrading FM for the sake of using a couple of databases.
       
      Has anyone else run into this situation and/or have a (possible) solution? Is there perhaps any other software that can read FM 5.5 databases? Thanks in advance for your help.
       
    • By bmill
      I am using a custom filemaker solution for medical office billing written with fp5 running on a mac with snow leopard. In addition, I have a patient management db (which I wrote) that is linked through pt. ID number to the billing program allowing transfer of some demographic information (name, DOB, etc).
       
      Other than being limited by hardware restrictions, the billing program serves our needs for now and upgrading to fp12 will take some time (and money).  In the meantime, I am upgrading my pt. management program to fp13 and would like to move new patient demographic information from the billing program ( fp5 running on snow leopard through Parallels) and the new pt management program ( fp13 running on OS X 10.9) on the same mac.   
       
      Ideally, demographic information would be entered once into fp5 and then a scipt would make the data available for fp13.
       
      Any ideas on how to make this work?
    • By randyinla
      Hi, can anyone tell me why my on-line database might have stopped allowing me to delete records?  All of my access privileges and passwords are correct.
       
      thanks!
    • By randyinla
      Hi, I still use filemaker 5.5 and am very happy with it and don't want to upgrade.  Does anyone still host filemaker 5.5?  Thanks!
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.