1 2 Previous Next 18 Replies Latest reply on Aug 3, 2017 12:15 PM by alquimby

    How to create a running balance for a lot of data ??

    saigopaldas

      Suppose I have 1000k records on table.

       

      I want to show a running balance for all these data .

       

      If I will show it in a list it will take time to load and same for the portal.

       

      Can anybody suggest a effective way of doing this ?

       

       

       

      Thanks.

        • 1. Re: How to create a running balance for a lot of data ??
          Johan Hedman

          ExecuteSQL can do that really fast

          • 2. Re: How to create a running balance for a lot of data ??
            coherentkris

            scripting the process to run PSOS and having a field to store the result is also a good option.

            • 3. Re: How to create a running balance for a lot of data ??
              beverly

              'really fast' is subjective.

              Search this forum for gotchas with ExecuteSQL(). Any record NOT committed will slow it down. The kind of query may also be a deterrent.

              Any suggestions on the proposed query, JH?

              Beverly

              • 4. Re: How to create a running balance for a lot of data ??
                planteg

                If you do not need to show the running balance for all records, here is something you could do:

                 

                • have the user select the starting date
                • calculate the opening running total at that date
                • on the report
                  • display the opening running total at the selected date
                  • display the remaining records and for each the running total
                • 5. Re: How to create a running balance for a lot of data ??
                  philmodjunk

                  You've requested a RUNNING TOTAL, I read that as a total that changes with each additional record correct?

                   

                  something like the second column shown here?

                  2   2

                  1   3

                  4   7

                  and so forth?

                   

                  if your data is fairly static, you don't have to go back and modify older records and you don't need this running total for sets of records that put the same record into different groups, you might be able to store the total in a number field calculated at the time the record is committed, but I just stated two very big "ifs".

                  • 6. Re: How to create a running balance for a lot of data ??
                    Johan Hedman

                    Got a nice lesson om ExecuteSQL by Bob B at DevCon and my thoughts where to use something like this

                     

                    ExecuteSQL ( "Select ID_Employee, sum(Balance) from

                    \"Time\" where DateWorked > ?

                    group by ID_Employee

                    "; "|"; "" ; get (currentdate )-30)

                     

                    or something equal to what field names that it might in the solution

                    • 7. Re: How to create a running balance for a lot of data ??
                      philmodjunk

                      Which computes a total, but not necessarily a running total....

                      • 8. Re: How to create a running balance for a lot of data ??
                        Johan Hedman

                        You are right! It will give a sum for each employee. But you can also add sum of all employees. I was just trying to show how you use ExecuteSQL to get sum from several records

                        1 of 1 people found this helpful
                        • 9. Re: How to create a running balance for a lot of data ??
                          philmodjunk

                          Yes, but that isn't normally how a running total works.... It normally adds the total from the previous record to a value in the current record as shown in my first reply here. I have assumed that tried setting up a running total summary field and found the delays caused by computing this total over very large record sets to be unacceptable.

                           

                          Not everyone uses this term correctly so maybe what you show is exactly what is needed, but if it's truly a running total, then I don't see where ExecuteSQL is going to help here. We need confirmation as to what is meant by "running" here.

                          • 10. Re: How to create a running balance for a lot of data ??
                            Johan Hedman

                            You are right, salgopaldas needs to give more information

                            • 11. Re: How to create a running balance for a lot of data ??
                              saigopaldas

                              HI ,

                              Yes it is a running total as you had shown above

                              2   2

                              1   3

                              4   7

                              and so forth..

                               

                              Execute SQL has no use as running total keeps on changing for every record and dependent on previous record.

                              In my Case , though sorting order is constant (creation order is default order) , but user can modify value in between , so the running balance will be changed for further records.

                               

                              For 1000k records , or lets say records for around 3-4 years , running balance with summary function will be slower and loading of that layout will take time every time.

                               

                              I am thinking of below method.

                               

                              - I will use a static field to store the running total using a script. I can add a new field called "Previous Value" . There will be a nightly schedule script which will loop through all the data , If Current value = Previous value , then it will skip that record , if Current Value <> Previous value, then it will update the current value in Previous value field and running total on further records will be updated .

                               

                              -If user wants to see real time running totals in day time , then I will use a PSoS script , which will move to first record whose current value <> previous value and it will update running total in the static on further records .

                               

                              philmodjunk , johanhedman

                              Please have your suggestions.

                               

                               

                              THanks,

                              Saigopal Das

                              • 12. Re: How to create a running balance for a lot of data ??
                                Johan Hedman

                                Looping through records is never fast even if you did with Perform Script on Server, especially when you do not know what records the user is looking for.

                                 

                                If you really want a fast report with running totals, you have to start looking at what fields you are show on the layout. Maybe you instead of having Calculated total field, you could store a Numeric field with the total based on a Trigger and then your field for running total could be based on that Numeric field instead of Calculated fileld

                                • 13. Re: How to create a running balance for a lot of data ??
                                  philmodjunk

                                  If the field that calculates the balance is an unstored calculation, then a summary field that references it will be very, very slow. But if the calculation field is stored summarizing data from it is no slower than summarizing a number field.

                                   

                                  GetNthRecord can be used in an auto-enter calculation to copy data from a previous record so it might help a bit here.

                                   

                                  Options to consider:

                                   

                                  Don't use the running total for large record sets. It may look pretty but I'm not convinced that it's all that useful. For smaller subsets of the total records in your table, you might manage this with a "balance forward" value that you add to the value of the running total field to show a total that then includes the data from records not in the current found set. ExecuteSQL, as suggested by Johan could be used to get that balance forward value.

                                   

                                  What you have described in your last post is pretty much what I had suggested in my first post. I think we all recognize that it isn't a magic cure and you still could see noticeable delays if a large number of records needs to be updated. 

                                   

                                  What you are attempting with a a scripted calculation of these values is a bit of sleight of hand. It's still slow but maybe the user isn't watching. Updates that recalculate a small number of records might be done with PSOS at the time the user's change is committed note, however, that another user might be editing a record and the resulting edit lock will keep the script from updating it.

                                   

                                  Changes that require updating large numbers of records are best done via schedule at night. Your idea to find and update only the records affected by the data change is a good one, but how many records then have to be updated and how often the user needs to see these totals will be major factors in determining if the approach will work for you. 

                                  • 14. Re: How to create a running balance for a lot of data ??
                                    alquimby

                                    If you just want to see a total of MyField at any given time (for all records), the attached has 1 relationship and 1 script to total MyField. Works pretty quickly (1000 records).

                                    1 2 Previous Next