8 Replies Latest reply on Oct 18, 2012 9:40 PM by JorgeFernandez

    Anual Balance by month

    JorgeFernandez

      Title

      Anual Balance by month

      Post

           Hi everyone!

           I´m working on a DB for invoices and expenses.

           What I´m trying to create is a balance layout where you can see total income and total outcome by month in one year, looking like this:

           Year: 2011

           Jan: income: $2478

                   outcome: $1890

           Feb: income: $2796

                    outcome: $1867

           ... Dec: ...

           The way I´m thinking is: I have 2 tables (invoices and expenses)

           Decided to create a new table (balance)

           If I create 2 new fields on each table: year and month (using Year (Date) and Month (Date) )

           And creating some relationships to fields on balance table:

           invoices::year = balance::year

           and invoices::month = balance::January

           then another relationship

            

           invoices::year = balance::year

           and invoices::month = balance::February

           and keep doing it till December

           And finally a relation to:

           invoices::total X balance::Total

           And creating a simple script using sum (invoices::total).... 

           Then I can make it, but i think there is another way.

            

           Can you please help me??

        • 1. Re: Anual Balance by month
          philmodjunk

               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.

          • 2. Re: Anual Balance by month
            JorgeFernandez

                 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??

            • 3. Re: Anual Balance by month
              philmodjunk

                   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.

              • 4. Re: Anual Balance by month
                JorgeFernandez

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

                     Sum (balance::total)

                     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???

                • 5. Re: Anual Balance by month
                  philmodjunk

                       Why use that instead of the approach I have recommended?

                  • 6. Re: Anual Balance by month
                    JorgeFernandez

                         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?

                         Something like:

                         If ( Month (Get (CurrentDate) ) > Month ( Last Record Date );

                         Last Record Date + 1 Month;

                         "Error" )

                          

                         Is it possible??

                    • 7. Re: Anual Balance by month
                      philmodjunk

                           Define a Summary Field, sMaxDate, as the Maximum of FirstOfMonthDate

                           Show All Records
                           If [Balance::sMaxDate ≠ Let ( T = Get ( CurrentDate ) ; T - Day ( T ) + 1 )]
                              New Record/Request
                              Set Field [ Balance::FirstOfMonthDate ; Let ( T = Get ( CurrentDate ) ; T - Day ( T ) + 1 ) ]
                           End IF

                      • 8. Re: Anual Balance by month
                        JorgeFernandez

                             Working like a charm! 

                             Thanks!