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 ?
ExecuteSQL can do that really fast
scripting the process to run PSOS and having a field to store the result is also a good option.
'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?
If you do not need to show the running balance for all records, here is something you could do:
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?
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".
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
Which computes a total, but not necessarily a running total....
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
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 saigopaldas 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.
You are right, salgopaldas needs to give more information
Yes it is a running total as you had shown above
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.
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
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.
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).
But how is that a running total?
Please review the discussion that has already taken place here as to what is meant by that term.
Duly noted. The attached shows a running total of MyField using a summary field (running). It works on a full set but updates with a found set. If this field doesn't work for him, then I don't understand his posts. He was concerned about speed with 1000 records, but the attached with 1000 records seems to be quite speedy.
No, he was concerned about a 1000 times that many. He has 1000K records, not a thousand and there lies the challenge here.
Your main concern seems to be speed. Here is a way to hide the running total field unless specifically called by a user:
(1) Place your Running Total summary field in the body of your List layout. (I think the FileMaker summary field is superior to "work arounds.")
(2) Create a global yes/no field called something like “Show Running Total” and place it as a Radio Button in the header of your List layout.
(3) Set the Running Total summary field (and field label) to Hide when the global field = “NO.”
(4) Create a script that sets the global field value to “No” and attach the script as a trigger in Layout Setup… (on the List layout) to fire on both “OnLayoutEnter” and “OnLayoutExit.” This way the summary field never shows itself unless a user selects “Yes” in the global Radio Button after the layout has loaded and selected records have been “found.”
Retrieving data ...