Javascript to calculate certain fields (related to the neighbour-field)

[stjg - tmo]

Hello Community 🙂

we are a group of youth clubs, trying hard to generate a form, that calculates differnt kinds of expenditures. 
In the first tab the youth facilities can choose per dropdown-list the kind of expenses ('KONTO,' 6 different kinds, like "hospitality", "repairs", "office material"...), and in the second tab a comment and in the third tab 'BETRAG' the amount of that kind of expanse.
In the image i have given names to the fields for better understanding.

Now we try to add all values of the same kind, --> all 'exp-amount'-fields, that have been picked the same kind of "exp-kind" in the second tab dropdown-lists.

For example: When a youth club adds three lines of expenditures of the kind 'Bewirtung' (hospitality), then the values of the corresponding fields "exp-amount" should be added together and the sum be displayed in the field "sum-xy", and so on...

So for example, the sum-field of "Summe Bewirtung" should have a JS which behaves something like:

{add together all exp-amount-fields, whose line-corresponding exp-kind-fields have the name / value = '4530 Bewirtung'}

Maybe for Javascript-Experts this is only a few-lines-Script, but i fear this is way too complicated for me (and i'm not totally new to JS...). I'm into this for about 1 week now, and whenever possible i scan the internet for approaches, but it doesn't work at all 😞

Maybe someone has a little idea or tipp how we could achieve this? I would be eternally thankful as this is getting me totally insane.

Thanks for reading and best wishes 🙂

tmo

[Nesa Nurani · Accepted solution]

Put this in one of the fields as 'Custom calculation script' (make sure field names and dropdown choices are correct):

var rein = 0, klei = 0, bewi = 0, fahr = 0, lebe = 0, sons = 0;
for(var i=1; i<=11; i++){
if(this.getField("exp-kind-l"+i).valueAsString == "4251 Reinigungsaufwand")
rein += Number(this.getField("exp-amount-l"+i).valueAsString);
if(this.getField("exp-kind-l"+i).valueAsString == "4270 Kleinreparaturen")
klei += Number(this.getField("exp-amount-l"+i).valueAsString);
if(this.getField("exp-kind-l"+i).valueAsString == "4530 Bewirtung")
bewi += Number(this.getField("exp-amount-l"+i).valueAsString);
if(this.getField("exp-kind-l"+i).valueAsString == "4540 fahrt u. Reisekosten")
fahr += Number(this.getField("exp-amount-l"+i).valueAsString);
if(this.getField("exp-kind-l"+i).valueAsString == "4630 Bürobedarf")
lebe += Number(this.getField("exp-amount-l"+i).valueAsString);
if(this.getField("exp-kind-l"+i).valueAsString == "4900 sinst. Ausgabe")
sons += Number(this.getField("exp-amount-l"+i).valueAsString);}
this.getField("sum-rein").value = rein;
this.getField("sum-klei").value = klei;
this.getField("sum-bewi").value = bewi;
this.getField("sum-fahr").value = fahr;
this.getField("sum-lebe").value = lebe;
this.getField("sum-sons").value = sons;

[Nesa Nurani]

Where are the prices?

[stjg - tmo]

Hello Nesa,

sorry for expressing in a misleading way...
These forms record all expenses incurred on a youth project. The youth center employees fill in all fields in a line (tab 1 to tab 4) themselves. 

Tab 1: They fill n the date

Tab 2: They fill in the kind of expanditure

Tab 3: (optional) comments

Tab 4: the amount of money

Here is a completed example form (photoshop mockup)  for a fictitious youth offer "Trip to the amusement park".

[Nesa Nurani · Accepted solution]

Put this in one of the fields as 'Custom calculation script' (make sure field names and dropdown choices are correct):

var rein = 0, klei = 0, bewi = 0, fahr = 0, lebe = 0, sons = 0;
for(var i=1; i<=11; i++){
if(this.getField("exp-kind-l"+i).valueAsString == "4251 Reinigungsaufwand")
rein += Number(this.getField("exp-amount-l"+i).valueAsString);
if(this.getField("exp-kind-l"+i).valueAsString == "4270 Kleinreparaturen")
klei += Number(this.getField("exp-amount-l"+i).valueAsString);
if(this.getField("exp-kind-l"+i).valueAsString == "4530 Bewirtung")
bewi += Number(this.getField("exp-amount-l"+i).valueAsString);
if(this.getField("exp-kind-l"+i).valueAsString == "4540 fahrt u. Reisekosten")
fahr += Number(this.getField("exp-amount-l"+i).valueAsString);
if(this.getField("exp-kind-l"+i).valueAsString == "4630 Bürobedarf")
lebe += Number(this.getField("exp-amount-l"+i).valueAsString);
if(this.getField("exp-kind-l"+i).valueAsString == "4900 sinst. Ausgabe")
sons += Number(this.getField("exp-amount-l"+i).valueAsString);}
this.getField("sum-rein").value = rein;
this.getField("sum-klei").value = klei;
this.getField("sum-bewi").value = bewi;
this.getField("sum-fahr").value = fahr;
this.getField("sum-lebe").value = lebe;
this.getField("sum-sons").value = sons;

[stjg - tmo]

Thank you so much for having the time to help me!  🙂

I tried to adjust everything exactly according to the real values. Here in the attachment I have sent along the current version.
As you can see, the sum-fields do not yet caluculate the expanses of one kind. 

For example, when i 
--line 1: a) dropdown-pick expanse-kind: "...Bewirtung" and b) fill in amount of 25 €

--line 2: a) dropdown-pick expanse-kind: "...Bewirtung" and b) fill in amount of 10 €

--> the sum-field for "...Bewirtung" ("sum-bewi") should display 35€ - but it still displays "0,00 €"

Can you see my mistake? Or am i misunderstanding something?

(Thank you so much - even though it's not solved yet, it feels much better not to be alone with that anymore.)

[Nesa Nurani · Accepted solution]

On your first photo there are 11 fields and in your file there are 8 fields, so in script change 11 to 8.

Use script ONLY in one field and delete in other fields.

In your dropdown fields, in 'Options' tab, check 'Commit selected value immediately'.

Above changes are done in this file:

https://drive.google.com/file/d/1fEwMFEOH7LOMBIdcNxX8Aeto3ImkEcSI/view?usp=sharing 

[stjg - tmo]

You are so  A M A Z I N G  ! ! ! It works!

Incredible... I am still completely stunned. After all the days of trying and researching, you come up with the right solution in seconds. 

and: yes, there were two more exp-kinds to add, i didn't know this would crash the script. And i in fact put the script in one sum-field but it automatically showed up in all sum-fields (i was sure this is for reason).

Thank you  S O  M U C H  !  🙂  you have done me such a huge favour! 🙂