I think you should consider setting up a search layout where the user enters criteria into global fields and then a script uses that data to perform the find. That way, the exact match operators and other such details can be added by the script when setting up the find requests.
Find the the "most recent" depends on one marks one record as "more recent" than another. If there is a creation date or auto-entered serial number, you can sort your records (there are several "sort strategies possible), to make the most recent record the first record in the found set and then your script takes the user to the first record in the found set to show the most recent. If "most recent" is the most recently created record, then immediately after your find, your records are unsorted and the last record in the found set will be the most recently created record of the set.
Ah. Ok. Would I have to create a new table for searches? The layout is based on the Lesson table now. That's why I have the script trigger to switch to find mode when entering the layout. I wouldn't want editors to mistakenly change info in the Lesson records while trying to perform a search. Can you explain global fields a little? Could I use value lists as the search expressions?
There is an auto-stamped creation date in the lesson history records that I would use to determine the last created history record for a specific lesson record.
You don't need a new table, but using a globals table for all global fields not used to define a relationship can be very useful. Global fields are fields where you specify Global Storage on the storage tab in field options. They are useful for script supported searches for two reasons:
Data entered into a global field by one user is not visible to other users--who can in turn enter different data not seen by the first user. Thus different users can put search criteria into these fields at the same time and not interfere with each others' searches of the database.
Data entered in to a global field remains accessible when a script enters find mode, so a script can copy data from global fields into the fields being searched in order to build the needed find requests.
In your case, you have an added bonus in that users do not enter data directly into any in your Lesson table and thus cannot accidentally modify data when they thought they were trying to perform a find.
Global field can be formatted with value lists like any other.
Here's a sample script using just one global field: gName to perform a search:
Go to Layout [Layout based on table being searched]
Enter find Mode  //clear the pause check box
Set Field [Yourtable::NameField ; "==" & Globals::gName]
Set Error Capture [on]
Rock n' roll! Thanks Phil. I'm checking it out now.
Quick question, If I have two self-join tables based on a lesson name, should I not make the lesson name a global field?
Why would you do that?
It's not impossible to get something like that to work, but I'm not sure I know what advantage you see to doing that in this specific case.
Actually, I was just erasing that comment cause I figured out it was a silly question.I won't be doing that. Sorry about that.
So, ultimately, I am making gloabal fields on my search layout. Those global fields correspond to the fields I am trying to search on. I make a global lesson name field. Then I make that global field a dropdown with a lesson name value list. Is that accurate?
Sorr if I'm missing a step.
OK. I think I have a real question this time. What if the search layout has fields from different tables? Should I stagger the find process to allow for multiple table searches?
It depends on the results that you want. It's important to understand what takes place when you perform a find on a layout after specifiying criteria in a field from a related table.
Take this example.
Say you have this relationship:
Contacts::contactID = Addresses::ContactID
A given contact might have one or two addresses, a billing addresses and a snail mail address, so this is a one to many relationship.
If you perform a find on the contacts layout where you specify "Sacramento" in the Address::City field, you will find all contacts where the contact has either a Billing OR a Snail Mail address with that name in the city field. This can be confusing since a portal to address on this layout will list at least one address from Sacramento, but will also list any addresses from other cities. If you just but Address::City on this layout, the results can even look wrong since that will show the city from the first related record and yet the find will find contacts where the second related record is from Sacramento even though the field on the layout may show a different city.
If you perform the same find on a layout based on the Addresses table, however, you are no longer specifying criteria in a related table and you will find all address records where the city is Sacramento and fields from Contacts can display the fields from the related contacts record. This should not be confusing under any circumstances.
Thus, the script works to find records whether the criteria specified is for the parent record or a related record, but the layout that's current and how it's designed is an important factor to consider when you design your system for finding records.
I want to combine information from three unique but related tables. The three tables are Lesson, File Usage, and Seasons. The relationship is Lesson::LessonID=File Usage::LessonID and FileUsage::SeasonID-->Seasons::SeasonID. So I may want to do a search with the fields Lesson::LessonName, FileUsage::LessonStatus and Seasons::Year. I would then switch to a results layout that organizes the found records in a list format. Does that make sense?
I was thinking that I could do three searches in one script and use the constrain set function to whittle down the results. I've attached the relationship graph for your information.
So I may want to do a search with the fields Lesson::LessonName, FileUsage::LessonStatus and Seasons::Year. I would then switch to a results layout that organizes the found records in a list format. Does that make sense?
It doesn't answer the key question: Which table, Lesson, FileUsage or Seasons do you want to find?
I'd guess that you want a list of Lesson records, not FileUsage or Seasons records. If so, you need only base your layout on Lesson and specify your search criteria--putting criteria in fields from all three tables as needed. The issue then at hand would be how to display any data from related tables if the relationship from Lessons to that table is one to many. You could display all related data or use a filtered portal where the portal filter uses the same criteria specified for that table in the find to limit the values displayed to only those related records that matched to that criteria.
Now to apply that to the relationships shown in your relationships graph. Your relationship graph shows this:
That is, it shows one to one matching for both of these relationships. That doesn't look right, given the names of the tables. Surely more than one Lesson has the same FileUsage? Surely more than one Lesson is linked to the same Season?
Well, the search may be on the File Usage field where an editor would like to see all the lessons with a "ready" use status. They may also want a list of all the lessons used in Spring 2012. Lessons probably do not have the same file usage but they do align to the same season.