System Generated Numbers
Up until now, we have been adding the account number for each new record ourselves. And, just as our customers are unique, so must their account numbers be. As you might imagine, having two or more customers with the same account number would make things pretty complicated. When we manually enter the account number, we greatly increase the chances of this happening. So then, what we need is a way to automatically assign a unique number to each record. (This is often a basic database requirement for many types of records.) "filePro" can provide this function in a number of ways. The following procedures show a most reliable and flexible method.
First, the field on the screen has to be "protected". This way, the user can not modify an account number by accident. The computer will generate the next highest unique number and once generated, it will stay with this customer record forever.
From the main menu, go into Define Screens. (Incidentally, from this point forward, it will be assumed that you will execute all filePro design operations from the main menu. It will not be mentioned again in the instructions.)
Update and modify Screen 0 to look like this:
Once the account code has been protected with an "!" , you can return to the main menu.
The next step in obtaining a unique number is defining a file that will hold this number. Such a control file can hold many unique numbers and other important information about the application we are designing. This will be a file that currently has only one field in it. Also, this file will only have one record, specifically record #1. The last assigned account number will be stored on record #1 of this file in field #1. Then, each time we add a new account, the program will do a lookup to this particular file and record and retrieve the number found there. It will increment this number by 1, thereby giving our new account its own unique number. This system will work until there are more than 999,999 account code's required. Not likely for our first Video Store. (Maybe once we're a nationwide chain, we can think about raising this field to a 7 digit number!)
Go into Define Files and create the following regular filePro file. It is called "vidctrl" because it will hold our control numbers and information.
Enter the following data:
When you are done, press ESC to save your work.
Press X to finish the file design. The options screen will appear.
Enter the following:
Next, go into Define Processing for the file "vidcust".
Select INPUT processing.
Make sure your cursor is somewhere in the first element and press F3 to push down all the current processing. This leaves you with a new blank element number 1.
We are only going to be getting a new account code for records that do not have one yet. If the account code field is already filled, the subroutine which gets the next unique number will not be run. The "if" condition on line 1 of this code assures this.
Enter the following:
We will now add the subroutine that does the actual work. It is based on doing a lookup to the control file and retrieving the next number.
Enter the following and leave your cursor in element 9:
To define the lookup, press F5 while your cursor is on line 9. The lookup editor will popup. Enter the name of the file we will be looking up, "vidctrl".
Enter an "r" for a record number lookup. (We are going to be looking up to record number 1.)
Enter "aa" as the field in "vidcust" that contains the record number. (We will fill the dummy field "aa" with a 1 so that we can lookup to this particular record.
It is also VERY IMPORTANT to put a "Y" in the "Protect Record in Lookup File?" prompt. This is only necessary on multi-user systems, but it is VERY necessary. It tells filePro not to let anyone else retrieve a unique number out of this record at the same time we are doing so. It will prevent duplicate account codes from being issued. Protecting the record means "locking the record" so no other process can read it while we have it locked.
Enter the data as shown:
If Lookup Fails: In other words if record number 1 in the "vidctrl" file can not be opened, what should filePro do? We will choose to have filePro do nothing in this case. If the lookup fails, we will take our own action to explain the situation to the user. (If we had chosen "E" - Report an Error, filePro would put up a standard "lookup failed" message. If we had chosen "B" - Blank the Field, filePro would have blanked our lookup retrieval field. Neither are very valuable options, and quite generally, you should always handle lookup errors yourself.)
To complete the lookup, answer N to the "Create Browse Lookup" prompt.
When you are automatically returned to the processing table, you will see that filePro has created a lookup line for you that looks like this.
Finish the code for this subroutine by adding the final three lines.
Line 10 handles the error if the record can not be found. Line 11 retrieves the value of field 1 in the "vidctrl" file and assigns it to the Account_code field in our current file ("vidcust"). Then, line 11 increments the value of field 1 in the "vidctrl" file. The WRITE command on this line ensures that the file will be updated on the disk as soon as the O/S can do it. Line 12 returns the processing back to the place it was called from on Line 1 (gosub getnum)..
Press ESC to save your work on this processing table.
You will see a prompt for "Check Syntax? (Y/N)". Enter Y , and fix any problems found by the syntax check.
Hardcopy the processing if you like when prompted. (Still, cross reference hardcopy is optional as well.)
Now we must go into the "vidctrl" file through IUA and tell our program which unique number (Account_code) we want to start with.
Go into Inquire, Update, Add (IUA).
Choose "vidctrl"
Choose Screen.0
Select 3 - Add Records.
We have manually assigned 4 account numbers already, the highest of which was 104. We can start the next account number at 105.
Enter the following data:
Press ESC to save this record.
Since you selected Add Records Mode, filePro will present you with another blank record and wait for you to fill it. Do not enter anything on this record. Press Control-C to stop adding records.
Press X twice to return to the main menu.
We are ready to try the new process. We can use the menu we designed earlier to do this.
Select G - Run A User Menu.
Choose "video".
The "Video Store Main Menu" will appear.
Select 1 - Inquire, Update, Add - Customer.
Select 3 - Add Records.
Add the following data:
Press ESC to save your work.
When the "blank" screen comes up, BREAK out with Control-C . Then go back to record #5 (pressing the UP Arrow should work to do this.)
You will see that the next Account_code of 105 has been properly added automatically by the "getnum" subroutine.
Leave this record and bring up record number 3.
Press U to update this record, but don't change anything.
Press ESC after you enter the Update Mode. You will see that the Account_code does NOT get changed. This is exactly the operation we want. New accounts (those with no Account_code) get new numbers, old accounts (those already having a code) stay as they are.