如何在Google App Maker中“添加/求和/总计/计算"动态表 [英] How to 'Add / Sum / Total / Calculate' dynamic tables in google app maker

问题描述

这可能是世界上最简单的事情，但是我似乎无法在App Maker中添加/求和/计算表.我已附上应用程序预览的图像.我会尽量保持简单.该页面具有3个数据源，如下所示:

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 -在这里，我们的员工输入客户的采购订单，包括PO编号(字符串)，PO的整体价值(编号)和详细信息(字符串)

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 -客户经常将采购订单分解为多个工作订单(WO)，任务将每个任务分解为具有自己的值，包括日期(日期) )，WO号(字符串)，WO数量(数字)，详细信息(字符串)

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 -在这里，我们可以跟踪已完成的工作以及已使用的每个工作单的金额.这些字段包括参考编号(字符串)，零件价格(编号)，零件明细(字符串)，工时(编号)，人工费率(编号)，总人工(编号)和发票总额(编号)，工作订单的所有发票(编号)

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)

我的数据关系如下:

• Apex_customer_po
• Apex_customer_po_wo_details(一个Apex_customer_po到许多Apex_customer_po_wo_details)
• Apex_customer_po_wo_costing(一个到很多Apex_customer_po_wo_costing的详细信息)

• 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)

显示PO app预览模式的常规配置.我包括2个工作单，每个工作单及其关联的发票

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

我已经弄清楚了如何计算人工成本:

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.

我尝试使用reduce和其他方法失败，但是即使在基本的1 + 1类型计算中，我似乎也无法正确获得语法.我在应用程式制作工具方面经验不足，但直到现在为止我一直做得很好并且喜欢它.

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.

更新001:

根据以下博客文章，我设法获得了reduce函数，以便为我执行简单的表添加. blog.supportgroup.com/google-app-maker-cold-calculations -球又滚了.现在，我需要重做如何设置数据源，将数字更改为字符串–

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 –

更新002:

我设法使reduce函数在我的应用中正常工作.但是，它将对数据源中所选单元格的值求和.例如，我有一个PO，将其称为PO#1，共有5个商品，总计$ 5.当我启动一个新的PO#2时，它将继承PO#1的总数.因此，无论它是2个不同的PO#，而且不知道如何停止它，它都会计算整个数据源.我使用的代码如下

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,",",".")

getFormatMoney(),2,",",".")是用于格式化货币的客户端Java脚本

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

预先感谢您的帮助.

更新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.33分钟-云端硬盘表总数/a>

YouTube 7.33min long - Totals in Drive Tables tutorial

我已逐步重新创建了该应用程序，但我的SQL查询代码出现以下错误:

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 GMT-700 2019异常:SQL格式错误.更多的 信息:SQL语句出错:SQL出错 句法;检查与您的MySQL服务器版本相对应的手册 在第1行的var totals = (wholesale:0, retail:0, profit:0); var records = app.models.autos.附近使用正确的语法.

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正在执行数据源查询 总计:(错误):格式错误的SQL.详细信息:SQL错误 声明:您的SQL语法有误；检查手册 对应于您的MySQL服务器版本以使用正确的语法 在第1行的var totals = (wholesale:0, retail:0, profit:0); var records = app.models.autos.附近.

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正在执行数据源查询 总计失败.

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?

更新004

成功-数据源类型必须是已计算" 类型，而不是已计算SQL" .现在，我将查看是否可以将这种新型的计算应用于我现有的问题.

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.

推荐答案

看来，这是我在Update 003中链接的教程是计算的可靠路径，是经过计算的模型路线.

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.

这篇关于如何在Google App Maker中“添加/求和/总计/计算"动态表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！