To order Alpha Four or Alpha Five, click above to go directly to Alpha, or call 800.451.1018 or 781.229.4500
|
|
|
|
|
|
Sunday, September 1, 2002
|
|
VOLUME 1
ISSUE 7
|
|
|
|
|  |
|
|
Invoicing with a Twist
by Robert Tishkevich
Background and the Challenge
I have a client, whom we will refer to as FBB Furniture, who uses a standard A5 invoicing application with one exception. In terms of this article, I think they key point is the fact that the company bills for shipping [Freight Invoices] separately.
Here is an important lesson for all developers, you must talk with and listen to your clients. We initially had a communication problem because of my failure to listen. Like an idiot, I simply assumed they would do what most companies in fact do, add shipping charges to their product invoice, so I built the application accordingly.
Fortunately, I eventually realized how the company worked so I had to make some serious modifications to the application. Product Orders/Invoices would now be totally separate from Shipping Invoices, which meant I had to modify a series of calculations on the Main Order form and the Product Shipping Invoice [report]. That was a piece of cake because all I had to do was remove the shipping charges from every calculation on both the form and the Invoice report. Although this was relatively easy, I could have avoided it in the first place if I was listening to what they client was telling me, as opposed to hearing what I wanted to hear.
Next, I had to build a data entry form and a Freight Invoice report for the customer’s shipping needs. Here are the requirements for this model.
- Products may be shipped all at once or may go out via multiple shipments.
- There is no set pattern for shipping dates such as 15 days after an order is received, the product is shipped after it is built and passes quality assurance tests.
- In addition, there is no pre-built schedule of shipping fees so I could not create a lookup table for shipping charges. Therefore, FBB personnel have to manually enter both the date the product was shipped and the actual amount of the shipping invoice.
In the end, FBB has two requirements for their shipping documentation.
- They want a Freight Invoice History report that shows all freight shipping invoices, each payment made, and the overall balance due [if any] for each order issued. This is information strictly for their internal records.
- They also want the ability to print a Freight Invoice showing only the current shipping charges for the last and most current Freight Invoice issued to the customer.
How could I build a model that incorporated the above features? This would be a two-part project. The first aspect required the creation of a new set to track all freight invoices, which was pretty standard fare. The 2nd part of the project was the more challenging aspect and the reason I wrote this article. FBB personnel want to print a freight invoice that reflects only the current charges for that specific invoice, as opposed to an overall balance due [if any] which includes all previous freight invoices shipped per that order number.
If it weren’t for that requirement, I would only have to create the aforementioned Freight Invoice History report. Solving the 2nd problem was harder than I thought it would be, only because Alpha provides so many options for accomplishing the same goal.
Solution Number One
In order to produce a shipping data entry form and freight invoice history report, I needed to construct a brand new set. Three of the tables, including the key table for the set, were already built for setInvoice [which tracks product orders]. The name of the parent table is tblInvoice, which contains most of the important information for the entire application. The key field in this critical table is Order_No, which I used as the linking field in the two One to Many child relationships I created, as described below.
The two other tables previously built for setInvoice, were tblCompanies and tblShipTo. The first one is self-explanatory, obviously we must know the name of the company ordering products. The 2nd table [tblShipTo] was necessary because one Company that orders products may have them shipped to multiple locations around the country. Both of these tables were designed via One to One relationships based upon either the Comp_ID and/or the Ship_ID fields.
I needed two more tables to track freight charges and payments so I created tblShipCharge [for charges / debits] and tblShipPaid [payments / credits]. The key field in both tables is the aforementioned Order_No field which links the parent tblInvoice to both child tables, via a One to Many relationship. Here is a view of the Set Design:
1. Set Design for tblShipCharge
Why not place all charge/payment information in the same table? That’s a good question and many developers use that technique. However, there are a couple of reasons why I prefer using separate tables for charges [debits] and payments [credits]. One of the reasons is a coding issue. If all debits/credits are stored in the same table, the data entry person must enter the appropriate code and that choice is always subject to human error. However, if you have separate tables for debits/credits, that eliminates one potential serious data entry error possibility.
Furthermore, in terms of auditing previous transactions, I just happen to like the idea of separating debits and credits into their own tables. I think it makes for a cleaner / neater overall design concept.
In the past, using a set with multiple One to Many relationships may have caused a potential slow down in terms of performance. However, with the advent of today’s high-speed processors, I don’t think is an issue, in fact, multiple One to Many sets work so well, that I use them in virtually all of my financial applications.
Please note, if your client is using an old 100-mz processor with only 64 mb of ram on board, using multiple One to Many relationships could create a speed related problem.
The 2nd aspect of utilizing multiple One to Many relationships is the need to become very familiar and proficient with the use of Alpha’s nifty Sub-Reports features. When I first started using them a few years ago, it took me a little while to figure out how they fit into the overall concept of one master report, but once I figured out how they worked, it was pretty easy. I personally love the Sub-Report paradigm because they add a great deal of versatility to my applications. Of course you don’t have to use Sub-Reports, but then your reports will almost certainly have many blank lines [because of the set design incorporating multiple One-To-Many relationships] that are very disconcerting to anyone who tries to read them.
How does one use sub-reports? Although this sounds like a possible topic for another Alpha Five article, let me briefly describe how they work. In a traditional One to Many Invoicing application report, the Invoice and customer information will be located in the Group Header while the charges/payments are shown in the lone child detail table. This is essentially what your credit card report looks like when it arrives in your mailbox every month.
But in this application, charges are stored separately in tblShipCharge, while payments are entered into tblShipPaid. Try to visualize a vertical line splitting the detail section of the report into two separate sections. The charges [debits] are listed in the Sub-Report on left half of the detail section, while the payments [credits] are listed in the Sub-Report located on the right half of the detail section. All totals can be summarized in the Group Footer. Of course this is all seamless to the reader because it appears to be one simple report.
Here is a typical Freight Invoice History report from our application that shows all invoice and payments linked to this specific Order_No.
2. Freight Invoice History Report for internal use only.
How and where did this information get into Alpha Five? OK, here is a look at the Freight Invoice Data Entry Form.
3. Data Entry Form for entering Freight Invoice Data
That was pretty easy, wasn’t it? At this point in the development process, FBB now has the ability to enter one or more freight invoices for each Order_No, track the history of those invoices, and provide a total balance due [if any] for all shipping invoices issued.
Solution Number Two:
In business, you must adjust to the client and how they work. As mentioned above, FBB does not want to send a Freight Invoice History to their customers reflecting all previous shipping charges, payments, and an overall balance due [if any]. They want to ship a separate invoice each time a finished product is shipped, billing only for the amount due on that individual freight invoice. As stated earlier in the article, this was a real challenge, only because there are several alternatives for doing such with Alpha Five. Here is the methodology I chose for this application.
I previously wrote a script for the tlbShipCharge OnSave event that fetches through all of the records in that child table, calculates a total amount due for all shipping charges issued so far, and posts that amount to the parent table each time a record is saved. Incidentally, I have a similar script in the OnSave event for tblShipPaid that calculates a total amount paid up to this date, which is also posted to the parent table.
In light of the above, I realized I could simply add several lines to my current script which will fetch the information from the current invoice and post it to the parent for this Order_No. But first I created four new fields in the parent, tblInvoice, that serve only one function, they store the information from the latest [LAST] freight invoice issued. Here are the four fields I created:
- FreightInv_No
- FreightInv_Date
- Pro_Number
- Ship_Amt
Since those four fields were previously created in the child, tblShipCharge, all I had to do was copy them to the parent table and then I was ready to modify the script I previously created for the Freight Invoice History report. Here is the new portion of the script I added to the already existing OnSave event script, which calculates the total shipping amount due.
You can read the script comments, which should explain everything this new portion of the script does. In essence, it accomplishes the following tasks:
- Orders tblShlpCharge by Date
- Fetches the last record in the table via the fetch_last() command
- Copies the value of Four Fields from the child, tblShipCharge [T2] to the parent, tblInvoice [T1].
- Those four fields are: FreightInv_No / FreightInv_Date / Pro_Number / and Ship_Amt
The value from those four fields reflect all of the information we need for the LAST or Current Freight Invoice issued.
SCRIPT:
'--------------------------------------------------------------
‘tblShipCharge OnSave event
'Purpose: After completing a new Freight Invoice ‘in the child tblShipcharge,
‘'replace the values from those four records in the parent, tblInvoice.
'Although the shipping related fields are stored in the parent tblInvoice,
‘shipping 'charges will be tracked separately from all other order/invoice
‘ related 'items. The shipping charges will be shown only on the Freight Invoice Rpts.
'--------------------------------------------------------------
'- Parent table is named "tblInvoice" [T1]
'- Child table is named "tblShipcharge" [T2]
'- Both tables are linked by the "Order_No" field.
'- Must be in the Set or this script will not work
'----------------------------------------------------------------
‘set the index in tblShipCharge to FrghtDate [Date Order]
'Fetch the LAST Record in tblShipCharge
'Obtain four Freight Invoice field values from the child table, tblShipCharge
' and then replace the corresponding values in those same fields located
‘ in the parent called tblInvoice.
'-----------------------------------------------------------------
Dim T1 as P
Dim T2 as P
T1 = table.current(1) ‘ tblInvoice
T2 = table.current(2) ‘ tblShipCharge
T2.index_primary_put(“FrghtDate”) ‘sort by shipping Date
T2.fetch_last()
T1.change_begin()
T1.FreightInv_No = T2.FreightInv_No
T1.FreightInv_Date = T2.FreightInv_Date
T1.PRO_Number = T2.PRO_Number
T1.Ship_Amt = T2.Ship_Amt
T1.Change_End(.T.)
ParentForm.resynch()
END
Once the script is executed, an individual Freight Invoice can be sent to the customer that reflects the shipping charges only for that last or current invoice. It looks like this.
4. Typical Freight Invoice sent to a customer
[PRINTER FRIENDLY VERSION]
|
|
| |