copying a calc field to a table field

[Tom Dang]

Hi all,
I have a field in my table that is calculated.

DBSUM("serial","Order_Number",ORDER_NUMBER,"serial->amount_sold_for")

Basically, it takes all items in a particular order, and gives you a total for that order.

This seems to give me the correct values. in the expression builder window it shows me what the value is for each record. Looks good.

I also have a calculated field on the form for my orders. The calculated field is correct, but my calculated value in the actual table/DB is not correct. it is not updating on my form or the database (ie. it still says zero). Can some one please help me out?

this is what I'm using to calculate the value on the form itself:
total(Serial->Amount_Sold_For,GRP->ORDERS)

thanks,
Tom

[Stan Mathews]

RE: copying a calc field to a table field

My thought is that

DBSUM("serial","Order_Number",ORDER_NUMBER,"serial-"amount_sold_for")

won't change until the record is saved, while

total(Serial-"Amount_Sold_For,GRP-"ORDERS)

may reflect the entry-in-progress.

You don't give much information about your table/set structure, but we see this type of question frequently, and must ask...

Why is it necessary to store the total for an order in a table. You have the ability to "total" the order for diaplay on a form or in a report. Why recalculate the total every time a change is made to an order? The total is likely only important when the order is being viewed.

If you must store the total in your "serial" table, you could have code for one of the form events, probably thecansave, to save the calculation value to the table field.

something like

formname:Tables:serial.fieldname=formname:calculation.value

where formname is your form name
fieldname is where you want to store the total
calculation is the name of the calculated field object on the form

[michaelklein]

RE: copying a calc field to a table field

Tom,

You should be careful with total(). It's been discussed many times in this phorum.
To quote the online Help...
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.

-Michael

[Stan Mathews]

RE: copying a calc field to a table field

Michael,

You are correct and I lobbied for that additon to the documentation.

Total(), as outlined in the docs, only makes sense "in context". The usual context, via the report writer, is a group defined for the report. The other allowed context, which applies to forms a well as reports, for group and sub-group is

The name of the current table
The name of a table in the current set

The reason for the warning is that in field rules, none of the above have any meaning and the function fails.

Many read the function name and want to start using the function with a "filter", which is not mentioned or allowed per the documentation. That's the time to start applying tablesum() or dbsum(), or wondering "why am I trying to store a dynamic total in a static field?"

[michaelklein]

RE: copying a calc field to a table field

Stan,

Thanks for the clarification. Now I understand the warning.

--Michael

[Tom Dang]

RE: copying a calc field to a table field

hence, I'm not trying to store it any longer :)

thanks for the help all.

I am, however, using total() on my form to display the value. "If it ain't broke, don't fix it?" Or, should I do something else to avoid any potential problems?

Thanks again,
Tom