How to produce a response from a query that performs calculations across multiple rows in a database?

[Kafui Adjogatse]

I have a database that has amounts owed between different people but want to have a query that provides netted/summed amounts that involve the active user. My current database structure is as follows:

On the user's dashboard page I want any amounts/database entries that involve them as debtor or creditor to be shown in a query. The Before Send mapping I have mapped a local storage user variable to the where parameter using the JS [return{"$or":[{"Creditor":"'+value+'"},{"Debtor":"'+value+'"}]}'].

I’ve been able to produce responses on a row by row basis mapped to button components, however, where there are multiple amounts between the same pair of users, I would like the response to calculate a net amount. The response would also then be dependent on whether the user owes or is owed the final amount.
I imagine I may need to use a combination of if/else and for statements, but I’m struggle to gain any progress. Please can you help me with what JS I need and also whether my mapping is correct. My current Success mapping is as below:

[Serhii Kulibaba]

Hello Kafui,

You should use JS on mapping parameter $ to the label.

all item's fields (Debtor, Creditor, etc) will be able as parameters of "value" object:
e.g:
value.Creditor

[Kafui Adjogatse]

Hi Sergiy,

In terms of getting the responses to perform calculations, how should I proceed? For example, assuming Kaf as the user, the responses should be grouped by the other party (whether debtor or creditor) such that in the above database pick, the final amount for Dave would take into account that Kaf owes Dave £300 and Dave owes Kaf £200.

I've thus far assumed that I need to establish a new variable for the net amount that performs the calculations on the "value" objects, but getting them to group is proving difficult. How do I do this?

Once I can't establish this variable, I would look to have an if/else function for the output in the manner of:

if (netAmount0) {
return counterparty + " owes you £" + netAmount;
}
else{
return “You owe ” + counterparty + “ £” +abs(netAmount);
}

[Kafui Adjogatse]

My current full JS code is as follows:

var cps = [];

function search(oppo) {
for (j = 0; j < cps.length; j++) {
if (oppo === cps[j].cpty) {
return true;
}
}
}

function add(cpty, netAmount) {
cps[cps.length] = {};
this.cpty = cpty;
this.netAmount = netAmount;
}

for (i = 0; i < data.length; i++) {
if (localStorage.User === value.Creditor) {
var oppo = value.Debtor;
if (search(oppo)) {
while (oppo === cps[j].cpty) {
cps[j].netAmount += value.Amount;
}
} else {
add(oppo, value.Amount);
}
} else {
var oppo = value.Creditor;
if (search(oppo)) {
while (oppo === cps[j].cpty) {
cps[j].netAmount += value.Amount;
}
} else {
add(oppo, value.Amount);
}
}
}

for (j = 0; j < cps.length; j++) {
if (cps[j].netAmount 0) {
return cps[j].oppo + " owes you £" + cps[j].netAmount;
} else {
return "You owe " + cps[j].oppo + " £" + cps[j].netAmount;
}
}

[Serhii Kulibaba]

You can execute all calculations on service's success event.

1) Create storage variable which has all neccessary fields
2) Add success event handler for service and save full service response to that variable
3) Add mapping from storage to page components on service complete event