I have been doing database design with Access and VB for quiet a while and started using FM about 3 months ago. I have two questions for the pros out there.

I have a database used for employees to input their weekly work report at the end of each week. How do I:

Q1. Once he/she selected name and date from 2 dropdown field, the program will do a search, if there is a record with the same name and date, bring the record for user to modify or add more info, if no match, generate a new record. It sounds easy but I really don't want to add a Go button or something to start the script. Can I start the script once these two fields are filled?

Q2. I want the uses be able to view their previous records but can only make changes to the current week's record. Is it possible to do that? I can not find any script steps for that.

Looking forword to your input. Thanks

You can solve your problem by defining relation ship for quick search and adding new record.

Let me explain this solution.

First define a global name and date field. Than define a relation ship with same table. Than place global fileds in the layout. This fields will be used for quick search. Than place other fields (wich are entered) from new relationship.

It is all done! When you select name and date if any match is found will be diplayed, if there is no such a data after filling empty fields creates new record.

If you wish to do that by scripts you can use a trigger plugin which trigers script after field value is changed. You can find such a plugin from http://www.24usoftware.com

Your second problem can be solved by user acounts.

Thank you for your response, Adam. It will take me a while to digest and try it on my program. I will let you know how it turns out. But for the second question, how can you set, for each user, different privileges for the most recent record and privious records? Thanks

I'd recommend using script branching to bring the user to a view layout or edit layout based on the whether they are on the current week. You can use the get(currentdate) function to help figure out if the current date is in the same week as the record they are on. The specifics depend on if you're holding week numbers or the date the week starts in those Weekly Work Report records.

I'd recommend using script branching to bring the user to a view layout or edit layout based on the whether they are on the current week

Thanks, Ender. Could you give me a little more detail? What is script branching? I don't think I have any trouble to decide which records are "current week". The problem is I can't make those records editable and the others only "viewable".

Thanks, please help!

Well, as I said, two layouts: one for editing, one for viewing. Your navigation script might look something like:

If [ get(currentdate) ≥ Week Start Date and get(currentdate) ≤ Week End Date ]

Go to Layout [ Edit Layout ]

Else

Go to Layout [ View Layout ]

End If

The two layouts would look pretty much the same, but the View Layout would have the Field Behavior set to not allow access in Browse Mode.

Thank you so much, never thought of that before. Sure is a clever idea.