Sub total of list based on relations

Question

I am new to App Maker. I am trying to find a way to total fields based on data relations. I have built a basic app based on this YouTube tutorial (7min long). In the video it shows you how to total up series of numbers. In there example they use car inventory that takes input for "Model" the "wholesale value" & "Retail value". It calculate the profit or loss and then gives you the "Total profit loss" on your full inventory. Basic stuff.

I am trying to take it a step further. I have set up a relational database for "Car companies" (one) to "Car inventory" (many). So say my companies are Saturn & Pontiac. I have different models that relate to each company and display the correct models for either Saturn or Pontiac when selected. But my issue is trying to get only the total value for each specific company to show and not the full value of all the vehicles

So Say I have picked Saturn and out of the full inventory of cars it pull up only the Saturn models. I only want to see the value of those Saturn vehicles

I don't want to use targeted "IF" statements because although it's only 2 companies now I want it to pull the totals based on the list regardless of how many companies I have in the future.

For the record, No I don'town a used car dealership, I'm just trying to learn the program and it seemed an easy way to explain it.

Update 001

I understand the following suggestion:

Doing this for a calculated datasource you would set a key parameter and feed that key to the calculated DS and run a query where your inventory.carcompany.id._equals = key parameter and reload this DS and feeding it a new key in the company on item change client script.

Unfortunately I don't understand the language enough to make the script do that without getting lost in weeks of trial and error. Here is the code I have in the calculated query:

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];

I understand that I need to make a new var for the company name. I understand that I would then need to get that name and reload the data source using that name to limit my calculation to only listings with that name. I just don't know how to say it correctly.

Answer 1

Based on your other answer here How to 'Add / Sum / Total / Calculate' dynamic tables in google app maker you would just need to expand on your existing script and do some very minor modifications to your calculated datasource. For one you would introduce an additional field for your Company (type string) and then do the following:

var query = app.models.company.newQuery(); // Should this not be models.company.newQuery as there is no datasource named "carcompanies" ?
query.prefetch.autos._add();
var results = query.run();
var calculatedRecords = []; //contained a capitalization error here

results.forEach(function(company) {
  var totals = {wholesale:0, retail:0, profit:0};

  var records = company.autos;

  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.Company = company.name;
  record.wholesale = totals.wholesale;
  record.retail = totals.retail;
  record.profit = totals.profit;
  calculatedRecords.push(record);
});
return calculatedRecords;