Auto Enter "Gotchas" - Part II
By Ilyse Kazar (kazar@datatude.net)
BONUS: None
PLATFORM: Macintosh & Windows
RATING: Advanced
NOTE: See issue #39 for Part I
At first glance, the option just below, which appears after you click "Import" in the Import Records dialog, seems to be very straightforward.
"Perform auto-enter options while importing (modification date, serial number, lookups, etc.)"
However, you can leave any auto-enter actions you've defined in a file turned on during an import, or you can turn them off in one fell swoop by unchecking the box next to this option.
But what if you need the auto-enter actions to take place on some fields, but not on others?
"Import Records" with Mixed Auto-Enter Requirements
Let's say you are building a solution for a business that issues many price quotes to its customers, but most of those quotes do not become actual orders. You don't want to inflate the size of the Orders file, nor use up Order Numbers, unless the customer decides to go with the order and sends in a deposit.
One way to handle this situation would be to use separate "Quotes" and "Orders" files, and if the customer decides to actually buy the merchandise, the program will import the Quote into the Orders file. You then create a Quotes file, and define the Product Description field and the Price field to be lookups, based upon Product Code, from a "Products" file. (For simplicity's sake here, we are treating Quotes and Orders as single, "flat-file" records, not as master records with related line items.)
Now let's say that some walk-in sales are entered directly into the Orders file, with no Quote being generated first. So, of course, you also define the Product Description and Price fields in the Orders file, as with the Quotes file, to be looked up from the Products file. The Order Number is an auto-entered serial number, so when you import a Quote you decide to leave auto-enter options turned on.
Everything sounds logical so far. Well, what happens if a Description or Price is modified in the Quotes file? You will get a call like this from the end-user: "I typed 'Dye Lot 8212' into to this guy's quote description, and charged him $2.00 extra, but when I clicked 'Create an Order' on the quote screen, my changes didn't come over into the order!"
Gotcha! When the record was imported into Orders with auto-enter options turned on, the Description and Price information were re-looked up! How can you fix this problem? You need auto-enter options turned on to generate the Order Number serial when you import a Quote. But you need auto-enter options turned off so that the Description and Price entered into a Quote are not changed upon import.
Let's examine some different techniques to accomplish this.
By the way, remember that when using either of these first two approaches to the problem, your file must be on a layout that contains a field being Replaced, or the command will not work.
Technique 1:
Import with Auto-Enter Options Turned Off and then Replace Serial Numbers
One way to handle this dilemma would be to leave the "Prohibit modification of data" option unchecked for the Order Number serial. Then, after importing a Quote with auto-enter options turned off, you could Replace the Order Number, selecting the "by serial number" option in the Replace dialog.
At first glance this appears to be a simple answer, but, in reality, once you allow modification of a record ID such as Order Number, you have a special responsibility. You need to ensure that the user can never accidentally change the Order Number. To be safe you cannot allow the users to design layouts and cannot switch to any layout where access is allowed to the Order Number. You'll also need to create a special layout where the user can enter an Order Number for a Find. (Better yet, just to be sure the user never ends up on that layout in Browse mode and inadvertently changes an Order Number, create globals for entry of the Find criteria and write a script to set the Find criteria from the globals.)
Technique 2:
Import with Auto-Enter Options Turned On and then Replace Data by Calculation
An alternative way to handle mixed auto-enter requirements upon import is to leave auto-enter options turned on, so that the Order Number (in the above scenario) will be generated (and leave the "Prohibit modification of data" option checked, thereby keeping that Order Number safe). Then, after importing, use a relationship to the source file (Quotes) for the import to restore any user-modified values in the Description and Price fields.
To achieve this, you will need to define a Quote Number field (not a serial) in the Orders file, and establish a relationship matching it to the Quote Number field in the Quotes file. Include the Quote Number field in the import and then perform a Replace by calculation on the Description and Price fields, using the relationship to Quotes. The following script steps will pull in the data as it was originally entered in a Quote:
Replace [No dialog, "Description", "Quotes::Description"]
Replace [No dialog, "Price", "Quotes::Price"]
However, in most real-life situations the Quotes file will have a related Quote Line Items file, and the Orders file will have a related Order Line Items file. Further, there may be many line items per master record, so keying the Replace by calculation would become much more complicated than in the simplified scenario described here.
Technique 3:
Pre-Create the Target Records and then Import Using the "Replace data in current found set" option
This is a good technique, but a bit tricky if you will be importing more than one record at a time.
Still using the above example, let's say you wish to import three Quotes at one time into the Orders file. First, you'll need to set a global "Number of Records" field in the Orders file to "3", the number of records that will be imported. Next, you will need to force the found set in the Orders file to 0 records (by using Find All and then Find Omitted).
Then, using another global "Loop Counter" field to compare how many new records have been created versus the value in "Number of Records," write a loop to pre-create the same number of records in the Orders file as you will be importing from the Quotes file. (The Loop will be exited when Loop Counter = Number of Records.) Of course, when these new records are created, the Order Number serial will be auto-generated.
Finally, when you Import the records from the Quotes file, select the "Replace data in current found set" checkbox, and leave auto-enter options turned off for the import. Since the Order Number field does not have a corresponding field in Quotes, the Order Number generated when you pre-created the records will remain in place. And, since you have left the auto-enter options turned off during the import, the data that is replaced into the found set in Orders will maintain any changes that were made to it in the Quotes records rather than being re-looked up.
More about Importing Serial Numbers
As we saw in the above example, when auto-enter options are turned off during an Import, serial numbers will not be generated. Depending upon the exact nature of an import, the results in an auto-entered serial field will vary:
If the serial number field IS NOT targeted in the import (i.e., there is no "arrow" pointing to it in the middle column of the Import Records dialog)
--With auto-enter options turned OFF, it will remain blank.
--With auto-enter options turned ON, the serial number will be generated, and the "next value" in the auto-enter options will be updated.
If the serial number field IS targeted in the import
--With auto-enter options turned OFF or ON, the serial number field will accept the data from the source file. If the serial number is imported and the auto-enter option is turned on when importing the "next value" in the auto-enter options will be updated by the number of records imported.
"Import Records" and Other Auto-Enter Options
The behavior of fields defined to auto-enter a value "By calculation", or to auto-enter the Creation Date, the Creation Time, or Creator Name will behave exactly the same as fields defined to look up values upon importing records. You can handle import issues as described in the above "Quotes and Orders" scenario for these auto-enter options.
For Modification Date, Modification Time and Modifier Name the first two techniques described for lookup fields will not work. This is because when you run an operation such as the Replace command used by the Techniques 1 and 2, fields having these auto-enter options will always default to the current settings on the computer being used. Only by using Technique 3 above will you be able to successfully maintain modification information upon import.
[Please see ISO Issue #39 for Part I of this Article, which discussed Auto-Enter Options and the Duplicate Record command.]
## END ##