Calculated field: what happens here?

[mronck]

I have an embedded browse on a form based on a set.
The set excists of a normal table as a parent and a table created by an operation as a child. Link one to one.

In the emb browse you see the column GIP, which is a calculated field. Formula:
IF (Child_table"FieldX"0,Child_table"FieldX,Parent_table"fieldY)
Then I have a column Techn Voorraad which is a field from the parent table.
Last field in the browse (Waarde) is a calculated field with the formula:
Parent_table"Techn Voorraad*Child_table"Average_Child_table"FieldX

All formulas in the browse work fine. All values in the image of the form are correct. But when I try to create a total field on the WAARDE column it does not give me the right result. As you see it should be 1910.00 but it ends up with 300.00

What goes wrong ?
I just took the last column of the browse and instead of VALUE chose TOTAL as usual. But...

Regards,

Marcel

[Edward Larrabee]

RE: Calculated field: what happens here?

Is this a typing mistake or your actual code?

IF (Child_table"FieldX"0, Child_table"FieldX, Parent_table"fieldY)

It should be:

IF (Child_table-"FieldX"0, Child_table-"FieldX, Parent_table-"fieldY)

[mronck]

RE: Calculated field: what happens here?

Sorry Edward, typing mistake. I did not mention the actual names because they are confusing, but you are right I had to add the stripe. Typo.

Marcel

[mronck]

RE: Calculated field: what happens here?

Let me add to this:

The column WAARDE in the browse has the following actual formula:

CALC-"Waarde_calc

The calculated field that produces the wrong value hads the following formula:

total(calc-"Waarde_Calc)

This must be right ?

Greetings,

Marcel

[Tom Cone Jr]

RE: Calculated field: what happens here?

Marcel,

Check the Functions reference on Total().

It's a report writer function and is not supported when used elsewhere.

-- tom

[mronck]

RE: Calculated field: what happens here?

Hi Tom,

That is strange, because the calculated field creates this code by itself if you set it for total instead of value.
If it does not operate in forms/embedded browses, how would you else create a totalling function on a calculated field?

Regards,

Marcel

[Peter.Greulich]

RE: Calculated field: what happens here?

Marcel to echo Tom, from the CHM:
"i"
Note: This function is a report writer function, not intended for table level field rules or other expressions. While the function may perform in some areas outside of the report writer, its use there is not supported.
"/i"
I know what you mean. I have used it a lot on forms, etc. myself. Recently, my Acctg. Mgr. got a new machine, and the total function won't work on a certain form there, while it still works on others. c'est la vie!

PG

[Peter.Greulich]

RE: Calculated field: what happens here?

Marcel to echo Tom, from the CHM:

Note: This function is a report writer function, not intended for table level field rules or other expressions. While the function may perform in some areas outside of the report writer, its use there is not supported.

I know what you mean. I have used it a lot on forms, etc. myself. Recently, my Acctg. Mgr. got a new machine, and the total function won't work on a certain form there, while it still works on others. c'est la vie!

PG

[mronck]

RE: Calculated field: what happens here?

Peter, Tom,

So if I understood correctly, it might work in some cases, might not work in other cases, but the use is not supported anyway. Okay, let's add that to my bagage.

Any idea on how to do it in another way?

Greetings,

Marcel

[Stan Mathews]

RE: Calculated field: what happens here?

Tablesum() and dbsum() would seem to be the choices to obtain totals.

Before you give up on total(), try substituting the background expression that derives the calc field.

Instead of

total(calc-"Waarde_Calc)

try

total(IF (Child_table-"FieldX"0, Child_table-"FieldX, Parent_table-"fieldY))

[mronck]

RE: Calculated field: what happens here?

Guys,

Another thing to it:

It is used in AlphaSports as well. In the form INVOICE you see an expression:
total(INVOICE_ITEMS-"Extension,GRP-"INVOICE_HEADER)
under the embedded browse in a totalling field.

This function looks rather important to simply state that it is not supported. In that case, the whole invoicing example of alphasports would not work, or, the functionality would not be supported.
Isn't that strange?

Greetings,

Marcel

[Peter.Greulich]

RE: Calculated field: what happens here?

I'll venture a theory (i.e speculation): It was supported for forms in a previous life, but something changed in v.5 to make that no longer be true.

[mronck]

RE: Calculated field: what happens here?

Hi Peter,

Nice humor. By the way, your name sounds German or Swiss. Are you from one of those countries ?

If not by total(), how would one calculate the total of a column in an embedded browse ?
The hints in this thread seem to work for a table only.

Regards,

Marcel

[Peter.Greulich]

RE: Calculated field: what happens here?

Here is the way I did it on the form that teh total() stopped working on that I mentioned before. Attached toa button:

DIM tbl AS P
DIM tbl1 AS P
DIM tbl2 AS P
DIM vMsg AS C
DIM vTotal_Exp AS N
DIM vTotal_Fee AS N
DIM vTotal_Hours AS N
'========================FETCH CHILDs
tbl = table.current()
tbl1 = Table.get("Hours")
tbl2 = Table.get("Expenses")

tbl1.fetch_first()
while .not. tbl1.fetch_eof()
vTotal_Hours = vTotal_Hours + tbl1.Total_Hour
vTotal_Fee = vTotal_Fee + tbl1.Total_Fee
tbl1.fetch_next()
end while

tbl2.fetch_first()
while .not. tbl2.fetch_eof()
vTotal_Exp = vTotal_Exp + tbl2.Total_Cost
tbl2.fetch_next()
end while

tbl1.fetch_first()
tbl2.fetch_first()

vFee = str(vTotal_Fee,12,2,"$(")
vExp = str(vTotal_Exp,10,2,"$(")

vMsg = "Total Hours: " + vTotal_Hours + chr(13) + \
"Total Hourly Fee: " + vFee + chr(13) + \
"Total Expenses: " + vExp

ui_msg_box(trim(tbl.sort_by) + " Totals",vMsg,UI_INFORMATION_SYMBOL)

END

I could have written it to a form var, but the msg box was good for what it was being used for.

PG

[mronck]

RE: Calculated field: what happens here?

Thanks Peter for sharing that. I will study it and see how it fits in. Thanks again.

Marcel