Trying to total columns for relation datasource

Question

I am putting together a PO Request manager.

I have created one model: PORequests

Then created a ONE to MANY relationship with another model: Items

Table on the left is the datasource: PORequests. The table on the right is PORequests:Items (relation)

So when you click on PORequest #1 on the left, you see only the items associated with that specific PO request.

Then when a user changes the quantity or cost of the item, the onValueEdit runs this code, creating an entry for the subtotal (a field in the Items model).

widget.datasource.item.Subtotal = widget.datasource.item.Quantity * widget.datasource.item.Cost;

All that works great, however now I want to add the subtoals for all the items and have it fill the Total field in the PORequest model.

How can I tell App Maker to add up all the subtotals for all items in PORequest #1?

Thank you for the help!

Update:

So I'm a little bit closer. I was able to get a label to display the total of subtotals. Stole the following Client Script from the Corporate Store template:

/**
 * Locale constant that is used for currency formatting.
 */
var CURRENT_LOCALE = 'en-US';


/**
 * Calculates and formats total cost of all POR items.
 * @param {Array<CartItem>} PORItems - list of user's POR items.
 * @return {string} formatted total cost of all POR items.
 */
function getSubtotalTotal(PORItems) {
  var cost = PORItems.reduce(function(result, item) {
    return result + item.Subtotal;
  }, 0);
  return '$' + cost.toLocaleString(CURRENT_LOCALE, {minimumFractionDigits: 2});
}

Then I put getSubtotalTotal(@datasource.items) as the text for the label.

So now I just need to figure out how to tell App Maker to take that result from the script and add it to the Total field in the PORequests datasource.

I think in part it depends on where you are storing the total for the PORequest. Is it a field within the PO request? If you are using App Maker drive tables, they do not offer aggregation so you would either need to use calculated models, or include code in your Items onCreate, onEdit, and onDelete events that recalculates the total of the PO assuming you have a PO total $ field in your PORequest model. — Markus Malessa, Oct 18 '17 at 14:44
My goal was to have it be a field in the PORequest model with (like you mentioned) some code in the onEdit, onDelete, etc. that wold recalculate the total and pop in the new amount. The only thing I can't figure out is how to tell App Maker how to sum JUST the subtotals that are related to that PO Request. — Adam Bergeron, Oct 18 '17 at 17:41

Adam Bergeron · Accepted Answer · 2017-10-27T23:18:38.483

Okay, so here is what I did:

1. I created two Google Drive Table models: PORequests and Items.

PORequests has fields like: PORequest Number, Vendor and the Total*.

*Note: The Total field had to be a String and not a number.

Items has fields like: ItemName, Quantity, Cost and Subtotal.

2. I created a ONE to MANY relationship with the two datasources (PORequests as the owner).

3. I created a page with two Tables.

Table 1's datasource = PORequests Table 2's datasource = PORequests: Items (relation)*

This way if I click PORequest #1 in Table 1 I only see Items associated with that POR.

*Note: I set the Subtotal field to "non-editable/label", so no one accidentally changes it manually.

4. Then I created a Client Script:

/**
 * Locale constant that is used for currency formatting.
 */
var CURRENT_LOCALE = 'en-US';


/**
 * Calculates and formats total cost of all POR items.
 * @param {Array<CartItem>} PORItems - list of user's POR items.
 * @return {string} formatted total cost of all POR items.
 */
function getSubtotalTotal(PORItems) {
    var cost = PORItems.reduce(function(result, item) {
    return result + item.Subtotal;
  }, 0);
  var total = cost + app.datasources.PORequests_HideArchived.item.Tax + app.datasources.PORequests_HideArchived.item.Shipping;
  return '$' + total.toLocaleString(CURRENT_LOCALE, {minimumFractionDigits: 2});
}

5. Then I set the onValueEdit for the Quantity and Cost fields to:

widget.datasource.item.Subtotal = widget.datasource.item.Quantity * widget.datasource.item.Cost;

var subtotalTotal = getSubtotalTotal(app.datasources.PORequests.relations.Items.items);
app.datasources.PORequests.item.Total = subtotalTotal;

This tells App Maker to first: multiply the cost by the quantity and put that value in the Subtotal field.

Then it tells App Maker to: use the getSubtotalTotal script to add all the Subtotal values and put THAT value in the Total field of the PORequest datasource.

Hope all that helps someone in the future.

This approach doesn't take into account security, anybody on the client can edit totals and prices... but I don't know your business requirement though, maybe it would be OK for that particular case. — Pavel Shkleinik, Oct 20 '17 at 23:25
Yeah, it is mostly a tool for me. Not super worried about locking everything down. — Adam Bergeron, Oct 23 '17 at 17:09

Trying to total columns for relation datasource

1 Answers1

Linked