- Supported features include:
- 1. Client selection (list box) (bogus contact-list courtesy of data generators: link1 link2)
2. Client Address (auto fill-in upon client selection)
3. Separate Shipping Address (optional type-in)
4. Item selection by Name or SKU/Serial Number (list box)
5. Quantity selection (spin button; default 1)
6. Price (auto fill-in upon item selection)
7. Item Discounts: adjustable in monetary units and/or percent (type-in)
8. Item SubTotals (SQL calculated field)
9. Invoice SubTotal (SQL calculated field)
10. Invoice Discount as preset percentages (list box selection; user-defined presets; default none)
11. Invoice Tax options and calculations (list box selection; user-defined presets; default tax type)
12. Item Tax Status as a stored boolean used to differentiate taxable 'products' from tax-free 'services or incidentals'
13. Shipping and handling including: Shipper selection, fee, and/or weight-rate, packaging fee per item (SQL calculated field)
14. Invoice Total (SQL calculated field)
15. Various Field Constraints and Defaults (leveraging back-end database features)
16. One Form is designed to invoice 'unique' items differentiated by serial number with 'sold' item filtering
As with most macro-free designs involving List Boxes, these Forms require several Push Buttons -- which could be eliminated with a simple 'Form refresh' macro. But I wanted to skip macros in this case in order to highlight the built-in features of Base Forms, particularly their ability to compliment a 'normalized' database structure through SubForms and standard SQL. There's even an attempt to make these macro-free designs “pretty” and “intuitive” for the end-user. In any case, the concepts and techniques demonstrated here should serve you well throughout your own Base projects. - 1. Client selection (list box) (bogus contact-list courtesy of data generators: link1 link2)
- In my early days with Base I resorted to macros rather quickly because I couldn't wrap my mind around the combined utility of the available tools. After all, Base applications combine the [often hidden] features of an Office suite with those of a full-featured SQL engine -- particularly when paired with HSQLDB 2.x. And coming from the MS Office automation environment, I was spring-loaded to workaround any perceived limitations or bugs using macros due to the ease of VBA in MS Office. Base, on the other hand, is a relatively primitive front-end with little ongoing development, and bugs that persist for years. The macro development environment is relatively difficult to learn due to a patchwork API and a very limited IDE by modern standards. Various add-ons to the IDE offer some welcome relief and critical features, but we still don't have a modern, context-sensitive, development environment for Base/*Office. Other developments have smoothed the transition from VBA to *Office Basic. But it remains prudent to avoid extensive macro development with Base...at least initially. After all, we do see a tendency to use macros to overcome a poor database design/structure, which rarely turns-out well. In general, macros are a last resort in Base, and best used to refine the final workflow.
Fortunately, Base redeems itself with powerful Forms. The more I work with these Forms, the more I'm intrigued by the power of SubForms leveraging SQL. And the more I'm convinced that a proper database structure is the key to Base Forms, as well as, ongoing development and support. Several concepts are necessary to master SubForms, including a working knowledge of SQL code, relational database concepts (i.e. normalization), Form-Controls, the Base query-parser, along with Base limitations/bugs. This all takes some study and experience, but until you've mastered the potential of SubForms in Base, you really can't determine when-or-where macros would be appropriate. There's no replacement for hands-on experience, but macro-free examples such as these Forms can really speed up the learning process.
- I'll outline the various concepts and techniques behind this example in some detail at a later date. But I do want to mention that I rarely utilize the 'stored' query feature of Base for Form construction purposes; I typically rely on the ability to store the SQL 'content/command' within the Form document itself, making each Form suitable for export as a 'standalone' Writer Form (.odt) or spreadsheet tab in Calc (.ods). This technique also tends to hide the SQL from the end-user, who might otherwise modify a Query (stored centrally) without understanding the impact or role that query plays in related/dependent Forms. However, due to the redundancy involved with a typical invoice, where a SubTotal might be reused in several SubForms as necessary to calculate Discounts, Tax, Shipping, etc., I found it easier and less error-prone to rely on a few Queries stored centrally within the Base document (.odb), as opposed to embedded within the Form. So you'll notice a few Queries shared by multiple SubForms, which alleviates the need to create redundant sub-queries (nested SQL) by hand in many cases.
- And here's a related demo of passing (cloning) multiple fields between tables using a macro-free form. This is a common requirement for invoicing because we often need a historical snapshot of each transaction including price, vendor, etc. Admittedly, this creates redundancies. A more elaborate data-structure and query-system based on time-stamps can be used to eliminate the data-redundancies otherwise created by passing (cloning) fields in this manner. But I think a little redundancy (denormalization) is just fine for historical transactions and invoicing purposes. In fact, selected denormalization such as this is preferred among large companies with millions of daily transactions for performance reasons (because the 'joins' necessary to re-correlate 'normalized' data would kill database performance under these extreme conditions). In our case, de-normalization serves to simplify the overall design including the database structure and queries.
-------------------------------
Please PM me with any corrections or suggestions. Questions should be posted as 'new topics' in the Base forum.
-------------------------------