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.
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.
Comment