1 2 Previous Next 20 Replies Latest reply on Apr 26, 2017 7:16 PM by philmodjunk

    Conditional Sum Script Emulation

    tleitzke

      I need to 2 different sums of the same field, separated by a field that has "Yes" or "No". I couldn't find a way to do it with just a calculation, so I went to do it the hard way with scripts. It goes to a blank layout, performs find (where "bid ID == $Bid Id", and "Is Manufacturer Rebate = Yes"(or "No")). The script did not produce the right numbers. I have (many times over) checked that the previous fields are working correctly and producing the right numbers, so it is just this script that is bad.

       

      (Totals table contains all my math. Yes, "Total Round X" is a bad naming scheme, when seen as just text)

      (ALL Credits table contains all the credits)

      (Credits table contains all the credits added to bid).

       

      I have to separately do a sum for manufacture rebate and non; because the manufacture rebate applies to the final cost as it comes instantly from manufacturer- while the non-manufacture rebates do not come right away and can take many weeks to arrive (4-6) and doesn't apply to the final cost.

       

      Capture.PNG

      (I am honestly unsure if Sum applies to only found records or all)

        • 1. Re: Conditional Sum Script Emulation
          philmodjunk

          Sum does not work like you have set up in this script.

           

          Sum can be used three ways:

           

          Sum ( Field1 ; Field2 ; field3 ) ---> This sums these three fields from the same record

           

          Sum ( Repeating field ) ----> This sums the different repetitions of this field

           

          Sum ( relatedTable::Field ) ---->sums field over all related records

           

          Note that none of these sum across the found set.

           

          Sum ( Field ) just returns the value of "field".

           

          Use a summary field instead of Sum

           

          And there are a number of ways to get these totals without using a script

          • 2. Re: Conditional Sum Script Emulation
            tleitzke

            I am a little confused how I can make a summary field based on a portal. I made an attempt by self-relating and using 'total', and it came out as not the right numbers.

            • 4. Re: Conditional Sum Script Emulation
              philmodjunk

              I'm not suggesting a summary field based on a portal.

               

              Simply define the summary field in Credits to compute the total of Rebate Amount.

               

              Change line 8 of your script to be:

               

              Set Field [$IsManufac ; Value: New Summary field here ]

               

              This assumes that you want to put a value into this variable that is the sum of Rebate Amount across all the records of your found set.

              • 5. Re: Conditional Sum Script Emulation
                siplus

                Given that "Is manufacture rebate" with its yes / no value governs your table, it's legit to define

                 

                RebateYesAmount = If (Is Manufacture Rebate; RebateAmount; 0)

                RebateNoAmount = If (Is Manufacture Rebate;  0;  RebateAmount)

                 

                and define summary sums on those 2.

                 

                Then you can do your finds and directly evaluate the values of the sums.

                • 6. Re: Conditional Sum Script Emulation
                  tleitzke

                  siplus, that sounds like a very simple resolution that I didn't think about. But it does make some other things more complicated (work wise) going with that system.

                   

                   

                  Philmodjunk, I will give this a try (I am heavily multitasking currently, so hopefully i can get it tried soon.)

                  • 7. Re: Conditional Sum Script Emulation
                    philmodjunk

                    You might also look at the discussion found here:

                     

                    Get total of certain related records from another table

                    • 8. Re: Conditional Sum Script Emulation
                      tleitzke

                      In the initial test, the script worked perfectly, HOWEVER, it does not. It only works great if there is at least 1 item in the non-manufacture rebate section, then totals will be zero'd out. I then made changes to the script to hopefully solve it. First, set the variables to 0 from the start, then added an if statement to only change the variable if they FoundCount is greater than 0 (stays 0 if there was nothing found), and reset the summary field to 0 after each count; but this had a weird effect of switching the spots of the variables.

                       

                      Did I do something wrong, or did I just misplace something? (It worked 100% on a piece that had both manufacture and non-manufacture rebates; but excluding 1 from the current version has odd displays).

                       

                      Capture.PNG

                       

                      (I don't know if you want to poke at the file. But might as well give the current version up)

                      (This script is in the Bid layout, Complete tab, only button on the screen)

                      • 9. Re: Conditional Sum Script Emulation
                        tleitzke

                        I can only assume, that for some reason it is finding records when it should not be finding records, so the summary field fills up a FoundCount is greater than 0. If there i nothing, it still subtracks some amount.

                         

                        On the if statements, I changed them to check if lasterror is 401 (Which is/should be no records found_Or the error code doesnt exist). But even when the rebates are empty and error message pops up; it still ends up coming out at a subtracted number.....why. (I can't activiely view the data, because I don't have Filemaker Advance's Data Viewer.)

                        • 10. Re: Conditional Sum Script Emulation
                          philmodjunk

                          "Odd displays" is too vague to tell us anything useful.

                           

                          Try putting a pause or show custom dialog in just after the find is performed and check to see what records are being found. Halt the script immediately after the find--you can insert a Halt script for temporary testing purposes and then you can not only examine the found set to see if it is correct, but you can select "modify last find" from the records menu and check the actual find criteria used to see if that is correct. (you might have to place additional fields, or fields with value list formatting removed on your layout to see the criteria--which can also be checked in the data viewer if you have FileMaker Advanced.)

                          • 11. Re: Conditional Sum Script Emulation
                            tleitzke

                            I tried that, but it always shows as 0's, including "Total Round 5"(and 6); or as blank spots. Even lasterror is just '0' (placed right after performfind)

                             

                             

                            idiot me didnt think about checking which if statement portion it went to; and even though none are found with error capture off- the custom dialog for the section for if records are found is used instead.

                            • 12. Re: Conditional Sum Script Emulation
                              philmodjunk

                              if last error is 0 records were found.

                               

                              Not sure what "that" you tried.

                               

                              You are aware that getting these totals can be gotten without using a scripted find at all?

                               

                              Relationships, filtered portal and ExecuteSQL can all provide you with aggregate values based on selected sets of records. That's why I posted a link to a very similar discussion that has been paralleling this one with a different person.

                              • 13. Re: Conditional Sum Script Emulation
                                tleitzke

                                SQL is something I have no experience in, so I am confused just reading it. I know what it is, but without experience, is not something I can currently handle so simply.

                                • 14. Re: Conditional Sum Script Emulation
                                  BruceRobertson

                                  Adding yet another reason to turn this project over to a competent developer.

                                  There is so much about this project that could be done faster, and with proper structure.

                                  1 2 Previous Next