Search multiple fields and tag them?

[Phil H.]

Hi guys,

I'm a relative newbie to Filemaker and have found myself managing and trying to improve upon our existing Filemaker 6 database (using Server 5.5).

I work for a company that rents out Macintosh and other computers for tradeshows, training sessions, etc. As a result, one of our most important tasks is keeping track of when equipment comes in, goes out, and when these rentals need attention. The owner of the company had designed the database with the Sart Date/End Date/Action Date as all one field so that he could search one date and find everything...with obvious limitations.

Thanks to Fitch and Robert I've separated these and I've figured out how to search through multiple fields and have the combined results show up. Now I want to create a report that shows the days duties separated by time, location, and type of job, i.e. delivery, pickup, action item.

I want to create a script that searches the days duties, then based upon whether today's date shows up in the delivery, pickup, or action date field assigns them the task. I figured that by creating a field with the values of Delivery, Pickup, or Action I could somehow create a script that looked at the dates and assigned the task... i.e. if the date of today showed up in the delivery date field then that item was a delivery, or if it showed up in the pickup date then it must be a pickup. I'm not quite sure how best to do this, nor how to deal with an item that could be both an action and one of the other categories and have it show up as both.

Any help would be greatly appreciated! I'm attaching picture (couldn't seem to cut and paste from the scripting window) of what I have so far to see if I'm on the right track?

[comment]

Well, you could make yourself a calculation field, along the lines of:

Case ( DeliveryDate ; "Delivery " ) &

Case ( PickupDate ; "Pickup " ) &

Case ( ActionDate ; "Action" )

so you would have a label determined by which date field/s were filled.

However, I believe a better strategy overall would be to move ALL dates into a SINGLE field and add a 'real' (i.e. not calculated) category field. Records with two or more dates should be split into multiple records. This will give you the ultimate flexibility for finding, reporting and displaying your data.