Hi, my name is Todd.
1. For the most part I avoid adding ObjectPal code unless absolutely necessary. I have found (as a general rule) and through lots of practice that I can find my way around it.
a. Granted I usually am not creating super-detailed systems -- my users are very accomodating -- but using ObjectPal commits you to much more system changes and ongoing maintenance.
b. My philosophy is "Don't ObjectPal" unless you have to, and below explains why.
2. In your example, you are having the user enter a couple of fields and then displaying the calculated result on a form. I have never had to store the calculated result in my primary tables. Here's why:
a. Everybody always wants alphabetical order on my reports. So I know that I always need a 3-step process: (1) Update primary tables, (2) Use a query or SQL object to create a temporary reporting table, (3) Run the final report, which references the reporting table. Note that you can still use foreign key fields in the reporting table to look up information from the primary tables.
b. To complete the 2nd step I start with a basic query, linking the tables I need by key and selecting the fields that I want. I also specify an alias (or drive and folder) for the result table. You can due this through the "Query Properties" button. The result table I usually call something like: "Reports.DB".
c. Once the query is created, my network administrator suggested that I click on "View", then "Show SQL". This converts the query on screen to an SQL window WHICH YOU CAN MODIFY AND SAVE. It will have an .SQL extension instead of .QBE, but you simply click on the "SQL" category in Project Viewer instead of "Queries".
3. Below are some examples of how I've modified the SQL (changes are in CAPS):
EXAMPLE 1: Create a field comprised of Last Name + First Name, etc. that can be used as a "group" field in Paradox reports for sorting in alphabetical order.
Select D.Ssn, D."LAST_NAME"+", "+D."FIRST_NAME"+" "+D.SSN AS NAMESORT, D.Dept, D1."Date Hired", D1.Organization, D2.Tng_Code
From "Empfile.DB" D, "Employee_History.DB" D1, "Modfile.DB" D2, "Modname.DB" D3
Where
(D.Status <> 'Inactive')
. .
. .
. .
Order By D.Ssn, D2.Tng_Code
EXAMPLE 2: Create a temporary reporting table that includes new calculated fields.
Select D."Date", D."Order No", D.Pieces, D."Work Time" as WorkTime, D2."Cost Center", (D.PIECES * D."PIECE RATE") + .01 AS AMOUNT1, (D."PREVAILING WAGE" * D."WORK TIME") + .01 AS AMOUNT2, "W" as Type
From "Payroll.DB" D, "Names.DB" D1, "WorkOrders.DB" D2
Where
(D."Date" >= '8/01/2004')
And (D."Date" <= '8/31/2004 ')
. .
. .
. .
Order By D."Date"
The calculation for AMOUNT1 is based on number of pieces processed (someone is paid by how many pieces they complete). The calculation for AMOUNT2 is based on an hourly wage (WORK TIME * PREVAILING WAGE). This helps greatly because now I don’t need the calculation formula in EVERY report.
Step 1: Data enter payroll info. (I have a field with the full formula on the data entry input form to show me the results). Iif(Pieces >0, calculate AMOUNT1, else calculate AMOUNT2).
Step 2: I run the SQL. This creates a REPORT.DB table that includes columns for AMOUNT1 and AMOUNT2. My reporting step (below) is now a lot simpler.
Step 3: If “Pieces” > 0, the reports use AMOUNT1. Otherwise, they use AMOUNT2. It’s that simple.
Once I'm done with my reports, I simply delete Reports.DB. Thus I get alphabetical order by name on my reports, simpler calculations on the report objects, and I don't have to store the extra calculated fields on my primary tables. I can always recreate a fresh Reports.DB table if any changes are made.
