PDA

View Full Version : calcultaed field


ABC123

David
01-23-2005, 04:13 PM
I have a form made from a one to many set.
My records in the child have a field called type, e.g. labor, part, paint, metal, etc...
I need a total (calculated field) of each of the types in the child.
When I run the total() function, I don't know how to filter the expression? Can that be done easily?

Thanks,
David

Cheryl Lemire
01-23-2005, 04:59 PM
David,

I do not think you want the total function, you should look into count instead.

Character Counting Functions

Good luck
Cheryl

David
01-23-2005, 05:22 PM
The child table has the fields:
Item, Description, Type, Quantity, Cost, Amount.

The different types are:
Parts, paint, labor.

I need to total the Amount field by Type.
I need calculated fields In my form for each of the total dollar amounts of parts, paint,and labor separately.

Thanks,
David

Cheryl Lemire
01-23-2005, 05:30 PM
David,

Thank you for the additional information. I misunderstood your intention from the original post. The following links should assist you better:

Calculated Fields

Working with Reports

Proper Use of the GRP->Sub-Group Expression

Cheryl

David
01-23-2005, 06:34 PM
Thanks Cheryl,

I always have trouble with the total function.

This expression gives me my total for the group in the child:

total(Part_List_Cost-"Costamt,GRP-"NEW_ITEM_COST)

Now I want to filter the above expression or group by the field "Type"
Here is what I'm trying to make work:

total(Part_List_Cost-"Costamt,GRP-"NEW_ITEM_COST,GRP-"PART_LIST_COST-""Labor")

I get a "Invalid or incomplete expression"

Cheryl Lemire
01-23-2005, 06:46 PM
Have you set a group within the report for PART_LIST_COST?

David
01-23-2005, 06:58 PM
This is a form.

Cheryl Lemire
01-23-2005, 07:42 PM
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.

That is from the online help files

"a href="http://support.alphasoftware.com/alphafivehelp/Functions/TOTAL().htm""Total()"/a"

Cheryl Lemire
01-23-2005, 07:43 PM
Sorry, I forgot we were talking about a form. You cannot use the total() function on a form. You can use a calculated field but you will need to find a different way to accomplish your task.

Total()

Cheryl Lemire
01-23-2005, 08:18 PM
David,

I think you will need to use the dbsum() function. You would have to create a separate calculated field for each of the types on your form.

DBSUM

Sorry for the confusion.

Cheryl

David
01-23-2005, 08:50 PM
This almost works.
I used the example from the link and this gives me a number, but now I need to group by "New_Item_No"

tablesum("part list cost",
"Part_List_Cost-"Type='Labor'",
"Part_List_Cost-"Costamt")

Cheryl Lemire
01-23-2005, 09:16 PM
OK David, it is time for me to call it a night. You are trying to create a 'report' using a form and I am not following your reasoning for doing this.

Perhaps you can give full details of what you are trying to accomplish and why. Maybe even zip your db and attach it here with detailed instructions to the table(s) form etc so we can assist you better.

Cheryl