Processing Running Totals

[David Gilmore]

I am very new to Memento, having it for less than a week. I am a software engineer and I am coming from the similar HanDBase program, so I suppose I have somewhat of a head start.

I have been searching how to process a table (library) that will have running totals in it. I could not find anywhere the information on how to accomplish this, so I thought I would publish my Javascript I wrote to do it. If there is a better way of doing it, let me know.

My table (library) is a credit card tracker, with multiple cards. I wanted to have a running total for each card, and have the running total start over for the next card. I still am not used to Memento terminology, so I will be using standard industry database terms, with the Memento term in parenthesis.

// Initialize a few variables that will be needed
  var saccold = "-";
  var sacc = "-";
  var cTotal = 0;
  var cAmnt = 0;
// Fetch all the rows (entries) in the table (library)
  var entries = lib().entries();
// The variable entries is an array of objects representing each row (entry), and they are ordered in physical record order.
// To get the running totals, we need to sort those rows based on account, then the record entry date
// The Javascript sort object requires that a "comparison of keys" logic be supplied.
// This is done by supplying a new "compare" function that returns an integer with the following values:
// 0 - Records are equal, less than zero or greater than zero the first record is less than/greater than the second
// In object oriented programming this new supplied function is often called an "override" method.
// In this case we want to sort on the credit card account as the first key, then a second key based on the record entry date.
  var order = {compare:
    function(a,b) {
      if (a.field("Account") < b.field("Account")) {
         return -1;}
      if (a.field("Account") > b.field("Account")) {
         return 1;}
// If there aree multiple text keys, enter thier comparison tests here.
// The last statement would be to "return 0", indciating that all the keys are equal.
// Dates are a number, not text, so the comparison test is different than for strings.
// Datea are simply a large number, we can just return the number difference of the date value
// instead (positive, negative, or zero). The division is simply to reduce the
// integer data/time stamp to a value Memento can handle.
      return (a.field("DateTime")/1000 - b.field("DateTime")/1000);
    }
  }
// Now that we have built the override compare function, call the sort:
  entries.sort(order);
// Now loop through all of the records (entrys) totaling the amount field as we go.
// The total gets reset when encountering the next card
  for (var I = 0; I < entries.length; I++) {
    sacc = entries[I].field("Account");
    if (sacc != saccold) {
      saccold = sacc;
      cTotal = 0;
    }
    cAmnt = entries[I].field("Amount");
    cTotal = cTotal + cAmnt; // cAmnt can be negative when making a card payment
    entries[I].set("Total", cTotal);
  }


Hope this helps someone. The above does demonstrate how to sort the rows, then looping through all the records. It is not that efficient, so I would not use this method for large number of records as performance would suffer.

If the folks working on the Wiki want to incorporate some of this, feel free to do so.

[Bill Crews]

As I read it, it seems that you sort the array of entries the way you want it, and then you go through it, noticing the breaks, and subtotaling & totaling as appropriate. This all makes sense as far as it goes.

One question would be what the end product should be -- what the user should see and how they'll see it. Your script does all that, but it has no output (that I noticed), so you set no field values in any entries of any libraries, and you don't emit any messages. Note that the entries array is always generated in entry creation DateTime order. After you sort it, it's yours, but it doesn't change the library data.

Usually, it comes down to this. Ideally, you use entries list tools to sort, group, and filter the data the way you want and use aggregation (Edit Library screen) to make the aggregation bars contain the values you want to see. If you set up groups & filters properly, you'll likely get what you want with no scripting.

If you do feel you need scripting, the output will likely need to be in library fields, in either the current or some other library, like a summary or rollup library. You'll need to use the Entry object's set() method to set those values. The messaging feature is below minimal, mainly useful just for debugging or saying "Done" or something.

So, think/learn about sorting, grouping, filtering, & filter tabs, then aggregation, and then let's talk about what works for you and what else you want beyond that.

--
You received this message because you are subscribed to the Google Groups "mementodatabase" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mementodataba...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mementodatabase/1e70c3a7-8d8c-46a1-839a-cf4b21c2bbacn%40googlegroups.com.

[Er Mo]

Hallo

Was Bill nicht erwähnt hat ist das Dashboard und Widgest . Diese könne nützliche Anzeigen sein .

Hello
What Bill didn't mention is the dashboard and widget. These could be useful advertisements.

Ernst

[Bill Crews]

Though I did look at the UI JavaScript library, I read about widgets and thought they were just tiles where you could arrange the charts & other stuff we do on entries list screens into widgets on dashboards without really adding much capability.

Based on what I've seen Ernst say over time, I think I must have underestimated the value of them and should spend some time learning about them. I'll do that, and, David, if you do, too, we can all share our thoughts on them.

David, the info on widgets is in Help; you can access it through the app. The info on the UI JavaScript library is under Scripting in the wiki.

To view this discussion on the web visit https://groups.google.com/d/msgid/mementodatabase/17ae33ec-253f-475c-bdc9-363b6097b6fen%40googlegroups.com.

[David Gilmore]

Did you miss the statement "entries[I].set("Total", cTotal);" (next to the last line)?

[Bill Crews]

Yes, I did. Sorry. I'm getting old, and mistakes like this are happening more & more often, I'm afraid.

So, you're setting the Total to the Total of all entries within each account into each entry as a running total based on the date & time. This will look good in the entries list whenever you sort that list by Account and then the DateTime field. If that's your goal, you have it.

Many would say that embedding totals into the fields of the database is bad design. If has to do with functional dependency based on the primary key or entry name. While I think most agree with that, it is one of those debatable things, and if this works for you, so be it. If the running totals could be done for you by Memento, I'd say that would be even better.

To view this discussion on the web visit https://groups.google.com/d/msgid/mementodatabase/4c389c4c-77a0-48df-bfa9-3f8fbee120a0n%40googlegroups.com.

[David Gilmore]

I am getting old to, so I understand. And I noted in my original post, this is very inefficient. But running totals are a staple of these type of programs, and if this was a proper database table with table indexes, more efficient code could be created. But as the saying goes, we use what we have rather than what we wish we had.

I spent a lot of time and effort trying to figure out how to do a running total in Memento. My purpose with this thread is to show how its done, so that others do not have to go through what just I did. And this methodology can be used for other things, such as running totals for exercise goals, and even for that recent thread regarding finding the highest sequence number. It would only require a slight modification to that Javascript to accomplish those things. I hope it will be considered a good training/education tool.

[Bill Crews]

Thank you for providing this example and description of how it's done.

Wiki development & support is in a state of crisis, and if the wiki is to live on and continue to be relevant & useful, we desperately need volunteers to join us, learn how to do wiki if you don't already know (it's sort of like ML), help to bring it up to date with new features, like new field types, widgets & dashboards, other features, and to make more complete & useful the pages, examples, etc that are already there there.

If you have any interest in helping with the wiki, please reply to me, and I will help in getting you started.

To view this discussion on the web visit https://groups.google.com/d/msgid/mementodatabase/e30991a8-0078-4c43-89a6-af85b1c0eefcn%40googlegroups.com.

[David Gilmore]

You should probably ask that question again about a month from now, after I learn and discover a lot more about Memento!

[Bill Crews]

It was meant for everyone, but definitely for you. 🙂 Maybe I'll repost it in its own thread.

To view this discussion on the web visit https://groups.google.com/d/msgid/mementodatabase/84b64340-f482-494d-9edb-5db0462cac3fn%40googlegroups.com.