1 2 Previous Next 18 Replies Latest reply on Aug 9, 2017 9:45 AM by jackrodgersjr

    Need Sum of Transactions > 0 & < 0

    springer01

      I have a VISA account in which the accounting period is from-to the 5th of each month. I have successfuly been able to get a Summary by Month of 's_total of transactions' when sorted by Month: 'd_month_sort'. However, my VISA Statement has monthly subtotals of Payments & Purchases to the 5th of each month. In my layout, in the Field 'n_transactions', Payments are a positive nbr (Debit): 10.99, and Purchases a negative nbr (Credit): (10.99).I created 2 Fields: 'n_trans_G_0' (transactions > zero) & 'n_trans_L_0' (transactions < zero). These Fields are:

      Calculation  = If (n_transactions < "0" ;  Sum (n_transactions ; "0"))

      and a duplicate Field, except ('n_transactions > "0"').

      Problem is: when sorted by 'd_month_sort' the resulting Sum is only the LAST transaction of each month, showing in the proper Field, not the sum of ALL Debits or Credits in the respective Field for the Month .

      How do I:

      1. Have a Function that shows the sum of ALL of the respective Debits or Credits for the Month?

      2. Have the equation calculate from the 4th thru the 5th of the succeeding month?

      If I can accomplish those I can compare the results to my Statement to see if I have entered each entry correctly. Of course I compare each entry with the Statement but, at the age of 85, I've been known to make errors and not be able to detect them.

      I will by Thankful for any help!

        • 1. Re: Need Sum of Transactions > 0 & < 0
          Stephen Huston

          Is your final total field located on a Trailing Grand Total/Summary part or on a Body part or on a Footer? The same field will give different results based on the Part type where it's placed In the report.

          • 2. Re: Need Sum of Transactions > 0 & < 0
            springer01

            It's in a Sub-summary by 'd_month_sort' Part. I just moved them into my Trailing Grand Summary Part and that's not the solution. That Part is not by Month. What kind of Part do you recommend? Perhaps a second Sub-summary Part? If so, what kind of Sort?

            • 3. Re: Need Sum of Transactions > 0 & < 0
              springer01

              Thanks for the suggestion. I've looked Execute SQL up in my "Functions Reference.pdf" and it looks quite complex. I'm going to have to study and trial-&-error for a while until I get it. There's a 'zillion' Functions in that document ... 268 pgs.  It's difficult to understand and select the proper one.

              • 4. Re: Need Sum of Transactions > 0 & < 0
                springer01

                Stephen Huston: For an unknown reason your reply showing the Execute SQL Function's detailed equation disappeared. Can it be recovered or can you re-post it?

                • 5. Re: Need Sum of Transactions > 0 & < 0
                  taylorsharpe

                  springer01 wrote:

                   

                  Stephen Huston: For an unknown reason your reply showing the Execute SQL Function's detailed equation disappeared. Can it be recovered or can you re-post it?

                   

                  Oh, I deleted it because I really didn't have field and table names and was just guessing at them.  But, yes, SQL is powerful and can do all kinds of things depending on what you want.  I think it would be helpful if we could see a screen shot of the report with examples of what you are trying to summarize, I think we can help you out.  Take out account info and change up numbers so we're not looking at real data, but we can probably get you a good calc on this. 

                  1 of 1 people found this helpful
                  • 6. Re: Need Sum of Transactions > 0 & < 0
                    beverly

                    See if this helps focus on the function. Get the PDF & example files:

                    Beverly

                    Sent from miPhone

                    • 7. Re: Need Sum of Transactions > 0 & < 0
                      springer01

                      Beverly: I went to the filemakerhacks.com link and got this msg:

                      This is somewhat embarrassing, isn’t it?

                      It seems we can’t find what you’re looking for. Perhaps searching can help.

                      • 8. Re: Need Sum of Transactions > 0 & < 0
                        beverly

                        I just tested the link I had posted. It's working right now. Perhaps you were on a server-pause-backup cycle?

                        Clear your browser cache & history for this site and try again.

                         

                        Sent from miPhone

                        • 9. Re: Need Sum of Transactions > 0 & < 0
                          jackrodgersjr

                          An old fashioned technique that i have used for years is to create a calculated field that grabs the record value if its conditions are met. This cleans up the various confusions that might come from using a bank statement.

                           

                          for instance:

                           

                          Create a calculated field:

                          (Using Let would work but this is psuedocode)

                           

                          //  get the day numbers you wish to use

                          set variable $startday to 1 or 5 or whatever

                          set variable $endday to whatever  could be something like the last day of month

                           

                          set variable $startdate to date( month(therecorddate) ; $startday ; year(therecorddate)

                          set variable $enddate to date( month(therecorddate) ; $endday; year(therecorddate)

                           

                          case(

                            therecorddate<startdate ; ""

                          ; therecorddate>startdate ; ""

                          fielddollaramount

                          )

                           

                          This calculated field will posses a value whenever the date of the record is inside your prefered date range.

                           

                          =====

                           

                          You can change these local variables to globals

                          $$startdate

                          $$enddate

                          $$month

                          $$year

                          ...then you could fill in these values in a script and have the dollar field value change according to your wishes. A gloval field on a layout could be used for your range and another to display the sum. The global could be calculated for the current date or last month, etc.

                           

                          You could then sum(mynewdollar) field at any time for all records or a selection.

                          • 10. Re: Need Sum of Transactions > 0 & < 0
                            springer01

                            Cleared the History in by Safari browser and now can’t post replies on the Community website. Apparently it cleared my Account Profile data.

                            eritenour@cox.net

                            • 11. Re: Need Sum of Transactions > 0 & < 0
                              jackrodgersjr

                              Perhaps Safari is storing your password? and you can ask for a password reset or just relogin.

                              • 12. Re: Need Sum of Transactions > 0 & < 0
                                springer01

                                Back on-line.

                                I've created the flwg FieldNames from your suggested equations above:

                                start_day;  Number; Calculated value; start day = 5    (Transactions start on the 5th of each Month)

                                end_day;  Number; Calculated value; start day = 4      (Transactions end on the 4th of each Month)

                                Now I'm confused by the 'functions' in the next 2 equations:

                                set variable $startdate to date( month(therecorddate) ; $startday ; year(therecorddate)

                                set variable $enddate to date( month(therecorddate) ; $endday; year(therecorddate)

                                For the Month I've been using with success to sort by Calendar Month:

                                d_month_name:  Text Year ( d_date ) + Case (Month ( d_date ) <10; 0 & Month ( d_date) ; Month ( d_date ) )

                                And:  d_month_sort   Calculation   =Year (d_date) & Right ("0" & Month (d_date); 2)

                                However I don't really understand these equations that were suggested by others. Altho' I've used FMP for years, since v3, 5, 8, now 12, the rate of change in functions & equations has passed me like I'm standing still! However, I understand the Case Function but need to understand the above equations.

                                Will the equations apply for all months, e.g. February?

                                • 13. Re: Need Sum of Transactions > 0 & < 0
                                  philmodjunk

                                  If (n_transactions < "0" ;  Sum (n_transactions ; "0"))

                                   

                                  Has some problems that I don't see noted here by others.

                                   

                                  Don't need the quotes around the numbers as these are numeric values, not text. Comparing numbers as text will cause problems since "2" > "1000000" is a true statement where 2 > 1000000 is not.

                                   

                                  Sum ( n_transactions ; " 0" ) is not correct and you'll get an error when you close the dialog. I assume you actually have something like one of the following, (both of which won't do what you need).

                                   

                                  If (n_transactions < "0" ;  Sum (n_transactions  ) ; "0" )

                                   

                                  If (RelatedTableOccurrenceNameHere::n_transactions < "0" ;  Sum (RelatedTableOccurrenceNameHere::n_transactions ) ; "0" )

                                   

                                  Sum ( field ) just returns the value of field. Sum is intended to sum either all the repetitions of a repeating field or a field over all the records related to the current record or to sum all the fields listed inside the parenthesis. You show just one listed local field and thus the value of that field is all that you get returned by the sum.

                                   

                                  Sum ( RelatedTable::Field ) returns the sum of field over the set of all related records. Putting it inside an If function does not change what records are or are not related. In this case, the If function causes the calculation to return the text "0" if the first related record has a value < "0" for n_transactions and the sum of all records if it does not.

                                   

                                  To get sum to return  the sum of all values of this field for a set of records, you'd need a relationship that only matches to records that you want to sum. This would mean including a comparison to zero into the relationship and that can be done using a calculation field defined in the parent table that you include as an additional match field.

                                   

                                  Note that there are many ways to selectively sum a set of records in a table. ExecuteSQL is a good option in many cases, but it's only one of may options.

                                  Other options include the use of summary fields in summary reports, summary fields inside filtered portals, the sum function with appropriate relationships. Even a script that finds the records, copies the value of a summary field and then sets a variable or field to that value might be used though that option strikes me as needlessly complex/slow in most cases.

                                  • 14. Re: Need Sum of Transactions > 0 & < 0
                                    beverly

                                    Sorry about that! Thus my adding "for this site". Clearing all history and cache removes stored passwords to sites such as this forum. While a real pain sometimes, it's possible to remove selective history of a browser.

                                     

                                    I do as much Web Developing as Database developing, so my browsers are set to zero caching and history. This is because I need to test fresh changes to a web page. But I realize most people like the convenience of storing all those goodies. It just become a problem if you need to view a page such as I linked.

                                     

                                    A TIP:

                                    If you don't want to clear anything, an age-old trick is to append something different to the end of an URL:

                                    mydomain/index.htm ==> mydomain/index.htm?blah=123456

                                    mydomain/index.htm?e=abc ==> mydomain/index.htm?e=abc&blah=34556

                                    mydomain/index.htm?e=abc&x=54 ==> mydomain/index.htm?e=abc&x=54&blah=987654x

                                    HTH,

                                    Beverly

                                    1 2 Previous Next