Invoicing using Alpha FOUR Sets 05/11/92 ------------------------------- A basic guide to setting up an invoicing system using Sets in Alpha FOUR. Many users purchase Alpha FOUR to use as an invoicing system. A database is a logical choice for this task. Some invoicing systems are simple enough so that a single (flat file) database setup will work just fine. A basic single database invoicing system would include fields such as INVOICE_NO, COMPANY, ADDRESS, LINE_ITEM1, DESC1, PRICE1, LINE_ITEM2, DESC2, PRICE2,etc... This simple example would allow for easy data entry and will be fine for the simplest of invoicing systems, however, as your needs grow you may want to take advantage of Alpha FOUR's powerful relational capability and create an invoicing system using a Set. There are two basic ways to set up an invoicing system in Alpha FOUR using Sets and what follows are examples of these two alternatives. The first section under each option contains a short description with the advantages and disadvantages. The second section goes into more detail about how to create the basic setup for each option. This write-up is only a start. You will no doubt want to customize your invoicing system to meet your own needs. At the end of this write-up are some suggestions for enhancing your invoicing system. Option 1 -------- An invoicing system with multiple line item records for each invoice. Option 1 involves linking a main INVOICE database (one-to-many or "All") to a child LINE_ITEM database. This option will have a separate LINE_ITEM record for each line item. The two databases are linked with the INVOICE_NO field as the linking field. Advantages: Saves disk space; it takes up only as much disk space as you need for each new line item and you are not storing empty LINE_ITEM fields. Allows you to input an unlimited number of line items for each invoice. This is because line items are represented by separate child records and line items (records) are added to the LINE_ITEM database only as they are needed. When Searching, Option 1 also has the advantage because there's only 1 Item number field (1 PRICE field and 1 description field, etc) for all line items. This method makes it much easier to determine the number of each product sold for all invoices. Since the LINE_ITEM database contains only one item per record you can simply print a report from the LINE_ITEM database indexed and subgrouped on DESCRIPTION or ITEM_NO. The primary method for applying the line items against an inventory database is to use the Post utility. This method makes this posting operation much simpler because there is only one field that will contain the line items. Disadvantages: You will not be able to view multiple line items from one invoice on a Form at the same time (you can however print a Report that will look like a traditional invoice form). You will not be able to see the total of all line items on a Form (although you will be able to total the line items for an invoice on a Report). More difficult to apply payments against multiple line item records and store the resulting balance in the database, however you can show the balance on a Report. 1. The first step is to create an INVOICE database with a common field such as INVOICE_NO (this field will be used to link this database to the LINE_ITEM database). You can also include other fields like NAME, ADDRESS, BILL_INFO and SHIP_INFO. 2. Next create a separate database for line items (LINE_ITEM). The only field that must be included in both this database and the parent database is the common field (e.g. INVOICE_NO). This field will be used to link the two databases into a Set. Other fields you will want to include are ITEM_NO, DESCRIPTION, QUANTITY and PRICE. For this application there's no need to have PART1, PART2, etc because each record in this LINE_ITEM database will represent one line item. 3. The next step is to link these databases together in a Set. Create an index in the child database (LINE_ITEM) on the common field (INVOICE_NO). Then link the two databases together into a Set with the INVOICE database as the parent and LINE_ITEM as the child. Use the INVOICE_NO index you just created in the child database as the Linking Index, and set "Link to ?" to ALL. 4. To make data entry easier, create a custom Form under layouts that includes both INVOICE information and LINE_ITEM information. During data entry you will enter the INVOICE information right on that Form and you can "Zoom" to the child (LINE_ITEM) database to enter each line item. Remember that each line item is entered in a separate record in the LINE_ITEM database. 5. You may want to create a Report to print these Invoices. The Report should have one level of subgrouping (see the Reference manual for information on subgrouping reports). On the Report's grouping Parameters screen, the group 1 break field would be the INVOICE_NO field. Place the fields from the INVOICE database into the group 1 header. Place the line item fields from the LINE_ITEM database into the detail section. The group 1 footer is for any totals that you need for each invoice number. For example you may want to create a calculated field using the expression PRICE*QUANTITY and place this in the group 1 footer section as a total to get a subtotal for that invoice. Option 2 -------- An invoicing system with multiple line item fields for each invoice. Option 2 involves linking the main INVOICE database (one-to-one or "First") to the child LINE_ITEM database. The main difference is that the LINE_ITEM database in this second option contains fields representing multiple line items (rather than a separate record for each line item). You will be only linking to one record in the LINE_ITEM database and this one record will contain all the line items for a given invoice. Advantages: This method allows you to view all line items for an invoice on a Form at one time. You can also display the line item total on a view Form. You can apply payments against the invoice total on the view Form. To do this, add multiple payment fields to the INVOICE database and a calculated BALANCE field: (TOTAL-PMT1+PMT2+PMT3). Disadvantages: This method may be more cumbersome to work with when searching (i.e. an item may appear in any one of multiple LINE_ITEM fields). The number of possible line items attached to a given invoice are limited by the number of fields you have pre-defined; if your line items exceed the pre-set number of fields, you will need to place the additional line items on another invoice. This method makes it more difficult to determine the number of each product sold for all invoices because the same product may not appear in the same product field in each record. This second type of invoice system using Sets in Alpha FOUR is the most popular and involves creating a LINE_ITEM database with multiple line items in one record (i.e. fields called ITEM1, ITEM2, ITEM3, PRICE1, PRICE2, PRICE3). Link the two databases together using a linking index on INVOICE_NO as described above. In this scenario, however, there will be only one LINE_ITEM record for each invoice. That one record will contain all line items for that invoice. These are not the only ways to create Invoicing systems with Alpha FOUR, they are only suggestions. Please feel free to modify these to meet your specific needs. Enhancement Suggestions Both Set options require you to zoom to the child database to enter the line items. Because this requires additional keystrokes, this can be seen as a disadvantage over a single database invoicing system. Using lookups, applications, scripts and the post utility can make zooming more efficient as you can see in the following examples. Lookups; copying INVOICE_NO from parent to child: Data entry can be made easier by creating a lookup in the child database that pulls the invoice number from the last entered record in the parent database. First create a index on the INVOICE_NO field in the parent database. Then create a second index in the parent database using the expression: INVERT(RECNO( )). In the child (LINE_ITEM) database, go to the field rules and create a lookup on the INVOICE_NO field. Set Auto Pop-up to "YES". Specify the parent database (INVOICE) as the lookup database. The linking index will be the one you created on the INVOICE_NO field in the parent database. The display index will be the one you created using the expression INVERT(RECNO( )). On the lookup mapping screen, specify the INVOICE_NO as the only choice and filled field. During data entry, when you "Zoom" to the LINE_ITEM database to enter each line item this lookup will automatically pop-up with the last entered invoice number at the top of the lookup window. Selecting the top choice will fill in the INVOICE_NO field so the user doesn't have to remember it. Lookups; linking an INVENTORY database to the LINE_ITEM database: If you add a database containing all the items you sell (INVENTORY), you can then attach this as a lookup to the LINE_ITEM database using a linking index based on the PART_NO field. Choose the DESC and PRICE fields as filled fields. If you are using option 2 above simply link the lookup to PART1, PART2, etc. This will make data entry quicker and more accurate. You should only need to type in the part number and quantity and the program should fill in the description and price. Using a script to make zooming easier & more efficient (Alpha FOUR version 2 only): The scripting feature in Alpha FOUR version 2 allows you to set the zoom trigger (%SYS_TRIG_ZOOM) to automatically store the INVOICE_NO value into a variable (%INVNO) and automatically place this into all new records in the child. First create a script that will set %SYS_TRIG_ZOOM to the name of the script to play when a person zooms (COPYFLD). The contents of this script are as follows: {SET %SYS_TRIG_ZOOM, "COPYFLD"} Then create a script called COPYFLD that copies the value in the INVOICE_NO field in the INVOICE database into the variable %INVNO, then zooms and enters this value into the INVOICE_NO field (in this case this is the first field on the child form): {SET %INVNO, INVOICE_NO}{TRIGKEYS {e{%INVNO}{ENTER}}} For more on scripts and triggers see the Alpha FOUR version 2 reference manual. Using another child database for customers: If your company is like most, you will have more than one invoice for the same customer. To economize you may want to add a CUSTOMER child database to store customer information. Link this to the INVOICE database based on CUST_ID or the customer name. This is beneficial in two ways: You only enter the customer information once (saves time and disk space). Address changes can be done once in the CUSTOMER database. Because the INVOICE database gets it's customer information from the CUSTOMER database, all invoices for that customer will now reflect the new address for that customer. Applying items sold against an "in stock", inventory database: If you have an INVENTORY database containing all items you sell and the current quantities in stock (IN_STOCK), you can apply the items sold each day against this INVENTORY database using the Post utility. Simply create an index in the INVENTORY database on PART_NO, use the Post utility to post the LINE_ITEM database against the INVENTORY database and specifically instruct Alpha FOUR to subtract the QTY field in the LINE_ITEM database from the IN_STOCK field in the INVENTORY database. Alpha FOUR should then look for a matching PART_NO and when it finds it, subtract the quantity sold from the quantity in stock. NOTE: To prevent a line item from being applied to the quantity in stock every time you post you will probably want to add a field (POSTED) to the LINE_ITEM database. Perform a Global Update on this field to place a "*" each time this post is performed. Each time you run the post use a filter: ISBLANK(POSTED). This prevents already posted line items from being applied against the inventory again. To automate this, you can create a script or application that first performs the post and then performs a Global Update to update the POSTED field. The suggestions contained here are only that, suggestions. You will no doubt want to enhance these basic setups to meet your own needs. We hope that you have found the information contained here to be useful.