Hiya,

I have this database of peronnel records that I am trying to create. Currently in each individuals file I have a portal which lists the certifications, the date of the certification, length of the certification, expiration date.

I would like to somehow like to each month make a list of each persons certifications that are going to expire soon with their name. I've messed around with it for far too long with no success! Any help would be appreciated

Trisha

Define 'soon'. Say that you want to find all employees with expiring certs during the next month (30 days from and including today).

From the child file with the certification data:

Enter Find Mode [ ]

Go to Layout {one which holds the expiration date}

Insert Calculated Result [select, "expirationdate", "DateToText( Status(CurrentDate) ) & "..." & DateToText( Status(CurrentDate) + 30 )"]

Perform Find [ ]

Yes... 30 days would be good. But unfortounately I don't think I'm following what exactly it is that would be good to do.

Well, once you have the found set, you can set up a report to view or print the list of employees with their name and expiration date.

Which part is it that you (or I) am not quite understanding?

From the child file with the certification data:

This would just be the related file? Not my main personel file?

Enter Find Mode [ ]

Go to Layout {one which holds the expiration date)

I'm not sure about this... in my related file I only have 1layout.

Insert Calculated Result [select, "expirationdate", "DateToText( Status(CurrentDate) ) & "..." & DateToText( Status(CurrentDate) + 30 )"]

when I type the above in the insert calculated result brackets, it comes up with several errors... I'm assuming I'm doing something wrong!

Perform Find [ ]

I'm quite sure that its me that's all confused. This silly database is going to make me lose my mind!

Hmm? You must be typing something extra, like the outer quotes? It's also important that the + 30 is inside the parenthesis, so the arithmetic happens before the phrase is converted to text.

I added this to the same files I'd attached earlier. It has a few embellishments also (which I couldn't resist), so that you can run the Find from either file. It will return to the People file if none are found; but it will stay in Certifications if some are found (which makes the most sense, at least for now).

Certifications.zip

Trisha:

quote: when I type the above in the insert calculated result brackets, it comes up with several errors...

What sorts of errors? I'm just curious. Otherwise...

If you think of the problem as straight logic, all you're looking for is a date, and a distance from that date. So, whether it is 30 days or 90 days (or whatever) all you have to do is search for things that fall within your parameter, or set up a system that indicates these items to you automatically. There are a number of ways to do it, and Queue and Fenton have given you a couple of fair examples. FileMaker can do (nearly) anything, if you can construct the logic of the problem ahead of time. Take a solid look at Fenton's solution and you should see a good example of how to use FileMaker's power to your advantage.

Just my five cents.

-Stanley

Go Sox!

Thank you very much everyone for your replys and your help in my endeavor. I was trying to work on it this evening, but I'm becoming bleery eyed. I shall resume probably on Monday! Hopefully I should be able to figure it all out with all of the help you've posted here already.

Trisha

Thank you again for your help. I got it figured out this AM

Trish