Sort and sum a database

[Beth Dixon]

Hi there forum members,

I have a request for assistance.

Request for Forum Assistance

Identify the Problem

Need a method to sort a library by an alphanumeric text field and then sum all returned results by a currency field.

List the facts.

Library has a text field to be completed with alphanumeric data and a currency field.  The alphanumeric field is called Invoice No., and the currency field is called Bill Total.

Solutions?

Beth

[Ernst Moser]

Hallo
Wenn ich das richtig verstanden habe ,willst du die Bibiolthek IM Textfeld durchsuchen ( zb Nach einen Namen ) und alle gefundenen Einträgen das Zahlenfeld summieren . Was machst du mit den Ergebniss ? Wo speichern oder Anzeigen lassen ?

Dafür sind ein paar Skriptzeillen erforterlich , nichts großartiges .

Hello
If I have understood correctly, you want to search the library in the text field (e.g. for a name) and add up all entries found in the number field. What do you do with the results? Where to save or display?
A few lines of script are useful for this, nothing great.

[Bill Crews]

So (1) a method, so it's a script question. Got it. of what object? (2) sort a library by a text field. Got it. (3) sum all returned results by a currency field. Uh, returned from where and by what? Are you in a script already, maybe an action script this time, and you want some object, of what type I don't know, to have a method that returns an array of strings (the alphanumeric field of the entries) in alphabetical order ....

You know, I already think I'm way off with my questions, and I haven't even gotten to #2 yet. I'm stopping here. Could you restate your problem and facts more clearly, please?

[Gilles Mons]

Bonjour Beth,

Je pense que le plus simple est un filtre + un agrégat :

Filtre:

Entrez là où les expressions recherchées au niveau du champ "Invoice N°"

Agrégats :

Somme champ "Bill Total"

Hello Beth,

I think the easiest is a filter + an aggregate: Filtered:

Enter where the expressions you are looking for in the "Invoice N °" field

Aggregates:

Sum of "Bill Total" Field

Gilles
"Hello and thank you" are the magic words in my world....

--
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/CACx3mG_vk5YGACeaLYR2PsiXW8ts71vyXobzMdsiS8pUhDdWsQ%40mail.gmail.com.

[Beth Dixon]

Hi Gilles, yes, your solution works.  Not quite the result I am looking for, but it does work.  :)  I will see what Bill and Ernst come up with as well.  Thank you!

Beth

[Beth Dixon]

Hallo Ernst, ja, das zu durchsuchende Feld enthält eine Rechnungsnummer. Die Rechnungsnummer kann alphanumerisch sein. 

Die Rechnung kann mehrere Artikel enthalten. Jeder Artikel hat ein anderes Steuerkennzeichen pro kanadischen Steuern und muss daher separat eingegeben werden. Jeder Artikel hat einen Preisbetrag, der in das Feld Rechnungssumme eingegeben wird. 

Ich möchte in der Lage sein, die Datenbank nach der Rechnungsnummer zu durchsuchen (was ich mit der Suchfunktion von Memento getan habe), und dies funktioniert hervorragend, aber ich kann aus den zurückgegebenen Ergebnissen keine Summe des Felds "Rechnungssumme" erhalten. Es sagt mir nur, dass 5 Einträge gefunden wurden. 

Die Ergebnisse könnten also in einem anderen Feld dargestellt werden, das meiner Meinung nach Rechnungssumme heißt. 

Ich glaube nicht, dass ich möchte, dass ein Auslöser die ganze Zeit läuft. Ich möchte, dass die Option wählt, wann sie ausgeführt werden soll. Derzeit habe ich die Einträge für die Rechnungssumme manuell hinzugefügt, um die Richtigkeit zu gewährleisten, da die Gesamtsumme in Memento der Gesamtsumme auf der Rechnung entspricht. Deshalb dachte ich, dass es einen einfacheren Weg geben muss, dies zu tun. Gilles Vorschlag wird funktionieren, aber ich bin gespannt, ob es eine JavaScript-Methode gibt, die auch funktioniert.

Hi Ernst, yes, the field to be searched contains an invoice number.  The invoice number can be alphanumeric.  

