Current Date explanation

[Turansky]

Hello All,

I'm trying to show only the records greater than or equal to a field that is called expire. The data in the field looks like this: 01182002 mmddyyyy. Do I need to have the op tag in there somewhere.

"http://xxx.xxx.xxx.xxx/FMPro?-DB=scoe_events.fp5&-Format=scoe/adminlist.html&expire gte [FMP-CurrentDate]&-Max=all&-Find"

Thanks

Kent

[Keith M. Davie]

While the -op and -lop tags are popular, it is also very useful to understand the construction of code for the symbols (found in the Find action within the FMPro database). While there is no one good source for understanding all the symbols (such as those used in a range-find), there is a good source for the exact search. If you understand how to use the exact search, creating code for the other symbols is rather easy and often very useful.

If you are not using JavaScript as a control in some cases such as a form action, the -op and -lop tags are often ineffective.

To gain better understandings

http://www.filemaker.com/support/index.html

Search and read: Article Number: 104829, and Article Number: 105687

SIMPLIFY ...

Keith

[ January 18, 2002: Message edited by: Keith M. Davie ]

[Garry Claridge]

Kent,

Give this a go:

"http://xxx.xxx.xxx.xxx/FMPro?-DB=scoe_events.fp5&-Format=scoe/adminlist.html&-op=gte&expire=[FMP-CurrentDate]&-Max=all&-Find"

All the best.

Garry

[Vaughan]

quote:

---

I'm trying to show only the records greater than or equal to a field that is called expire. The data in the field looks like this: 01182002 mmddyyyy.

---

If this is a date it should be in a Date field, not a text or number field. Only then can the search operators (gt, lt etc) properly work on it. FMP has no idea that 01182003 is actually "after" 02182002 because it sees it as text, and it will be sorted alphabetically.

For your URL to work correctly the date field and the [FMP-CurrentDate] tag need to be set to the same format.

[Turansky]

That makes sense. I have pull downs for the Month, Day, and Year. Do I need to add a forward slash to the value? Or is there a way Filemaker will convert it when I send the numbers?

<select>

<option value="01/" selected>January</option>

<option value="02/">February</option>

<option value="03/">March</option>

etc. . .

<select>

<option value="01/" selected>1</option>

<option value="02/">2</option>

<option value="03/">3</option>

etc. . .

<select>

<option value="2002" selected>2002</option>

<option value="2003">2003</option>

<option value="2004">2004</option>

etc . . .

[Vaughan]

Hmmm. You'll have to set the form up so that the values build up the date in the format FMP expects to see.

BTW be *very* careful converting the text field to date: backup before doing anything. Just changing the field data type will probably trash the data.

You'll most likely need to create a new date field, then use Replace command and paste a calculated result into the date field. The calculation will need to massage the text and convert it into the date format FMP expects to see. Be prepared to do it several times before getting it right! (Tthat's why the backup is *really* important.)

[Turansky]

Ok, I have worked with this for a few days now and just cannot get it to work.

I have created 5 fields:

expiremonth

expireday

expireyear

expireconversion

expire

The expire month, day, and year all have values.

The expireconversion combines the fields together to make it look like a Filemaker date.

expiremonth &"/"& expirday &"/"& expiryear

Calculation result is number Example: 1/20/2002

And this is where I get stuck, trying to change that number into a date.

I have tried changing the "Calculation result is date" but that really messes up the numbers.

Any ideas. I keep feeling that I am doing some really long method to just get a date so any input would be helpful.

Thank you

Kent

[Garry Claridge]

We use a Javascript for this type of thing; e.g.:

function doSubmit()

{

with (document.FormName)

{

//concatenate the dates together

var sDate;

sDate = (elements[12].value + "/" +elements[11].value +"/" + elements[13].value)

elements[2].value = sDate;

submit();

}

}

Hope this helps.

Garry

[Garry Claridge]

The Javascript method I have posted here is one I use for most pages where a date needs to be entered/edited by a user. The reason for this is to provide popup menus for "day", "month" and "year" so that the user is less likely to make a mistake in typing a free-form date.

The "day", "month" and "year" form elements do not have a 'name' so that the submitted form does not return an error. The element that the concatenated date is assigned to has a 'name' within a 'hidden' <input> tag.

Dates being returned by a format file can be split into '"day", "month" and "year" and displayed as the 'selected' options.

This works for me.

Garry

[Vaughan]

You calculation for converting the entries to a date needs to be like this:

code:

---

Date( TextToNum(monthfield), TextToNum(dayfield), TextToNum(yearfield) )

---

