calcultated fields

[David]

I have asked this before and still trying to come up with a way to do this.
I assemble items. I am trying to create a form so I can evaluate the costs of the New Item/Assembled Item.
I have a set, my parent table is "New_Item"
the child tables, one to many, are: "Parts", "Labor", "Paint", "PaintLabor", "ShopCharge"
The child tables are embedded browses in the form.
I have calculated fields using the total() function to total each of the tabels filtered by New_Item.
I would like these totals to be in the parent table in a field, not just a calculated field.
I don't think this is possible.
If not, how can I get all of the calculated fields in my form into my report without re-entering all of them again one by one?
Is there a way to do this?

Thanks,
David

[Scott Emerick]

RE: calcultated fields

David,

In some rare cases TOTAL() works on a form, but it is really meant for a report.

You should look into the function TABLESUM()

This will allow you to get the total of the records from the children and then add them together.

You could place a field in your parent table and then make it a calculated field in the field rules. This value will be stored since it is table based and not form based.
"Parts", "Labor", "Paint", "PaintLabor", "ShopCharge"

You could then use an expression like:
TABLESUM("parts","new_item-"item_no = item_no","Price")+TABLESUM("Labor","new_item-"item_no = item_no","Price")+TABLESUM("Paint","new_item-"item_no = item_no","Price")+
+TABLESUM("PaintLabor","new_item-"item_no = item_no","Price")+TABLESUM("ShopCharge","new_item-"item_no = item_no","Price")

The above is just a rough example since I don't know what your table or field names are, but it assumes they all have the fields Item_no and Price.

Hope this gets you in the right direction.

Scott

[Tom Cone Jr]

RE: calcultated fields

A clumsy way to do this would be to put a button on your form.

Call it something like:

"Store current totals"

This button would run a script that put the primary table in change mode and updated the fields using the values in your form's calc fields. It would then commit the changes to disk.

There's probably a better way to do this silently and automatically, but the button approach will work.

Once the initial values are seeded in the primary table, why not put post field rules in the transaction tables and "add" new values to the primary table seeds, as new records are entered in the child tables?

-- tom

[David]

RE: calcultated fields

Hey Scott and Tom, thanks!

Scott,
When I use tablesum() in the expression builer, and select "Parts" and then when I try to select filter (F2), it does not offer any field in the 1 to many child tables.
Parent table is "New_Item"
Child table is, "Parts", one to many link.
Child Key is, "New_Item_No"
The field to sum is "costamt"

this is what I have tried and I get an invalid expression, or an error message comes up and says, "expression does not evaluate to a numeric value"

TABLESUM("parts","new_item-""new_item_no" =new_ item_no","costamt")

I must have something wrong. Am I missing something obvious?

[Scott Emerick]

RE: calcultated fields

David,

If you edit the field rules of the set opposed to a single table(the parent in this case) you should see all the fields for all the tables in the set when using the expression builder.

You have too many quotes in your filter.

Change this:
TABLESUM("parts","new_item-""new_item_no" = new_ item_no","costamt")

To this and see if it works:

TABLESUM("parts","new_item-"new_item_no = new_ item_no","costamt")

Scott

[David]

RE: calcultated fields

Tom,
I have a field called New_Price. Now I copy the calculated field and paste in the New_Price field. That is clumsy. If I can't get this in the field rules, a button will be my next try.

[David]

RE: calcultated fields

I still get the message "expression does not evaluate to a numeric value"
In the Title Bar of the Expression Builder, has the word in parentheses (numeric)
I am using the "set" field rules.

[Scott Emerick]

RE: calcultated fields

David,

Sorry if I wasted your time, I was shooting from the hip when I answered your post. After some testing, field rules won't do what you need. Tom's approach will work better using a button script on the form.

You can still use tablesum() and probably hide the button and do a button.push() in the 'CanSave' event of the form. You will have to change your syntax in the filter expression to.

vitemno = new_item_no.value
TABLESUM("parts","new_item_no = '"+vitemno+"'","costamt")

The above assumes that new_item_no is a character field.

Scott

[David]

RE: calcultated fields

Thanks, I do appreciate both of you guys help.

David

[Raymond Lyons]

RE: calcultated fields

David,

Scott's approach is fine, if that's the way you want to go.

However, I would take issue with his statement to the effect that "In some rare cases TOTAL() works on a form, but it is really meant for a report." I have used it with calc fields on forms totaling embedded browse fields with no problems at all. Then using Tom's table change suggestion you can put the totals wherever you want. You can use a button or put the script on some event like save.

The key is getting the total() parameters correct but if I remember correctly you can let A5 do that for you when you use drag and drop to create the total calc field (advanced or something like that). Anyway, my point is that it always works for me, not just rarely.

Ray

[Stan Mathews]

RE: calcultated fields

"However, I would take issue with his statement to the effect that "In some rare cases TOTAL() works on a form, but it is really meant for a report."" - then you disagree with the documentation.




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.

[Raymond Lyons]

RE: calcultated fields

Stan,

Good point. Perhaps what this means is that in spite of my experience it would not be a good idea to use it on forms at all because in the next patch (at least in v6) it may quit working and Alpha would not be inclned to "fix" it.

However, to clarify, I have not used it in the context of a field rule, only on "other expressions" on forms ("not intended for table level field rules or other expressions.").

On the other hand, since total() in a calc to total one-to-many child fields on a form with an embedded browse does seem to work and in some sense seems to be a natural way to show such totals on a form, why the heck shouldn't Alpha support this? Since I can't get it to fail, at least in the way I have used it, I do not see what the issue is--which of course does not mean there isn't one!

Ray

[Stan Mathews]

RE: calcultated fields

I believe the issue is that the proper use of the function(s)* is a difficult concept to explain. In a report you define the groups that can be used as you design the report. In the context of a form, as you have demonstrated, the groups consist of the table levels of a set. In a field rule and certain other situations, neither of these pertain.

Trying to clarify this usage is problematic and there are other functions available to accomplish the same result.

* - run_total(), average(), count(), etc that carry the same warning -

[David]

RE: calcultated fields

In Alpha sports they use the total() function in the invoice example to get the total of an invoice. (on the form)
What should I use to get totals of each of my tables in my set?
This is what I use for tablesum()
tablesum("parts",Parts-"New_Item_No,Parts-"Costamt)
This gets an invalid expression.
This was created using the genie.

"Parts-"New_Item_No" is the linking key in the child table and is a character field.

"Parts-"Costamt" is numeric field, but in the expression builer it asks for C field

In the online help it has this example
TABLESUM( Lookup_Table as C, Filter as C, Lookup_Expression as C )
What am I doing wrong in the tablesum() ?

[David]

RE: calcultated fields

This is in a calculated field on the form.