The invoice can have several items.  Each item has a different tax code per Canadian taxes and thus must be entered separately.  Each item has a price amount which is entered in the Bill total field.

I want to be able to search the database for the invoice number (which I have done using Memento's search feature) and this works great  but, I cannot get a sum of the Bill Total field from the results returned.  It just tells me 5 entries were found.

So, the results could be represented in another field called Invoice Total I think.

I don't think I would want a trigger to run all the time.  I would want the option to choose when to run it.  As it stands right now I have been manually adding the Bill Total entries to ensure accuracy in that the total in Memento equals the total on the invoice.  Hence, why I thought there must be a simpler way to do this.  Gilles suggestion will work, but I am interested to see if there is a JavaScript method that also will work.

--

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/14c8387c-70ca-4371-b50d-51e3815ab274n%40googlegroups.com.

[Beth Dixon]

?? Hmmm, ok, I am absolutely confused by your response.  Arrays?  Objects?  I will try and explain better.

I have two fields in my library that are relevant to my query.  These fields are as follows:  I have bought something and need to record the purchase in my Expense library.  I may have purchased 5 items which are all listed on the stores invoice as separate line items. 

I create an entry in the Expense Journal library and enter the store's invoice number into my Invoice No text field. Note, I used a text field for this because some invoices are alphanumeric.  I then enter the details of the item purchased and finally in my Bill Total currency field I enter the total for that one item.

I then repeat the above steps for the remaining items.  Each item needs a separate entry in the database in order for the item to be allocated to the appropriate tax code as per Canada revenue.

Now, at present, I have to manually add up the Bill Total of each entry (on the phone calculator) to obtain a total and verify that all entries add up to the invoice total.  

You are probably thinking that if each entry total matches the line item on the invoice what's the problem?  The problem is that VAT's (value added taxes) are not applied on all items and the store invoice does not separate this out on their invoice. So the only way to confirm accuracy is to manually add up the Bill Total field for all entries related to the invoice number.

So, Gilles provided a solution in which I can use a filter to obtain the sum of the field Bill Total for a particular invoice.   

Is there a solution with JavaScript?  So I wouldn't have to get out the calculator and manually add everything or create a filter and reset each time with an invoice number.

If there is a JavaScript solution I could create another currency field and call it Invoice Total, and have the result displayed there.

Beth

On Sun., Nov. 15, 2020, 4:11 a.m. Bill Crews, <bill....@gmail.com> wrote:

So (1) a method, so it's a script question. Got it. of what object? (2) sort a library by a text field. Got it. (3) sum all returned results by a currency field. Uh, returned from where and by what? Are you in a script already, maybe an action script this time, and you want some object, of what type I don't know, to have a method that returns an array of strings (the alphanumeric field of the entries) in alphabetical order ....

You know, I already think I'm way off with my questions, and I haven't even gotten to #2 yet. I'm stopping here. Could you restate your problem and facts more clearly, please?

--
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/b9380bc4-388c-4c60-a104-c038af73ef9dn%40googlegroups.com.

[Er Mo]

Hallo
Ich habe verstanden wie du die Daten sammelst , Aber was machst du mit den Ergebnis ?
Zum Skript A :
 Du hast in der Bibiolthek einen " Play Pfeil" . Wenn du darauf Klickst öffnet ein Fenster wo du die "Rechnungsnummer" eingibst . Das Programm durchsucht die Bibiolthek nach der " Rechnungsnummer" und summiert den Betrag , der Angezeigt wird.
Zum Skript B :

Du hast einen Eigene Bibiolthek ( Rechnungen ) in der für jeden Kunden eine Rechnung angelegt wird , die verschietene Daten enthält und auf Befehl hin werden die Daten von der Ersten Bibiolthek aktualisirt (Datum der Aktualisirung / Summe)

Hello
I understand how you collect the data, but what do you do with the result?
About script A:
  You have a "play arrow" in the library. If you click on it, a window opens where you enter the "invoice number". The program searches the library for the "invoice number" and adds up the amount that is displayed.
About script B:
You have your own library (invoices) in which an invoice is created for each customer that contains various data and on command the data is updated by the first library (date of update / total)

[Gilles Mons]

Ernst, Which script A and B are you talking about ....?

Gilles
"Hello and thank you" are the magic words in my world....

To view this discussion on the web visit https://groups.google.com/d/msgid/mementodatabase/bc50b9ec-a164-4b42-9341-de54ce4ac3c4n%40googlegroups.com.

[Bill Crews]

A trigger script, I think -- Before saving & synchronous. Expenses are actually invoices -- no cash & receipt expenses (petty cash).

As for arrays and so on, yet again, I misunderstood you, so I thought you wanted to call JS from JS & receive the updated entries as Entry objects in an array & and that you would do what you want with the resulting entries, probably hoarding. No matter now. I am no longer misunderstanding. I don't use the word method with my JS friends, I guess, as long as none similar words will do; I can think of around 6 others. I misunderstood and responded automatically, so there was no conscious thought about it. Enough of all this.

So, you just want your totals summed up & updated in the library by the script, right?

So, your multiple items are side-by-side in the entry? And the total, as well, rather than a grand total across all invoices? By item, you mean invoice (paragraph 3 at end), right? The items are invoice items, and the total is 1 field? If so, try something like this:

var e = entries();

// Replace itemx with your field names, x = 1-5

var billTotal = 0;

for (var x = 0; x < entries.length; x++) {

    billTotal = e.field("item 1") + e.field("item 2") + e.field("item 3") + e.field("item 4") + e.field("item 5");

    e.set("Bill Total"), billTotal);

    }

