Included with this file are four modules for use with PC-File III+: NAME, INVOICE, AR, SALEITEM. Each module can be easily modified to meet your individual needs. However, the name and address fields in the INVOICE module get data from the NAME module. Any changes to the modules must incorporate this relationship. See Design Notes for additional information if you want to make changes to these files. You can also create additional relationships by using the calculate function for a field. The structure of the calculation is: (@f1,DB,f2,f3); f1 is the field name from the current database which has an equal value as a field from the related database DB is the name of the related database f2 is the field name from DB which has the same value as f1 f3 is the field name from which data will be transferred to the current database. For example, in the three modules presented INVOICE depends on NAME for the name and address for each record. In the INVOICE field Sold_To we set up the calculation (@Cust_No,NAME,ACCOUNT_NO,NAME): using the Cust_No go to the NAME database and find the record whose ACCOUNT_NO equals Cust_No and get NAME data. This procedure is repeated for the address, city, state and zip, as well as, the Ship_To section. The NAME module is used for tracking your mailing list. The INVOICE module is used for shipping and invoicing sales. The AR module tracks your accounts receivable. The SALEITEM module is used for tracking item sales, as well as, periodic total sales by sort type(monthly, quarterly,etc.) NAME ---- We use SORTLINE for sorting all accounts alphabetically. Since we have both corporate and individual customers, sorting from the NAME field was impractical. ADD_DATE is auto when a new account is added. MOD_DATE is auto an any changes to an account. ACT_DATE can be used for future action, such as follow-up phone call. Use Ctrl-L to print individual labels for an account presently on the screen. TAX_TYPE: exempt, resale, taxable, out of state. You can use whatever codes or classifications you need. ACCT_TYPE: a further classification for grouping accounts. Note: be sure to be consistent with the classification codes you use or future reporting may not be meaningful. ACCOUNT_NO: we use 5 digits to identify each account. The remaining space is used to allow multiple shipping addresses for each account. You will also find in the INVOICE module space for a Ship_To account number. We use the following convention for numbering accounts. The account is assigned a number such as 71001. Additional addresses are 71001.001, 71001.002, etc. Each of thes accounts should be assigned the same SORTLINE. Two basic sort sequences are used: 1. Sort by SORTLINE, ascending, and ACCOUNT_NO,ascending. Useful for finding an account when you only have a name. With PC-FILEs search capability this sort sequence is not necessary. It will speed-up searches. 2. Sort by ACCOUNT_NO, ascending. Required search for processing invoices. If you do not perform this sort, you can expect to spend alot of time waiting for PC-FILE to transfer the name,address,etc. to INVOICE. INVOICE ------- Invoice_No is manually entered for each record. If the last invoice issued was previously on the screen(add or modify) use Ctrl-F to duplicate the number and change the last digit. Cust_No must be a number assigned in NAME database in order to get the name & address to this database. Ship_No same as Cust_No. Allows use of different number for multiply shipping addresses. Type should be consistent with TAX_TYPE from NAME. Used to sort records for reports. Tax field is converted to a decimal. For example, to enter 5% enter 5. Disc is discount rate. Like Tax, the field is converted to a decimal. Sp1 and Sp2 are special description fields used on the invoice for messages you may want to include. Each item sold is made up of 5 parts. Item is item #.(Future change is to relate to Inventory File). Des is item description. Order is amount ordered. Ship is amount shipped. Price is dollar amount. Space limitations(70 fields in PC-File III+) only allow for 7 items per invoice. T1,T2,T3,T4,T5,T6,T7 are respective totals for each item sold. DT is total discount. Taxes is total sales tax. -2- REPORTS-INVOICE --------------- INVOICE You will need to modify this report to add your name, address, telephone no. and Fed Tax ID.(It's easier to use EDLIN or other text editor than PC-FILE. If you haven't done it before, be sure to use a back-up copy The program calculates total amount due. You may want to make cosmetic changes to the text used on the INVOICE. PACKLIST This report must also be modified as was INVOICE. Report will show by item, amount ordered, shipped and backordered. INVDISC Same as INVOICE but prints discount amounts. DROPSHIP Same as PACKLIST but Sold_To is printed in place of your company name & address. Ship_To is printed in both positions on the report(sold to and ship to). BACK01-07 Lists all items backordered for each invoice record in the file. We start a new datafile each month creating a backup and permanent file copy. Program will run faster. You can use the smart keys for producing the above reports. Alt-8 prints BACK01 thru BACK04. Alt-9 prints BACK05 thru BACK07. Alt-0 sets up for printing 3 copies of INVOICE,PACKLIST or DROPSHIP. Cursor is placed in the Invoice_No field. Enter the invoice # for that report. Alt-- sets up for printing 2 copies as above. Two additional reports are available: ARTRANS.REP, SALESTAX.REP. ARTRANS.REP:This report creates the transactions for accounts receivable-AR. At the report menu change line per page to 1 and records to S, specific. Enter a valid name at the prompt(we use ARTRANS.DAT). For search selection C, complex. Enter a calculation which will give only those invoices which have not been processed before(Invoice_No>9559). If your invoices include prepaid orders, you need to confirm each invoice which should be transferred to AR. SALESTAX.REP:Use this report for totaling the individual items in INVOICE; shipping charges, sales tax, discounts, etc. Sub-totals and Grand-totals are also included. Besides giving totals for any period you specify, this report is also useful in totaling sales by account, sales by type of account and sales by terms.Sort by the appropriate field for these totals. -3- AR -- To load transactions from INVOICE select Utilities, Import, etc. Then specify User input delimiter, entered from keyboard, "|", all transactions. The first transaction appears blank, don't import. If the second transaction looks good, enter "X". ARDAILY Lists and totals all new receivables or payments for the parameters entered. If you import daily, request specific records and complex search. Enter calculation as INVOICE_DATE="08/18/87". For payments use DATE_PAID="08/18/87". Useful for balancing checks deposited. ARTOTAL Lists all invoices in the database. Calculates the balance due for each. If you sort the records by ACCOUNT_NO first and specify Sub-Totals for the report it will give total due by account. If you don't need detail reports each time, at the report menu select "N" detail lines. Periodically, delete paid invoices to reduce size of file and search times. Total receivables can be balanced period to period by adding or subtracting the ARDAILY totals to the previous ARTOTAL printed. SALEITEM -------- A subset of INVOICE transactions. First sort by ITEM to get totals of each item sold. Or sort by Cust_No and then ITEM to get listing of by customer. DESIGN NOTES ------------ Care should be taken when modifying the reports included with these modules; especially, SALESTAX.REP, ARTOTAL.REP and ARDAILY.REP. If you must change the reports use EDLIN or other text editor which produces ASCII files. The above three reports use a sub-total and total technique available in PC-FILE but not supported in the report design program. When PC-FILE creates a report which includes sub-totals or totals, it places the following character " ` " after the definition of the field in that report file. Following the ` is a lower case alpha character. Each total item is identified in order. For example, Item1 would be `a and Item2 `b. In the sub-total and total section of the report, the totals for these items would be identified as [`a] and [`b]. PC-FILE will not allow totals for line items which are calculations (Item1 + Item2). By using a text editor you can add `c to the report definition of Total(Item1 + Item2). Then create a space for the sub-total and/or total and add ['c]. -4- FUTURE DEVELOPMENTS ------------------- We have tried to produce a statement for each account in our AR. However, PC-FILE does not give us enough flexibilty without creating yet another database. It appears that we will need to write a stand-alone for this purpose. An INVENTORY module is the logical addition to these modules. We are now in the design process. Again, PC-FILE itself does present some problems. Plus, there are several areas to be addressed in the module: inventory costs, inventory pricing, inventory transactions, inventory on hand, automatic re-ordering, etc. Additional modules which may be added: purchase orders and payables. I trust you will find the modules included useful. If you have any questions, comments or suggestions, please leave message on PC-EXEC. Or contact me at: GK Enterprises 8634 West National Avenue West Allis WI 53227 (414)546-0111 (please call after 4:00pm) Glenn Klein -5-