Nothing else: FMP does the rest. Note that the syntax for the Date() function is Date(month, day, year) regardless of OS and country preferences.

Personally, at this point I'd ditch the separate pop-up menus for month, day and year and just use a single text field. Most users actually find it faster and easier to enter anyway. Make explicit what the necessary date format is -- like mm/dd/yyyy or dd/mm/yy or whatever.

[ January 22, 2002: Message edited by: Vaughan ]

[Keith M. Davie]

This forum is a valuable resource. Most of the questions which are asked here have already been raised and answered.

I do not understand your problem.

Can you perform a search on a date range in FileMaker the db? Yes, of course you can, and you can do it using FMPro formatted dates. I know, I just confirmed that.

Ok.

Then you can do the same thing using the proper code in html/cdml. I earlier posted reference papers for you which should have led to an understanding of how to handle this. Perhaps you should spend more time researching answers. Let me help you further. View this forum for the past year. Find a thread entitled "refine search". Read it. Apply what you learn there to the earlier cited reference, and you should be able to perform the task, provided you set things up correctly. No JavaScript is necessary. It can all be done with cdml/html.

Research is good for the soul.

[Turansky]

Well, after reading everything I have chosen to go with Vaughan's solution. Mainly because I need to rely on Filemaker to do all my sorting. I was able to have expire become a date field. I then put in a few dates some old some new and some today. When I do my search it's bringing back everything? Any solutions to this?

-op=gte&expire=[FMP-CurrentDate]&-Max=all&-Find

Just a thought...My date is now a 4 digit year will Filemaker be able to compare that with the current date?

Thank you

Kent

[ January 23, 2002: Message edited by: Turansky ]

[Vaughan]

The -Op code is specifying gte. Delete it completely to return to the default (bw I think).

[Turansky]

Well . . . I took it out and now it can't find any records. Here is the link as it stands now.

Thank you

Kent

"http://xxx.xxx.xxx.xxx/FMPro?-DB=scoe_events.fp5&-Format=scoe/adminlist.html&expire=[FMP-CurrentDate]&-Max=all&-Find"

[Vaughan]

I notice that you don't have a -lay tag specified. Put one in: make a layout in the database (it can look ugly) with only those fields on it that are used by the web. I usually name it "web" other people like Anatoli name it "cgi" but it doesn't really matter. Add the "-lay=web" (or whatever) tag to the url and try again.

If it still doesn't work...

Go to the server that is running Web Companion. Brint the scoe_events database window to the front and change to the "web" layout. In the browser run the url. Straight away, go to the scoe_events database and choose Modify Last Find from the Records menu. Take a look at exactly what Web Companion was searching for -- it might give a clue as to what's going wrong.

Also check the activity log to see what it says.

--

A thought: you are processing the url with the [FMP-CurrentDate] tag aren't you, and not sending it straight to Web Companion? If you are it won't work (as you know). You'll need to type the date in manually.

[Turansky]

----------

I notice that you don't have a -lay tag specified. Put one in:

----------

I added it

----------

...Take a look at exactly what Web Companion was searching for -- it might give a clue as to what's going wrong.

----------

It showed [FMP-CurrentDate] in the expire field.

----------

Also check the activity log to see what it says.

----------

The following was in the log last:

/FMPro?-DB=scoe_events.fp5&-lay=Event&-Format=scoe/adminlist.html&expire=[FMP-CurrentDate]&-Max=all&-Find HTTP/1.1" 200 2007

xxx.xxx.xxx.xxx - - [24/Jan/2002:17:48:42 -0800] "GET /FMRes/FMP-IWPErr.js HTTP/1.1" 304 0

xxx.xxx.xxx.xxx - - [24/Jan/2002:17:51:18 -0800] "GET /default.htm HTTP/1.1" 304 0

xxx.xxx.xxx.xxx - - [24/Jan/2002:17:51:28 -0800] "GET / HTTP/1.1" 302 0

xxx.xxx.xxx.xxx - - [24/Jan/2002:17:51:28 -0800] "GET /default.htm HTTP/1.1" 304 0

----------

A thought: you are processing the url with the [FMP-CurrentDate] tag aren't you, and not sending it straight to Web Companion? If you are it won't work (as you know). You'll need to type the date in manually.

----------

I'm sending the actual [FMP-CurrentDate]. I don't know how else to get the current date? My current link is.

"http://xxx.xxx.xxx.xxx/FMPro?-DB=scoe_events.fp5&-Lay=Event&-Format=scoe/adminlist.html&expire=[FMP-CurrentDate]&-Max=all&-Find"

Thank you

Kent

