Sesame Library | More on Sesame Form/Subform Capabilities

We've received a considerable number of questions as to just what the mechanism and procedures will be in Sesame for making use of its powerful Form/Subform capabilities. Here's an example. Suppose you need to set up the classical customer Sales database.

In Q&A, this would probably consist of three separate databases:

Inventry.dtf
Customer.dtf
Sales.dtf

The Customer database would contain basic information about each customer, such as their address, contact information and a unique Customer Number or ID.

The Inventry database would contain basic information about each inventory item that you sell, like pricing, description and unique Inventory Item Number.

The Sales database would be a large, complex form with lots of fields.

In one section, the Sales database would have a field for the customer number. You'd enter the customer number and your Q&A programming would perform an XLookup to Customer to retrieve the name, address and other pertinent data about the customer for the current Sales record.

Another section would have a fixed number of lines on which you would enter each item the customer was buying. Each line would have fields for Item Number, Description, Quantity Sold, Price, Extended Price, and so forth. You'd enter an item number, and Q&A programming would perform an XLookup to Inventry and retrieve the description, price and other pertinent data about the item for the current line in the current sales record line. Of course, you could have some fancy programming to display pop-up lists and perform automatic calculations. In any case, in most Sales orders, you wouldn't use all the lines in this section. For Sales orders containing more items than the fixed number of lines available in the form, you'd create multiple records.

You might also have a section in Sales for the sale totals, where all the line items were totaled, along with fields for shipping cost, shipping method, sales tax, and the like.

For first-time users, or those upgrading from their Q&A databases, Sesame can be set up exactly the same way! No difference in what you are used to doing!

However, if you want to take advantage of Sesame's subform capabilities, the setup will be somewhat different. There will the same three kinds of records:

Inventory
Customer
Sales

But they will all be contained in the same Sesame database.

In addition, there would be a fourth kind of record, called LineItems. You'd set up LineItems as a subform or "child" of the Sales form. This way, Sales would be a much simpler record, containing only fields for the customer information and the sale total information. The LineItems records will also be very simple, containing fields for Inventory Item Number, Description, Quantity Sold and Price. There is no need to have a field for Sale Number to link the LineItems to the Sale. This will be done automatically as you enter your Sales. You can include the Sale Number on the LineItem, but it is not necessary.

Now, instead of having to enter anything about the customer to do a lookup, you will simply select the customer from a drop-down list that is linked to Customer. Sesame will then look to Customer to get the rest of the information. These lookups can be set up either to keep the current customer information permanently with the sale (static, using XLookup), or to automatically show the current customer information each time you view the sale record (dynamic).

So, you've selected the Customer for this Sale, and you're now ready to enter the items sold.

To do this, you move into the LineItems subform and fill out the first record. You can enter the Item in the same manner as you did the Customer, by selecting from a list of items drawn from Inventory. Sesame can then look up that item's current description and pricing information. You enter only the number of line-items that you actually need. If you're selling only three items on this sales order, you add three subrecords. If it's 50 items, you add 50 subrecords. If it's 1000 items, you add 1000 subrecords. When you save the Sale, all of its LineItems will automatically be saved with it and all the LineItems will automatically be linked to their Sale. There will be some retraining here for Q&A users, but the benefits should be apparent.

Once you've entered all your LineItems, you return to the main Sale form and enter your shipping charges, sales tax, and so forth. That completes the sales order.

You will now be able to get reports of how many people bought widget xyz by simply retrieving xyz in the LineItem subform, without regard to what line it appeared on in the order. Here's where things may appear a bit strange to Q&A users. Searching for P.. in a City subform of States could bring up a record showing the State Pennsylvania and the City Altoona. Altoona starts with "A" not "P". What is happening? Subform searching works this way. Sesame, like Q&A, is form-based. When you specify retrieve criteria in a subform, you are asking the system to find all of the parent forms with at least one subform that meets your request. Therefore, Pennsylvania is correct since both Pittsburgh and Philadelphia are somewhere in the city forms for that state. This allows you to retrieve the entire Sale and all of its LineItems for any sale where widget xyz was sold. If needed, you can use reports, or search the LineItems subform stand-alone (outside of its parent form) to be more subform-centric about the information you retrieve.

To sum up, information in Sesame forms can be kept static or made dynamic. Individual Sales line-item reports can be run right from the Sales form. Line-items will be virtually unlimited and can be presented in a scrolling window. So, it's some of what you do now and some new stuff to get you a better, more efficient database.