stale date = change subject listing

[hafnerd]

using Mac OS 9 and Filemaker 4.1

i have a layout called "Inventory". in the inventory, i have a list of books. each book has a subject category. there are about 11 items to choose from in a drop down list.

2 subjects, "Stationary" and "Comic Book", have new items arriving weekly. the new arrivals i assign a new subject in the pull down list. ("new stationary" and "new Comics".)

i would like to make a field that i can put a Stale Date in to trigger the subject to change.

example: a comic is new and assigned the "New Comics" subject in pull down list. there would be a field called Stale_date_subject. in that field i put a date that is a week or 2 from entry date. once that date arrives in the Stale_date_subject, the field triggers the subject to change to "Comic Book" in the subject field and it is no longer considered as a new item.

Can i do this w/ FMKpro? how? please help. newbie in a bad situation. (had 2 break-in's and all but one iMac stolen. i miss my OS X flat panel.)

[ESpringer]

I might go at it the other way around. You have some sort of "ReceiveDate" to indicate when these things arrive, yes? Then, start with your simple category listing which doesn't specify new or old.

Then, add another calculation field "DatesensitiveSubject" which looks at the subject and checks whether the receive date is recent, and makes a tweaked set of subjects based on this info, something like (though I haven't checked the syntax exactly)

If(Today-ReceiveDate<7 and subject="Comics","New Comics",If(Today-ReceiveDate<14 and subject="Stationery","New Stationery",subject))

(I'm assuming you mean to refer to "stationery" rather than "stationary" but If I'm wrong, please forgive!)

[hafnerd]

ESpringer:

Thank you. but i need a date field that i can modify from time to time that would change the subject pull down field for the 2 subjects mentioned above. not all new items will remain "new" from 14 days or what ever is plugged into the script. [say i have 2 new titles come in. (blade of the immortal and chobits) i want the title "Blade of the Immortal 1" to expire as a new comic on 11/15/2003, but Chobits vol. 1 to expire 11/20/2003]. can i create a script in a date field to change the 2 items in question in the pull down subject?

sorry. i spelled the date wrong. could say i am still upset at theft of flat-panel imac *sniff*, but i'm just a bad speller.

ps: i grew up in CT.

[ESpringer]

Hafnerd -- OK, in that case, just define a field "FreshDays" (or whatever) for the number of days for which *this* item is to count as "new"... You'll leave the field blank for all records except comics and stationery.

So, this makes your calculation even easier: now you just need to say that ANY record's DateSensitiveSubject should yield "New ______" exactly when Today - freshdays <Today. You could have a standard "FreshDays" interval auto-entered for comics, or for Stationery...

Or, if you prefer, use a StaleDate field for each record, and make the calculation add "new" to the beginning of the time-sensitive category calculation exactly when Today<StaleDate. You can even have the StaleDate field automatically set itself -- in the case of comics or stationery -- to a certain date, so that you often have an acceptable StaleDate without having to enter it in yourself.

I still don't see why you want to make use of a script... why rely on something that has to be triggered when you can have the database do it as a matter of course? It's much cleaner to have the Subject category field just report what category it is (comics, stationery, etc.) and have a calculation modify it to add "new" and then to stop adding "new" as time goes on. No?

(There is, though, the odd fact that FileMaker doesn't know how to recalculate what "today" is except through a re-launch of your database. So, if you keep your FileMaker stuff open round the clock, perhaps you would at least to set up an AppleScript that closes and opens the database at 12:01 am...)

Sorry to hear of the thefts -- it can be quite distressing indeed!

-E Springer

[hafnerd]

Or, if you prefer, use a StaleDate field for each record, and make the calculation add "new" to the beginning of the time-sensitive category calculation exactly when Today<StaleDate. You can even have the StaleDate field automatically set itself -- in the case of comics or stationery -- to a certain date, so that you often have an acceptable StaleDate without having to enter it in yourself.

i think the above is sounds like something i would like to work w/. but i would enter the data and, say "FLCL" as "New Comic" in the subject field. then i would put in a date (whateverdatethatis) and once the time arrives, the field StaleDate would trigger the item to change to just "Comic" in the subject field. same w/ sationary. best if for each record since there are many other subjects, but only comics and stationary will require use of the stale date field.

Can i do it that way?

thank you for your kind words about the theft. REALLY bummed me out.

[ESpringer]

Hafnerd, it seems you're still imagining that a text field is going to change its value, based on a date. You could set up a script to do something like that, but it's unnecessarily cumbersome.

The trick to my suggestion is that *you* never data-enter anything as "New Comic" at all. Your data entry just deals with what the category is ("Comic"), not whether it's new. Then, a separate calc field works constantly behind the scenes (on the basis of that data-entry text field along with the StaleDate field) to show "New "&Category UNTIL the first time the database opens and the stale date has arrived. Then, the calc field shows just Comic (or whatever) from then onward. In other words, the calculation field would look like this:

If (Today<StaleDate,"New "&Category,Category)

The above calculation says: *If* the stale date hasn't come yet, *then* toss in "New" before the category label; *otherwise* just give me what I already entered as category label.

What you'd have to be willing to do is to display, print, and work with this new calc field in whatever place you used to just display the category field.

-ESpringer