Relate calculated data model to Cloud SQL model

Question

I am needing to create a single table that displays values from a Cloud SQL data model and Calculated data model side by side as if the data models were related.

Costs Table

I have a calculated data model named 'Costs' that is populated from a spreadsheet. The data in this table looks like this:

PROJECT_ID, ACCRUED_COSTS, REMAINING_COSTS
0001      , 10000        , 25000
0002      , 25000        , 2000
0003      , 15000        , 5000

The code I'm using to generate the calculated model is here:

return getSSData();

function getSSData(){
  var values = SpreadsheetApp.openById("SPREADSHEET_ID").getSheetByName("SHEET_NAME").getRange("RANGE").getValues();
  var ssData = [];
  for (var i = 0; i<values.length; i++){
    var newRecord = app.models.costsTable.newRecord();
   // add all fields to the new record
    newRecord.project = values[i][0].toString();
    newRecord.projectID = values[i][1].toString();
    newRecord.actual = values[i][2];
    newRecord.estimate = values[i][3];
    ssData.push(newRecord);
  }
// return the array of the model.newRecord objects that would be consumed by the Model query.
  return ssData;
}

Projects Table

I also have a Cloud SQL data model named 'Projects'. The data in this table looks like this:

PROJECT_ID, PROJECT_NAME   , PROJECT_BUDGET
0001      , project_alpha  , 50000
0002      , project_beta   , 30000
0003      , project_charlie, 19000

Combined Table

I need to create a page containing a table widget that displays all columns of data from both tables side by side like this:

PROJECT_ID, PROJECT_NAME   , PROJECT_BUDGET, ACCRUED_COSTS, REMAINING_COSTS
0001      , project_alpha  , 50000         , 10000        , 25000
0002      , project_beta   , 30000         , 25000        , 2000
0003      , project_charlie, 19000         , 15000        , 5000

The Problem

This would be easy if the tables could be related to each other but unfortunately calculated models do not support relating.

Ditto on @Morfinismo comment. You can include the extra fields in your calculated model and either pull all SQL model records in and than match ID via indexOf() function when generating each line of your calculated model or just run a query against your SQL model by ID match for each line of your calculated model. I’d suggest pulling all records and matching via indexOf() because line item queries are more ‘expensive’ from an execution time aspect, index function will execute way faster. — Markus Malessa
Thanks @MarkusMalessa. Pulling all info into the calculated model is a good idea and should work for me. But my next question is how. I understand conceptually the idea but if you could post some sample code I could work with that would be greatly appreciated. I'm brand new to App Maker. — bellwether
If you can edit your question to include your code for how you generate your calculated model then I can provide the additional code needed. That way it will match your already established process. Might take me a little time before I post the sample. — Markus Malessa
@MarkusMalessa Appreciate it! Edited my question to include the code I'm using to generate the calculated model. — bellwether

Markus Malessa Markus Malessa · Accepted Answer · 2019-08-06T02:53:22

Per earlier comment you have two options to get this accomplished. Either way add the additional fields like ‘Project_Name’ and ‘Project_Budget’ to your calculated model.

Option 1 (fetch all records with index function):

function getSSData(){
  var values = SpreadsheetApp.openById("SPREADSHEET_ID").getSheetByName("SHEET_NAME").getRange("RANGE").getValues();
  var projects = app.models.Projects.newQuery().run();
  var ids = projects.map(function(p){return p.PROJECT_ID;});
  var ssData = [];
  for (var i = 0; i<values.length; i++){
    var newRecord = app.models.costsTable.newRecord();
   // add all fields to the new record
    newRecord.project = values[i][0].toString();
    newRecord.projectID = values[i][1].toString();
    newRecord.actual = values[i][2];
    newRecord.estimate = values[i][3];
    var index = ids.indexOf(values[i][1].toString());
    if(index !== -1) {
      var project = projects[index];
      newRecord.Project_Name = project.PROJECT_NAME;
      newRecord.Project_Budget = project.PROJECT_Budget;
    }
    ssData.push(newRecord);
  }
// return the array of the model.newRecord objects that would be consumed by the Model query.
  return ssData;
}

Option 2 (run line item query):

function getSSData(){
  var values = SpreadsheetApp.openById("SPREADSHEET_ID").getSheetByName("SHEET_NAME").getRange("RANGE").getValues();
  var ssData = [];
  for (var i = 0; i<values.length; i++){
    var newRecord = app.models.costsTable.newRecord();
    var query = app.models.Projects.newQuery();
    query.filters.PROJECT_ID._equals() = values[i][1].toString();
    var results = query.run();
   // add all fields to the new record
    newRecord.project = values[i][0].toString();
    newRecord.projectID = values[i][1].toString();
    newRecord.actual = values[i][2];
    newRecord.estimate = values[i][3];
    if(results.length > 0) {
      var project = results[0];
      newRecord.Project_Name = project.PROJECT_NAME;
      newRecord.Project_Budget = project.PROJECT_BUDGET;
    }
    ssData.push(newRecord);
  }
// return the array of the model.newRecord objects that would be consumed by the Model query.
  return ssData;
}

I would encourage you to try both. Create a variable for startdate = new Date() at the beginning and then before you return the data do console.log(new Date() - startdate), which will give you the time difference in milliseconds of your total script execution. That way you can also figure out for yourself which process is faster.