Date Range issues...

[leesome]

Gday!

I'm having some problems with trying to get filemaker to perform a search (or at least a constrain) for a user entered date range.

Essentially all the records in the file have a 'Service Date' (service_date) assigned to them and what I'd like to be able to do is allow the user to enter a date range of their choice that they can search by.

I've created two global (date) fields as parameters (x_service_date _low and x_service_date_high).

Next, in scriptmaker I've tired to create a find or omit based on:

---------------------------

-Show all Records

- Omit:

service_date < x_service_date_low

service > x_service_date_high

---------------------------

However, it will not let me enter x_service_date_low or x_service_date_high as valid paramters, as the service_date is set as a 'date' field. (it says I must enter something in dd/mm/yyyy form).

I've tried changin service_date to 'text' instead and it will then allow me to use the x_date high and low as valid parameters, however it then doesn't perform the omit correctly!

Essentially, does anybody know how I can perform a search (or) omission of records based parameters that the user can set themselves? I'm relatively new at this, so a response in lamens terms would be awesome!

Cheers!

[LaRetta]

Instead of omitting, perform a find based upon your date fields. [color:blue]Script might look something like this after User enters dates in globals and clicks a find button:

Set Error Capture [ On ]

If [ x_service_date_low and x_service_date_high ]

Enter Find Mode [ ] ... uncheck pause

Set Field [ ServiceDate ; x_service_date_low & ".." & x_service_date_high ]

Perform Find [ ]

If [ not Get ( FoundCount ) ]

Show Custom Dialog [ OK ; "No records found." ]

Show All Records

Else

... do whatever with your found set

End If

Else

Show Custom Dialog [ OK ; "You need a date in both fields. Try again." ]

Halt Script

End If

Above is pseudo-code and untested.

Edited August 28, 2007 by Guest
Added blue

[Genx]

I forget... why can we use ".." instead of "..." for a range search?

[LaRetta]

I'm not sure I understand ...

Either are perfectly acceptable. I'm lazy and prefer to only type two. This is documented behavior so I trust FM won't change their minds eventually - it's in Help. :wink2:

[Genx]

Righto, just double checking they don't behave differently.

[LaRetta]

Upon re-read ... you won't need that Halt Script at the end because the script always stops when it hits the end anyway. I had moved a few steps around and I forgot to remove it. :

Oh! And leesome ... welcome to FM Forums!! :laugh2:

Edited August 28, 2007 by Guest

[leesome]

Thanks LaRetta, that worked brilliantly!!

I have one more question if I may ask (on a completely un-related topic). I have a status field taht I would like to change automatically depending on what other fields say.

So let's just say the field I want to automatically update is 'vehicle_status'

There are several other fields that contain dates such as 'date_ordered', 'date_recieved_in_uk', 'date_received_at_office' and 'date_delivered'.

Essentially, I'd like it to look at 'date_delivered' and if a date is present I'd like vehicle_status to change to 'Delivered to Customer'. If the field is blank I'd like to to look at the next field (date_recieved_at_office). If this field has a date I'd liek vehicel status to change to 'Received at offices' etc. etc.

I assume I have to use alot of if and case statements, but I can't seem to get them to work... The other problem i have with this is if it comes back as a positive response, the vehicle_staus field returns an '0' instead of the text I want...

I realise that is isn't scriptmaking as much as it is ca calculation... but any suggestions?!?!

CHEERS!!

What I'd like it to

Edited August 28, 2007 by Guest

[LaRetta]

Yes, it would involve a Case() statement. It sounds like you want this vehicle_status field to be totally handled by the value in other fields? If not, ie, if there is a possibility that you want Users (or even a script) to sometimes overwrite the standard response, then you will need to use a standard text field (with an auto-enter Replace calculation). But if you want it always handled depending upon other field values, then make it a calculation.

So, make vehicle_status a calculation (result is text) with something like:

Case (

date_delivered ; "Delivered to Customer" ;

date_received_at_office ; "Received at offices" ;

[color:gray]... repeat with each one then the last one will be

date_ordered ; "Product Ordered"

)

Notice the order - put the LAST date first (or the last order of the activity). In this way, FileMaker will stop when it hits the first true (a date in the field) and will display that resultant text. Also notice that if there IS a date in a date field, FileMaker will evaluate it as boolean true and select that value; otherwise it will continue down the list until it finds a date field with a date.

UPDATE: When I was very new, I didn't understand this so I'll explain further for anyone uncertain of my meaning:

Case ( a date ... ; true ; false )

If ( a date ... ; true ; false )

When you ask "If a date" you are asking "If there is a date in this date field" then it is true. Boolean is more efficient than asking "If the field isn't empty" then it is true. This works because a date ALWAYS has a number in it and boolean responds true to ANY number (other than 0), including minus numbers.

LaRetta

Edited August 28, 2007 by Guest

[leesome]

Thanks again LaRetta!! You're a champ! I've been trying to sort that out for days. I knew it had to be something bloody basic, but I couldn't work it out!!

Cheers!