# Thread: Calculated field totaling in an embedded browse based on a child table value.....fail

1. ## Calculated field totaling in an embedded browse based on a child table value.....fail

I am trying to construct a system to track and forecast the cash flowing in and out of my business.
These are early steps and I am trying to see if Alpha can help me with this.

Invoices are entered into the TRANSACTIONS table
and a popup loads a character value into the Type field of the ACCOUNT table.
So every invoice will the following characteistics: a date, account number, amount, and a type
where TYPE is a category of supply or service lables as Groceries, Supplies, Utilities etc.)

The idea is that I could create a form that displays at a summary that consists of 6 columns.
column 1 = Date
Column 2= total amount due to "Supplies"
Column 3= total amount due to "Groceries"
Column 4= total amount due to "Utilities"
Column 5= total amount due to "Payroll"
Column 6= total amount due to all TYPES of expenditures

I have a set made up of 3 tables (Main_date_frame)

Table 1= MAIN_DATE
Date_key ( date field )

Table 2= TRANSACTIONS
Date ( 1 to many relationship to Date_key above) D
Account (supplier or service provider) N
Amount (Amount payable) N

Table 3= ACCOUNT
Account ( 1 to 1 with Account above) N
Type (category of supply or service eg Groceries, Supplies, Utilities etc)

The Date_key field in Table 1 MAIN_DATE is essentially a column of sequential dates.

I created a embedded browse within a form and the browse has 6 columns consisting of:

Date_key field and the following calculated fields

Calculated fields:
Supplies_col = total(iif(Account->Type="Supplies",Transactions->Amount,0),grp->transactions,grp->main_date)

Groceries_col = total(iif(Account->Type="Groceries",Transactions->Amount,0),grp->transactions,grp->main_date)

Utilities_col = total(iif(Account->Type="Utilities",Transactions->Amount,0),grp->transactions,grp->main_date)

Payroll_col = total(iif(Account->Type="Payroll",Transactions->Amount,0),grp->transactions,grp->main_date)

TRANSACTIONTOTAL= total(transactions->amount,grp->main_date)

I entered a series of dummy data and all the TRANSACTIONTOTAL amounts calculated correctly but I can't get
supplies_col, Groceries_col, utilities_col or Payroll_col to show anything but a zero value.

2. ## Re: Calculated field totaling in an embedded browse based on a child table value.....

Greg,
Total() was created for use in reports
Using total() in a form may work for some (or even most) calculations, but then using the groupings I think is where the problems start to manifest. Try using tablesum() or dbsum() to determine the totals needed as they are a much better fit for what you are trying to do.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•