Figure 1: Relationship -- Showing the relationship from Reports to Owners.


Figure 2: Relationship -- Showing the relationship from Owners to Farms.


Figure 3: Selection options -- See the difference between selecting one owner and multiple owners.


Figure 4: Relationship -- From Reports to Farms.


Figure 5: Selections -- The different fields that appear based on the selections made from the Farms field.


Figure 6: Reports screen for Farms -- All the available reports and the checkboxes to select are based on what the user wants to see in the report.


Figure 7: Easy selection options -- There may be dozens of different options, so these buttons make it easy to select or deselect all of them at once.

Figure 8: Script code -- Setting a field with the value list items.

The Hay Manager solution has some unusual aspects in that the program can handle multiple owners, each with one or more farms, and each of those farms having multiple fields. I had to find a way for the user to quickly find the records and report he wanted. Scripting a search was impractical because the parameters each user could search would vary dramatically and probably involve multiple find requests. Also, the users weren't familiar with computers and it would be too complex for most users to do the necessary finds manually.

First, I created a Reports table with some global fields:

G_RanchID
G_FieldID
G_OwnerID
G_StartDate
G_EndDate

I set the G_OwnerID field to use a value list (using checkboxes) of all the Owners in the system. In the value list, you're only going to display the second field, which provides a series of checkboxes allowing multiple selections.

Next, create a relationship to the OwnerID field in the Farms table (figure 1).

Then assign another value list to the G_RanchID field that shows only related values (figure 2).

Thus if the user selects one owner, the results display only the farms for that owner. If the user selects more than one owner, the list of farms expands to show all the farms for all the selected owners (figure 3).

Now define another relationship from the Reports table to the Fields table (figure 4).

Add a checkbox value list to the field G_FieldID, which will display only those fields that belong to the selected ranches and that belong to the selected owners (figure 5).

There are different reporting requirements in this solution. If you're running a report based on Owners, you only need to see the ranches for the selected Owners. If you're running a report based on Farms, you only need to see the Fields for those Farms. The most common requirement is to look at Farms and Fields, so put each set of buttons and fields on separate tab panels on the Reports screen so you can focus on the Farms section of the Reports screen (figure 6).

There's just one more thing to do: Make it easy for the user to deselect all the checked values or select all available options (figure 7).

The Deselect button is obvious: It simply clears the Global field. The Select All button uses the value list items to populate the field; i.e., select all the checkboxes. I already set up a series of value lists to display all records from each file. The script is simple: It starts with the field you want the values entered into and enters all the different values with a carriage return between each value. This is the second line of code shown in figure 8.

SetField [Reports::G_RanchID; ValueListItems(get(filename);"Ranches")]

Ranches being the name of the value list.