Australian Open Access User Group
![[oa logo]](oa4.gif)
Meeting Notes - 9 February 1999
These are draft notes prepared just after the meeting.News
OAWinBase should be released by DISPI around June or July this year.Q & A
1.Counting sub-totals in a print form
In essence, the question is how do you count and print the number of sub-total levelbreaks that are in a database print form. For example, you have X clinets and a total of Y invoices. You create a print form with a record for each invoice and a levelbreak for each client. At the end of the report you can easily print the total number of invoices (eg COUNT([number field]) but there appears to be no easy way to print the number of clients.One answer is to use the VAR c-call. See below ("VAR C-call") for previous tips on the use of this c-call. In effect, it allows you to create a working variable in the print mask. To use it to analyse sub-totals:
Design your PMK and add the following item to the sub-totals section
call('var','s',1+call('var','g','','real1'),'real1')
[this adds 1 to an external varible called "real1" (that is set up
by
the VAR function) every time a sub-total is printed - luckily it is
"recursive"]
then add the following to the Totals section
call('var','g','','real1')
[this prints the value of "real1" at the end of the report
It is also advisable to add
call('var','s',0,'real1') in the totals section, below the
other one, so that "real1" is reset to zero at the end of thre printout,
otherwise it might not be zero when you repeat the printout straight
away.
You can use it in any section of the report and it should also work
in
SMKs, if you can think of a use! You can also use other variables:
str1, str2, str3, int1, int2, int3, bool1, bool2, bool3, date1, date2,
date3, real2, real3 (but the "int" variable might not work properly)
You will need to "attach" VAR every time you load OA with the intnention
of using the c-call.
2. Importing OA data into Word for Windows
Keith O'Donnell demonstrated some Word formatting tricks to ensure that the data is imported in the desired format.[add notes from Keith - include Excel?]
3. OA Printer Driver for the BJ Bubblejet
Some drivers are incouded in the file CONFIG1S.PRT which is available for downloading from the AOAUG website as part of OA_PRT.ZIP. It might need some customising for recent BJ models (at least you can customise OA printer drivers if you can handle the 64 "impossible questions".).4. Calculations in screen forms - avoiding too many fields
Graeme Vines demonstrated a method he has developed to limit the number of fields in a complicated screen which involves several calculations. The usual method of achieving the calculations is to have extra fields in the database. These are dependent fields in the screen mask and are used for interim calculations. The problem is that they fill up your database with fields that are redundant.Graeme's solution is to create a one recoed dummy database with all of the fields needed for calculations. A screen mask is then created with a query which join the dummy database to the main database. The fields in the dummy database are then used for the interim calculations. Because of the nature of SQL relational databases the same fields in the single record are used each time that a "joined" record is used with the screen form.
To make the join you will need a dummy key field in both databases. The easiest way is to have a single character text field (perhaps called "link") which is blank. The join in the SMK SQL might be [maindb.link]=[dummydb.link]
To view the SMK you will need to retrieve the joined records using the
COMPOSE QUERY or STORED QUERY function. Calling the SMK from the BROWSE
window will not work becuase the SMK requires joined databases. In the
SMK it is important that the fields from the dummy database are set to
"Dependent" in the EVALUATED section of the field properties - otherwise
you will be prompted to save the changes when you move between records.
A similar trick (having a dummy one record database) can be used in
print masks. The dummy database could even heave a field containing client
specific information for customising the printout.
5. Record locking under WIN95
A two PC network was set up to try and get reocrd locking to work properly when running OA4 Netbios version under WIN95. Various tricks (WIN95 network protocol settings) were tried without full success and it seems that the Netbios emulation provided with WIN95 does not fully emulate the reocrd locking features used by OA. Let us know if you have some tips.The main problem is that a when PRIVATE LOCK is set on a view by one user another user can access those records and change them (even worse, when OA retrieves records for a print form it is supposed to lock those records to prevent changes but this doesn't seem to work). At present the only way around the problem under Programmer seems to be to ignore the record locking features and create a true/false field in the database. Set this field to TRUE when the first user accesses the record - then under program code, prevent other users from accessing the record whenever the field is set to true.
Special Item: Day of the week in print forms and screen forms
OA Programmer contains the function DAYNAME([date]) which returns the day of the week (Monday etc) for the given date. There is no equivalent function in Database screen forms or print forms. Several methods of achieving this were discussed in a demonstration by Terry Litchfield.A: In Spreadsheets
1. Create a reference spreadsheet DAYNAMES.CMP containg the followinglist of daynames, and store it in a directory which is available on the
Open Access searching order:
W1 A |
1 Sat
]
2 Sun
]
3 Mon
] Named range 'DAYNAMES
4 Tue
]
5 Wed
]
6 Thu
]
7 Fri
]
8 Sat
]
2. To use it to provide daynames against a given date, this spreadsheet
must
be linked to the calling spreadsheet as EXTERNAL Channel
#1
(use <menu> Xternal).
In the body of the spreadsheet, use the formula
+VLIST(WKDAY(CCrr),A1#1)
each time the day name is required, where CCrr is the
absolute address
of the cell containing the date in question. Eg
in the following example,
W1 A | B | C | D | E | F | G | H |
11 Tue 08-10-1996
The date is in C11, and the corresponding dayname in B11
is derived by
the formula +VLIST(WKDAY(C11),A1#1)
B: In Screenmasks and Printmasks (tip from Marc Sapper)
Create a dependent field containing the nested conditional statement:testdate\7=0|'Wed'|(testdate\7=1|'Thu'|(testdate\7=2|'Fri'|(testdate\7=3
|'Sat'|(testdate\7=4|'Sun'|(testdate\7=5|'Mon'|'Tue')))))
In this formula, "testdate" is the name of the field containing the date in
question. The modulo 7 division gives the remainder after dividing the
date by 7, and returns a number between 0 and 6. Remainder 0=Wednesday etc.
C: In Databases
The above method would work if the dependent formula were in a screenmask(implying that a separate field would have had top be created to store the
dayname in the database), provided that the record has been viewed through
the screenmask and saved (to force the result of the formula to be stored
in the database. A more efficient way is by using Michael Paine's
LINKDATE method, which is available on the OAUG web site as LINKDATE.ZIP. (first created at an OA workshop in August 1991 - recently updated to be Y2K compliant!).
VAR C-call
(by Michael Paine - from OAUG Newletter, August 1993)The Open Access 4 manual (page 276 of Utilities Reference) describes
a C-call VAR.OAC for passing strings and numbers to database print
forms.
One of the questions raised during the August (1993) meeting was along
the lines "how can I print percentages of the grand total against the record
items in a print mask?". The problem is that the printing procedure works
through the records, printing as it goes. Therefore it doesn't "know" the
grand total until the end of the report. A two-pass procedure is required
in order to calculate the grand total then use this in the percentage calculation
for each record.
One of the suggested solutions was to create a one-record dummy database with a number field (to store the grand total) and a blank text field. A blank text field is also added to the source database. Then the OA Database Query is used to join the two databases (via the blank text fields - which must both be key fields) and the Update function is used to set the grand-total field in the dummy database to its value plus the value of the source record. After the Update is finished the grand total field will be the sum of all the source records (see the OA Database manual for a more thorough explanation of this use for the Update command). The dummy database can then be used in the print mask, using the same blank text field to join the databases.
An alternative solution is to use the VAR C-call under Programmer. First include the following item in the print mask, in order to print a percentage value:
100*amount/(call('var','g','','real1')+0.0001)
This "gets" a value called "real1" from an area in memory set aside
by the C-call. Incidentally, the 0.0001 is a lazy way of trapping divide-by-zero
errors. Now load the Programmer and type in the following program:
- ATTACH "var"
ok = TRUE
VIEW va = FROM [myfile]
USE va
IF COUNT(va) > 0
gtot = DB_SUM(va,"[fieldname]")
ok = call('var','s',gtot,'real1')
PRINTER IS ""
REPORT va WITH [printform]
PRINTER END
END IF
USE va END
DETACH "var"
Save the program ([F2]S). Now just press [Alt F10] to run the program.
First it retrieves the desired records. Then it calculates the grand total
and passes this value to the C-call. Finally it prints the retrieved records
via the print form which uses the C-call value "real1" to calculate
percentages.
The 'g' and 's' parameters in the C-call refer to "store" and "get".
Note that the OA4 manual contains an error - the SETORGET parameter is
not Boolean - it must be 'g' or 's'.
The 's' option of the C-call also works in a print mask (or database
screen mask!) therefore there is another method for obtaining our grand
total in order to work out percentages. The trick is to have a "store"
C-call at the end of the print form in the totals section and to run the
print form twice. For example, the totals section could have:
"sa":sum(amount)
call('var','s',sa,'real1')
This assigns the grand total to the "real1" variable in the C-call.
First you will need to "attach" the VAR C-call using the Utilities/Language_call
selection from the Main Options Menu. Then "print" the report to the screen.
If the print form is now printed a second time (to paper!) the grand total
will be passed to the formula in the record section.
Notice: Users should not act solely on the basis of the material
contained in this document. Items contained in this document are
presented as possible solutions to problems but do not
constitute advice. In particular, no assurance can be given that
the possible solutions will work in every situation or that loss
of data will not occur. Always back-up data before trying to
rectify a problem.