Database Calculations

General
1

Good day,
This is not a difficult question for most, but I am struggling…
I am creating a POS system, I have two data tables:

  1. Customers:
  • Customer Name
  • Surname
  • TotalDue
  1. Invoices:
  • Name
  • Surname
  • Price
  • Customer_ID

The first table is only, the customer details, with a column for TotalDue.
The second table logs all transactions made on the platform, but links the transaction to the Object_ID of the customer table, and stores it as Customer_ID in transactions table. So each ‘Transaction’ has the customer ObejctID pulled through.

All I want to do, is sum the “Price” column per customer_ID, and store the summed value in the Customers table in the TotalDue column, where the Customer_ID from ‘Transactions’ matches the Obejct_ID from Customers. This needs to happen live, as the app will be launched on multiple devices simultaniously.

2

Hello @Anrich_Piek

There are different ways to solve your task depends on your needs.

You could made an request with needed grouping and query:

Sum('Price') as 'total'

And group by Customer_ID

Or you can create a view with same logic and send requests to it.

Also I could advice you to use relations, not only string ids, it will help you when your app will grow.

3

Thank you, I did attempt this both through the Rest Console, as well as UI Builder, but my difficulty is displaying and saving this value in my ‘Customers’ data table.

4

Are you sure that the decision to save these values is a good idea?
With the View approach, you always get the live data, but with saving these values, you need to carefully maintain them and always update, handling deletion of records also will be a task.

If you are sure in your way, just use the save record method. You could share your code/codeless blocks, and I will help you with that.

5

Hi Dima,
I have managed to create relations between my Customers and Invoices table, and it is working well. However, I am still struggling to sum the values of the orders for each customer and display the TotalDue in the customer table.

image
image1057×649 36.1 KB

This is my current workflow.

6

Hello @Anrich_Piek

I see “ObjectId” column name you use in blocks. Where is from this column name? Is it custom column in the “Customers” table? Or you tru to use system “objectId” column?