9 Replies Latest reply on Feb 2, 2017 1:42 PM by BKamp

    Is this calculation achieveable?

    BKamp

      Hi guys!

       

      I have the following tables:

      • Properties
      • Monthly Financials
      • Property Taxes

       

      Properties has the ID field '__pkPropertyID' that is used by the other two tables to relate them to the Properties table.

       

      Monthly Financials is used to track monthly rent incomes each month, it has a field 'period' where you select a date. the field 'period_year' extracts the year from the 'period' field.

       

      Property Taxes is used to track tax expenses on the properties. each record has a field 'Year' that has a dropdown in which you can pick the year this expense was paid.

       

      now here's what I need:

      I need to automatically equally spread all the taxes of year X over all the Monthly Financials entries of year X

      example:

      Monthly Financials has three records:

      1. january 2017
      2. februari 2017
      3. march 2017

       

      Property Taxes has two records:

      1. year: 2017 ; tax amount: $500
      2. year: 2017 ; tax amount: $1000

       

      now the to be made 'tax_per_month' field in Monthly Financials should have calculated values of $500 each ($1500/3).

      If I add the month April to Monthly Financials, it should have the value $375.

      If I add the month April 2016 to Monthly Financials, it should remain on the value $375 (2016 is not 2017

      If I add to the Property Taxes 2017 $1000, the 'tax_per_month' field should be recalculated again.

       

      Is this achievable? I guess I'll need SQL calculations right?

       

      Thanks guys!

        • 1. Re: Is this calculation achieveable?
          Johan Hedman

          With a Relationship between Property Taxes and Monthly Financial on same key field and Year you can use Sum( TaxAmount) to get sum of Taxes from Monthly Financial table. Then from there you know you have 12 months, so you should divide the Sum with 12 and add that to each Monthly Financial record

          1 of 1 people found this helpful
          • 2. Re: Is this calculation achieveable?
            Tigeen

            Hi BKamp,

             

            Values of unstored calculations and statistic fields will automatically be updated "on demand" by FileMaker.

             

            On the other hand, using SQL calculations would just help you not have to create the relationships that Johan has proposed you.

            But then you would have to handle the calculation to be unstored and have to correct triggers to have the calculation to be updated when expected.

             

            At this point of my understanding of your question, Id' go for a solution similar to Johan's proposition.

            1 of 1 people found this helpful
            • 3. Re: Is this calculation achieveable?
              BKamp

              Awesome guys! thanks for the info.

               

              Then from there you know you have 12 months, so you should divide the Sum with 12 and add that to each Monthly Financial record

              this is not always a given fact, say we start with one property in june 2017, then I would only have 7 months.... how to handle that?

               

              appreciate the support, great help

              • 4. Re: Is this calculation achieveable?
                Johan Hedman

                If you know number of months from start date to end date of year you can have a calculated for for number of months in monthly financial table

                 

                12 - Month(StartDate) will give you that number

                1 of 1 people found this helpful
                • 5. Re: Is this calculation achieveable?
                  BKamp

                  I understand, but I need Filemaker to automatically get the first (oldest) entry from the year to substract from 12.

                  Image the following entries:

                   

                  • november 2016
                  • december 2016
                  • january 2017
                  • february 2017
                  • march 2017

                   

                  for 2016 I have $1000 in taxes, and 2017 $1000 in taxes as well.

                   

                  the calculation should be: Sum( Property Taxes 2::Amount ) / "12" - Month ( firstdate_of_entries_year  ).

                   

                  So this should be the result:

                  • november 2016 ( $500 )
                  • december 2016 ( $500 )
                  • january 2017 ( $333,33 )
                  • february 2017 ( $333,33 )
                  • march 2017 ( $333,33 )

                   

                  any clue on how to calculate this 'firstdate_of_entries_year' field?

                  • 6. Re: Is this calculation achieveable?
                    Johan Hedman

                    Never use "12" in a calculation. Then it is used as Text.

                     

                    You need to have start- and end date showing you from where to get information, then you can use what you are asking for. I would create a script that FileMaker Server runs every night that does this i several steps to make sure all numbers work out correctly instead of trying to make a calculation.

                    • 7. Re: Is this calculation achieveable?
                      BKamp

                      I getting lost here, Johan said in in his first reply that I should create a relationship between taxes and monthly financials with the Year and the Property ID. Now why can't I count the related records from taxes by now? the year between monthly financials and taxes match, so I should be able to count how many monthly financials records are related to on tax entry record right?

                       

                      I'm working from the main layout based on Properties table, in here I have a portal to Monthly Financials and another one to Property Taxes.

                       

                      I created a field called 'count_related_records' in Property Taxes with the calculation: Count ( Monthly Financials::period_year ) this gives me all the records as a result, instead of only the ones with matching Year field.

                       

                      Any clues?

                      Screenshot 2017-02-02 11.32.26 copy.jpg

                      • 8. Re: Is this calculation achieveable?
                        Johan Hedman

                        You can add a calculation, but for each time your write something you add functionality that makes things very complicated with just a Calculation field. If you have a script that can go to several records in different tables to look for data, you can save your value from that script very night or trigger it to save and have the calculation much easier to handle.

                         

                        If you want to have related records for just that year, you need to add that in your relationship or do a ExecuteSQL towards that table.

                        • 9. Re: Is this calculation achieveable?
                          BKamp

                          Okay I get it, but in my opinion I'm almost there because of the two field relationship between monthly financials and taxes (year and propertyID). so why do I get all records instead of only the ones that match both relationships? see my previous reply.

                           

                          Really appreciate the support, thanks man!