Alpha Video Training
Results 1 to 2 of 2

Thread: Calculated field totaling in an embedded browse based on a child table

  1. #1
    Real Name
    Join Date
    Jan 2001
    Dartmouth, Nova Scotia, Canada

    Default Calculated field totaling in an embedded browse based on a child table

    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 )

    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. #2
    "Certified" Alphaholic
    Real Name
    Mike Christensen
    Join Date
    Nov 2005
    Michigan U.P.

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

    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.
    It is only when we forget all our learning that we begin to know.
    It's not what you look at that matters, it's what you see.
    Henry David Thoreau

Similar Threads

  1. Filtering lookup in Embedded Browse based on previous field
    By jwolfe11 in forum Alpha Five Version 10 - Desktop Applications
    Replies: 1
    Last Post: 12-02-2009, 10:33 PM
  2. child table filter based on field in parent table
    By turbojack in forum Alpha Five Version 8
    Replies: 11
    Last Post: 03-19-2007, 07:35 PM
  3. Browse (Child) Records Fail to Show?
    By forskare in forum Alpha Five Version 4
    Replies: 3
    Last Post: 11-30-2002, 09:51 AM
  4. Totaling calculated field in embedded browse
    By Greg Fong in forum Alpha Five Version 4
    Replies: 1
    Last Post: 11-11-2002, 03:00 PM
  5. Form's calculated field isn't totaling properly
    By acceptthis in forum Alpha Five Version 4
    Replies: 4
    Last Post: 02-03-2002, 07:01 AM


Posting Permissions

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