calculation on the sum of a field

[TC Freeman]

Hello, I have a library with a mileage field and am able to see the sum of that field using aggregation.  I was wondering if it's possible to perform a calculation on the sum of my mileage field? I can't find a way to reference the sum of the mileage field in a calculation field.

[Er Mo]

Hallo

Die Summe ist kein " Wert " sonder wird immer neu berechnet . Wenn du mit der Summe Rechne willst muss du sie selber errechnen . Das muss man in einem Skript machen .

Hi
The sum is not a "value" but is always recalculated. If you want to calculate the total, you have to calculate it yourself. You have to do that in a script.

Ernst

[TC Freeman]

I don't know how to code JavaScript.  Are there any good tutorials to learn how to do something like this?  I'm willing to learn.  Thanks!

[Bill Crews]

Just search the forum, and you can read any of several messages I've sent recommending the use of Google's Grasshopper app for learning JavaScript. There's more to say, but it's in the previous messages, so I'll quit driving everyone batty with me repeating it. You must be new to the forum.

Just go to the Google Play store, install the app on your Android device -- I'm not sure if it's available for the PC -- take the Fundamentals 1 & Fundamentals 2 courses. All you need to know for Memento is fundamental JavaScript, so you don't have to get into how to program a Web site unless you really want to. Read one of my previous messages for the rest.

--
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/7fce7c8d-91d5-4cf6-976a-7065573ec85cn%40googlegroups.com.

[TC Freeman]

Thank you very much!  This is exactly what I'm looking for.  I will take Fundamentals 1 & 2 and search for your previous posts about JavaScript and Memento.  Yes, I am new to this group since recently having more questions about a database I have been working on for the last 6 or 7 months. 

[Bill Crews]

No problem at all. I'm glad you see a good path through this. Let us know if we can assist further.

To view this discussion on the web visit https://groups.google.com/d/msgid/mementodatabase/5ecc99da-ba67-43ad-a96c-0d39adffbd50n%40googlegroups.com.

[TC Freeman]

I've completed JavaScript fundamentals 1 & 2  in the Grasshopper app and have watched several videos on learning JavaScript for beginners and have been successful at writing a few if else statements to make some calculations in my database but I'm struggling with making a for loop to loop through all the records and sum up a mileage field. I'm using the following code from the wiki.

var amountField = "Amount"; // CHANGE Amount to your amount field name

var linkedEntries = field("Them"); // CHANGE Them to your link field name

var numLE = linkedEntries.length; // The number of linked entries

// Loop through linked entries, creating the sum

for (var lex = 0, result = 0; lex < numLE; lex++)

    result += linkedEntries[lex].field(amountField);

result; // The result is the SUM

I guess I'm not understanding what the difference is between the "amount field name" and the "link field name". I can't get the linkedEntries variable to equal the total amount of records in my library so it ends up looping through the number of characters of whatever value I try and assign to that variable. I hope this makes sense. I'm assigning my milage field to the amountField variable and am uncertain what to assign to the linkedEntries variable. I've tried the name of my library but it only loops through 8 times which is the amount of characters. It needs to loop 143 times (amount of records in the library).

Thanks for any help on this

[Bill Crews]

I'm struggling with making a for loop to loop through all the records and sum up a mileage field. I'm using the following code from the wiki.

var amountField = "Amount"; // CHANGE Amount to your amount field name

var linkedEntries = field("Them"); // CHANGE Them to your link field name

var numLE = linkedEntries.length; // The number of linked entries

// Loop through linked entries, creating the sum

for (var lex = 0, result = 0; lex < numLE; lex++)

    result += linkedEntries[lex].field(amountField);

result; // The result is the SUM

Excellent that you've taken the JavaScript (JS) courses on Grasshopper! Remember the caveats regarding the mobile edition's limitations due to its use of an old version of JS. I talked about them in previous forum posts. They have to do with the old JS's need for you to use for..in rather the newer for..of form of the for statement. Also, use var on mobile rather than either let or const for the same reason; they are newer.

And YES, after learning JS, the next task is to then learn how to apply that new knowledge to handling Memento using JavaScript fields on one hand and the features supported by the Memento JavaScript Library on the other -- actions, triggers, and custom data sources. There are also some useful JS pages in the About column of the index -- Trigger Examples, Tips:Using JavaScript in Memento, and How:Write scripts in JavaScript for Memento. You should probably read them in that order, though I'm not sure, as there've been a few years since I wrote them, and I don't remember it all.

You don't have to use my variable names & field names so long as you keep yours coordinated with mine & consistent with each other. But let's use mine, since I don't know yours ...

var amountField = "Amount"; // CHANGE Amount to your amount field name

var linkedEntries = field("Them"); // CHANGE Them to your link field name

var numLE = linkedEntries.length; // The number of linked entries

// Loop through linked entries, creating the sum

for (var lex = 0, result = 0; lex < numLE; lex++)

    result += linkedEntries[lex].field(amountField);

result; // The result is the SUM

 

