Overdue Popup. Simple Q.

[Sambuca]

I'm still a Newbie to Filemaker but I did use it a couple of Years ago in my Year 12 IT class. I've made a Database for a Physio Practice. They hire out Items to customers. I remember doing a Video Store Database in Year 12 but I can't seem to remember how to get the Overdue thing working. What I was thinking of doing is to make an Overdue field and for a 'Yes' to come up

"IF Date Due is > Current Date and

Date Returned Field is Empty."

Then I could do a find button to press for a list of all Overdue items (ie. when Overdue Field = Yes)?

Thing is I can't remember how to do the Overdue Field to Popup with a 'Yes' if it is Overdue. Should it be a calculation of some sort?

If anyone has any more Advice on ways to improve this Hiring System please share them.

Thanks

[danjacoby]

You got it backwards; it's:

if((CurrentDate > OverdueDate) and isEmpty(ReturnDate), "Overdue", "")

Make the CurrentDate field a calc field, unstored, that returns status(CurrentDate).

That should fix it.

[Sambuca]

Thankyou very Much. I've got "Overdue Item" popping up now when someone has something overdue.

I do have another quick question though if that's alright.

I've made a Button and I want to make it print out a report of all those with Overdue Items. I've made a New Extended Columnar report and am making a Script. But Is it possible to make a script so it automatically "Finds" Fields only with "Overdue Item"?

I can only seem to make the Script Enter Enter find Mode but not actually find only "Ovedue Items".

Some help would be greatly appreciated.

[mikerobertson]

anthony, you need to go to find mode and actually do a find of the items you are seeking, then immidiately open your script in scriptmaker that is to do the find, and close it. it will ask if you want to keep or replace your find, and you want to click on replace. that will save the last find that you set in find mode in that script. make sure that from then on when you edit that particular script, that you leave the "keep" option selected when you close it, or you could inadvertently change the find criteria, making soup out of your script.

two points...make sure that your "perform find" script step has the option selected to restore finds, and the only way (that i know of) to see what find criteria is saved for a script is to print it to a printer. also, one find only can be stored for a single script...if you need more finds in one script, you need to put each separate find in its own subscript, saving the appropriate finds in each.

hope that helps!

[helfire]

one other minor point - Case() works a bit faster than If().

[Sambuca]

Ok So I'm going well now. I'm using this line for the Overdue items:

If((Today> Date Due) and IsEmpty(Date Returned), "Overdue Item", "")

Only problem with this is that when I create a New Client and the "Date Hired" field and the "Days Borrowed" field are empty then "Overdue Item" is displayed. I only want that Displayed if there is an entry in the "date Borrowd" and "Days Borrowed" field.

So basically what Im asking is how do I ad a statment to the IF that says

Display "Overdue Item" only IF "Days Borrowd and Date borrowed" have data!

Cheers.

[danjacoby]

So add it into the calculation:

If((Today > Date Due) and IsEmpty(Date Returned) and not IsEmpty(DaysBorrowed), "Overdue Item", "")

[Vaughan]

Don't use TODAY use the Status(CurentDate) function instead.

[Sambuca]

Cheers Guys. This is a wicked Site.

[Thom]

Unstored calcs are good for printing, bad for searching. Each find has to build a new index. With lots of records, this can get very slow.

Make a calc that shows a 1 if the item as been returned, 0 if not.

code:

---

Case(

IsEmpty(dateBorrowed), TextToNum( "" ),

IsEmpty(dateReturned),0,

1

)

Have your search script insert the current date:

code:

---

Script: Find Overdue

Go to Layout [search layout]

Enter Find Mode

Set Field [isReturned, 0]

Insert Calculated Result

[dateDue, "<" & DateToText(Status(CurrentDate))]

[select entire contents]

Perform Find

Go to Layout [results layout]

It sounds like you are experiencing architectural difficulties. You should have separate files for ITEMS, BORROWS, and BORROWERS. Each file needs a uniqe id field. The BORROWS file should have fields for itemID, borrowerID, dateBorrowed, dateDue, dateReturned, etc. You could also add a field to record in what condition an item was returned.