[Er Mo]

Hallo Beth
Hier das Skript . In Zeile 2 und 3 sind die " F1 ,F2 " durch den Feldnamen zu ersetzen .
F1 = Das Feld das die" Rechnungsnummer " enthält
F2 = Das Feld mit den Betrag ( Wärung )

Du Erstellst einen Neue Auslöser in " Einstiegsaktion " , in den kommt das Skript . Wenn du auf den " Play Pfeil " klickst werd die Summe ( F2 ) von dieser Rechnungsnummer ( F1 ) angezeigt.

Hello Beth
Here is the script. In lines 2 and 3, "F1, F2" must be replaced by the field name.
F1 = The field that contains the "invoice number"
F2 = The field with the amount (currency)
You create a new trigger in "Entry Action", in which the script comes. If you click on the "play arrow" the sum (F2) of this invoice number (F1) will be displayed.

var e=entry()
var f1 = "F1"
var f2 = "F2"
var zahl1 = 0
var argf1 = e.field(f1)
var libclients = lib();
var entries = libclients.entries(f1);
var l = entries.length;   
for (var j=0;j<l;j++){
var sintex =entries[j].field(f1);
if (sintex == argf1){
var zahl =entries[j].field(f2);
zahl1 = zahl1 +zahl
}
}
message (zahl1)

[Bill Crews]

Beth, are your items & totals all in one library as I assumed, or are line items in a separate library, as Ernst appears to be assuming? Also, what's the significance of the bill # in all this? You might want to set unique the library, if bill # is entry name, but otherwise, I don't know of anything else.

To view this discussion on the web visit https://groups.google.com/d/msgid/mementodatabase/0143b8b7-4d75-48a7-9819-6d1b04aaa604n%40googlegroups.com.

[Beth Dixon]

Großartig, ja, das funktioniert ernsthaft. Ich tippe auf das Suchsymbol, tippe dann meine Rechnungsnummer ein, um alle Einträge für diese Rechnung aufzurufen. Dann tippe ich auf einen Eintrag und unten erscheint eine kleine schwarze Nachricht mit der Rechnungssumme. Perfekt!

Great, yes, that works Ernst.  I tap on the search icon, then type in my invoice number to bring up all entries for that invoice, then I tap on an entry and a little black message pops up at the bottom with the invoice total.

Perfect!

To view this discussion on the web visit https://groups.google.com/d/msgid/mementodatabase/0143b8b7-4d75-48a7-9819-6d1b04aaa604n%40googlegroups.com.

[Beth Dixon]