So, let's say you've got a produce stand along a road somewhere, and you're selling peaches, among other things. Depending on how big your operation gets to be, you might also want to keep track of your customers, all your products, quantity on hand, and you could get into taking & fulfilling orders, and so on. Each of these would be a library ( "Customers, "Products", and "Orders". You could have warehouses ("Warehouses"), warehouse receipts ("Incoming"), bills of lading ("Outgoing") , and so on, as well.

But let's stick to that quite simple produce stand where we sell peaches, maybe among other fruits & vegetables (products). And let's say we sell those peaches in bags, paid for by the weight of the bag, in lbs. So all significant nouns will be libraries. (Insignificant ones might be varieties or sizes, which can often be handled using SCLs (single-choice lists) in a field within a library. We can talk more about all that stuff later.

You've got the main Peach Sales library, and a field in that library, "Sale Total", is the number you're after for each sale.  The amount field in the code above is your numeric field (Integer or Real (or Currency, if appropriate)), which is the amount of each individual sale in dollars, while the link field is the field that contains the link to your primary library, which is like a pointer that links to the subordinate library, "Sale".

Now in real life, you'll want to handle a quantity (# of lbs), a price (dollars per pound) and multiply the quantity times the price to get the sale amount, but who knows? You might negotiate prices and that would change things, so again, let's stay very simple, like the example in the wiki is.

Almost done now. So, to get a list of the sales of oranges, you need to add up the list of all the individual sales and total them. That's what "Sale Total" (in Peach Sales) is (a JS field in Peach Sales, and this is set by the script wherein it runs (loops) through all the individual sales (the entries in the subordinate, linked library).

So there has to be a field that holds the link itself. It's not an amount or price or total, but a Memento construct that gets the amount for each sale (entry) that's in the Sale library adds it into the Sale Total field that is initially set to zero and for each entry, the Sale is added into the Sale Total that, when the loop is finished, ends up as the sum that will be returned as the value of the script (the value of the JS field Sale Total).

I can't get the linkedEntries variable to equal the total amount of records in my library

That all done by that one statement: var numLE = linkedEntries.length;

numLE is the number of entries ("Sale"s). entries().length is the number of things in the array (remember them from your course?). If numLE isn't the number of entries, then Memento is broken, and of course, it's not. So, all your ...

"ends up looping through the number of characters of whatever value I try and assign to that variable"

... is just wrong. Sorry. I don't know what you're doing in your code. Mine in the wiki just uses that number to determine how many array elements to loop through. It never adds to the length of the array at all. So, if your code does something else, please just copy it and paste it into this message, so we can see where you went wrong and how to fix it.

I'm assigning my milage field to the amountField variable and am uncertain what to assign to the linkedEntries variable. You don't assign any value. You're making it way too hard and complicated. The thing is already done in my code. Exept for renaming bags of peaches into miles I've tried the name of my library but it only loops through 8 times which is the amount of characters. It needs to loop 143 times (amount of records in the library).

You can just copy my code, change the name "Amount" to "Miles" and "Them" to the name of your subordinate, linked library, and then use the result to set the value of the total ("Mileage"?) field.

You have told us neither the problem you're working to solve nor the way you came up with to solve it, so except for the word "milage", I have no idea what you're doing or whether you need the separate library or not.

If this doesn't allow you to fix it, give us some hints, and we'll try to help, instead of offering examples that may not relate to your problem.

Thanks for playing!

To view this discussion on the web visit https://groups.google.com/d/msgid/mementodatabase/6b2b6b9f-81af-42d3-a2a4-68b988aff69cn%40googlegroups.com.

[TC Freeman]

Bill,  thanks for the detailed reply!  I'm going to have read through all the links when I get some time to really sit down and study this.  I drive a semi truck so it's hard to find the time to work on my database and learn how to write JS but eventually I'll get through it.

What jumps out at me is when you talk about changing "Them" to the name of your subordinate, linked library.  My JS field is in the same library(primary) as my mileage field so it's hard for me to understand linking it to a subordinate library.

Here is the structure:

My Loads(primary library)

     Linehaul Miles(numeric integer field)

     sumMilesjs(this is my JavaScript field)

Addresses(subordinate library)

Trailers(subordinate library)

The following code for sumMilesjs returns "3392" when it should be much higher like "54785" according to the aggregation(sum by Linehaul Miles).  It should be looping through 143 times.  The last several entries for Linehaul Miles has been "424" so if it's starts looping from the most recent entries then it looks like it looped 8 times.  424 x 8 = 3392  Also, 8 is the number of characters in "My Loads".  Or maybe it's just taking the value of field('Linehaul Miles') from that one entry and adding it to itself 8 times. I'm not sure.

var amountField = field('Linehaul Miles'); // CHANGE Amount to your amount field name
var linkedEntries = "My Loads"; // CHANGE Them to your link field name

var numLE = linkedEntries.length; // The number of linked entries // Loop through linked entries, creating the sum

for (var lex = 0, result = 0; lex < numLE; lex++) result += amountField;

result; // The result is the SUM

If I change the linkedEntries variable to field("My Loads") like your original code as shown below I get 7632 which if you divide by 424 you get 18 so I assume this code looped only 18 times.  If you count the semicolon that is 18 characters long. Maybe this is just coincidence.

var amountField = field('Linehaul Miles'); // CHANGE Amount to your amount field name
var linkedEntries = field("My Loads"); // CHANGE Them to your link field name

var numLE = linkedEntries.length; // The number of linked entries // Loop through linked entries, creating the sum

for (var lex = 0, result = 0; lex < numLE; lex++) result += amountField;

result; // The result is the SUM

Sorry for the ramble. I'm going back on the road and will look at all this later.

Thanks for your help!

[Er Mo]

Hallo
Ich versuche mal eine Wegbeschreibung . Du hast eine Bibliothek in der du das Meilen Feld Summiren willst . Du muss den Inhalt der Bibliothek in einen Variable laden --> Die Länge bestimmen --> In der Schleiffe jeden Eintrag durchlaufen und Summieren .

var sum = 0
var libclients = lib()  // Neme diser Bibliothek
var entries = libclients.entries()  // Alle Einträge laden
var l = entries.length;   // Anzach der Einträge
for (var j=0;j<l;j++){  // Schleiffe
var m =entries[j].field('meilen')  // Eintrag j . Feldneme Intex
var sum = sum + m
}  // Ende der Schleiffe

message (sum)   // Ergebniss

Hi
I'm trying to get directions. You have a library in which you want the Total Miles field. You need to load the contents of the library into a variable --> Determine the length --> Loop through each entry and sum.

var sum = 0
var libclients = lib() // Rename this library
var entries = libclients.entries() // Load all entries
var l = entries.length; // Number of entries
for (var j=0;j<l;j++){ // loop
var m =entries[j].field('miles') // entry j . Feldneme Intex
var sum = sum + m
} // end of loop
message (sum) // Results

Ernst

[Bill Crews]

Here is the structure:

My Loads(primary library)

     Linehaul Miles(numeric integer field)

     sumMilesjs(this is my JavaScript field)

Addresses(subordinate library)

Trailers(subordinate library)

This tells how & where they are defined, but it doesn't tell how each gets its value and how it's used.

The following code for sumMilesjs returns "3392" when it should be much higher like "54785" according to the aggregation(sum by Linehaul Miles).  It should be looping through 143 times.  The last several entries for Linehaul Miles has been "424" so if it's starts looping from the most recent entries then it looks like it looped 8 times.  424 x 8 = 3392  Also, 8 is the number of characters in "My Loads".  Or maybe it's just taking the value of field('Linehaul Miles') from that one entry and adding it to itself 8 times. I'm not sure.

var amountField = field('Linehaul Miles');

var linkedEntries = "My Loads";

var numLE = linkedEntries.length;

for (var lex = 0, result = 0; lex < numLE; lex++) result += amountField;
result; // The result is the SUM

Well, right away, I see that you have My Loads as the subordinate, linked library, which it's not. It is the primary library, so this can never work. I wish we had your library structure field definitions.

You could try screenshots, but they are often partial. I think the easiest way would be to just go into (in mobile) Edit library in each library and doing an export, which will result in a template file. If you send those (zipped or not) to us, we'll be able not only to see your definitions, but to create the libraries in our To/From Others groups and work with them if needed. That would be great.

If I change the linkedEntries variable to field("My Loads") like your original code as shown below I get 7632 which if you divide by 424 you get 18 so I assume this code looped only 18 times.  If you count the semicolon that is 18 characters long. Maybe this is just coincidence.

Well, I don't have any idea what your Link to Entry fields look like or what library each is aimed at, but I'm surprised the script even worked, as My Loads seems to be linked to itself. If lineHaulMiles is defined in My Loads, it can't be the detailed, individual, maybe dated mileages, because there's only one of them, and if there are many of them, you seem to have just as many sums; it's very confusing. So, I think we need to (1) understand better what you're trying to do & what your trying to do it with and (2) reorganize your database structure before spending much time scrutinizing the current structure.

So, if you can help us get #1 & #2 and the template files, I think we can help you get this done.

Maybe instead of the above, let me take a shot at imagining what you're trying to do, and you can correct that to whatever you like. This will make sense to me, at least...

My Time Periods...

   Interval as Date field (month, year, whatever)

   TotalMiles as JavaScript field

   Trips as Link to Entry field, many-to-many, aimed at the Trips library

Trips...

   Date as Date field

   MilesThisTrip as Integer field

   (Maybe also destinations, client, etc)

Other libraries good to know about, but not detailed

Given this definition, here would be the script for the TotalMiles JavaScript field...

var trips = field("Trips");

var numTrips = linkedEntries.length;

for (var lex = 0, result = 0; lex < numTrips; lex++)

   result += field("MilesThisTrip");

result; // The result is the SUM of all miles

We're down to 4 statements now (5 lines, because I indented). I have not tested this code; I haven't even constructed the imaginary libraries. I hope I haven't included any errors.

If My Loads is supposed to be equal to Trips (Is a load a trip?), then maybe all you need is a single mileage library with an aggregation to show the Total Miles in the bottom bar.

To view this discussion on the web visit https://groups.google.com/d/msgid/mementodatabase/af43fb51-6be5-41eb-b8d6-108fa6937d6an%40googlegroups.com.