You can reduce the number of calculation fields by using this calculation field, cMonth, set to return date:
DateField - Day ( DateField ) + 1
it Computes the date of the first day of the month for any given date and so can replace using separate year and month calculation fields.
Then set up your Balance table with one record for each month of the year instead of one record for each year. You can then use these relationships:
Balance::FirstOfMonthDate = Expenses::cMonth
Balance::FirstOfMonthDate = Invoices::cMonth
You can manually or with a script, create a record in Balance for each month fo the year and enter the date for the first of the month in the FirstOfMonthDate field. This same field can be formatted to display only the month and year or just the month name for your report and a list view can list your months and the income and expenses, as well as a calculation field that can use them to compute a net profit/loss result for the month and a summary field can give you the total for the year or whatever set of these records you choose to pull up in a found set.
And if i use the relationship:
invoices::year = balance::year
How can I find the specific month on the date using a script? Something like "Find related record", and Sum ( "Related Records Only" ) ??
Is that possible??
Using your relationship,
Sum ( Invoices::Amount )
if defined in the balance table, will give you the total of amount for all invoice records of the same year.
You can also refer to a summary field defined in invoices that computes the total of Amount and get the same exact total.
Ok, I pretty much understand.
But what about having a relationship like: invoices::year (defined as year (DateField)) = balance::year
And then having for every month a script like:
SetField ( balance::month; 1) --when balance::month could be used as a global field for every different month--
BUT!!! using something like "Using only related records by invoices::year = balance year ; and get only related records on month
I know that doesn`t make any sense, but is the idea I want to get.
Is it posible???
Why use that instead of the approach I have recommended?
Sorry, I didn´t get it before, but now is nicely working!
To add a "plus" on the balance...
How can I "auto add" a new record every new month?
If ( Month (Get (CurrentDate) ) > Month ( Last Record Date );
Last Record Date + 1 Month;
Is it possible??
Define a Summary Field, sMaxDate, as the Maximum of FirstOfMonthDate
Show All Records
If [Balance::sMaxDate ≠ Let ( T = Get ( CurrentDate ) ; T - Day ( T ) + 1 )]
Set Field [ Balance::FirstOfMonthDate ; Let ( T = Get ( CurrentDate ) ; T - Day ( T ) + 1 ) ]
Working like a charm!