Hi Bill, yes, both fields are in the Expense Journal library.  Invoice No isn't the name.  The significance of the invoice number in accounting is to track items purchased via the suppliers invoice number in case there are concerns.   This could be in regards to a improper price charge on the invoice, an item that needs to be returned, a balance owing etc.  

Beth

To view this discussion on the web visit https://groups.google.com/d/msgid/mementodatabase/CAORXpsTQ1k5PwekT%2BVFigzCfP6XsfEAV9%2BUBZXvFBjLwtAJBfA%40mail.gmail.com.

[Bill Crews]

I know what a bill is and what a bill # is, just not why it is needed in this implementation. For instance, if one wanted to use Memento search, maybe the entry name is needed; I've never used Memento search in script, only in the UI.

By the way I noticed a few references to the word bill and your total is called Bill Total, so I switched to bill #, but now you & Ernst keep using invoice number, so what gives? So long as we all know that bill and invoice are synonyms, it doesn't matter, but why not use what you use in Memento? Besides, it's my name. 😀

OK, then, let me know if my code works for you.

-Bill

[Er Mo]

Hallo Beth
Für ihre vorgehensweise habe ich eine 2 Version ( für Fortgeschrittene :) ) . Erstellen sie eine Aktionsskript , diese mal in " Sammlungsaktion " . Kopiren sie das Skript hierher und ersetzen die Zeile 5 durch
var argf1 = arg("F1")
Im rechten Bereich unter " Argumente " Erstellen sie ein Text Feld mit den Namen " F1 " ( Bein änder von F1 beite ändern ) . Speichern .

Sie müssen in Habtteil einen " Play Pfeil " haben , der Öffnet ein Fenster in den das Feld " F1 " ist . Hier können sie die " Rechnungsnummer " eingeben und nach den speicher wird die Summe angezeigt .

Hello Beth
I have a 2 version of your approach (for advanced users :)). Create an action script, this time in "Collection Action". Copy the script here and replace line 5 with
var argf1 = arg ("F1")
In the right area under "Arguments" create a text field with the name "F1" (change if F1 is to change). To save .
You must have in part a "Play Arrow" that opens a window with the "F1" field. Here you can enter the "invoice number" and the total will be displayed after the memory.

[Beth Dixon]

French and German translations below.

So, you have all provided me with solutions.  What a great team effort! Thank you so much everyone.    Bill, I will be entering all solutions in my Memento Notebook as we discussed.

Ps...Bill, I haven't tried yours yet, and will do so shortly.

Ernst, clicking on any entry returns a little black message at the bottom of my screen.  Is there a way to have it so that it only runs when I tap the search button and the result matches an invoice number?  If not, no worries.  This still works for me.

Französische und deutsche Übersetzungen unten. Sie haben mir also alle Lösungen geliefert. Was für eine großartige Teamleistung! Vielen Dank an alle. Bill, ich werde alle Lösungen in mein Memento-Notizbuch eingeben, wie wir besprochen haben. Ps ... Bill, ich habe deine noch nicht ausprobiert und werde es in Kürze tun. Wenn Ernst auf einen Eintrag klickt, wird am unteren Bildschirmrand eine kleine schwarze Nachricht angezeigt. Gibt es eine Möglichkeit, es so auszuführen, dass es nur ausgeführt wird, wenn ich auf die Suchschaltfläche tippe und das Ergebnis mit einer Rechnungsnummer übereinstimmt? Wenn nicht, keine Sorge. Das funktioniert immer noch bei mir. Beth

Traductions en français et en allemand ci-dessous. Donc, vous m'avez tous fourni des solutions. Quel bel effort d'équipe! Merci beaucoup à tous. Bill, je vais entrer toutes les solutions dans mon carnet de notes comme nous en avons discuté. Ps ... Bill, je n'ai pas encore essayé le vôtre, et je le ferai bientôt. Ernst, cliquer sur une entrée renvoie un petit message noir en bas de mon écran. Existe-t-il un moyen de le faire pour qu'il ne s'exécute que lorsque j'appuie sur le bouton de recherche et que le résultat correspond à un numéro de facture? Sinon, pas de soucis. Cela fonctionne toujours pour moi. Beth