1 2 Previous Next 21 Replies Latest reply on Jul 8, 2017 3:20 AM by AitchB

    Having a problem with a summary calculation

    AitchB

      Hi,

       

      Can someone help?

       

      I have a portal in my booking layout which shows credits and debits from the table cashbook.

      Some amounts are in US Dollars some in Euros. There is a field Cashbook::currency" that is either "$" or "€" (automatic depending on the bank account.)

       

      I probably need to have a second portal so I can separate the $s and €s

       

      I also suppose (dangerous to say that here!) that I need a new field using the GetSummary function with an "if" Cashbook::currency" that is either "$" or "€"

       

      I can't see where to put the "if"!

       

      Hoping....

       

      Henry

      FMP15

        • 1. Re: Having a problem with a summary calculation
          bigtom

          Doing a summary of related records will get you the summary of all currencies. Unless you do some creative relationships.

           

          Since both currencies are mixed in one table you might try and ExecuteSQL SUM for each currency to get the numbers you are looking for.

          • 2. Re: Having a problem with a summary calculation
            AitchB

            Thanks Tom. I'd prefer not to have to make 2 tables. PITA.

            Can I use a calculated field something like this?:

            If(currency= "$", GetSummary(amount;amount), 0)

             

            • 3. Re: Having a problem with a summary calculation
              bigtom

              Let(

              [

              symbol = "$";

              result = ExecuteSQL ( "SELECT SUM( \"amount\") FROM Cashbook WHERE currency = ?"; ""; ""; symbol)

              ];

              result

              )

              • 4. Re: Having a problem with a summary calculation
                AitchB

                Wow!

                 

                The ? in place of breakfield was there 'cos I have no idea what field that should be.

                Questions -

                I suppose SELECT SUM is a temporary field but not a table field.

                amount is the cash amount.

                if symbol = "$" or "€" what replaces your "?" or is the end symbol just popping the $symbol onto the result?

                 

                Sorry to be so ignorant but I am a beginner in FMP and have programmed a long time ago but not in SQL

                 

                Let(

                [

                symbol = "$";

                result = ExecuteSQL ( "SELECT SUM( \"amount\") FROM Cashbook WHERE currency = ?"; ""; ""; symbol)

                ];

                result

                )

                • 5. Re: Having a problem with a summary calculation
                  bigtom

                  The ? designates that there is a query parameter at the end. In this case it is the super local variable "symbol". The parameter follow in order of appearance.

                   

                  This is still a good reference.

                  https://filemakerhacks.com/2012/10/19/the-missing-fm-12-executesql-reference/ 

                   

                  You could use dates as well to narrow the results where entry_date is your transaction date field. "date" is a reserved word and if used needs to be used as \"date\". This is really just to show for the ? and parameters work. You se 3x ? and 3 parameters at the end.

                   

                  Let(

                  [

                  start = datefield1;

                  end = datefield2;

                  symbol = "$";

                  result = ExecuteSQL ( "SELECT SUM( \"amount\") FROM Cashbook WHERE currency = ? AND entry_date BETWEEN ? AND ?"; ""; ""; symbol; start; end)

                  ];

                  result

                  )

                  • 6. Re: Having a problem with a summary calculation
                    philmodjunk

                    You could also use two portals to cash book with portal filters that select for specific currency. A summary field from cashbook placed in the portal will then show a total of just the specified currency.

                     

                    The portal need not be more than a single row.

                    1 of 1 people found this helpful
                    • 7. Re: Having a problem with a summary calculation
                      bigtom

                      Questions -

                      I suppose SELECT SUM is a temporary field but not a table field.

                      SELECT SUM is part of the SQL query that is in a calculation. You can set a field or variable to the result.

                      amount is the cash amount.

                      amount is the name of the field that you are summarizing.

                      or is the end symbol just popping the $symbol onto the result?

                      Correct

                      • 8. Re: Having a problem with a summary calculation
                        bigtom

                        philmfdjunk has a good solution as well. Maybe easier for you. When the summary field is outside of the portal you get a sum of both currencies. If it is in a filtered portal you get the sum of the filtered results.

                         

                        Duplicate the currency filtered portal. Set it to 1 row and only use the summary field in that portal.

                        • 9. Re: Having a problem with a summary calculation
                          AitchB

                          Hi Philmodjunk. Right guys, thanks for the pointers. I shall now go and ponder this. I was just thinking about 2 portals and a filter.

                          • 10. Re: Having a problem with a summary calculation
                            philmodjunk

                            A follow up thought: It is possible to make the portal "chrome" (the borders etc) invisible such that a single row portal with a single summary field looks just like another field on your layout.

                            • 11. Re: Having a problem with a summary calculation
                              AitchB

                              You knew I’d be back!!

                               

                              The portal “Payment details” shows records from the table - cashbook::…

                              The field cashbook::currency is either $ or € and is set automatically when the bank is chosen. From a value list.

                               

                              On the portal there is a sort to put the list in date order.

                               

                              Then I have 2 fields that need to be filtered,  amount_credit and amount_debit

                               

                              If they are filtered, do I need to do anything with the two summary fields (titled Total Rcvd and Comm + B Chgs?

                               

                              I will place a second portal (either as a tab or below the other portal) with just the Euro transactions.

                               

                              I open the Portal setup and select Filter portal records / Specify

                               

                              if (Cashbook::currency = “$” ; Cashbook::amount_credit ; 0 )  does not work and how do I include the Cashbook::amount_debit in the if statement (nested) or is it a case?

                               

                              Bigtom said that the summary field/s should be inside the portal for this to work, but where? in the grey area, on the white part?

                               

                              “When the summary field is outside of the portal you get a sum of both currencies. If it is in a filtered portal you get the sum of the filtered results.

                               

                              Duplicate the currency filtered portal. Set it to 1 row and only use the summary field in that portal.”

                               

                              Sorry to appear so ignorant but then…

                               

                              Thanks in advance,  HenryScreen Shot 2017-07-06 at 19.09.15.png

                              • 12. Re: Having a problem with a summary calculation
                                philmodjunk

                                Inside the portal means inside the portal row like you would for any other portal field. When you drag the portal a few pixels, the field should move with it.

                                • 13. Re: Having a problem with a summary calculation
                                  AitchB

                                  OK in the white bit then.  But something is wrong 'cos I tried that.

                                  • 14. Re: Having a problem with a summary calculation
                                    philmodjunk

                                    But not just "in the white bit", but "owned" by the portal. That's where the test of moving the portal to see if the field moves with the portal comes in.

                                     

                                    But in your screen shot, I'm not sure that I see a one row filtered portal with a summary field or two in it is to be found nor can I see what portal filter expression you are using for the portal filtering.

                                    1 of 1 people found this helpful
                                    1 2 Previous Next