How do I post a calculated value to a table from a form? 2

[Lightjetter]

I have a form and a table set up. The form allows me to enter two numbers and the third field adds the two numbers. When I look at the table it contains the two entered numbers, but does not show the calculated amount. I tried entering a formula in ObjectPAL. I'm quite new to this and I need an example.

 

[TonyMcGuire]

and the third field adds the two numbers"

How?

What formula did you try, and where did you put it?

When do you want the calculation to take place? Exiting a field, entering the calc field? Is the field you intend for the total of the other two an actual field, or is it undefined?

Basically, on each of those other two fields' depart() method (likely), I would enter

targetField = fieldToAddOne + fieldToAddTwo

Depending on your system, that may total zero until both fields are filled in.

Tony McGuire

http://www.thedbcommunity.com

"It's not about having enough time. It's about priorities

 

[Lightjetter]

This is th code I tried on the third field.

method arrive(var eventInfo MoveEvent)
var

N1 Number
N2 Number
tc TCursor
endvar
tc.open("Calculator.db")
tc.Edit()
N1 = tc."FirstNum"
N2 = tc."SecNum"
tc."Sum" = N1 + N2
tc.postRecord()
tc.endEdit()
endMethod

 

[TonyMcGuire]

Why are you using a tcursor?

If the field is on the form (and since this code is attached to that field I'm guessing it is), you can just do something like:

self.value=FirstNum+SecNum

FirstNum and SecNum are used here as the name of those other two fields on the form.

By using a tcursor, you are assuming that the current record is the record currently displaying on screen; which may be correct but may not always be correct.

Please let us know if you get it working, and especially if you don't.

Tony McGuire

http://www.thedbcommunity.com

"It's not about having enough time. It's about priorities

 

[Lightjetter]

Thanks for your responses. I couldn't get anything to happen on the table if I just used the kind of commands that you suggested. I'll try it again though just in case I missed something the first time. Getting the calculated results to be put into the table is my cheif concern because those have to appear on the report. If they are not in the table, they come up as zeros on the report.

 

[TonyMcGuire]

Can you not just use a calculated field directly on the report?

Particularly since you are putting the calculation on the arrive of that third field; if the user never moves to that field then the field won't have a value.

Tony McGuire

http://www.thedbcommunity.com

"It's not about having enough time. It's about priorities

 

[SonOfTed]

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.