[Garry Claridge]

You can use Javascript, for example:

In the <head> place:

<script>

dtoday = new Date() ;

stoday = (dtoday.getMonth() + 1) + "/" + dtoday.getDate() + "/" + dtoday.getFullYear() ;

</script>

For the URL do this:

<script>document.write("http://xxx.xxx.xxx.xxx/FMPro?-DB=scoe_events.fp5&-Lay=Event&-Format=scoe/adminlist.html&expire=" + stoday + "&-Max=all&-Find");</script>

Hope this helps.

Garry

[Turansky]

Well that does sound interesting but I have this whole thing setup and working using CDML except for the current date problem. I don't really know Javascript and could see doing it that way would take me at least another week. I can't believe Filemaker doesn't have a way to check the date against the current date.

Kent

[Garry Claridge]

It does, however the page will need to be processed by FM first so that the date can be inserted into the displayed format/html file. This is what Vaughan noticed earlier.

You may be able to use a calculated field in the database which uses the 'current date' to compare against the expire date and returns the value "expired". Hence the URL would look like this:

"http://xxx.xxx.xxx.xxx/FMPro?-DB=scoe_events.fp5&-Lay=Event&-Format=scoe/adminlist.html&expire_test=expired&-Max=all&-Find"

Hope this helps.

Garry

[Turansky]

Let me make sure I understand this. If I set a token as the current date I could then on the following page compare my field with the token.

Right?

Kent

[Garry Claridge]

Kent,

Yes, that is correct. So a page which is processed by FM with these contents would work:

<input type="hidden" name="-token" value="[FMP-CurrentDate]">

or

&-token=[FMP-CurrentDate]&-max=all&-find"

Then your next format page could contain:

"http://xxx.xxx.xxx.xxx/FMPro?-DB=scoe_events.fp5&-Lay=Event&-Format=scoe/adminlist.html&expire=[FMP-CurrentToken]&-Max=all&-Find"

However, this is a double step when the format page containing the URL will still need to be processed by FM to replace the Token.

The calculated field solution may be the best, because it will also make searches and sorting of 'expired' records easier when using FM client.

All the best.

Garry

[Turansky]

I see your point. I will go work on a calulation and see if I can make that work.

Thank you

Kent

[Garry Claridge]

Kent,

Here is an example of the calculated field, 'expire_test':

If(Date(TextToNum(expiremonth),TextToNum(expireday),TextToNum(

expireyear)) >= Today,"expired","ok")

Place this on the layout. Then the URL:

"http://xxx.xxx.xxx.xxx/FMPro?-DB=scoe_events.fp5&-Lay=Event&-Format=scoe/adminlist.html&expire_test=expired&-Max=all&-Find"

will work!

Good Luck.

Garry

[Turansky]

That worked and I actually understand most of it. I think I was trying too hard and going in the wrong direction. Thanks for the lesson.

Kent

[Keith M. Davie]

I've been off-line and my Mac was down, so that kept me from responding to this thread earlier. But that is ok.

I must say I have enjoyed watching Turansky sweat. It took him from the 18th to the 24th - 6 days - to arrive at a workable solution which (by his own admission) he does not fully understand. Since then we have seen the kind of developer he is by the number of code questions he has posted. To bad the people writing his code don't get his paycheck.

Since Turansky had his solution written for him I thought that others who are Developers might like to know more about the currentdate and why the calculation field was totally unnecessary, as were the suggestions of JavaScript and the use of the logical operators.

For that reason I am posting a solution addressing those issues on this forum under the title "currentdate-Research is good for the soul".

Peace and love

[Turansky]

quote:

---

Originally posted by Keith M. Davie:

I must say I have enjoyed watching Turansky sweat. It took him from the 18th to the 24th - 6 days - to arrive at a workable solution which (by his own admission) he does not fully understand. Since then we have seen the kind of developer he is by the number of code questions he has posted. To bad the people writing his code don't get his paycheck.

Since Turansky had his solution written for him I thought that others who are Developers might like to know more about the currentdate and why the calculation field was totally unnecessary, as were the suggestions of JavaScript and the use of the logical operators.

For that reason I am posting a solution addressing those issues on this forum under the title "currentdate-Research is good for the soul".

Peace and love

---

I'm not sure how to take all this but I can't wait to read what you have to say. I wouldn't say that all my code was written by other people though. There isn't a whole lot of examples out there and the people here have done a wonderful job at helping me understand this stuff. I look forward to reading your stuff so I can learn from you too. I hope that is what part of this forum is all about.

Have a great day.

And glad to hear your Mac is up and running again.

Kent