Alpha Video Training
Results 1 to 2 of 2

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

  1. #1
    Member
    Real Name
    gfong
    Join Date
    Jan 2001
    Location
    Dartmouth, Nova Scotia, Canada
    Posts
    369

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

    Default 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.
    Mike
    __________________________________________
    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

Bookmarks

Posting Permissions

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