Calculations within data tables

Good day,

I am creating an invoicing system and as you can imagine there will be a couple calculations which would need to be done, namely;

  • Cost of items (cost of one unit + number of units), Cost of labour (cost per hour + number of hours).
  • Total (Cost of Items), Total (Cost of Labour).
  • VAT added ((Total items + Total Labour) x 15%).
  • Grand Total (Total items + Total Labour + VAT)

I thought the best way to break it up would be to have an “Invoice” database with a relation to a “Items” & “Labour” databases. In the “Items” & “Labour” data base there is a column for “Cost per unit” (DataType: Money), a column for “Number of Units” (DataType: Int), also a column called “TotalCost” (DataType: Money).

  1. How do I get the “Cost per unit” + “Number of Units” to automaticly calculate in “TotalCost” column?

In my Front end I would like to make it as user friendly as posible, so I have two input areas where the User can add “Items” & “Labour” sepretly and it be shown in two seperate data tables.

  1. How can I add the “Total Cost” of all the rows in the table, and show the Total amount in a text field below the table?

  2. I would then like to add the two Totals of the “Items” & “Labour” together and show in a text field, I would imagine this would be the same logic as Point 2?

  3. I would like to then save the total of Point 3 to the “Invoice” database, which i will then use to calculate VAT and the Grand Total (Total + VAT), would this be the best way to calcualte this or is there a simplre way?

  4. How can you set a currency in the money in the DataType: Money?

Kind Regards
Donovan

Good Day,

So I figured out Point 1 using Functions and Examples of Generated Columns | Backendless documentation.

But now I am struggling with Point 2, I tried the same solution as Point 1 but it does not work as it does not recognise the child column.

Kind Regards
Donovan

Hello @Donovan_Hardwick

I am glad that you have found the solution for Point 1.

For Point 2 you can use the aggregation function count COUNT - Backendless REST API Documentation but it will be the other call to db.

For Point 4 will you calculate it by scheduler like once a day, if yes then you can use backendless timer What is a Codeless Timer - Backendless Codeless Development Guide

For point 5 you can not set up currency for type Money, but you can create columns with the name of the currency, for example, invoiceUSD or invoiceEUR

@sergey.kuk Thank you for your reply.

I am still struggling with point 2, I am using codeless logic and still cannot find a way to add items in a dynamic row to show a total. The user will add units and cost of units in input field, this will automaticly give a total (Units + Cost of units), the user will then click on an “Add” button (where the logic is stored) the Units, Cost of Units and Total will all be added to a table (Dynamic list in frontend).

When the user clicks on the “add” button I would like for the Grand total to be worked out by adding each row total. Is there codeless logic which can do this?

Kind Regards

I think yes. You could get Dynamic List, iterate over them, and calculate the sum of any property you want. If you want to get help with that, you could create a page with a minimum of logic, and we will try to help you with that issue(don’t forget to provide container and page name, and app id)

Regards, Dima.

@Dima_Vak @sergey.kuk

I would really appreciate the assistance.

    1. App ID: FAB3842F-AA5A-3932-FF0C-652D56409B00
    1. Container: default
    1. Page Name: testPage

So I have added two input fields which contain logic, they add up and equal to “RowTotal”. The two buttons add and delete items in the dynamic list “RowAddedUnits”. I have not added any other logic. I have added the calculations below which I am struggling with.

  1. “TotalOfColumn” = Sum of all “RowTotal”
  2. “GrandTotal” = “TotalOfColumn” (In my application I will have two “TotalOfColumn” added up to equal “GrandTotal”).
  3. “VAT(15%)” = “GrandTotal” + 15%
  4. “GrandTotalWithVAT” = “GrandTotal” + “VAT(15%)”

Kind Regards
Donovan

Already add logic for Total Of Column. You could check it.

@Dima_Vak

Thank you it works, the only problem is if a row is deleted then the “TotalOfColumn” does not decrease.

i have also tries working out the VAT and Grand Total but keep getting a return of NaN.

Kind Regards
Donovan

Thank you it works, the only problem is if a row is deleted then the “TotalOfColumn” does not decrease.

Check if dynamic list was reassigned.

i have also tries working out the VAT and Grand Total but keep getting a return of NaN.

NaN(Not a Number) usually appears when you try to make math operations with another type of data than Number.