Summarizing totals

Wappler General How To
#1

Hi,
I’m looking for a tutorial or some guidance on how to do the following work in a server action. I imagine that with the power of Wappler, this s/b easy to accomplish, but I don’t know how.

Starting Point:
Let’s say I have two tables: ITEM and SUMMARY.

To drastically simplify the question, let’s say the tables have the following structure:

ITEM
- idCLIENT
- Amount

SUMMARY
- idCLIENT
- Total

There is a one to many relationship between SUMMARY and ITEM. Also, let’s assume for now that I only need to sum amounts from one table into the other.
More specifically, at the end of my server action, SUMMARY should have one and only one record for each idCLIENT and its field Total should contain the sum of all the Amount fields in the ITEM table that belong to that Client. All the work needs to happen on the server side – nothing on the client side.

For example:
Before the server action, let’s say ITEMS contains 4 records:

  1. Client A 1,000
  2. Client B 1,200
  3. Client A -250
    4 Client B -800

At the end of the server action, SUMMARY should have 2 records as follows:

  1. Client A 750
  2. Client B 400

Any pointer in the right direction will be appreciated.

Many thanks!

Alex

#2

Answering my own question:

Step 1: Create a numeric variable and initialize it to 0. Let’s call it LastClientID. We’ll use this to compare it against the record IDs later, and these are usually > 0 if you use the auto-increment function in your database.

Step 2: Create a query to the ITEM table, add the necessary filtering conditions, and sort it by Client

Step 3: Add a Repeat function, feeding it from a query to the ITEM table.

Step 4: In the exec part of the Repeat, add a condition to determine if the ClientID > LastClientID.

Step 5a: If the condition above is true, insert a new record in the SUMMARY table. I did not manage to add in the insert command, so I created another variable to summarize the totals and then set the values of the record to that variable – but I imagine that this s/b doable within the insert command. That said, it took me awhile to make this part work. Set the value of LastClientID to the identity created by the insert. This is important so the next iteration of the Repeat can work properly.

Step 5b: If the condition is false, update the SUMMARY record whose ClientID equals LastClientID

At the end of the Repeat loop, SUMMARY will have one record per Client, with the other fields set as per the calculations.

Hope this helps,

Alex