Search code examples
google-app-maker

How to 'Add / Sum / Total / Calculate' dynamic tables in google app maker

It's probably the simplest thing in the world but I can't seem to add/sum/calculate tables in App Maker. I have attached an image of the App preview. I will try to keep this simple. The page has 3 data sources as follows:

Apex_customer_po - This is where our employee's input customer purchase orders including the PO number (string), the overall value of the PO (number), and the details (string)

Apex_customer_po_wo_details - Purchase orders are often broken up by our customer in to multiple work orders (WO), a task break down each with it's own values including Date (date), WO number (string), WO amount (number), details (string)

Apex_customer_po_wo_costing - This is where we keep track of what work we've done and how much of each work order's money we've used. The fields include a reference number(string), parts price (number), Part details (string), Labour hrs (number), Labour rate (number), Total labour (number), and Invoice total (number), Overall total of all invoices for the work order (number)

My data relations are as follows:

  • Apex_customer_po
  • Apex_customer_po_wo_details (One Apex_customer_po to Many Apex_customer_po_wo_details)
  • Apex_customer_po_wo_costing (One Apex_customer_po_wo_details to Many Apex_customer_po_wo_costing)

Shows the general configuration of the PO app preview mode. I includes 2 work orders each with their associated invoices Shows the general configuration of the PO app preview mode. I includes 2 work orders each with their associated invoices

Shows the general configuration of the Layout view of the app page Shows the general configuration of the Layout view of the app page

I have figured out how to calculate the cost of my labour:

@datasource.item.Labour_total = @datasource.item.Labour_time * @datasource.item.Labour_rate

I have figured out how to calculate the total of each invoice: 

@datasource.item.Invoice_total = @datasource.item.Parts_amount + (@datasource.item.Labour_time * @datasource.item.Labour_rate)

But I can't for the life of me figure out how to 'Add / Sum / Total / Calculate' the individual invoices from each Work Order to get a total value.

I've tried unsuccessfully to use reduce and a bunch of other methods, but I can't ever seem to get the syntax correct even on basic 1+1 type calculations. I don't have much experience in app maker but I've been doing fine and loving it... until now.

Update 001:

I have managed to get a reduce function to perform a simple table addition for me based off the following blog post. blog.supportgroup.com/google-app-maker-cold-calculations - And the ball is rolling again. Now I need to rework how my data sources are set-up changing the numbers to strings –

Update 002:

I managed to get the reduce function to work in my app. However it totals the values from the selected cells across the data source. For example I have a PO, lets call it PO#1 and there are 5 items that total $5. When I start a new PO#2, it carries over the total of PO#1. So it calculates the entire data source regardless of the fact that it's 2 different PO#'s and I don't know how to make it stop. The code I have used is as follows

@datasource.item.Total = getFormatMoney((@datasources.Apex_customer_po_wo_costing.items).reduce((b,a) => Number(a.Parts_amount) + (Number(a.Labour_rate) * Number(a.Labour_time))+ Number(b) , 0   ),2,",",".")

The getFormatMoney(),2,",",".") is a client side java script that formats the currency

Thanks in advance for the assistance.

UPDATE 003:

I am now trying to do this in a different way with a calculated Data source and I am following this tutorial:

YouTube 7.33min long - Totals in Drive Tables tutorial

I have re-created the app step by step but I am getting the following error regarding my SQL query code:

E Mon Nov 18 13:38:49 GMT-700 2019 Exception: Malformed SQL. More information: Error with SQL statement: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near var totals = (wholesale:0, retail:0, profit:0); var records = app.models.autos. at line 1.

E Mon Nov 18 13:38:49 GMT-700 2019 Executing query for datasource totals: (Error) : Malformed SQL. More information: Error with SQL statement: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near var totals = (wholesale:0, retail:0, profit:0); var records = app.models.autos. at line 1.

E Mon Nov 18 13:38:49 GMT-700 2019 Executing query for datasource totals failed.

My code in my data source is as follows:

var totals = {wholesale:0, retail:0, profit:0};

var records = app.models.autos.newQuery().run();

records.forEach(function( item ){
  totals.wholesale += item.wholesale;
  totals.retail += item.retail;
  totals.profit += (item.retail - item.wholesale);
});

var record = app.models.totals.newRecord();
record.wholesale = totals.wholesale;
record.retail = totals.retail;
record.profit = totals.profit;
return [record];

Anyone have any thoughts on what the mistake is?

Update 004

Success - The data source type needed to be a "Calculated" type not a "Calculated SQL". Now I am going to see if I can apply this new type of calculation to my existing problem.

Solution

  • It seems like the way to go on this is the calculated model route the tutorial I linked in Update 003 is a solid path to go down.