A common need in business environments is to input numeric data from paper forms to Alpha Five. It is good practice when doing data entry of this type to keep a running balance of the data that has been entered. At the end of the data entry session the total manually derived (often using a calculator) can be checked against the batch total on the computer to check whether entry errors have been made.
A batch total of this type can be done with xbasic, but here I will create a batch total routine using Action Scripting. The batch total can be kept in a variable but this value will be lost if the application is closed for any reason, unless it is specifically saved before closing the application. I will save the batch total to a table so it will retain it’s value from session to session until it is reset by the user. Note also that the table that stores this value can easily store many batch values, so it is not necessary to create a new table for each batch total required.
First create a simple table named "Numhead" to hold the values as shown.
The only essential fields are Number1, this is the field on which the batch total is based, and Link, which will be used later.
Now create a table to store the batch value. Call this table "Batch". It has only 2 fields, Number2, where the batch total is stored, and Link, which is used later.
Next define a posting field rule for the field "Number1" in the "Batch" table. This will post the value in "Number1" to "BATCH->Number2" (Table=Batch, Field=Number2"). Notice that this also takes care of situations where you change the value in "Number1" after it has been saved. If the value in "Number1" is deleted the post will undo and if the value in "Number1" changes the value in "Number2" will change. The "Link" field in both tables is used here to provide a link, or common value between the tables. The condition 1=1 is used as the linking condition. If 1=1, which it always will, then "Number1" is linked to and will post to "Number2". This condition could also be .T. or any other condition that evaluates to .T. all the time.
Since we are using "Link" as the linking field, the value must be the same in both tables. In "Batch" , simply put 1 in the link field. In "Numhead" you must enter 1 for each record so it will link. This can be done in field rules by making it the default as shown below.
Now we need a way to display the value that is in "Number2" on the form created for data entry. There are 2 ways to do this. One is to create a set, with "Batch" as the parent and "Numhead" as the child. The other way is to use Action Scripting and a variable to show the value. Use of a variable is shown here.
Make a data entry form called "Testbatch". This would be your actual data entry form. The "Number1" field is the field that will be totaled and "bat number2" displays the batch total but since it doesn’t exist yet you can’t put it on the form yet.
Create the variable "Bat Number2". I want this variable to be initialized every time I activate the form. Right click on the form (in design mode) and select Events, on activate as shown below.
The Action Script for this event must create a variable called "Bat Number2" and then refresh the screen to make sure that the current value of the variable is displayed.
Select Add New Action, then Set variable(s) to field valued - external table. Now select the table "batch", specify record by record number 1, since there is only one record, and create a variable named "bat" for the field "Number2". Notice that you have named the variable "bat" but Alpha Five will add the suffix _Number2 to it to fully specify the variable. The final variable is named "bat_Number2". Now click Next and Finish. Next in Action Scripting add the action: Form, Refresh Display. Make sure that the Refresh is after the Set variable, click on the blue disk to save the Action Script, and close the Code Editor. The variable that was just created will now appear in the drag-drop list and can be dragged onto the form as shown.
Now the variable is on the form, and it will show the value each time the form is opened. We also need to be able to update the variable when the value in "Number1" changes. To do this once again use Action Scripting on an event. The event is "OnSave" and the Action Script is exactly the same as what was put on the "OnActivate" event since we want it to do exactly the same thing. If you open the Action Script for the "OnActivate" event and right click on the first event you can copy it to the "OnSave" event. Shown below is the "Copy Script Actions" dialog box that pops up when you right click on the first action and select Copy.
Now go to the "OnSave" event, and in the Code Editor, right click and select "Paste". The Action Script actions are now copied to this event.
The only problem left to solve is how to reset the batch total to 0 to start a new session. This could be done by opening the "Batch" table and manually resetting it but it is more convenient to do it with a button on the "Testbatch" form. From the toolbox, drag a button to the form. Call the button "Reset" as that is what the button will do. Now in the Code Editor you can add the code to reset the value to 0. Add the actions shown below to do this.
First add the action "Xbasic Change Record" and format it as shown. Next add the action "Set variable(s) tofield values - External table" which can be created from scratch or just copied from a previous event, it’s the same one again. Lastly, add the action "Refresh Display" to make sure the correct value is displayed.
That’s all there is to it. You can use the same "Batch" table for other batch totals by either adding a new field to "Batch" or adding a new record and setting the variable definition to field 2 rather than field 1.
Russ Boehle is president of NESSteel Inc., a specialty steel distributor and processor headquartered in Stafford Springs, CT. One of his passions is Alpha database software which he has been using since Alpha Four version 2. NESSteel currently runs on Alpha Four version 6 but Russ is working, as time permits, to migrate to Alpha Five Version 5. With no formal training in computers, Russ is self taught, starting with an old IBM PS2-30 which incidentally came with no operating system installed. His college training in engineering was before personal computers. Aware that he needed to computerize his business, and finding that what was commercially available was not very good, Russ tried various database programs and found that Alpha Four was perfect for him and his business.