7 Replies Latest reply on Jul 7, 2014 2:11 PM by philmodjunk

    Sum (if) based on multiple conditions

    NataliePiguet

      Title

      Sum (if) based on multiple conditions

      Post

           Hi Gents

           I am trying to make a cash flow database for my business and I am new to use FM.

           I have one table in which I am saving all purchases i.e.:

           Unique ID, Invoice Number, Invoice Date, Supplier Name, Invoice Value, Type of Expense

           I am also trying to make a yearly report where I can display a summary of all expenses in a table, i.e.: the column names are months and rows are types of expenses, like: Tools, Materials, Equipment. So in theory, I want to have 3 rows 12 columns each of data. Now I assume I need a summary field in my table which will do the calculation. I have been doing a little bit of Visual Basic in it's time years ago on a PC, so i tried to play with the functions and read help files but didn't get far.

           If any of you kindly could explain what function I need to calculate how much I have spent for let's say Tools in January I would really appreciate. So a function is needed to look up all invoices with date containing January as month and Type Of Expense "Tools".

           Now it would be great to have a drop down list with years somewhere aside so I view the spending for each year ;)

           I know I am probably asking much.

           Any help would be really appreciated.

           Thank you

           Nat x

            

        • 1. Re: Sum (if) based on multiple conditions
          philmodjunk

               This is a commonly expressed request.

               See these two threads for some ideas on how to do it:

          Sum_Calculation based on condition

          FMP 12 Tip: Summary Recaps (Portal Subtotals)

          • 2. Re: Sum (if) based on multiple conditions
            NataliePiguet

                 Thank you for your reply PhilModJunk

                 This seems so complicated. Is there any other simple way to do it? I was under an impression you can use something like DCAOUNTA function in Excel. Point the table, and sum based on few conditions?

                  

            • 3. Re: Sum (if) based on multiple conditions
              philmodjunk

                   The simplest solution for the new developer is to set up the needed relationships using match fields. Since a Sum function or a summary field defined in the related table both return aggregate values based on all related records, you can use the relationship to match to only those records you need for a total.

                   And if you only need to display such subtotals, the filtered portal option is even simpler.

              • 4. Re: Sum (if) based on multiple conditions
                NataliePiguet

                     I'll give it a try and will post an update.

                     Thank you again PhilModJunk ;)

                • 5. Re: Sum (if) based on multiple conditions
                  NataliePiguet

                       Do you think I could send you my database, could you have a look into it?

                  • 6. Re: Sum (if) based on multiple conditions
                    NataliePiguet

                         This is doing my headache now..

                         When paying £££ for FM and reading all this stuff on FM website 'how simple to use it is' I was under impression I'll have this pretty much sorted out in no time, especially that I am not trying to design anything fancy here, just stuff which I was able to do in Excel..

                         All I want to do is this:

                         My table have i.e the following Fields:

                         Unique ID     Invoice Date     Total Value    Supplier Name    Expense Type

                         00001            1 Jan 2013         $100             xxx                      Materials

                         00002            15 Feb 2013       $150             yyyy                     Tools

                         00003            18 Feb 2013       $200             zzzzz                    Tools

                         00004             2 Mar 2013        $100             aaaa                     Tools

                         All I initially want to achieve is a TextBox on my Layout which will show the total of i.e all Invoices paid for Tools in February which in this case is $350.

                         At later stage I would like to have this TextBox to be driven by a DropList where I can choose the month availiable in the Invoice Date column.

                         This is killing me :(

                    • 7. Re: Sum (if) based on multiple conditions
                      philmodjunk

                           assuming that Invoice date is properly a field of type date like it should be, add this calculation field to your table:

                           Invoice date - Day ( Invoice date ) + 1

                           Call it cMonth.

                           Define a date field, SelectedMonth in your layout's table with this auto-enter calculation:

                           self - Day ( self ) + 1

                           Clear the do not replace existing value check box.

                           Define this relationship, but using your Tutorial: What are Table Occurrences? names in place of mine:

                           LayoutTable::SelectedMonth = RelatedTable::cMonth

                           Define a calculation field in Layout table as: Sum ( RelatedTable::cMonth )

                           Put that field on your layout and you can get a total for any month/year date you select in the SelectedMonth table.

                           LayoutTable and RelatedTable can be two occurrences of the same table linked in a self join relationship.