6 Replies Latest reply on Mar 31, 2014 8:44 AM by tigerdad

    Sum If

    tigerdad

      I am trying to create a layout/report that pulls all information off of a master record, with the exception of a few detail records that need to show on one side of the page. I have a PayAppMaster table that is related to the COMaster table. I need to sum all the records on the COMaster Table that have an approved date of last month or earlier. the first thing that jumps to mind is the sumif, similar to excel, but I can't find anything similar to this in FM12Advanced. I have googled a few custom functions, but they only take text arguments. any help or ideas would be greatly appreciated.

        • 1. Re: Sum If
          wimdecorte

          Two typical ways of doing this:

           

          - create a relationship to the COMaster table using a global date on the left and the approved date on the right side and use the right comparison operator (<=, >=).

           

          - use the ExecuteSQL() function with the right WHERE clause and sum the results.

          • 2. Re: Sum If
            BruceHerbach

            Wim's suggestion of ExecuteSQL is definitly a good method.  I just did a report that had to come up with sums from a number of different tables and ExecuteSQL worked very well.  Seedcode's SQL Explorer and Beverly Voth's ExecuteSQL the missing manual are very helpful in getting this type of thing started.

             

            Bruce

            • 3. Re: Sum If
              tigerdad

              Guys, thanks for the excellent suggestion, ExecutSQL worked perfectly, and Seedcode's SQLExplorer definitly made it easy to learn.  On question though, it only returns the data type of text.  I tried using GetAsNumber(SQLReesult) and using field formatting to format the result as a currancy, but the GetAsNumber seems to concantinate the 2 values into one number. so instead of having

              50000

              0

              I end up with

              500000

              I need the final output to bwe $50,000.00

              $0.00

               

              How can I work around this?  do I parce the result list, then GetAsNumber each value, then put them back in a list()?

               

              I am so close and again appricate everyone;s help.

              • 4. Re: Sum If
                Vyke

                areyou using Select Sum() or are you trying ot sum them in a calc first?

                • 5. Re: Sum If
                  wimdecorte

                  I would probably loop through the result and apply the format, rather than try to complicate the ExecuteSQL query itself.

                  • 6. Re: Sum If
                    tigerdad

                    Thanks for everyone's help.  I endedup using a SUM in the SQL statement to get just the filtered total I needed, then used GetAsNumber() to format it.  I had to write a sperate ExecuteSQL() for each total, but no big deal.

                     

                    Thanks again.