I don’t know how familiar you are with an “Array”, but, until recently, an Array was just one of the myriad features Alpha Five provides that I had not used. Well, a Client wanted a Report being able to select not only a particular Salesperson but select starting & ending dates as well. The Starting and Ending Report dates were no problem – it was selecting a Salesperson from a list that had me wondering.
Alpha provides Lookups from an external table, but that’s only during Data Entry, defining the Lookup in the Field Rules. I was definitely not creating a record, so what were my alternatives? Then I remembered, Mr. Finian Lennon made a presentation at one of our New York Alpha User Group Meetings and he showed how easy it is to populate an Array. I got out my Xbasic Reference Manual, looked in the index, found (ui_get_list_array(). After reading the information about an array I found out it certainly was just as easy as my colleague Finian Lennon had said.
Now I was ready to start.
Here is the SALES table. It contains the Salesperson information needed for the Array.
NOTE: The Table name, as well as the field names were changed to make them “Politically Correct”.
Fig 1 – Sales Table
Now I needed a form. I usually use my “Date_from_to” Form so I copied it and made just a few additions and here it is in Design Mode.
Fig 2 - Date_salesperson Form
Some explanation is necessary. First let’s cover the Form Variables that were setup.
Fig 3 – Form Variables
Choicex - contains the result of Pressing either the Preview or Print Radio Buttons. It is a Layout Level variable
The remaining variables are all Global Level because they will be passed on to the Report.
Starting_date – Starting Date as entered on the Form.
Ending_date – Ending date as entered on the Form.
Empl_id - Empl_id from the Selected Salesperson in the Array.
Namex – from the Selected Salesperson in the array so I can print the Salesperson’s Name in the Report Header.
Now that the variables are all setup, I wanted to make certain my Global Variables were cleared before starting so I added an Xbasic script to the OnInit Form event.
''XBasic OnInit Form Event
dim global empl_id as c
dim global namex as c
dim global starting_date as d
dim global ending_date as d
empl_id=""
namex=""
starting_date={} 'a pair of “French Braces”{} clears a Date field
ending_date={}
ending_date.refresh() 'ensures that this field on the screen is cleared
end
Script 1 – OnInit Form Event
Next, I need to “Populate” the Array. I decided the best place would be the Ending_date field OnDepart Event.
''XBasic Ending_date field OnDepart Event
1 dim salesperson(15) as c ?Line numbers are not part of the Script –
for clarity only
2 dim inputf as p
3 dim name as c
4 dim global empl_id as c
5 dim global namex as c
6 dim i as n
7 inputf=table.open("sales",file_ro_shared)
8 i=1
9 inputf.fetch_first()
10 while .not. inputf.fetch_eof()
11 salesperson(i)=inputf.sales_id+" "+trim(inputf.sales_fullname)
12 inputf.fetch_next()
13 i=i+1
14 end while
15 name=ui_get_list_array("Select a
Salesperson",1,"salesperson")
16 empl_id=substr(name,1,4)
17 namex=substr(name,6,32)
18 namex.refresh()
19 empl_id.refresh()
20 inputf.close()
21 end
Script 2 – Ending_date field OnDepart Event.
Script Explanation
Lines 1 - defines the array as “salesperson” with a maximum of 15 data elements.
Lines 2 – 6 define other variables
Line 7 – Opens the file that will be the source for the array contents.
Line 8 - Sets the numeric variable to 1. This indicates the element number.
Line 9 – Accesses the first sales record.
Line 10 – Sets up a “while” loop until the end of file.
Line 11 – Inputs an array element from the input record. Note if you want formatting, you must do it yourself.
Line 12 – Accesses the next Sales record.
Line 13 – increments i by 1.
Line 14 – End of the while loop
Line 15 – Display the Array, fill the name variable with contents of the selected Array Element.
Lines 16-17 Fill the variables from sub-strings of name variable.
Lines 18-19 Refresh fields on the form.
Line 20 – Close the Input Table.
The figure below shows how the ui_get_list_array() pops up and displays the Array Elements after the Ending_date field OnDepart Event fires.
Fig. 4 – Ui_get_list_array() display.
After selecting the desired Salesperson’s name and clicking OK, or double clicking the name, the Array closes and the Salesperson Name and Sales Id fields are shown filled in.
Fig 5 – Completed Salesperson Request Form.
Well, only a few more things on the form to cover.
Remember at the beginning, we declared a Layout variable named “choicex”? Well, on the form the Preview Print radio buttons are attached to “Choicex”. The results of pushing either button will be checked in the final script.
The CANCEL button, does just that. It closes the Form with no further action.
Because the scope of this Article focuses on Arrays, I’ll just briefly cover the OK button and it’s attached Xbasic script. The OK button has an OnPush Script. Starting and Ending date validation occurs and the datea saved in the two corresponding Global variables. The Report is executed in either Preview or Print depending on the contents of the Choicex variable.
When the Report is initiated, all Global variables are accessible and are used to properly filter the Detail Records. The filter contains the following expression:
Between(sale_date,var->starting_date,var->ending_date) .and. empl=var->empl_id
Don’t forget the namex global variable, it contains the Salesperson Name. It is inserted into the Report Header along with the starting and ending dates. Now the Report Header shows the Name and the period of this report.
In conclusion, while an Array is easy to setup, it may not be a feature you will use very often, but in the right place it does a great job.
Barry Rochford is an Alpha Developer soon to be located near Newtown, PA. He can be reached at brochford@enter.net
last revised 3/29/99 - pkw
Don't forget, we need your feedback to make this site better!