CDML query using CurrentDate

[kfutter]

Hi,

I'm trying to set up a search for a large calendar database that returns records matching particular values in a status field, with date values forward of the current date. I can grab the proper status records without a problem, but getting only those forward of the current date seems impossible. At the moment I'm manually incrementing the find every week or so using -skip. I can use FMP-If to filter out prior dates on the format file itself, but I'd much prefer to do it in the initial query. Surely there's a way?

Here's an (amended) example of what I'm currently doing:

http://validurl/FMPro?-db=calendar.fp5&a...ax=50&-Find

Cheers,

Kevin Futter

[Garry Claridge]

We often use a little bit of Javascript to determine the current date (and format it). We can also use the Javascript to calculate a future date.

Good Luck.

Garry

[Jeff Spall]

Hi, you can create calculation fields in the database which will return a "yes" if the date is greater than today and then just search for those, like this one that colours up a list for me, light grey for past, red for today, dark grey still in the future:

If(ad_copy_date=Today,"CC0000",

If(ad_copy_date < Today,"999999",

"333333"))

........the drawback is that to make this update on the fly, it can't be indexed.

regrds, jeff

[Steve T.]

Hi, Kevin! I was making a FileMaker-based web calendar in which I used calc fields... something like...

thisday = status(currentdate)

weekfromnow = thisday +7

monthfromnow = thiday +30

To make sure only current/future listings showed up in the search results, I added a GTE thisday requirement. I also had buttons for searching up to 1 week, and up to 30 days. It worked great. The good folks at FMFORUMS like Garry and Jeff reminded me to use Status(CurrentDate) instead of the Today function because Today only works on the day the db was opened whereas Status(CurrentDate) is an immediate check.

I'll dig up the code if you need it.

--ST

[kfutter]

Thanks for your input guys. Unfortunately I'm still not there yet and I think I need to explain more fully what I'm trying to do.

Firstly, it's difficult for me to modify the database as it's being hosted on FM Server 5.5 and served to the Internet by a v6 Unlimited guest. It's very busy and not easily taken offline for modification. I'm really looking for a way to use either the existing date field info or some other built-in FM date variable as part of the query. I found some code on the 'net recently that seemed to fit the bill, but unfortunately it doesn't seem to work at all:

...&date=[FMP-CurrentDate]&...

Are there any alternatives to this, or am I stuck with the prospect of modifying the database as previously suggested?

Cheers,

Kevin Futter

[Garry Claridge]

The only problem with [FMP-CurrentDate] is that the page needs to be "processed" by the WebCompanion before it is sent to the browser. An alternative to this is by using Javascript to substitute the date, however it is the date from the users computer.

You could have the page pre-processed by the WebCompanion by using the "-view" action. This is a way of having the [FMP-CurrentDate] tags replaced.

If you want to have a look at some Javascript I can post a couple of examples here.

All the best.

Garry

[kfutter]

So you're saying that by using [FMP-CurrentDate] in the initial URL query, it never gets passed to Web Companion for processing? That kinda makes sense to me, but I don't understand why someone else put it in a web tutorial as if it worked fine (is it a technique that may once have worked, say in v4, but no longer does?).

I'm reasonably conversant with JavaScript's Date object, but I'm confused as to how it can solve my problem, so if you're good to post some code and an outline of the process, I'd be appreciative.

Cheers,

Kevin

[Garry Claridge]

I don't know about the tutorial, however WebCompanion does need to "process" the page. You can have WebCompanion do this by using the "-view" action, which is a bit of a "dummy" action which allows certain WebComanion processing.

You can use the Javascript like this:

<script>mydate = new Date()

document.write("<a href='http://validurl/FMPro?-db=calendar.fp5&-op=gte&mydate=" + mydate.getDate + "/" + mydate.getMonth + "/" + mydate.getFullYear + "&-Find'>");

</script>

All the best.

Garry

[kfutter]

Thanks Garry. Having thought about it after my last post, I realised that this method was probably what you were alluding to. I'd have to modify the code to include writing iframe tags, as the data is pulled into an iframe using its src attibute (no biggie). My only reservation about it is relying on client clock hardware to be accurate, but it's a nice technique all the same.

I tried using -view as you suggested, but my format file is returned displaying no records, so I'm a bit confused as to how this works. If you have time to elaborate, I'd appreciate it.

For interest's sake, here's the URl for the tutorial I mentioned:

http://scrtec-ne.unl.edu/SCRTECNE/TechTopics/tutorials/tutorial2/10index.html

The code in question is in the bordered tables, if anyone cares to check it out.

Thankyou all for your time.

Cheers,

Kevin Futter

[Garry Claridge]

The "-view" would be used to load the first page, which contains the link. This gives WebCompanion a chance to replace the tag. For example:

http://validurl/FMPro?-db=calendar.fp5&-format=/publications/calendar.html&-view

Then this should work fine:

<iframe src="http://xyz/FMPro?-db=calendar.fp5&-format=/publications/calendar/table.html&-Op=cn&status=n&-Op=gte&date=[FMP-CurrentDate]&-sortField=date&-max=50&-Find">

If the "calendar.html" is on another server you may be able to use whatever utillities that server has to subsitute the date.

Good Luck

Garry

[Steve T.]

Yeah, no [FMP-anything] tags can be used before there is at least 1 FileMaker action beforehand (-view on an preceding page as Garry suggests is a good one). I have no JavaScript skills, but I would probably suggest going that route if you can or just taking the db offline for about 8 seconds to make a calc field ThisDay = Status(CurrentDate). That's just my opinion, but I think it'd save you a lot of trouble.

Hope it works out!

--ST

[kfutter]

Thanks Garry - all is clear to me now. I've got the JavaScript technique working fine, though I amended your example so that the Date methods included parentheses [eg myDate.getMonth()], as it wouldn't work without them for me. Also, because getMonth returns a zero-based value, I had to increment it by 1 to get the correct result, like so:

myDate = new Date();

var myMonth = myDate.getMonth() + 1;

That way, July is reported as 7, not the default 6.

Given that CDML is not supported in v7, the JavaScript method is more futureproof for us than the CurrentDate technique, so I'll stick with that.

Thanks again for your help and patience!

Cheers,

Kevin Futter