DP Tool Club 17

home *** CD-ROM | disk | FTP | other *** search

/ DP Tool Club 17 / CD_ASCQ_17_101194.iso / vrac / eb202.zip / PACK1.PRG / REFER.REP < prev next >

Wrap

Text File | 1994-07-10 | 204KB | 8,456 lines

════════════════════════════════════════════════════════════════ EASY BASE PROGRAMMERS REFERENCE Contents ──────────────────────────────────────────────────────────────── Procedure Commands Page ────────────────── Clear Records From 13 Copy All From 19 Create Temp As 21 Declare 33 Delay 36 Delete Record 38 Derive Off 39 Display Status 40 Do..Loop 41 Escape On/Off 43 Exit Batch Menu 44 Exit Procedure 45 For..Next 48 If Then Else 62 Index Off 64 List Variables 75 Multiple Lines 89 Odd/Even Page Print 90 Pause On/Off 100 Preselect 102 Print 103 Printer Control 104 Remark 110 Skip Group 122 Skip Record 123 Subindex 129 Update Record 141 System Values Page ───────────── Blank. 7 Bottom Margin 8 Current Record 23 Global Number 58 Output 97 Page Number 99 Pi 101 Record Number 109 Sequence 120 System Date 131 System Time 132 Total Copies 138 Total Records 139 ................................................................................ Functions Page ───────── Abs 1 Chr$ 12 Datetext 29 Dayofmonth 30 Dayofweek 31 Dayofyear 32 Default 34 Hours 60 If 61 Intext 70 Jointext 71 Lefttext 72 Lengthtext 73 Lookup 76 Lower 77 Makedate 78 Maketime 79 Maths 80 Midtext 84 Minutes 85 Mod 86 Month 87 Proper 105 Random 107 Retail 112 Reverse 114 Righttext 115 Round 116 Seconds 119 Spacepad 124 Spellday 125 Spellmonth 126 Stringof 128 Timeampm 137 Upper 142 V.A.T. Functions 143 Year 144 Zeropad 145 Field Controls Page ────────────── Beep 6 Clearfields 14 Delay 35 Run 117 ................................................................................ Field Types Page ─────────── Choice 11 Date 28 Fixed Point 46 Floating Point 47 Formatted Text 56 Integer 69 Text 134 Text Block 135 Time 136 Field Derivation Prefixes Page ───────────────────────── Current 22 Goto (Field) Next 59 Retest 113 Start Here 127 Utilities Page ───────── Alter Vat Rates 4 Backup Application 5 Change Password 10 Data Import 25 Delete Choice Lists 37 Install Form 66 Install Printer 67 Install Procedure 68 List File Names 74 Options 96 Pack Data Forms 98 Rebuild Directories 108 Restore 111 Screen Colours 118 Set Paper Length 121 ................................................................................ User Menu Functions Page ─────────────────── Menu Calls 83 Operators Page ───────── Arithmetic 93 Logical 95 Relational 94 Methods Page ─────── Address Labels 2 Bypass Sign-on Sreen 9 Compound Index 15 Compound Lookups 18 Correcting Stats. 20 Cyclic Procedures 24 Data Type Conversion 27 Duplicate Prevention 42 Form Letters 54 Global Defaults 57 Input Screen Format 65 Maximizing Speed 81 Multiple Columns 88 Q And A Input Screen 106 Tabulation 133 Totals & Sub Totals 140 ................................................................................ ABS FUNCTION ABS This function returns the absolute (positive) value of a number. Ex. abs(length - breadth) This returns 6 if length = 12 and breadth = 6 or if length = 6 and breadth = 12 Acceptable parameters Numeric value Numeric expression Numeric field/variable Any function which returns a numeric value - 1 - ................................................................................ ADDRESS LABELS METHOD ADDRESS LABELS The following procedure prints two columns of address labels from a "Addr" form. .......................Procedure code................ Declare output fields Addr.name : Addr.street : Addr.town : Addr.pcode Lname : Lstreet : Ltown : Lpcode end for Addr if Lname = blank then Lname = Addr.name : Lstreet = Addrstreet Ltown = Addr.town : Lpcode = Addrpcode else print list items Lname = blank : Lstreet = blank Ltown = Blank : Lpcode = blank end if next if Lname <> blank then print list items ....................Output Format........................ .List Items {Lname field } {Addr.name field } {Lstreet field } {Addr.street field } {Ltown field } {Addr.town field } {Lpcode field } {Addr.pcode field } .end The field positioning and the distance between the .List Items and .end can be adjusted to fit the label paper. If your label paper has three columns then you could use the following procedure. declare output fields Addr.name : Addr.street : Addr.town : Addr.pcode Mname : Mstreet : Mtown : Mpcode Lname : Lstreet : Ltown : Lpcode end declare variables x as number end Continued. - 2 - ................................................................................ ADDRESS LABELS METHOD ADDRESS LABELS for addr x = x + 1 if Mod(x,3) = 1 then Lname = Addr.name : Lstreet = Addr.street Ltown = Addr.town : Lpcode = Addr.pcode end if if Mod(x,3) = 2 then Mname = Addr.name : Mstreet = Addr.street Mtown = Addr.town : Mpcode = Addr.pcode end if if Mod(x,3) = 0 then Print list items Lname = Blank : Lstreet = blank Ltown = blank : Lpcode = Blank Mname = Blank : Mstreet = blank Mtown = blank : Mpcode = Blank end if next If Lname <> blank then print list items .........................output format................... .list items { Lname } { Mname } { Addr.name } { Lstreet } { Mstreet } { Addr.street } { Ltown } { Mtown } { Addr.town } { Lpcode } { Mpcode } { Addr.pcode } .end See Also:- Form Letters - 3 - ................................................................................ ALTER VAT RATES UTILITY ALTER VAT RATES The five VAT rates used by the VAT functions can be altered either from the utilities menu or from a user menu function. Just select "Alter VAT rates" - edit the percentages and press F2 to save the new values. - 4 - ................................................................................ BACKUP APPLICATION UTILITY BACKUP APPLICATION Easy Base includes an inbuilt Backup system for your applications and data. If you select "Backup Application" from the utilities menu then Easy Base will backup your entire application - Choicelist fields - Forms - Data and procedures. The backup system will split large files across multiple disks automatically. When an application is finished and you have backup copies then backing up choice lists and procedures is wasteful of time and floppies. You can backup only the users data by calling "Backup Data" from a user menu. Backup Principles. The reason for a backup is that in the event of a computer or hard disk failure you will have your application or data to restore on the new machine. If you have no backup and your machine fails then you have lost all your data. If you have only one backup and your machine fails while doing a backup then you have lost all your data. The principle of safe backing up is to keep two sets of backup disks in separate boxes marked "Latest" and "Previous". Whenever you do a backup you use the disks from the "Previous" box - move the disks from the "Latest" to the "Previous" and put your new backups in the latest box when completed. This not only overcomes the problem of a failure during backup but also provides a secondary set of disks if any of the "Latest" disks turn out to be faulty when you come to restore. If you ever have to use a backup disk and find that it has developed a bad sector then you can attempt a recovery with a utility such as "Norton" Disk Test. Easy Base leaves approximately 5K of unused disk space on all backup disks so that such a utility has somewhere to move bad sectors to. To save disk space, Easy Base does not backup index files. If you ever have to use a backup disk then the index files are rebuilt after restoring the data. See Also:- Restore - 5 - ................................................................................ BEEP FIELD CONTROL BEEP The "beep" control is used in field derivations to alert the user to input errors. In addition to sounding the beeper the "beep" control has two optional additional parts - A message to be sent to the message line and cursor redirection. This is normally to the field in which the error has been made but can be to any field. Field controls are appended to field derivations and are enclosed in square brackets. Ex. If(partno < 0,blank[beepPart Numbers must not be negativeCursor partno],partno) In this example, if the user enters a negative number in the partno field the computer will beep,the number will be cleared from the field, the message "Part Numbers must not be negative" will be displayed on the message line and the cursor will return to the "partno" field. Ex. If(paid = "no" and chequeno <> blank,paid[beepEither bill has been paid or cheque number entered in errorCursor paid],paid) In Easy Base, division by zero is not trapped as an error. There is a very good reason for this. When you start a new record and all the fields are blank then any field which provides a value for a division in another field would cause a division by zero error before its value had been entered. Whenever Easy Base encounters a division by Zero it divides by one instead. This is perfectly acceptable in most business applications. However, if you need to be warned of a division by zero in formulae then you can provide your own error trap using the "Beep" control. Ex. If( T4 = 0,blank[beepDivision by Zero Value from Field T4], T4) - 6 - ................................................................................ BLANK. SYSTEM VALUE BLANK. In Easy Base the value of any field which does not contain characters is represented by the word "Blank" irrelevant of data type. A numeric field returns the value "Blank" only when the field is empty. If it has a value of zero then it is NOT blank. In fact, the main advantage of recognizing "Blank" is that you can distinguish between zero value and empty numeric fields. There is, however, one slight disadvantage. If a text field contains ONLY the word blank and you use its value in a derivation then the word "Blank" from the field will disappear. The "Name" field at the top left hand side of this page is derived by the formula Upper(Name). You will notice that "Blank" is followed by a full stop. If it were not then the upper function would return " ". You can also remove a fields contents by setting it equal to blank. - 7 - ................................................................................ BOTTOM MARGIN SYSTEM VALUE BOTTOM MARGIN Throughout a procedure which has a printed output, Easy base keeps track of the length of page remaining (in inches) in the system value "Bottom Margin". The Bottom margin value is used to control pagination in procedures which "Report" on data. The "Bottom Margin" value is based on the paper type entered from the Utilities Menu. You must set the type of paper you are using before using "Bottom Margin". Ex. Line feed: Line feed: Line feed For employees print list items if bottom margin < .5 then page feed: line feed :line feed : line feed else line feed end if next In the above example the list items section is printed with a blank line between each and with a half inch top and bottom page margin. When you base decisions on the "Bottom margin" value you should always do so with a "Greater Than" or "Less Than" operator. If you used " If Bottom margin = .5 then Page feed" your page would only be ejected if and when the "Bottom Margin" value was exactly 0.5 As most printing is done at six lines to the inch it is most unlikely that the "Bottom margin" value would ever be half an inch. - 8 - ................................................................................ BYPASS SIGN-ON SREEN METHOD BYPASS SIGN-ON SREEN You can bypass the sign-on screen for both the developers password and any User-menu password by adding the password to the start up command line. This is particularly useful in finished applications as the application can be called directly from a hard disk menu system - the sign on screen will not appear and the application will start as if it were a stand alone program. You can use either:- EB/password or EB password as your command line to bypass the sign on screen. - 9 - ................................................................................ CHANGE PASSWORD UTILITY CHANGE PASSWORD You can change your developers access password with this item on the utilities menu. - 10 - ................................................................................ CHOICE FIELD TYPE CHOICE If you define a field as the "Choice" type a window will open into which you can type up to seventeen different choices. When you have entered your choices you will be asked to give a name to your choice list. Once a choice list has been saved it can be used in any other choice field in any other form or input screen. Once a choice list has been saved you can edit the contents but not the field length. Editing a choice list in one form automatically edits it for any other form. If you need to alter a list such that the field length will change then you must create a new list. You can erase the old one from the utilities menu. If the list is used in more than one form then you must redefine it in all the other forms in which it is used When the cursor enters a choice field your choice list will be displayed for the user to choose from. He cannot enter any value other than one from the list. - 11 - ................................................................................ CHR$ FUNCTION CHR$ The Chr$ function returns the character whose ASCII number is supplied as the parameter. Ex. Chr$(171) < Returns "½"> Ex. Jointext(Chr$(227),"r",Chr$(253)) < Returns "πr²" In Easy Base, text fields are automatically justified left. This is desirable in record entry as it prevents the user from entering leading spaces in indexed fields and consequently trashing alphabetic printouts. However, you may wish to right justify or centre justify text in printouts. For example, the righthand "Chr$" at the top of this page is right justified in its field. You can use the ASCII character 255 to achieve right or centre justification. Ex. Heading = jointext(stringof((30-lengthtext(name))/2,Chr$(255)), name) < This centres name in the 30 character field "heading"> Ex. Heading =Jointext(stringof(40-lengthtext(name),Chr$(255)),name) < This right justifies name in a 40 character field "Heading"> CAUTION: You can produce any ASCII character ( 0 - 255 ) with the Chr$ function. ASCII characters 0 to 31 are used as printer controls and although you can use them freely on screen you should never include them in a procedure output which will be sent to the printer. Acceptable parameters: Numeric value, field, variable, expression or function returning a number between 0 and 255 - 12 - ................................................................................ CLEAR RECORDS FROM PROCEDURE COMMAND CLEAR RECORDS FROM The "Clear Records From" command clears all records from a form by erasing its data and index files. If you need to erase all records form a form always use the "Clear Records" command rather than "Delete Record" command. It is much faster and does not require the form to be re-packed afterward. Ex. If input.confirm = "yes" then clear records from invoices - 13 - ................................................................................ CLEARFIELDS FIELD CONTROL CLEARFIELDS The "Clearfields" control blanks out all other fields on a form or procedure input screen whenever the field containing the control is changed. The primary use for the "Clearfields" control is to force default lookups to re-derive for procedures which edit data. For Example - If you wrote a procedure to alter customers addresses and telephone numbers then you would have three fields on the input screen. Accountno derived lookup(customers,accountno) address derived default(lookup(customers,address)) Tel derived default(lookup(customers,tel)) When you enter a customers account number, his old address and telephone number will be looked up, but the "Default" function will allow you to edit them. The procedure code would then update the record with the new address and phone number. The problem is, that having entered an account number and looked up the other fields, if you then realize you have entered the wrong account number and change it, the other fields will not re-derive because they now have a default value. To get them to lookup based on the new account number you would have to take the cursor to each field and clear it with the F6 key. you can automate this process by adding the "Clearfields" control to the accountno derivation:- lookup(customers,accountno)[clearfields] Note: The "Clearfields" control will only work when appended to a derivation which is already dependent on the value of the field. If you want to clear fields based on a change to a field which has no other derivation then you must derive the field with its own name plus the "Clearfields" control. In a field "Number" the derivation - Number[clearfields] - will clear all other fields when the "Number" field is edited but the derivation [clearfields] will have no effect. - 14 - ................................................................................ COMPOUND INDEX METHOD COMPOUND INDEX If you need to list records from a form in such a way that they are grouped by one field but with each record in the group ordered by another then you can either use a compound index field or "Subindex" the group during the procedure. A compound index field is simply an additional field in which the contents of two or more fields are compounded using the "Jointext" function. The field is indexed and the index on that field can then be used to select records with the desired group ordering. For example:- A "Videos" form has fields for "Title", "RentalPrice" and "Category". To list the records grouped by "Category" but with the "Titles" in each category in alphabetic order you add another field to the form which is Text, Indexed and long enough to hold the contents of both the "category" and "Title" fields. The field is derived by joining the text of the "Category" and "Title" fields. There are a couple of minor complications in creating compound index fields. Firstly the "Jointext" function by default strips any trailing space characters from the text it is joining. If a straight "Jointext" function was used and the first two videos entered were "A Bridge Too Far" and "Snow White" then the compound fields would derive as:- "WarA Bridge Too Far" and "CartoonSnow White" When what you need is:- "War A Bridge Too Far" and "Cartoon Snow White " To produce the desired spacing in the compound field use the "Spacepad" function. If the length of the "Category" field is 15 then derive the compound field with - Jointext(spacepad(category,15),title) If you are compounding more than two fields then "spacepad" all the fields being joined to their own field's length except the last one. The maximum length of a text field in Easy Base is 80 characters. If the fields you need to compound total more than 80 then you must reduce the length of the field names. Compound indices in Text Block fields should not be used. - 15 - ................................................................................ COMPOUND INDEX METHOD COMPOUND INDEX Once you have created a compound field and checked that it derives correctly it is normal to define it as invisible and no entry anyway as it is not its contents that are of interest but the order which its index produces. Ex. Assuming the compound field was called "catgroup" ..........................code.................. for videos with catgroup in order print list items next ........................format................. .List Items {Videos.Category Field} {Videos.Title Field} .End Ex. ...........................code.................... declare variables catcheck as text end for videos with catgroup in order if catcheck <> videos,category then print group header catcheck = videos.category print list items next ..........................format............... .Group Header ------------------------------------- Films categorized as {category field} ------------------------------------- .List Items {Title field} .End There is yet a further complication if one or more of the fields to be compounded is numeric. Easy Base index files are sorted alphabetically if the field is text and numerically if the field is numeric. A compound field is always text so if you need to compound a numeric field you have to ensure that it will sort alphabetically to the same order that it sorts numerically. The text of numeric values sort alphabetically to the same order as their values only if they all have the same number of digits either side of the decimal point. 6 12 and 34 sort alphabetically as 12 34 6 but 06 12 and 34 sort alphabetically as 06 12 34 - 16 - ................................................................................ COMPOUND INDEX METHOD COMPOUND INDEX Easy Base provides the function "Zeropad" to pad numbers to the same length for compounding. The "Zeropad" function has three parameters - The number to be padded, the number of digits left of the decimal to pad to and the number of digits right of the decimal to pad to. All three parameters must be supplied. If the number is an integer then the third parameter is 0. Ex. To list the "Videos" records grouped by rental price with the titles in each price group in order you would create a compound field derived with:- Jointext(zeropad(rentalprice,2,2),title) Ex. A "Ships" form has fields for "Type" and "Displacement" To list the ships grouped by type with the Displacement in each group in order you would create a compound field derived with:- jointext(spacepad(type,15),zeropad(displacement,8,0)) In many cases where you wish to compound a mixture of text and numeric fields you will require the numeric values to be listed in descending order while the text value is to be in alphabetic order. In the above example for ships, had you wished each group to be listed with "Displacement" in descending order you would derive the compound field as:- jointext(spacepad(type,15),reverse(zeropad(displacement,8,0))) The "Reverse" function simply inverts the ACSII number of each character in its parameter so that it will sort in reverse order in the index file. The "Reverse" function works equally well on text values although reverse alphabetic lists are seldom required. The "Zeropad" function will only pad out a number to the size required. It will not trim the number if it is already longer than one of the pad lengths. You should not therefore create compound fields with Floating Point numbers. If you compound a date or time field you should zeropad it to 5,0 as the text which will be used is actually the date or time fields numeric value. - 17 - ................................................................................ COMPOUND LOOKUPS METHOD COMPOUND LOOKUPS Occasionally, in an input screen, you will wish to lookup details from a form whose unique field is a compound of two others. As an example, if you wished to lookup details from the "Userlist" form, mentioned in the manual, then you would have to base the lookups on a relationship between a field on the input screen and the "UNI" field in "Userlist". The "UNI" field in userlist is a combination of the "Aircraft" forms "Knownas" field and the "Airlines" form "Name" field. Although you could simply use a field "UNI" on the input screen and derive it :- Lookup(userlist,uni) - this would mean that the end user would have to enter the whole of the aircraft Knownas part plus part of the airlines name part plus the "*" in order to get the lookup. This would not only be awkward to use but would also entail explaining compound fields to the end user. You can avoid this situation by providing two fields, one in which the user looks up the aircraft knownas field and one in which he looks up the airline name. These are based on separate relationships between the input screen and the "Aircraft" and "Airlines" forms. The input screen's "UNI" field can now be made invisible with no user entry and derived:- Jointext(knownas,name). A relationship is entered between the input screen and the "Userlist" form linking the "UNI" fields and all the required details can then be derived :- lookup(userlist,whatever). - 18 - ................................................................................ COPY ALL FROM PROCEDURE COMMAND COPY ALL FROM When you are transferring values from the fields of one form to the fields of another in preparation for a "New Record" or "Update Record" you can use "Copy All From" to save time. Ex. for invoiceitems new record invoiceitems.item = input.item invoiceitems.price = input.price invoiceitems.quantity = input.quantity invoiceitems.vatrate = input.vatrate next can be replaced with:- for invoiceitems new record copy all from input next The data transfer in a "Copy All From" is based on the source and destination fields having the same name. If the source and destination fields have different data types Easy Base will attempt a conversion. The conversion will succeed between different numeric types and from numeric to text. It will fail from text to numeric and between any mismatch of date and time. See also:- Derive Off, Duplicate Prevention - 19 - ................................................................................ CORRECTING STATS. METHOD CORRECTING STATS. If you write an application in which records are entered via procedures and "running" statistics are kept, you will have to provide procedures which allow the user to correct any mistakes he has made. For Example: - In a "Time Sheets" application the user enters a time and customer on the input screen of a procedure. The procedure then calculates the charge, writes the charge and customers name to the "Timesheet" form, adds the charge to the "Balance" field in the "Customers" form and also adds it to the "Total" field in the "Workinhand" form. If the operator makes an error then correcting the entry in the "Timesheet" form is simply a case of updating it but to correct the "Balance" and "Total" fields you must add the corrected value and subtract the old incorrect value. To get the two values together for your procedure, create an input screen where the operator enters the "line" ("line" is the unique sequenced field in "Timesheet"). Then create two fields for the values. The first, "oldval" is derived lookup(timesheet,charge) and the second, "newval" is derived default(lookup(timesheet,charge). The "Oldval" field can be invisible and has no user entry. You would also add two similarly derived fields "Oldcustomer" and "Newcustomer". When the user edits the "Newval" and/or "Newcustomer" field and presses F2 , all the required values are available to the procedure code:- pause off : escape off for timesheet with line = input.line timesheet.charge = input.newval timesheet.customer = input.newcustomer update record next for customers with name = input.oldcustomer customers.balance = customers.balance - input.oldval update record next for customers with name = input.newcustomer customers.balance = customers.balance + input.newval update record next for workinhand workinhand.total = workinhand.total+input.newval-input.oldval update record next - 20 - ................................................................................ CREATE TEMP AS PROCEDURE COMMAND CREATE TEMP AS The "Create Temp As" command makes a temporary form during a procedure. There are two main uses for the temporary form. 1. To physically sort the records in a form. Ex. create temp as addresses index off:pause off:escape off:derive off for addresses with surname in order for temp new record copy all from addresses next next rename temp as addresses The other main use for the temporary file is to save time when the majority of records in a form are to be deleted. Ex. A purchases ledger form is to be cleared down at the year end but any unreconciled payment entries are to be retained and flagged as last years. If there were 15000 records in the form of which only 50 were unreconciled then the procedure for purchases if purchases.reconciled = "yes" then delete record else purchases.lastyear = "yes" update record end if next would not only take a very long time to run but would leave the form full of deleted records and it would have to be re-packed. The same result can be achieved very quickly and without the need to re-pack with the following procedure. create temp as purchases for purchases with reconciled = "no" for temp new record copy all from purchases temp.lastyear = "yes" next next rename temp as purchases Do not use the "Clear Records" command to wipe the old file before using "Rename Temp As". If you suffered a power failure after clearing and before renaming you would lose your data. - 21 - ................................................................................ CURRENT DERIVATION PREFIX CURRENT In Easy Base, fields are not normally derived when a previously entered record is viewed on screen. You can force a field to be rederived when viewed by prefixing the derivation with "current". Ex. An invoice form has three fields Ivdate,Cdate and age. Ivdate is derived as "system date" Cdate is derived as "current system date" age is derived as jointext(Cdate-Ivdate," Days old") Each time a record is viewed the Cdate field is rederived with the current system date and the age field shows the number of days since the record was filed. Ex. A form is used to price a certain job. It consists of fields for materials and their prices which are looked up from a "stock" form. If the primary lookup fields are prefixed with "current" - "current lookup(stock,item)" then each time a record is viewed it will reprice the job using the latest prices from the "stock" file. Please note that in the case of lookups, prefixing a secondary lookup has no effect. In the above example "current lookup(stock,price)" would not work Provided that the primary lookup field is prefixed with "current" then any secondary lookups will also be rederived. The values of "Current" fields are also rederived whenever they are accessed in procedure code but they are NOT REDERIVED when they are accessed by a Lookup function. Note: You can only have one prefix on any one field derivation. - 22 - ................................................................................ CURRENT RECORD SYSTEM VALUE CURRENT RECORD Whenever a procedure is processing a "FOR" loop the number of the record being processed is available in the "Current Record" system value. The "Current Record" value is the records actual number in the form, not the number of records processed. Ex. for books with category = "fiction" count = count + 1 Posn = current record next If the first book found with the category "fiction" is the fifteenth record in the file then count will return 1 and Posn will return 15. The "Current Record" value has no meaning in field derivations. - 23 - ................................................................................ CYCLIC PROCEDURES METHOD CYCLIC PROCEDURES In many applications you will come across the situation where you need a procedure which will perform the same actions on a given set of records. For example, in a payroll system the procedure which calculates the employees wage and deductions has to be repeated for each employee so that their hours can be entered. If you simply enter each employees Worksno and lookup his or her details then it will be easy to miss an employee or to do one twice. To avoid this you can create a procedure which will cycle each employee through the input screen and terminate when all employees have been processed. On the employees form, add a field "Cycled" which is a single character indexed field derived :- Default("N"). On the procedure input screen add an invisible field "Cycled" which is derived "N" You now enter two relationships between the procedure and the employees form. The main relationship "Employees" links the fields "Worksno" and the second, lets call it "Cycled" links the "Cycled" fields. On the input screen you derive the "Worksno" field as Lookup(cycled,worksno) and all the other details as lookup(employees,Whatever). When you run the procedure, the first employees details will be loaded automatically and all you have to enter are his hours. To have the next employee loaded after you run the procedure, the procedure code simply includes the lines:- for employees with worksno = input.worksno employees.cycled = "Y" update record next When all employees have been processed the "Worksno" field will derive blank as there are no employees with "N" in the "Cycled" field. You can therefore pass a "finished" message by altering the derivation of "Worksno" to :- if(lookup(cycled,worksno)=blank,blank[beepAll Employees have now been processed],lookup(cycled,worksno)) To reset cycling for the next payroll you write an additional procedure with the code:- for employees employees.cycled = "N" : update record next - 24 - ................................................................................ Data Import UTILITY Data Import There are three Data Import routines in Easy Base. The first imports data from files which conform to the Xbase standard first introduced by Dbase. The second imports data from "Comma Delimited" files. This is the standard used by most sequencial access and non relational data systems. The third routine imports data from fixed length record text files. Although few systems actually store data as fixed length text, this is the format which is used by all systems to output data. You can therefore import data from virtually any other system by first reporting on it to a disk file and then importing it with the fixed length ASCII import routine. XBASE If the data you wish to import is in Xbase format, Easy Base will report the structure of the file. You can print this out by pressing F10. You now design a form to hold the imported data. The names that you give to the Easy Base fields do not have to be the same as the Xbase field names but they must be in the same order. If the Xbase fields are text then the Easy Base fields must be the same length. If the Xbase fields are numeric then the Easy Base fields must be numeric. It does not matter if they are different lengths and any of the three types, integer, fixed point or floating point can be used. If an Xbase field is a date field then it must be imported to an eight character text field in Easy Base. You can reformat it to an Easy Base date field after importation (See the Method - Data Type Conversion). Easy Base does not support "Memo" data. If the Xbase file has a memo field you can still import from it but the memo field itself will be skipped by the import routine. COMMA DELIMITED To import data from comma delimited files all you have to do is design a form with the same number of fields as there are in the file to be imported. Numeric fields can be any of the three types and text fields should be long enough to hold the longest data expected in the incoming field. If any of the fields to be imported holds a date then it must be imported to a text field. You can convert it to a date later. FIXED LENGTH ASCII To import data from fixed length ASCII files you must design a form in which all fields are text and are exactly the same length and in the same order as the fields in the file to be imported. If the file to be imported has a header you can allow for this by entering the header length in bytes. If you are using the fixed length import routine to import data which you have output as a report from another system then - 25 - ................................................................................ Data Import UTILITY Data Import please note the following points. The output report from the original system should have no headers, footers or left margin and there should be no spaces between fields. When the original system outputs the data it will add a carriage return and line feed sequence to the end of each record. When you design the form to import this data to, you must have a final two character text field to trap this sequence and maintain the same record length. This field can be deleted after importation. All fields are imported as text. If any of them contain numeric values which you wish to convert to numeric fields you must do this after importation. (See Method Data Type Conversion) - 26 - ................................................................................ Data Type Conversion METHOD Data Type Conversion In Easy Base, modifying the structure of a data file after it contains data is very easy and flexible. If you select Modify Existing Form from the Forms menu you can add fields, delete fields, change field order, change field lengths and Easy Base will reformat your existing data automatically. You can NOT however change data types simply by changing the field type. If you change a field type and save the form you will almost certainly lose the data that was in that field. (Other than changing from one numeric type to another) If you work entirely within Easy Base there is no reason why you should ever need to change a field type. However, if you have imported data from Dbase or Fixed Length ASCII you may find yourself with numeric or date values held in text fields. The procedure for converting data from one type to another is as follows:- 1. Select Modify Existing Form 2. Add a new field of the desired type with a derivation formula such that it will derive its value from the field of the old type. 3. Save the form. 4. Select Modify the form again. 5. Cancel the derivation formula in the new field and delete the old field. 6. Re save the form. To derive a numeric field from a text field the derivation formula is simply the text field name. To derive a date field from a Dbase imported date in a text field called DT the formula is:- Makedate(midtext(DT,5,2),midtext(DT,7,2),midtext(DT,3,2)) - 27 - ................................................................................ DATE FIELD TYPE DATE Date fields hold dates in the eight character format 11/11/94 The assumed century runs from the first of January 1981 to the 31st of december 2080. You can select whether you wish your dates to be displayed as day/month/year (European) or month/day/year (North American) from the "options" item on the utilities menu. You can perform addition and subtraction operations on date values in days. Ex. System date + 30 Arithmetic operations are only correct for the assumed century. Date fields are automatically checked for validity by the system. Easy base does not provide a ready made ten character date field but you can define one with the "Formatted Text" field type. See Also:- Makedate - 28 - ................................................................................ DATETEXT FUNCTION DATETEXT This function returns the date parameter in the form:- 14th October 1994 or October 14th 1994 depending on the date format selected in the utilities menu. Ex. datetext(system date) Ex. datetext(invoicedate + 30) Acceptable parameters: System date Date field Makedate function Date expression - 29 - ................................................................................ DAYOFMONTH FUNCTION DAYOFMONTH This function returns the day no.(1-31) of the date parameter. Ex. dayofmonth(system date) Ex dayofmonth(registered) Ex. if dayofmonth(system date) = 28 then for statements with settled = "No" statements.overdue = "Yes" update record next end if Acceptable parameters: System date Date field Makedate function Date expression - 30 - ................................................................................ DAYOFWEEK FUNCTION DAYOFWEEK This function returns the day no.(1-7) of the date parameter. Ex. dayofweek(system date) Ex. day = spellday(dayofweek(diary.date)) Ex. The following procedure, if called from a batch execute menu will only run on a Monday. declare output fields takings.date takings.dailytotal end if dayofweek(system date) = 1 then for takings with date in reverse order print list items if system date - takings.date > 7 then exit for next end if Acceptable parameters: System date Date field Makedate function Date expression - 31 - ................................................................................ DAYOFYEAR FUNCTION DAYOFYEAR This function returns the day no.(1-365) of the date parameter. Ex. dayofyear(system date) Ex. days = dayofyear(events.date) Acceptable parameters: System date Date field Makedate function Date expression - 32 - ................................................................................ DECLARE PROCEDURE COMMAND DECLARE If you write a procedure which outputs information to the screen, printer or a disk file you must start it by declaring the output fields. Ex. Declare output fields Input.name Customers.name:customers.address:customers.acno countofinvoices end If fields you intend to print exist in a form then declare them as Formname.Fieldname. Fields from the procedures Input Screen are declared as Input.fieldname. Easy Base will then use the field definitions from the form as defaults when you create the Output Format for the procedure. If a field which you intend to print is to be derived during the procedure and does not exist in a form as "countofinvoices" you can give it any name you wish up to forty characters but the name must not contain a ".". Once you have declared fields you can create the Output Format. If you declare an "Ad Hoc" field such as "countovinvoices" then you must include it in the format in order to define its type and length. If you require variables to store values during a procedure but you will not be out-putting their values then declare them as variables not fields. Ex. declare variables count as number lasttype as text end Unprinted variables can only have one of the two types "Number" or "Text". Field declarations must precede Variable declarations and both must precede executable code. - 33 - ................................................................................ DEFAULT FUNCTION DEFAULT This function returns the parameter if not overridden by user entry. Ex. Default(5) Ex. Default(system date) Ex. Default(lookup(stock,price)) The Default function is only used in form and input screen field derivations - It has no meaning in procedure code. Acceptable parameters Any value, expression, field, variable or nested function of the same type as the field in which it is used. - 34 - ................................................................................ DELAY FIELD CONTROL DELAY The "delay" control suspends processing for a given number of milliseconds. Ex. A procedure screen is used from a startup Batch execute menu as an opening screen to an application and has the following field derivations 1. "Welcome to this wonderful program" [delay 1000] 2. "Copyright Fred blogs"[delay 1000] 3. "Press F2 to start" If the field colours are "text" ,"alt1" or "alt2" then the three fields will "Popup" with a one second delay between each. - 35 - ................................................................................ DELAY PROCEDURE COMMAND DELAY The "Delay" command suspends processing for a given number of milliseconds (1 - 5000). Ex. declare variables x as number : total as number end for customers total = 0 for invoices with acno = customers.acno display status "Totalling invoices for" + Customers.name delay 200 customers.balance = total update record next next In the above example, wherever a customer only has one or two invoices to total, the status display would change very quickly. By including a 200 millisecond delay you can ensure that each customers name is on screen at least long enough to be read. Delay values outside of 1 - 5000 are ignored. - 36 - ................................................................................ DELETE CHOICE LISTS UTILITY DELETE CHOICE LISTS If you have Choice lists for choice fields that are no longer required you can delete them with this utility. - 37 - ................................................................................ DELETE RECORD PROCEDURE COMMAND DELETE RECORD The "Delete Record" command is used for the selective deletion of records in a form. Ex. for books with title = input.title delete record next This deletes the single book whose title is that entered on the input screen. for books with author = input.author delete record next This deletes the records of all books by the author entered on the input screen. When you use the "Delete Record" command it has the same effect as deleting a record in data entry. In other words the record is marked for deletion but will not actually be removed from the form until the next re-pack. If you use the "Delete Record" command to delete large numbers of records then you should pack the form afterward. If you wish to delete all records in a form use the "Clear records" command not the "Delete Record" command. - 38 - ................................................................................ DERIVE OFF PROCEDURE COMMAND DERIVE OFF Whenever an Easy Base procedure adds or updates a record then by default it checks the derivations of all fields during an add operation and any fields that are affected by an update operation. In other words, if you update the netprice field in a stock form and that form has derived fields for VAT and grossprice then those fields will also be updated. If the procedure adds or updates all fields then this process is superfluous and simply slows the procedure. You can disable form derivation checking with the "Derive Off" command. derive off for stock with item = input.item copy all from input update record next In the above example a stock form has several derived fields but the input screen also does the same derivations. There is therefore no need to re-check them when the record is updated. As a general rule, in procedures which add or update records from an input screen, you should derive all fields on the input screen rather than during the update process. The user will not notice the time taken to derive fields while he is filling them in but he will notice the time taken to derive them after he has pressed F2 There is no corresponding "Derive On" command. Derivation checking is automatically turned back on at the end of the loop to which is prefixed by the "Derive Off" command. If you require derivation checking to be off for more than one loop in a procedure then you must issue the command before each loop. Note:- If the "Derive Off" command is not issued before a loop then form level derivations override procedure derivations. This is not normally a problem, but can be if you try to change the case of a field for printing. For customers customers.name = proper(customers.name) print list items next If the "Name" field on the "Customers" form is derived as Upper(name) then the procedure will print the customers names in upper case. You can overcome the problem either by cancelling the field derivation or by transferring the customers name to a variable and converting that to proper. - 39 - ................................................................................ DISPLAY STATUS PROCEDURE COMMAND DISPLAY STATUS If you write a procedure which has no output then Easy Base will display a "Running" flash at the top right hand side of the screen. For procedures which only take a few seconds to run this is sufficient to let the operator know that something is happening. For procedures that take some time to complete you should include a status display showing the progress of the procedure. A status display not only allays the nagging suspicion that the system has hung up but also allows the operator to estimate how long it will take. To create a status display just issue the command "Display Status" followed by a message composed of text in quotation marks and fields or variables concatenated by the + sign. Ex. declare variables x as number : y as number end for employees with taxcode = input.oldtaxcode y = total copies :exit for 'get copies to y for display next for employees with taxcode = input.oldtaxcode x = x + 1 display status "Updating Record" + x + "of" + y employees.taxcode = input.newtaxcode update record next The status line is displayed centrally in a small window when the procedure runs. A Status line can have a maximum of 68 characters. If you create a status line of more than 68 characters then it will be cut short to that length. If you need to display counters in two nested loops then write the full display line in the outer loop and "display status" with no parameters in the inner loop. for customers x = x + 1 - loop statements - display status "Checking Invoice" + y + "of customer" + x for invoices with customer = customers.name y = y + 1 - Loop Statements - display status next next - 40 - ................................................................................ DO..LOOP PROCEDURE COMMAND DO..LOOP The "Do" loop structure is used in the Easy Base Procedure code to repeat a series of commands or statements. Whenever a procedure reaches a "Do" command it repeats all the statements between the "Do" and the "Loop" statements until the loop is broken by an "Exit Do" command. Ex. The following code fragment prints 10 copies of the top fifty best selling items in a "stock" form. .........................code............................ declare output fields stock.item : stock.sales : today end Declare variables copies as number : items as number end today = system date do copies = copies + 1 :items = 0 if copies = 11 then exit do bold on print report header bold off for stock with sales in reverse order items = items + 1 if items = 51 then exit for print list items next bold on : print report footer : bold off page feed loop ............................format..................... .Report Header ═════════════════════════════════════════════════════ Top 50 Best Selling Items {today field} ═════════════════════════════════════════════════════ .list items {stock.item field} {stock.sales field} .Report Footer ═════════════════════════════════════════════════════ .end - 41 - ................................................................................ DUPLICATE PREVENTION METHOD DUPLICATE PREVENTION 1. Where "Blank" values are acceptable. Quite often you will come across a situation where a field must not have duplicate entries but can still be left blank. You cannot define this field as unique because that would prevent more than one blank entry. A good example of such a situation is in the "Menus Form" of Easy Base. The definition and unique field is the "Menu Title" but no two records may have the same entry in the sign on "Password" field. To prevent duplicate entries other than blanks you must enter a relationship between the form and itself with the field to be tested as the related field in both primary and secondary forms. The field is then derived as :- If(lookup(menus,password) <> blank,blank[beepDuplicate Password !],password) 2. When entering records via procedures. When you use a procedure to enter a new record to a form it is not automatically checked as "Unique". To ensure that duplicate entries are not entered via procedures you must check that the data entered on the procedures input screen is unique to the form you are about to enter it to before running the procedure. For example: If you were about to enter a record to the "Manufacturers" form in which the "Name" field was unique then the "Name" field on the input screen would be derived:- if(lookup(manufacturers,name) <> blank,blank[beepDuplicate Name !cursor name],name) - 42 - ................................................................................ ESCAPE ON/OFF PROCEDURE COMMAND ESCAPE ON/OFF When a procedure is running in Easy Base, it cannot, by default, be interrupted. If your procedure simply lists data to the screen then the end user may not need to see the entire output. You can allow the user to terminate a procedure prematurely with the "Escape On" command. If a procedure performs several tasks, you can selectively enable and disable the Escape Key with the commands "Escape On" and "Escape" Off" Ex. For newpayscales print list items next escape off for newpayscales for employees with scale = newpayscales.scale employees.rate = newpayscales.rate update record next next escape on for employees print newpay next In the above example a procedure lists new pay scales, updates the "Employees" form with the new pay rate and then lists the employees with their new rates. While the procedure is running the operator can terminate it during either of the lists but he cannot terminate it during the process of updating the "Employees" form. - 43 - ................................................................................ EXIT BATCH MENU PROCEDURE COMMAND EXIT BATCH MENU The "Exit Batch Menu" command terminates a current Batch Menu and returns control to the menu which called it. Ex. An application starts with a batch menu which calls a second batch menu which performs regular daily tasks. It then calls the main user menu. If the application is exited after the daily tasks batch menu has been completed then you will not wish to re-run the daily tasks when the application is restarted on the same day. To prevent this create a form "tasksdone" with a single field "date" and enter a single record with yesterdays date. The first procedure on the daily tasks batch menu would then be for tasksdone if tasksdone.date = system date then exit batch menu else tasksdone.date = system date update record end if next - 44 - ................................................................................ EXIT PROCEDURE PROCEDURE COMMAND EXIT PROCEDURE The "Exit Procedure" command terminates a procedure. Ex. if input.codeword <> "fred" then exit procedure for staffconfidential with name = input.name print list items next If the procedure has a repeating input screen then this is cancelled by the "Exit Procedure" command. - 45 - ................................................................................ FIXED POINT FIELD TYPE FIXED POINT Fixed point fields can display up to 14 digits. They use the comma for thousands separation and the full stop for decimal separation. There are no rounding errors with fixed point fields. See - Operators arithmetic - 46 - ................................................................................ FLOATING POINT FIELD TYPE FLOATING POINT Floating point fields can display up to 14 digits. They do not have a thousands separator. They are left justified. - 47 - ................................................................................ FOR..NEXT PROCEDURE COMMAND FOR..NEXT The "For" command initiates loops which load records from forms for processing. All statements between the "For" and the "Next" commands are repeated for each record selected. Ex. For Videos If Videos.price = 2.00 then Videos.price = 2.50 update record next In the above example each record in the "Videos" form is loaded into memory - the price field is checked and if it is 2.00 then it is altered to 2.50 - the record is then updated on disk. "For" loops can be nested to any depth. Ex. for videoprices for videos if videos.price = videoprices.price then print list items end if next next In the above example the outer loop loads each record from the "Videoprices" form. While each of these is in memory it then loads each record from the "Videos" form, tests to see if the price field in "Videos" is the same as the price field in "Videoprices" and if so prints the list items section of the report format (containing the video title field) thus grouping all video titles by price. If a "For" loop is unqualified as above then every record is processed starting at one and advancing in sequence. "For" loops can be qualified in several ways in order to select records in other orders or groups. The following qualifications are available:- For (form) with (fieldname) in order For (form) with (fieldname) in reverse order For (form) with (fieldname) = (value) For (form) with (fieldname) > (value) For (form) with (fieldname) < (value) For (form) with (fieldname) >= (value) For (form) with (fieldname) <= (value) For (form) new record With the exception of the "New Record" qualification, all accept the suffix "Unique" and an "Alias" for the form name. - 48 - ................................................................................ FOR..NEXT PROCEDURE COMMAND FOR..NEXT If a "For" loop is qualified by "in order" or "in reverse order" then the records are selected in alphabetical or reverse alphabetical order if the selection field is text, and in numeric or reverse numeric order if the selection field is numeric. Ex. for books with title in order print list items next Ex. for salesmen with monthlysales in reverse order print list items next If a "For" loop is qualified by "in order unique" or "in reverse order unique" then records will be selected in order or in reverse order as above. However, where there are multiple occurrence of the same field value a "unique" loop will only select the first occurrence of each value if the loop is in order or the last occurrence if the loop is in reverse order. Ex. for books with category in order unique count = count + 1 print list items next print categorycount This example prints and counts the different categories in the "books" form. Ex. for theatreseats with price in reverse order unique for seatprices new record seatprices.price = theatreseats.price next next The above example selects one example only of each seat price in an existing "theatreseats" form in descending price order and enters a new record in the new "seatprices" form. - 49 - ................................................................................ FOR..NEXT PROCEDURE COMMAND FOR..NEXT If a "For" loop is qualified by a "Fieldname = " condition then only the record or records where the field contents match the condition will be selected. Ex. for books with category = "fiction" print list items next This example prints all books which have the category "fiction" Ex. For customers with acno = input.acno creditlimit = input.creditlimit update record next In this example the procedure has an input screen where the operator enters a customers account number and new credit limit. On pressing F2 the procedure finds the single record in the "Customers" form which has the input account number and updates the "creditlimit" field. - 50 - ................................................................................ FOR..NEXT PROCEDURE COMMAND FOR..NEXT If a "For" loop is qualified by a "with (fieldname) > " or "with (fieldname) >= " condition then records are selected in order like the "with (fieldname) in order" qualification but starting with the first record where the field contents match the condition value rather than the first record. Similarly the qualifications "with (fieldname) < " and "with (fieldname) <= " select records in reverse order but starting with the last record to match the condition value. In case you are new to progamming it should be pointed out that the relational operators >, <, >= and <= can be applied to text as well as to numbers. When they are applied to text they relate to the text's alphabetical order. These four qualifications provide the means to select any subset of records which has a range of values in a particular field. Ex. for pupils with age > 6 if pupils.age = 12 then exit for print list items next This example prints the names of all pupils between the ages of seven and eleven. The "For" qualification starts selection in numeric order of age starting at seven and the "Exit for" command terminates the loop when the first pupil aged twelve is loaded. Ex. A "Videos" form has a compound index field called "catorder" which is derived by joining the text of the "category" and "title" fields. The following procedure selects all the videos which have the category entered on the input screen and lists them with the title in alphabetic order. for videos with catorder >= input.category if videos.category > input.category then exit for print list items next Ex. for vehicles with seats > 4 unique count = count + 1 next "count" returns the number of different seat capacities > 4 - 51 - ................................................................................ FOR..NEXT PROCEDURE COMMAND FOR..NEXT If a "For" loop is qualified by "new record" then no records are selected from the form. Instead, a new blank record is created at the "For" command, all statements between the "For" and "Next" commands apply to this new record and it is entered to the form at the "Next" command. Ex. for books new record books.dateinstock = system date copy all from input next This example enters a new record to the "Books" form making the "dateinstock" field equal the system date and copying all other fields from the procedures input screen. Ex. declare variables hourcount as number end '..............clear last wages clear records from wagelist '..........count employees hours from timesheet... for employees hourcount = 0 for timesheet with worksno = employees.worksno hourcount = hourcount + timesheet.hours next for wagelist new record wagelist.worksno = employees.worksno wagelist.name = employees.name wagelist.hours = hourcount wagelist.grosswage = employees.wagerate * hourcount next next '.................print the wages list for wagelist with worksno in order print list items next This procedure starts by erasing the present contents of the "wagelist" form. It then selects each record from the "employees" form, counts all entries in the "timesheet" form for the employee and enters a new record in the "wagelist" form. Finally it prints the new wage list. - 52 - ................................................................................ FOR..NEXT PROCEDURE COMMAND FOR..NEXT Occasionally you may need to nest a "For" loop of a form within another "For" loop of the same form. If you do this, then in order to differentiate between the fields in the outer and inner loops you must allocate an "alias" name to the form in one of the loops. When you allocate an alias to a form name then all the fields of the form can also be referenced by the alias name. Ex. .......................procedure code........... declare output fields males : females : pupils.age end for pupils with age in order unique males = 0 : females = 0 for pupils alias agegroups with age = pupils.age if agegroups.sex = "male" then males = males + 1 else females = females + 1 end if next print list items next ....................output format...................... .list items There are {males} males and {females} females aged {pupils.age} .end This example counts and prints the number of male and female pupils in each age group. If you intend to print fields from one of the nested loops as the "Age" field above then you should give the alias name to the other loop. You can only declare an output field with the real form name. If you have to print fields from both loops then you must declare an "Ad Hoc" field and pass the alias fields contents to it for printing. If you need to exclude individual or groups of records from selection just use the "Skip Record" or "Skip Group" command. Ex. for videos with category in order if videos.category = "Cartoon" then skip group print list items next - 53 - ................................................................................ FORM LETTERS METHOD FORM LETTERS To print form letters (circulars) with a different address and salutation for each entry in an address form simply type the entire letter between the .List Items and .End of the output format. .........................procedure code........ Declare output fields Addr.name : Addr.street : Addr.town Addr.salutation date end date = datetext(system date) for addr print list items page feed next .........................output format........ .List Items My street My county {date field } {Addr.name field } {Addr.street field } {Addr.town field } Dear {Addr.salutation}, You are invited................................ ................................................... ................................................... .......................................... Yours faithfully, Fred A Blogs .End Although this is the fastest way to produce a short form letter, the Format Editor is not the nicest place to write text. If you want the benefits of word wrap and spell checking then you can create a form in which to write the letter. Create text block fields to hold a page of text and a name field so that you can store many different letters and print them with the same procedure. If your letters are to be more than one page long then put the same name on each page. - 54 - ................................................................................ FORM LETTERS METHOD FORM LETTERS To print the form letters from the "Letters" form your procedure must now have an input screen which looks up the name of the letter to be printed. '............................CODE..................... declare output fields addr.name : addr.street : addr.town :addr.salutation date :letters.block1 :letters.block2 :letters.block3 end declare variables page as number end date = datetext(system date) for addr page = 0 for letters with name = input.name page = page + 1 if page = 1 then print list items if page > 1 then print extra page feed next next The output format is similar to that shown on the previous page but the text in the .list items section is replaced by the text block fields Letters.block1 ,letters.block2 and letters.block3 and an additional section .extra is added which has the same text block fields but no address or salutation. - 55 - ................................................................................ FORMATTED TEXT FIELD TYPE FORMATTED TEXT If you define a field with the type "formatted text" a window opens up in which you enter the formatting characters. The characters which are to be entered by the user are shown as question marks. Any other characters which you enter become permanent fixtures in the field. The "formatted text" field can be up to twenty characters long and can be used for dates, national insurance numbers etc. Formatted text fields should not be derived. - 56 - ................................................................................ GLOBAL DEFAULTS METHOD GLOBAL DEFAULTS In many instances, an application will use the same default values in many procedures and field derivations. If you "hardwire" these as constants into your procedures and code then they will all have to be changed when the default value changes. Easy Base is supplied with a single global defaults form for VAT rates but you can create your own for any particular application. For instance, should you write a payroll system you would wish to be able to update the tax rates and bands globally throughout your application when they change. To do this, create a form to hold all the global defaults. In addition to the default fields add a single character text field derived "X" and index it. You then enter a single record with all the default values. The "X" field is there to create an artificial relationship between any input screen and the defaults form in order to lookup defaults. Wherever you need one or more global defaults in an input screen you simply add an invisible "x" field and enter a relationship between the procedure and the defaults form linking the "x" fields. The field "Tax" can now be derived:- taxablepay * lookup(defaults,taxrate) Similarly, you can pre load variables in procedure code for the default values:- Declare variables Taxrate1 as number:Taxrate2 as number Taxband1 as number:Taxband2 as number end for defaults Taxrate1 = defaults.taxrate1 :Taxrate2 = defaults.taxrate2 Taxband1 = defaults.taxband1 :Taxband2 = defaults.taxband2 next When the taxrates change you only have to alter them once in the Defaults form. - 57 - ................................................................................ GLOBAL NUMBER SYSTEM VALUE GLOBAL NUMBER The variables you create in Easy Base procedures are local variables - they cease to exist when the procedure ends. So that you can pass messages between procedures Easy base provides a single global variable "Global Number". Once you allocate a value to "Global Number" that value remains unchanged (even if your computer has been turned off) until you allocate another value to it. You can of course pass many messages between procedures simply by entering records in forms and re-reading them but using "Global Number" is much quicker. The following example uses "Global Number" to make access to a user menu restricted by password. Two procedures are defined. The first, "getpassword" has a password field on its input screen and the following code:- if input.password = "gingerbread" then global number = 1 else global number = 0 end if The second procedure "checkpassword" has the following code:- if global number <> 1 then exit batch menu global number = 0 The open access menu system has an item for the restricted menu but instead of calling it direct it calls the menu "Password" which is a batch execute menu with the items run procedure ---- getpassword run procedure ---- checkpassword user menu ---- restricted If the password "gingerbread" has not been entered in the "getpassword" input screen then "checkpassword" will return control to the original menu. - 58 - ................................................................................ GOTO (FIELD) NEXT DERIVATION PREFIX GOTO (FIELD) NEXT In an Easy Base form or procedure input screen the cursor moves from field to field in the default order top left to bottom right when you press the Return key. You can alter this default order by deriving any field with "Goto (fieldname) next". If the field from which you wish to redirect the cursor already has a derivation formula you simply add the "Goto - Next" as a prefix. Ex. goto price next goto item next default(lookup(stock,name)) Cursor redirection with the "Goto - next" derivation only occurs when the Return key is pressed it is not activated if you move the cursor with the directional arrow keys. Note: You can only have one prefix on any one field derivation. - 59 - ................................................................................ HOURS FUNCTION HOURS This function returns the hour ( 1 to 23) of the time parameter Ex. Hour(system time) Ex. Labourcharge = (hours(endtime)-hours(starttime))* rate Acceptable parameters: System time Time field Maketime function Time expression - 60 - ................................................................................ IF FUNCTION IF Ex. if(num >= 0,"Positive","negative") The first parameter of the "if" function is an expression, the second is the value to be returned if the expression is true and the third is the value to be returned if the expression is false. If the third parameter is omitted and the expression is false then the field or variable retains any value it had before the function was called. The "if" function can be extended to a full "case" function simply by adding more expressions and return values. Ex. if(num > 0,"Positive",num = 0,"Zero","Negative") There is no limit to the number of expressions and return values. If more than one expression is true the value returned is that for the first true expression. If none are true then the trailing value is returned as the "case else". Acceptable Parameters: Numeric expressions Quoted text Fields and variables Other nested functions System Values - 61 - ................................................................................ IF THEN ELSE PROCEDURE COMMAND IF THEN ELSE Easy Base recognizes the following "If Then Else" constructions. 1. if (condition) then (action) ------------------------------------------ 2. if (condition) then (action) else (action) ------------------------------------------ 3. if (condition) then (action) (action) (action) end if ----------------------------------------- 4. if (condition) then (action) (action) else (action) (action) end if If then constructions can be nested to any depth. if (condition) and (condition) then (action) if (condition) or (condition) then (action) (action) else (action) if (condition) eqv (condition) then (action) (action) end if if (condition) then (action) else (action) end if Easy base does not interpret "Else if" constructions. Easy base does not interpret two "if" conditions on one line. In the above examples "action" applies to any Easy base command or full "Do Loop" or full "For Next" structure. You cannot split a "Do Loop" structure and you can only split a "For Next" structure by a simple "If then" and "End if" with no "else" command. Ex. on next page. - 62 - ................................................................................ IF THEN ELSE PROCEDURE COMMAND IF THEN ELSE In the following example the procedure has an input screen with a field "type". "type" is a choice field with the choices "In age order", "In alphabetic order" and "By sex". The procedure code selects one of three listings from the "Pupils" form by splitting "For Next" structures by "If Then" structures. if input.type = "in age order" then for pupils with age in order end if if input.type = "in alphabetic order" then for pupils with name in order end if if input.type = "by sex" then for pupils with sex in order end if print list items next next next The structure above is the only one in which "For Next" structures can be split. There must be a separate "If Then" followed by "End If" for each "For". There must be a "Next for each "For" and there cannot be an "Else" anywhere in the construction. - 63 - ................................................................................ INDEX OFF PROCEDURE COMMAND INDEX OFF When an Easy Base procedure adds, updates or deletes a record then by default it updates any affected index files. As the majority of transaction procedures act on a single record this is the fastest method of processing. It does of course require that the form is periodically re-packed. If, however, a procedure is to add, update or delete many records then updating each affected index file on each iteration of the "For" loop can be much slower than ignoring the indices during the loop and re-writing them from scratch afterward. You can take the "re-write" option by issuing the command "Index off" before the "For" loop. index off for employees for wagelist new record wagelist.worksno = employees.worksno wagelist.taxcode = employees.taxcode next next Obviously re-writing an entire index file when only one or two records are affected would be slower than simply updating the indices. The actual point at which "Index Off" becomes faster can only be determined by trial but as a general rule the two systems take equal time when between 12 and 15 percent of the records of a file are affected and the advantage of "Index Off" becomes greater the bigger this percentage. There is no corresponding "Index On" command. Indexing is turned back on automatically and the index files are re-written at the end of the loop to which the command applies. If you require indexing off during more than one loop you must issue the command before each. index off for invoices with date < makedate(05,04,93) delete records next index off for invoices with customer = "john smith" invoices.customer = "John Smith Ltd" update record next If you prefix a loop which deletes records from a form then the form is packed at the end of the loop. - 64 - ................................................................................ Input Screen Format METHOD Input Screen Format All form and input screens can be presented either mounted in a window against a mottled background or exactly as drawn in form design. The windowing effect is done automatically if you leave a clear border around all text and fields. If you place fields or text anywhere against the edge of the screen (even invisible fields) then the window effect will not be invoked. If you do not want the window effect but you still wish to leave a clear border then you should place the invisible text character (ASCII 255) in the top left hand corner of the screen to disable it. To produce the invisible character hold down the Alt Key and type 255 on the numeric keypad. - 65 - ................................................................................ INSTALL FORM UTILITY INSTALL FORM The "Install Form" utility is accessed from the Utilities menu. If you wish to include a form in one application which you designed in another then you cannot simply copy the files to the new applications directory as there will be no entry in the "Forms Directory" file. To install a form from another application - first get the forms DOS filename from that application then select "Install Form" from the utilities menu. You will be asked to supply the forms name and the full path and filename from where it is to be copied. Easy Base will then copy the forms definition file and make the appropriate entry in the "Forms Directory". It will then ask whether or not you wish the data to be copied from the original application. If you install a form from another application and it has "lookup" derivations or Choice List fields then you will have to create the relationships and choice lists in the new application. They are not automatically reproduced. - 66 - ................................................................................ INSTALL PRINTER UTILITY INSTALL PRINTER The "Install printer" utility is accessed from the Utilities menu. It can also be included in a user menu so that end users of finished applications can install their printer without access to the system menus. When you select "Install Printer" you are offered a choice of ten printer drivers - five for named printers and five generic drivers. The drivers supplied cover virtually all printers except daisy wheels. When you have chosen your printer driver Easy Base then asks for the parallel port number to which printer output is to be sent - one or two. Easy Base does not have a built in serial port printer output. If your printer only has a serial input then you must redirect a parallel port with the DOS "Mode" command. Easy Base then asks how much paper wastage there is at the top of the page. This is quite important as it is used in conjunction with the paper size in use to determine the "Bottom Margin" value, used for pagination in printed reports. You are then asked for the wastage at the left margin. This information is used in the format editor to display the right edge of the paper at the different print sizes. There are only two settings for wastage at the left margin - zero and ¼ inch. If you have a cartridge fed printer such as a Laser or Bubble Jet then it will almost certainly be ¼ inch. If you have a traction or manual feed then you will be able to adjust the left margin and you should set it to zero. Finally you are asked for the top margin required in printed reports. This can only be between the value set as the printers inherent wastage and one inch. The value you set here will be the default top margin for all printouts. If you need a larger top margin for certain printouts then you must include blank lines in the page header of the procedures output format. - 67 - ................................................................................ INSTALL PROCEDURE UTILITY INSTALL PROCEDURE The "Install procedure" utility is accessed from the Utilities menu and allows you to install procedures which you designed in another application. It is used exactly like the "Install Form" utility previously described. - 68 - ................................................................................ INTEGER FIELD TYPE INTEGER Integer fields can have up to 14 digits and use a comma as the thousands separator. Although integer fields display integers they store the result of any division correct to 15 significant figures. See - Operators Arithmetic - 69 - ................................................................................ INTEXT FUNCTION INTEXT This function returns the starting character number of one length of text within another. Ex. Intext(companyname," and ") < Returns 7 if companyname = "Turner and Smith" The intext function returns 0 if the second parameter is not found in the first. Ex. If intext(customers.companyname," Ltd") <> 0 then customers.Ltd = "yes" update record end if Acceptable parameters: Text field/variable Any function returning a text value - 70 - ................................................................................ JOINTEXT FUNCTION JOINTEXT Ex. Jointext(forename,surname) The jointext function takes any number of parameters. Ex. Jointext("Mr ",forename," ",surname," Esq.") Ex. Jointext(if(sex=male,"Mr ","Ms "),surname) The "jointext" function will also perform any arithmetic operations required for a text output. Ex. Jointext("The Gross price is ",netprice *120/100) The "jointext" function is also used for creating compound index fields. Ex. Jointext(reverse(zeropad(price,4,2)),stockitem) This creates a field whose index allows a stock file to be listed with stockitem in alphabetical order but grouped by price in reverse order. If you "Jointext" a date or time field you will get the date or time's numeric value not the date or time's string representation. (See Compound Index) By default the "Jointext" function strips trailing blank space characters from the texts being joined. If you wish to join text in a Text Block field so that the second text starts on a new line then you can force this by including Chr$(13) which Easy base uses as a "New Line" flag. Ex. Jointext(block1,chr$(13),block2) <The text from block2 will start on a new line> Ex. Jointext(block1,chr$(13),chr$(13),block2) <There will be a blank line between block1 and block2> Acceptable Parameters: Numeric expressions Quoted text Fields and variables Other nested functions System Values - 71 - ................................................................................ LEFTTEXT FUNCTION LEFTTEXT This function returns a specified number of characters from the start of a field or variable. Ex. Lefttext(salutation,2) < returns "Mr" from "Mr Smith" or "Ms" from "Ms Smith" Ex. Lefttext(datetext(system date),4) < Returns 12th , 20th Etc.> (European date format) Acceptable parameters: Text field/variable Any function returning a text value - 72 - ................................................................................ LENGTHTEXT FUNCTION LENGTHTEXT This function returns the length of text in a field or variable Ex. lengthtext(name) < Returns 4 if name = "Bill" 10 if name = "Bill Smith" Acceptable parameters: Text field/variable Any function returning a text value - 73 - ................................................................................ LIST FILE NAMES UTILITY LIST FILE NAMES When you give a name to a Form or Procedure in Easy Base it is not the actual DOS file name used. A Form has separate DOS files for its definition, its data and its indices. Similarly a procedure can have one or two files depending on whether or not it has an input screen. Within an application you never need to know the DOS filenames as Easy Base takes care of all disk activity in the background. You may however need to know the DOS filenames if you intend to export data to another program or copy a Form or Procedure from one application to another. You can list the DOS filenames for Forms and reports with the "List File Names" utility. The "List File Names" utility can also be called from a user menu without accessing the system. - 74 - ................................................................................ LIST VARIABLES PROCEDURE COMMAND LIST VARIABLES The "List Variables" command is a de-bugging facility for developers. If a procedure is not producing the expected results you can interrupt it at any point and view the contents of all fields and variables in memory at that point. Ex. for authors with surname in order if authors.surname = "kippling" then list variables for books with author = authors.surname print list items next next - 75 - ................................................................................ LOOKUP FUNCTION LOOKUP Ex. Lookup(customers,name) The first parameter is the name of a relationship defined in the relationships form. The second parameter is the name of the field in the secondary file whose value is to be looked up. The parameters are absolute. You cannot supply them as variables fields or the results of other functions. Unlike other functions, "lookup" can only be used in a field derivation, entry conditions and mandatory conditions. It has no meaning in the procedure language. A full description of how to use the Lookup function is given in the users manual Acceptable parameters 1. Relationship name 2. Field Name - 76 - ................................................................................ LOWER FUNCTION LOWER Returns the lower case of the parameter. Ex. lower(partname) Ex. Heading = Jointext("List of parts supplied by ",lower(makers. name)) Acceptable parameters: Quoted text Text Field/variable - 77 - ................................................................................ MAKEDATE FUNCTION MAKEDATE The Easy base Date field is a six digit field which covers the century from 1 Jan 1981 to 31 Dec 2080 and upon which you can perform addition and subtraction operations in days. Ex. Duedate = Invoicedate + 30 In order to provide this facility the date is held as a numeric value. You can only type a date directly into a date field. If you need to enter a date in a derivation or in a procedure code you must use the "makedate" function. Ex. Makedate(5,11,95) < Returns 05/11/95 > Ex. Nextmonth = month(system date)+1 if nextmonth = 13 then nextmonth = 1 Nextdelivery = makedate(1,nextmonth,year(system date)) < Returns the date of the first of next month > Acceptable parameters Numeric value Numeric expression Numeric field/variable Any other function which returns a numeric value - 78 - ................................................................................ MAKETIME FUNCTION MAKETIME The value of a time field in Easy base is held as a numeric value representing the number of seconds past midnight. This allows you to perform addition and subtraction operations in seconds. Ex. Timetaken = Round(Endtime - Starttime / 60) < This returns the time taken in minutes > You can only enter a time directly into a time field. If you need to enter a time in a field derivation or in a procedure code you must use the "Maketime" function. Ex. Time = Maketime(20,20,00) < returns 20:20:00 > Acceptable parameters: Numeric value Numeric expression Numeric field/variable Any other function which returns a numeric value - 79 - ................................................................................ MATHS FUNCTION MATHS The following maths functions can be used anywhere in Easy Base code or field derivations. Log() Log10() Sqrt() Sin() Cos() Tan() Atan() Deg-rad() converts degrees to radians Rad-deg() converts radians to degrees Exp() Raises e (the base of natural logarithms) to the power of the parameter Acceptable parameters: Numeric value Numeric expression Numeric field/variable Any function returning a numeric value - 80 - ................................................................................ MAXIMIZING SPEED METHOD MAXIMIZING SPEED Because the procedures you create in Easy Base have to be interpreted each time they are run they will tend to be slower than similar routines created in a compiled system. This is the unavoidable cost of ease of use. You can increase speed vastly by having the best DOS environment and by the way in which you write procedure code. DOS Environment. 1. Do not use a disk compression system. 2. Always load "Fastopen" - The default settings are fine - just add the line "fastopen C:" to your AUTOEXEC.BAT file. 3. Use a Disk Cacheing system - The more memory you can allocate to it the better. Easy Software recommends PC-Cache from Centre Point with 2 Megabytes of expanded memory allocated. Smartdrive (Version supplied with Windows 3.1) was slightly faster but caused widespread disk corruptions during test "Power Failures". 4. Do not run any TSR programs (Especially Virus Checkers). Procedure Code. Avoid making calculations and derivations within "For" loops in procedures. If, for example, you had a form in which was recorded the length, breadth and depth of various blocks and you knew that at some point you would write a procedure which listed their volume. Add a field for volume to the form and derive it from the other fields. When you write the procedure you will simply list this field. If the field had not been added to the form then you would have to calculate its value on each iteration of the "For" loop. The time taken to derive each individual "Volume" field during record entry will not be noticeable but the time taken to derive the volume for every record during the procedure will. Keep "Running statistics". If your program requires statistics derived from many hundreds or even thousands of records then having to wait for a procedure which calculates them each time you want up to date figures is a real pain. To keep "running statistics" create a form with a field for each statistic you require and enter a single record with zero values in each field. Make all entries, modifications and deletions to your data via procedures and you can update your statistics each time a record is added, modified or deleted. Example on next page. - 81 - ................................................................................ MAXIMIZING SPEED METHOD MAXIMIZING SPEED The following procedure code enters a record (collected via the input screen) to a purchases ledger and updates statistics used in the profit and loss account. It also updates the balance for the suppliers account. pause off : Escape off for purchases new record copy all from input next for stats if input.type = "Invoice" then stats.creditors = stats.creditors + input.amount end if if input.type = "CreditNote" then stats.creditors = stats.creditors - input.amount end if if input.type = "Payment" then stats.creditors = stats.creditors - input.amount if input.paidby = "cash" then stats.cashbalance = stats.cashbalance - input.amount else stats.bankbalance = stats.bankbalance - input.amount end if end if next for ACbalances with supplier = input supplier if input.type = "Invoice" then ACbalances.balance = ACbalances.balance + input.amount else ACbalances.balance = ACbalances.balance - input.amount end if next Whenever you need to know statistics for "Debtors", "Cash Balance" etc you can produce them instantly with a procedure which simply lists data from the statistics forms. Whenever you use running statistics in this way you should also create a procedure which does calculate them from the raw data. If you ever, for one reason or another, have to edit data directly in "Data entry" then your "running statistics" will no longer be accurate. You can run this procedure to correct them. - 82 - ................................................................................ MENU CALLS MENU FUNCTION MENU CALLS In addition to the utilities which can be called from user menus the following calls can also be made. Run Procedure : Runs a pre-defined procedure Last Output : Recalls the output from a procedure Data Entry : Allows direct access to a form User Menu : Calls another user menu System Menus : Calls the Easy Base System menus Run external program : Shells to another program - You cannot run another major program from within Easy Base - there is very little spare memory - this call is however useful for calling batch files which export data to other programs for later use. - 83 - ................................................................................ MIDTEXT FUNCTION MIDTEXT This function returns a given number of characters from a given starting point in a field or variable. Ex. Midtext(fileref,4,4) < Returns "Bill" from "TR/Bill/1243" Acceptable parameters: Text field/variable Any function returning a text value - 84 - ................................................................................ MINUTES FUNCTION MINUTES This function returns the minutes number from the time field parameter. Ex. minutes(system time) Ex. Unitsperhour = rounddown(60/(minutes(endtime)- minutes(starttime))) Acceptable parameters: System time Time field Maketime function Time expression - 85 - ................................................................................ MOD FUNCTION MOD The Mod function returns the remainder of an integer division Ex. surplus rounds = mod(bullets,soldiers * ammoissue) Ex. for Historydates historydates.leapyear = "No" if mod(historydates.year,4) = 0 then historydates.leapyear = "Yes" update record end if next < This example fills a new field "leapyear" which has been added to the "Historydates" form after data has been entered> Acceptable parameters: Numeric value Numeric expression Numeric field/variable Any other function which returns a numeric value - 86 - ................................................................................ MONTH FUNCTION MONTH This function returns the month number from a date parameter. Ex. Month(system date) Ex. Month(birthdate) Acceptable parameters System date Date field Makedate function Date expression - 87 - ................................................................................ MULTIPLE COLUMNS METHOD MULTIPLE COLUMNS You may occasionally need to list data in order but in more than one column. Indexes for technical manuals are a common example. It would be nice if you could send the printer head back to the top of the page for each column but you can't. You must therefore get all the data for each horizontal line into memory at the same time and then print it. The following example prints the "Title" field from a "Films" form in alphabetic order in two columns with fifty lines on each page. To do this a field "No" is added to the form. This is an integer field and it is indexed. declare output fields films.title : righttitle end declare variables x as number : y as number : lasttitle as text end '................UPDATE THE No FIELD...... for films with title in order y = total records x = x + 1 display status "Updating No field record" + x + "of" + y films.No = x update record next '.................PRINT IN TWO COLUMNS................. x = 0 'reuse x as counter do for films with title > lasttitle x = x + 1 for films alias col2 with No = films.No + 50 righttitle = col2.title next print list items lasttitle = righttitle : righttitle = blank if mod(x,50) = 0 then page feed : Exit for end if next if lasttitle = blank then exit do loop ........................Format.................. .list items { Films.title field } { Righttitle Field } .end - 88 - ................................................................................ MULTIPLE LINES PROCEDURE COMMAND MULTIPLE LINES You can place multiple code instructions on a single line of the code editor by separating them with a colon. Ex. for ledger with lineno = input.lineno ledger.net = input.net:ledger.vat = input.vat ledger.acno = input.acno:ledger.gross = input.gross update record next - 89 - ................................................................................ ODD/EVEN PAGE PRINT PROCEDURE COMMAND ODD/EVEN PAGE PRINT If you want to print procedure output on both sides of the paper then, unless you have a VERY expensive printer, the only way to achieve this is to print the odd pages, turn your paper over and print the even pages. For a short report you can do this by setting paper feed to "Manual" from the utilities menu and turning each sheet over individually. If, however, you have a cut sheet feeder or a cartridge fed printer it is much quicker to print all the odd pages, turn them all over and then print all the even pages. To do this in Easy Base, issue the command "Odd Page Print" immediately before the print output and "Even Page Print" immediately after it. declare output fields customers.name : customers.phone end odd page print for customers with surname in order if bottom margin < .75 then page feed print list items next even page print '(Easy Base pauses here till you are ready) If you will be binding your printed report down the left hand edge then you will need a larger left margin on the odd pages than on the even. If you start odd page printing with the command "Odd Page Print for Binding" then Easy Base will add half an inch to the format left margin when it prints the odd pages. You cannot alter the fixed value of half an inch but if you create your output format with a left margin of half an inch(suitable for the even pages) and print for binding, you will find that the resulting output suits most office binding systems. If you use Odd/Even page printing then it is important that you realize it is achieved by running the portion of your procedure between the "Odd Page Print" and "Even Page Print" twice - simply cancelling output to the printer at the appropriate times. You must not, therefore include any commands which add, update or delete records between the commands. If you do they will be performed twice. Similarly if you are running a loop counter during the print process then it will have double the expected value after the "Even Page Print" command. The system value "Page Number" is reset to one at the "Even Page Print" command. If you include two separate sets of odd and even printing in a single procedure then you cannot use "Page Number" to number the second printout. You can of course use an ad hoc field. Just remember to reset it to one immediately before the "Even Page Print" command. - 90 - ................................................................................ ODD/EVEN PAGE PRINT PROCEDURE COMMAND ODD/EVEN PAGE PRINT This document was written in an Easy Base form "Ref" which has fields "Name", "Type", "Pageno" "Topicpage" and three text block fields "T1", "T2" and "T3". Each record holds one page of the reference manual and the pages were entered in no particular order. The following procedure was used to number, sort, index and print the manual. declare output fields ref.name : ref.type :ref.pageno ref.t1 : ref.t2 : ref.t3 : type : name end declare variables x as number : y as number namelen as number : typelen as number end '.....................NUMBER REFERENCE PAGES.... for ref with name in order y = total records x = x + 1 display status "Numbering page" + x + "of" + y ref.pageno = x : update record next odd page print for binding '...............PRINT COMMANDS INDEX........ bold on : print commandheader : bold off for ref with type = "procedure command" subindex name next for ref with subindex in order if topicpage > 1 then skiprec 'only first page ref.name = proper(ref.name) 'of each topic listed print commands next '..............PRINT SYSTEM VALUES INDEX... bold on : print valuesheader : bold off for ref with type = "system value" subindex name next for ref with subindex in order if topicpage > 1 then skiprec ref.name = proper(ref.name) print values next page feed '... eject first index page '......... subsequent index pages are produced with exactly '........ the sane code as above and are omitted for clarity. '......................MAIN REFERENCE............. page feed for ref with name in order namelen = lengthtext(ref.name) - 91 - ................................................................................ ODD/EVEN PAGE PRINT PROCEDURE COMMAND ODD/EVEN PAGE PRINT typelen = lengthtext(ref.type) '....centre justify type type = jointext(stringof((20-typelen)/2,chr$(255)),ref.type) '....right justify name name = jointext(stringof(20-namelen,chr$(255)),ref.name) bold on : print refheader : bold off 'type and names fields print list items 't1, t2, t3 bold on print reffoot 'pageno page feed next even page print - 92 - ................................................................................ OPERATORS ARITHMETIC OPERATORS The arithmetic operators used in Easy Base are:- + (addition) - (subtraction and negation) * (multiplication) / (division) ^ (exponentiation) Where there are multiple operators in a derivation they are processed with the following precedence:- 1. Exponentiation 2. Negation 3. Multiplication and Division 4. Addition and Subtraction The default precedence can be altered by the use of parenthesis. Easy base does not perform Integer Arithmetic. When a numeric field shows the result of a division the "displayed" number is "truncated" to an integer in Integer fields and has the last digit rounded in Fixed Point and Floating Point fields. The "Stored" value which is used in any further calculations, however, is always correct to 15 significant figures irrelevant of field type. For Example: If three fields x,y and z are defined as integers, y is derived as x/4 and z is derived as y*4 then when 9 is entered in x, y will derive as 2 but z will derive as 9 because it multiplies the "stored value" of 2.25 by 4 not the displayed value of 2. Handling arithmetic in this way means that, by default there are no rounding errors. This has several advantages not least that currency values need only ever be defined to two decimal places. It does mean, however, that should you require rounding errors to be carried forward you must use one of the Easy Base rounding functions on the result of any intermediate division. In the above example, had y been derived as "round(x/4)" then z would derive as 8. If a fourth field had the formula:- if(y = 2,5,10) it would only derive as 5 if y had been rounded or if 8 had been entered in x. - 93 - ................................................................................ OPERATORS RELATIONAL OPERATORS Easy base uses the following relational operators:- = (equals) <> (not equals) > (greater than) < (less than) >= (greater than or equal to) <= (less than or equal to) Where relational operators and arithmetic operators are both used in a derivation. Relational operations are performed after arithmetic operations. Text values are not case sensitive when compared with relational operators. When you compare two fields or variables, the comparison is numeric if both are numeric and alphabetic if both are alphabetic. If you compare a numeric field with a text field then Easy Base will test to see if the text field contains a number. If it does then the comparison will be numeric and if it does not then the comparison will be alphabetic. WARNING If you compare the result of a division in an integer or fixed point field using the = or the <> operators, then the value used for comparison will be the (accurate) stored value and not the "Truncated" number shown in the field. See Operators Arithmetic - 94 - ................................................................................ OPERATORS LOGICAL OPERATORS Easy Base uses the following logical operators:- 1. and 2. or 3. xor (or exclusive) 4. eqv (equivalence) 5. imp (implication) Logical operations are performed last after arithmetic and relational. Logical operators return a boolean (true or false) value from two other boolean expressions. Ex. If(x = y and a = b,"Yes","No") < Yes is returned if both expressions are true > If(x = y or a = b,"Yes","No") < Yes is returned if either or both expressions are true > If(x = y xor a = b,"Yes","No") < Yes is returned if either one but not both are true > If(x = y eqv a = b,"Yes","No") < Yes is returned if both expressions are true or if both expressions are false > If(x = y imp a = b,"Yes","No") < Yes is returned for all combinations except the first expression being true and the second false > The Easy Base logical operators are primarily designed for use within the "If" function and in "If..Then..Else" constructions where their actual value is not relevant. If you wish to use the value of "false" in formulae then it is represented by 0. True can be represented by any value other than 0 and should not be used. - 95 - ................................................................................ OPTIONS UTILITY OPTIONS The "Options" utility allows you to select your preferred date format (European or North American). You can also enable or disable screen saving. If enabled the screen saver operates after three minutes without a keypress during record entry, procedure screen entry or if a menu is being displayed. The screensaver is never invoked while a procedure is running or while reformatting or re-indexing is taking place. The third option allows you to select the border style you require around menus and message boxes. Option four selects whether or not screen clearing will be done with the curtain effect. The options utility can be called as a user menu function so that an end user can access the facilities without access to the system menus. - 96 - ................................................................................ OUTPUT SYSTEM VALUE OUTPUT When a user runs a procedure his choice of output, screen, printer or disk is stored in the system value "Output". By accessing the system value "Output" you can make your procedure do different things depending on where the output is to be sent. Ex. if output = "screen" then print heading1 else print heading2 end if - 97 - ................................................................................ PACK DATA FORMS UTILITY PACK DATA FORMS The process of actually removing a deleted record from a form is time consuming. What actually happens is that all the "Live" records are copied to a temporary file. The original file is then deleted and the temporary one re-named to the original name. Because of this it is actually quicker to remove fifty "Dead" records than it is to remove one. To save you time Easy base follows the traditional data-base custom of simply marking records for deletion when you delete them. They are only physically removed from the form during a "Pack" operation. When you "Pack" a data file Easy Base also re-writes all the index files for that form. You can pack individual forms with the "Pack Data Forms" utility accessed from the Utilities menu. You can also call the "Pack Data Forms" utility as a User menu function. In a finished application any necessary packing would normally be done from a batch execute menu which would run while the computer was unattended. Forms in which records are regularly deleted should be packed regularly. If packing is not done then not only will you be wasting disk space but search operations will become progressively slower as the percentage of "Dead" records increases. This is fairly obvious - however it is not so obvious that a forms index files can accumulate "Dead" records even though none have been deleted from the main file. An index file is simply the indexed fields contents and record number arranged in order. If, having filed a record you then alter the contents of an indexed field and update the record then the original record in the index file is deleted and a new one entered. Index files with "Dead records" also waste disk space and slow search operations. You should therefore regularly pack any forms which are regularly updated. If you are unsure which forms need to be packed you can use the "Auto-Pack" utility which tests each form and each forms index files for "Dead" records. The form is packed if it or any of its index files is found to have more then 100 total or 10 per cent "Dead" records. Although "Auto-Pack" may seem the easy answer, an application will almost certainly have several forms with "Core" data which remain unchanged and therefore never require packing. If you can identify the forms which do need to be packed and do them individually or from a batch execute menu this will be less time consuming than always using the "Auto-Pack" facility. - 98 - ................................................................................ PAGE NUMBER SYSTEM VALUE PAGE NUMBER Whenever Easy Base is processing a procedure with an output it keeps track of the current page number (Even if you have not included Page Feed commands). You can use the system value "Page Number" to number the pages of your output. To use the "Page Number" value just include "Page Number" in your output field declarations. Declare output fields Customers.name : Customers.ACNO : Page Number end You can then use the "Page Number" field in the Page Header or Page Footer section of your output format. - 99 - ................................................................................ PAUSE ON/OFF PROCEDURE COMMAND PAUSE ON/OFF When an Easy Base procedure has an output to the screen, the output, by default scrolls continuously until the procedure is complete. You can allow the user to pause the output with the "Pause On" command. If a procedure performs several tasks then the pause facility can be selectively enabled and disabled at any point. Ex. for employees alias types with type in order unique pause off:escape off for employees alias group with type = types.type subindex surname next pause on for employees with subindex in order print list items next next The above code fragment prints employees details in type groups with surname in alphabetic order. During the procedure the user can pause the report while it is listing the output but not while it is subindexing the group. - 100 - ................................................................................ PI SYSTEM VALUE PI You can use the value of π anywhere in field derivations or procedure code by quoting "Pi". Ex circlearea = Pi / 4 diam^2 - 101 - ................................................................................ PRESELECT PROCEDURE COMMAND PRESELECT The "Preselect" command sets highlighting of the next user menu when the procedure terminates. If a procedure is normally followed by a particular selection from the menu then you can save the user the trouble of choosing it by issuing the "Preselect" command within the procedure. for currentcustomer currentcustomer.acno = input.acno update record next preselect 4 - 102 - ................................................................................ PRINT PROCEDURE COMMAND PRINT In Easy Base procedures, all output to the screen or printer is initiated with the "Print" command. The "Print" command prints sections from the procedures output format. Ex. '......................procedure code.... Print report header for customers print list items next print report footer '......................Output Format....... .Report Header ════════════════════════════════════════════════ Customer List ════════════════════════════════════════════════ .List Items {customers.name field} {customers.phone field} .Report Footer ════════════════════════════════════════════════ .end The format sections are marked with a full stop in the margin followed by the section name. When you type a full stop in the margin of the "Format editor" Easy Base will offer you a selection of section names. You do not have to use these - they are just some common names that can save you time. To enter your own section names just press escape and type in any name you wish. The ".end" section is not strictly necessary but it is easy to accidentally insert blank lines at the end of your format. By inserting ".end" at the end of the last section you will prevent these from being printed. - 103 - ................................................................................ PRINTER CONTROL PROCEDURE COMMAND PRINTER CONTROL When you send a procedure output to the printer Easy Base will initialize your printer at ten characters per inch, six lines per inch in draft mode. You can alter the printer mode anywhere within a procedure with the following printer controls. Bold On Bold Off Underline On Underline Off Italic On Italic Off NLQ On NLQ Off 8 lpi Sets printing to eight lines per inch 6 lpi Sets printing to six lines per inch 10 cpi Sets printing to ten characters per inch 12 cpi Sets printing to twelve characters per inch 17 cpi sets printing to either 15 or 17 depending on printer Line Feed Advances the print head one line Page Feed Ejects the current page. Ex. NLQ On Bold on Print report header Bold Off 12 CPI For customers print list items if bottom margin < 1.5 then Line feed : Bold On : 10 CPI Print Page footer Page feed Print Page header Bold Off : 12 CPI end if next Bold on:Italic On :10 CPI print report footer The above example turns Near Letter Quality printing on for the entire report. It prints page headers and footers in bold at 10 characters per inch, list items at 12 characters per inch and the report footer in bold italics. - 104 - ................................................................................ PROPER FUNCTION PROPER This function returns the parameter with the first letter of each word in upper case and the rest in lower case. If you wish to enter initials in a field derived as proper and keep them in upper case then there must either be a space or a full stop between them. Ex. Proper(Address) Ex. labelname = upper(company.name) insetname = proper(company.name) Acceptable parameters: Quoted text Text field/variable - 105 - ................................................................................ Q AND A INPUT SCREEN METHOD Q AND A INPUT SCREEN If, for a procedure you have to collect many items of data via an input screen, then presenting all the fields and their labels at once can be confusing to an operator. You can start with an empty screen other than the first field and its prompt then have each subsequent prompt "Pop up" when the previous field has been filled. To do this you make all your input fields without a background (Text, Alt1 or Alt2) and you create fields for the prompts which have no user entry and also show no background. Ex. To collect data for paymethod, customer, and amount lay out the following fields:- Enter Method of payment.... { Paymethod Field } { Pop1 Field } { Customer Field } { Pop2 Field } { Amount Field } The "Pop1" field is derived:- If(Pop1 = blank and paymethod = blank,blank,"Enter Customers name.........") and the "Pop2" field is derived:- If(Pop2 = blank and customer = blank,blank,"Enter Amount... ...............") - 106 - ................................................................................ RANDOM FUNCTION RANDOM This function returns a random number between the upper and lower parameters. Ex. Random(1,100) <Returns a random number between 1 and 100> Ex. The two fields No and card simulate the random cutting of a deck of cards each time the No field is cleared. No field derivation:- if(no <> blank,no,random(1,52)) Card field derivation:- jointext(if(mod(no,13) = 1,"Ace",mod(no,13) = 0,"King", mod(no,13) = 12,"Queen",mod(no,13) = 11,"Jack",mod(no,13)), " of ",if(no < 14,"Hearts",no < 27 ,"Clubs",no <40,"Diamonds", ,"Spades")) Acceptable parameters Numeric Value Numeric expression Numeric field/variable Any other function which returns a numeric value - 107 - ................................................................................ REBUILD DIRECTORIES UTILITY REBUILD DIRECTORIES In Easy Base, all DOS file handling is taken care of for you in the background. To do this Easy Base maintains three "Directory" files in which the names which you give to forms, procedures and choice field lists are linked to the actual DOS filenames. The three files are :- BASE.DIR The forms directory PROC.DIR The procedures directory CHOICES.DIR The choice list directory Because these are key files, Easy Base includes a utility which will rebuild them in the event of accidental erasure or corruption. - 108 - ................................................................................ RECORD NUMBER SYSTEM VALUE RECORD NUMBER If you derive a field with the formula "record number" it will be filled with the record number at the time of filing or reformatting. Record number differs from "sequence" in the following way. Each time you derive a field with "sequence" you get the next sequencial number irrespective of whether the form has had records deleted and has been repacked. If a file has had at some time 500 records but now only has 20 the next derivation of "sequence" will be 501 and the next derivation of "record number" will be 21. The main use of "record number" is to renumber a set of records after deletions. To do this just add a new field to the form derived as record number. The field will be filled automatically during the data reformatting process. Record Number has no meaning in procedure code. See Also:- Current Record Number - 109 - ................................................................................ REMARK PROCEDURE COMMAND REMARK You can place remarks anywhere within Easy base procedure code by prefixing them with an apostrophe. Ex. '.................This entire line is a remark..... for pupils with age > 9 if grade = "g" then skip record 'exclude g's from count count = count + 1 'These are remarks next '.............................................. All text to the right of the apostrophe is ignored at run time. - 110 - ................................................................................ RESTORE UTILITY RESTORE Easy Base will restore applications or data which it has backed up. You can only restore an application with the "Restore Application" utility called from the utilities menu and you can only restore data-only backups with the "Restore Data" utility called from a user menu. See Also:- Backup - 111 - ................................................................................ RETAIL FUNCTION RETAIL The retail function returns the retail price given the wholesale price and the discount as a percentage of retail. Ex Retail(cost,discount) < returns 125 if cost = 100 and discount = 20> - 112 - ................................................................................ RETEST DERIVATION PREFIX RETEST In Easy Base, fields are derived when a new record is started and when any other field which affects the derivation is changed. You can force a field to be rederived when F2 is pressed by prefixing the derivation with "retest". Suppose you have a field "degrees" in which the user must enter a number between 1 and 360. To prevent him entering an out of range number you could derive the field :- If(degrees<1 or degrees > 360,blank[beepInvalid number of degreesCursor degrees],degrees) The above derivation would prevent the user from filing a record with an out of range number. However, by the time he read the message, his entry would be blanked out. It would be better if the number he had typed could be left in the field so that he could see his error. To do this all you have to do is change "blank" to "degrees" in the above derivation. This however would allow the user ( Should he decide to ignore the message ) to then file the incorrect value. To leave the value in the field and still prevent the user from filing it, use the "retest" prefix. Retest If(degrees<1 or degrees > 360,degrees[beepInvalid number of degreesCursor degrees],degrees) Note: You can only have one prefix on any one field derivation. - 113 - ................................................................................ REVERSE FUNCTION REVERSE The reverse function is used in the creation of compound index fields. What it actually does is alter the characters of the parameter in such a way that an alphabetic listing of a field after reversal will have the reverse order to that of the original characters before using the reverse function. In the example shown for the function "zeropad", had you wished to list the pupils in descending age order there would be no point in using the procedure code: for pupils with unifield in reverse order as this would not only reverse the age grouping but would also reverse the name order. To produce the desired printout use exactly the same procedure but derive "unifield" as:- Jointext(reverse(zeropad(age,2,0)),name) - 114 - ................................................................................ RIGHTTEXT FUNCTION RIGHTTEXT This function returns a specified number of characters from the end of a field or variable. Ex Righttext(name,10) Ex. Righttext(datetext(system date),4) < returns 1993 , 1994 Etc.> Acceptable parameters: Text field/variable Any function returning a text value - 115 - ................................................................................ ROUND FUNCTION ROUND There are three rounding functions in Easy base:- Round Roundup Rounddown If a rounding function is called with a single parameter then the return value is the parameter rounded to the nearest integer value. Ex. Round(items/reps) < if items = 9 and reps = 4. 2 is returned > All rounding functions accept a second parameter indicating the number of decimal places (or powers of 10 if negative) to round to. Ex. Rounddown(vaton(net),2) < Rounds down the VAT on field "net" to the nearest penny. Ex. Jointext(roundup(if(mod(years,100) = 0,years+1,years),-2 )/100,"th Century") < Returns "20th Century" for all values of years 1900-1999 > Round Produces a default rounding system in which:- 2.4 rounds to 2 2.5 rounds to 3 -2.4 rounds to -2 -2.5 rounds to -3 In the Roundup and Rounddown functions the direction is sign sensitive:- 2.5 roundsup to 3 2.5 roundsdown to 2 -2.5 roundsup to -2 -2.5 roundsdown to -3 Acceptable parameters: Numeric value Numeric expression Numeric field/variable Any other function which returns a numeric value - 116 - ................................................................................ RUN FIELD CONTROL RUN The "run" control is used to initiate procedures with input screens without the user having to press F2. Ex. If(lookup(customers,name) = blank,blank,lookup(customers, name)[run]) In the above example, a procedure which lists customers accounts has an input screen with a single field where the customers name is entered. When the user enters a name the above derivation will test whether or not the entered name is a customer. If the entered name does not exist in the customers file the field will be blanked out but if it does then the procedure will run without the user having to press F2. - 117 - ................................................................................ SCREEN COLOURS UTILITY SCREEN COLOURS The "Screen Colours" utility provides 14 pre defined colour sets including two for "Mono Graphics" cards together with a user defined setup. To change the colours of your system simply select "Screen Colours" from the Utilities menu and select the set you want. The "Screen Colours" utility can also be called as a User Menu function allowing an end user to change his screen set from your menus without accessing the system. - 118 - ................................................................................ SECONDS FUNCTION SECONDS This function returns the seconds number from the time parameter. Ex. seconds(system time) Acceptable parameters: System time Time field Maketime function Time expression - 119 - ................................................................................ SEQUENCE SYSTEM VALUE SEQUENCE When you create a new form a sequence value is updated each time a record is entered. The "sequence" value starts at 1 and continues to 2 billion irrespective of whether or not records have been deleted from the form. If you derive a field as "sequence" then each new record will have the next sequenced number. Sequence derivations are useful for such things as invoice numbers. A sequence field need not start at one however. Ex. sequence + 1000 would number the first record 1001 The sequence value is only available in data entry to a form. A common problem in data systems is to produce a sequence number when records are entered to forms via procedures. To accomplish this define fields to hold the number on both the form and the procedure input screen. Also define a single character text field, let's call it x on both. This field can be invisible. The x field in the form is indexed but not derived. The x field on the input screen is derived as "x". A relationship, let's call it "invoice" is entered between the input screen and the form relating the two "x" fields. Assuming the fields for the sequence number are called "invno" the field on the input screen is derived as:- lookup(invoice,invno)+ 1 To update the x field in the form which is used purely as a flag to mark the last record your procedure code would be something like:- for invoices with x = "x" invoices.x = blank update record next for invoices new record copy all from input next - 120 - ................................................................................ SET PAPER LENGTH UTILITY SET PAPER LENGTH The "Set Paper Length" utility is accessed from the Utilities menu and can also be called from a user menu. There are settings for A3, A4, A5, American Letter and American Legal in both portrait and landscape. When you change the paper length in use then you update pagination for all report outputs. The landscape options are for use in wide carriage printers where the paper can be fed in landscape orientation they do not produce "sideways" printing on narrow carriage printers. - 121 - ................................................................................ SKIP GROUP PROCEDURE COMMAND SKIP GROUP The "Skip Group" command excludes an entire group of records from selection by a "For" loop. Ex. for employees with type in order if employees.type = "Part time" then skip group print list items next The "Skip Group" command is not the same as filtering with a "<>" condition. You could achieve the same result with :- for employees with type in order if employees.type <> "Part time" then print list items end if next but the second procedure would take much longer to run as each part time employee's record would be loaded into memory only to be rejected by the "If" condition. The first procedure on the other hand only loads a single part time employee's record then skips the entire group by advancing the index pointer in use for record selection. You can also use the "Skip Group" command to terminate selection of a group of records at a given point. declare variables count as number end for employees with type in order count = count + 1 if count = 51 then count = 0 skip group end if print list items next The above procedure prints the first fifty employees in each "type" group. The "Skip Group" command can be shortened to "Skipgrp" - 122 - ................................................................................ SKIP RECORD PROCEDURE COMMAND SKIP RECORD The "Skip Record" command is used to exclude individual records from selection in a "For" Loop. Ex. for employee with worksno in order if employees.worksno = 12 then skip record print list items next The "Skip Record" command has exactly the same effect as filtering with a "<>" condition but is much easier to write if there are many items to exclude for employees with worksno in order if employees.worksno = 12 or employees.worksno = 15 then skip record end if if employees.name = "smith" then skip record if employees.age > 50 then skip record next You can exclude groups of records from an inner loop by applying the "Skip Record" command to a "Unique" outer loop. for employees alias types with type in order unique if types.type = "director" then skip record if types.type = "part time" then skip record for employees with type = types.type print list items next next If you need to exclude groups of records from a single loop you must use the "Skip Group" command. The "Skip Record" command can be shortened to "Skiprec". - 123 - ................................................................................ SPACEPAD FUNCTION SPACEPAD The Spacepad function pads a text variable to a given length with spaces. Ex. Spacepad(name,10) < returns "Fred " if name = "Fred" The spacepad function is used in the creation of compound index fields. For example: A "Films" form has the fields "title" and "category". To list the films grouped by category in order and with the titles in each category in order create the field "groupindex" as text,long enough to hold both category and title fields and derived as :- Jointext(spacepad(category,15),title) {15 is length of category field} You can then produce the required printout with the following procedure. ....................format......................... .Report header ═══════════════════════════════════════════════════════════ FILM LIST BY CATEGORY ═══════════════════════════════════════════════════════════ .Group header Films in {category field} .List items {title field} Starring {Star field} Running time {length Fld} .end ......................procedure code................. declare output fields films.title:films.star:films.length:films.category end declare variables catcheck as text end '............ bold on print report header for films with groupindex in order if films.category <> catcheck then bold on print group header bold off end if print list items next - 124 - ................................................................................ SPELLDAY FUNCTION SPELLDAY This function spells out the day of the number (1-7) parameter. Ex. Spellday(dayofweek(system date)) Acceptable parameters: Numeric value (1-7) Numeric expression evaluating to (1-7) Numeric Field/variable holding value (1-7) Any function returning a numeric value (1-7) - 125 - ................................................................................ SPELLMONTH FUNCTION SPELLMONTH This function spells out the day of the number (1-12) parameter. Ex. Spellmonth(month(system date)) Ex. Month = Spellmonth(month(invoices.date)) Acceptable parameters: Numeric value (1-12) Numeric expression evaluating to (1-12) Numeric Field/variable holding value (1-12) Any function returning a numeric value (1-12) - 126 - ................................................................................ START HERE DERIVATION PREFIX START HERE If, in a form or procedure input screen, you want the cursor to start in other than the top left field then derive the field in which you wish the cursor to start with "Start here" . If the field in which the cursor is to start already has a derivation then simply prefix the derivation with "Start here". Ex. start here Lookup(customers,name) Note: You can only have one prefix on any one field derivation. - 127 - ................................................................................ STRINGOF FUNCTION STRINGOF This function returns a string of characters. Ex. Stringof(12,"-") < Returns "------------"> Ex. Stringof(5,chr$(240)) < Returns "≡≡≡≡≡"> One of the most useful applications of the "Stringof" function is to pad output fields in tabular reports with "." characters. Ex. Declare output fields stock.name : stock.price end for stock with name in order stock.name = jointext(stock.name,stringof(30-lengthtext( stock.name),".") print list items next The printed output from the above report would be in the form:- Gizmo......................... 2.50 Sprocket...................... 9.95 Widget........................ 3.10 Acceptable parameters: 1 Any numeric field, variable, expression or function 2 Single quoted character - Function returning a single character - 128 - ................................................................................ SUBINDEX PROCEDURE COMMAND SUBINDEX If you need to list records from a form in such a way that they are in groups but with each group in order, there are two ways to accomplish this. You can include a compound index field on the form or you can "subindex" the group during the procedure. The advantage of having a compound index is that no time is wasted "sorting" the sub group during any procedure that uses it and the disadvantage is that it uses extra disk space. As a general rule you should use a compound index where the sub groups are likely to be large and "subindex" during the procedure only if each sub group has up to a few dozen or so records or if you need to order unnatural groupings. Ex. for pupils alias agegroups with age in order unique for pupils alias sortgroup with age = agegroups.age subindex name next for pupils with subindex in order print list items next next The grouping can be extended to more than one level. The following example lists records from a "Videos" form grouped by rental price then by category and with the sub sub group titles in alphabetic order. for videos alias pricegroups with price in order unique for videos alias catgroups with category in order unique for videos alias group with category = catgroups.category if group.price = pricegroups.price then subindex title end if next for videos with subindex in order print list items next next next As you can see from the listing the output from these examples has to pause between groups while the subindex is written. If you have a 66 Mhz PC and there are twenty records in the sub group you probably won't notice but if you have a 12 Mhz machine and there are a thousand records in the sub group you may as well break for lunch! If you write a "subindexing" procedure and find that the "Sorting" time is unacceptable then add a compound index field to the form. - 129 - ................................................................................ SUBINDEX PROCEDURE COMMAND SUBINDEX Although using compound index fields to produce group ordering is normally preferable to using "Subindex", there is one important area where "Subindex" is more flexible. That is where you need to order records in unnatural groups. Consider the "Videos" form which has fields for "Title" , "RentalPrice" and "Category". Natural Groupings would be:- Titles with RentalPrice = £2 Titles with Category = "Comedy" Titles with RentalPrice = £2 and category = "Horror" Unnatural groupings would be :- Titles with RentalPrice = £2 or Rentalprice = £1.50 Titles With RentalPrice = £1.50 or Category = "Western" Although you can make compound index fields to produce unnatural groupings they are not flexible - You need a separate compound index for each unnatural group. If you wished to write a procedure which allowed the user to enter any two video "Categories" and have all titles with either of those two categories listed in order then you must use "Subindex". Ex. for videos alias cat1 with category = input.cat1 subindex title next for videos alias cat2 with category = input.cat2 subindex title next for videos with subindex in order print list items next You will notice in the above example that two "For" loops add records to the "Subindex" before it is used. Once you start a subindex you can add records to it as often as you wish before using it. Once you use it it is erased. You can only have one "Subindex" active at any time. You can use any number of "Subindexes" within one procedure but each one must be "used" before the next is started. - 130 - ................................................................................ SYSTEM DATE SYSTEM VALUE SYSTEM DATE You can use your computers inbuilt date anywhere in field derivations or procedure code by simply quoting "system date". Ex. for diary with date = system date print list items next - 131 - ................................................................................ SYSTEM TIME SYSTEM VALUE SYSTEM TIME You can use your computers inbuilt time anywhere in field derivations or procedure code by simply quoting "system time". Ex. for stockitems with stockno = input.stockno lastcheckeddate = system date lastcheckedtime = system time update record next - 132 - ................................................................................ TABULATION METHOD TABULATION To create a printout in which fields are enclosed within a lined table use the linedrawing facility in the format editor to create a page header and page footer containing the top and bottom of the table and insert only the vertical lines between the fields in the list items section. Ex. Declare output fields stock.name : stock.price end print page header for stock with name in order if bottom margin < 0.7 then print page footer page feed print page header end if print list items next print page footer Page feed ........................format......................... .page header ╔════════════════════╤═══════════════╗ ║ Name │ Price ║ ╠════════════════════╪═══════════════╣ .list items ║ {Name Field } │ {Price Fld. } ║ .page footer ╚════════════════════╧═══════════════╝ .end - 133 - ................................................................................ TEXT FIELD TYPE TEXT The text field type can be up to 80 characters long. Cut copy and paste operations are available between all text, and textblock fields : Use Shift plus the arrow keys to mark the text you wish to cut or copy. When you release the keys the cut or copy choice will appear. To paste cut text position the cursor where you want it inserted and press Shift plus Ins. Spell Checking is also available in text fields - Press Ctrl + S - 134 - ................................................................................ TEXT BLOCK FIELD TYPE TEXT BLOCK When you Define a field with the field type "Text Block" then instead of entering a field length you shade out the area which you wish the field to cover using the arrow keys. Textblock fields offer many of the facilities of a wordprocessor including cut and paste, wordwrap and spell checking. Useful widths for A4 and Letter size paper are 63 characters if you intend to print at 10 characters per inch and 75 characters if you intend to print at 12 characters per inch. This document for instance was created in 63 character wide text blocks. The maximum width of a text block field on screen is 80 characters. You can however redefine it up to 230 characters wide in procedure printouts and Easy Base will reformat your paragraphs to the new width. A text block field is not a "memo" field. It can be indexed and derived. - 135 - ................................................................................ TIME FIELD TYPE TIME The Easy Base "time" field has the eight character format:- 11:20:20 (Hours:Minutes:Seconds) You can record time data in any format you wish by using a "Formatted Text" field but you must use a "Time" field if you wish to perform addition and subtraction operations on time values. (in seconds) Time fields are automatically checked for validity. See Also:- Maketime - 136 - ................................................................................ TIMEAMPM FUNCTION TIMEAMPM This function returns the hour ( 0 - 12) plus am. or pm. from the time field parameter. Ex. Timeampm(system time) Ex. -------------------------format---------------------------- .Report header Appointments for {Apps.Date Field} .List items {Apps.Name Field } at {Time Field} .End -------------------------code---------------------------- declare output fields Apps.name Apps.date Time end print report header for apps with date = system date time = timeampm(apps.time) print list items next Acceptable parameters: System time Time field Maketime function Time expression - 137 - ................................................................................ TOTAL COPIES SYSTEM VALUE TOTAL COPIES Whenever a procedure enters a "FOR" loop qualified by "With fieldname = " the total number of records which match the qualification value is available in the system value "Total Copies" Total Copies is extracted directly from the index file and relieves you of a time consuming loop all the way through a form updating a counter. In other words:- for books with category = "fiction" count = count + 1 next can be replaced with:- for books with category = "fiction" count = total copies exit for next Unlike "Total Records" the "Total Copies" value never includes records marked for deletion. Easy Base does not track separate values for "Total Records" or "Total Copies" when there are nested "For" loops. The values are set for the outer loop on the first iteration and reset for the inner loop on its first iteration. If you need a value for "Total Records" or for "Total Copies" after your procedure has entered another loop then you must transfer the value to a variable while it is available. (See the example for "Display Status") Easy Base does not update "Total Records" or "Total Copies" when records are added or deleted during a procedure. - 138 - ................................................................................ TOTAL RECORDS SYSTEM VALUE TOTAL RECORDS Whenever a procedure enters a "FOR" loop the total number of records in the form is available in the system value "Total records" Ex. The following code fragment updates a "Customer summary" form with the total number of customers in the "Customers" form. declare variables tot as number end for customers with acno = 1 tot = total records exit for next for customersummary customersummary.totalcustomers = tot update record next Caution. Provided that the "For" loop is qualified by a "with" statement then the "Total Records" value is extracted directly from the open index file and is the total number of "Live Records". If you access the "Total records" value in an unqualified "For" loop then there is no index file open and the "Total Records" value is derived by dividing the forms file length by the record length. It therefore represents the total number of records in the form "Live" and "Deleted". Ex. If a form "books" has 2000 records and 10 are marked for deletion at the next re-pack then "count" returns 1990 in:- for books with catalogueno = 1 count = total records next and 2000 in :- for books count = total records exit for next - 139 - ................................................................................ TOTALS & SUB TOTALS METHOD TOTALS & SUB TOTALS To produce totals and sub totals in printouts use ad hoc fields to hold the values and increment them during each iteration of the output loops. The following code lists all entries in a clients time sheet form with the cost of work in hand totalled and sub totalled for each client. Declare output fields Clients.Name:Timesheet.Hrs:Timesheet.Mins:Timesheet.Amount Timesheet.date : Subtotal : Grandtotal end Print Report Header for Clients with name in order Print Group Header Subtotal = 0 For Timesheet with Acno = Clients.Acno Subtotal = Subtotal + timesheet.amount Grandtotal = Grandtotal + Timesheet.amount Print List Items next Print Group Footer next Print report footer .......................Output Format......................... .Report Header ═════════════════════════════════════════════════════════ Work in Hand Totalled by Client ═════════════════════════════════════════════════════════ .Group Header ██████████████████ .List Items ████████ ██Hrs ██Mins ██████ .Group Footer ────────── Total for ██████████████████ ██████████ ══════════ .Report Footer ═════════════════════════════════════════════════════════ Total Work In Hand ██████████ ═════════════════════════════════════════════════════════ .end The field in the Group Header is Clients.name The fields in List Items are Timesheet.date, Timesheet.Hrs, Timesheet.Mins and Timesheet.amount. The fields in Group Footer are Clients.name and Subtotal. The field in Report Footer is Grandtotal. - 140 - ................................................................................ UPDATE RECORD PROCEDURE COMMAND UPDATE RECORD The "Update record" command is used to alter the contents of one or many records in a form. Ex. for stock with item = input.item stock.price = input.price stock.pricechangedate = system date update record next This example changes the price of a single "Stock" item. for employees with taxcode = input.oldtaxcode employees.taxcode = input.newtaxcode update record next This example changes the taxcode field of all the employees whose present taxcode is the same as the input screen "oldtaxcode" to the input screen "newtaxcode". for employees employees.taxcode = employees.taxcode + 60 update record next This example increases the taxcode of all employees by sixty. - 141 - ................................................................................ UPPER FUNCTION UPPER Returns the Uppercase of a text field or variable. Ex. Upper(postcode) < shows the postcode field in uppercase> Ex. '....................... declare output fields labelname labeladdress labelcode end for customers with maillist = "yes" Labelname = Upper(customers.name) Labeladdress = Upper(customers.address) Labelcode = customers.postcode print labels next Acceptable parameters: Quoted text Text field/variable - 142 - ................................................................................ V.A.T. FUNCTIONS FUNCTION V.A.T. FUNCTIONS Easy Base has a set of five VAT rates which can be altered from the utilities menu. There are four VAT functions:- VATin VATon PlusVAT MinusVAT Ex. VATin(retailprice,2) < Returns the VAT content of "retailprice" at VAT rate 2 Ex. VATon(netprice,4) < Returns the VAT to be added to "netprice" at VAT rate 4 Ex. PlusVAT(netprice,3) < Returns the VAT inclusive price at VAT rate 3> Ex. MinusVAT(retailprice,2) < Returns the net price of "retailprice" before VAT at VAT rate 2> (assumes Retailprice as VAT Inclusive and Netprice exclusive) If the second parameter (rate) is omitted from any of the VAT functions then Rate 1 is assumed. Ex. Gross = PlusVAT(input.net) < The variable "Gross" = the VAT inclusive of the input screen field "net" calculated at rate 1.> Although the VAT functions are primarily included for business applications, they can be used for any purpose where a percentage needs to be changed globally throughout an application without the need to alter each procedure or field derivation in which it is used. Acceptable parameters: 1 Any numeric value, field, variable, expression or function 2 As Parameter 1 but evaluating between 1 an 5 - 143 - ................................................................................ YEAR FUNCTION YEAR This function returns the year number from a date parameter. Ex. Year(system date) Ex. Carage = jointext((year(system date)-year(registered))," Years old") Acceptable parameters: System date Date field Makedate function Date expression - 144 - ................................................................................ ZEROPAD FUNCTION ZEROPAD The Zeropad function pads an integer or fixed point field to a given number of places left and right of the decimal point. Ex. Zeropad(price,3,2) <Returns "001.50" if price = 1.5> Ex. Zeropad(acno,6,0) <Returns "004532" if acno = 4,532> The Zeropad function can be used as in the second example to return a fixed length numeric string from an integer field. It's main use, however, is in the creation of fields for compound indices where one of the fields to be compounded is a number. For example: A school has all pupils names and ages recorded on a form in fields "name" and "age". To create an index which will list pupils names alphabetically but grouped by age a compound field which is invisible and does not allow user entry is created as a text field derived with:- Jointext(zeropad(age,2,0),name) Because the compound field is text the index file will be sorted alphabetically. If you sort the numbers 8,9 and 12 alphabetically you get 12,8,9 because the one in 12 comes before 8. If however you sort the numbers 08,09 and 12 alphabetically you get the correct numeric order. By using the zeropad function in the above example and indexing the compound field,let's call it "unifield" you could then print out the pupils in age groups with the names in each group arranged alphabetically. .........................format................. .group header Pupils aged {pupils.age field} .list items {pupils.name field} .end .........................procedure code......... declare output fields pupils.name:pupils.age end declare variables agecheck as number end agecheck = 0 for pupils with unifield in order if pupils.age <> agecheck then print group header agecheck = pupils.age print list items next - 145 - ................................................................................