In App Maker, how do you make dynamic table cell text?

0
votes

In App Maker, I am displaying a table and want to replace table cell data with different text using a data lookup from another table. Assume two tables, Departments and Employees.

  • Departments is two fields, DeptID and DeptDescription.
  • Employees is multiple fields including DeptID.

In the table listing for Employees, I would like to replace the DeptID with the DeptDescription. (The page datasource is Employees. I do not want to set up a relationship between the data models.)

I am guessing I want to do some scripting in the onDataLoad event for the table cell label for DeptID. I have this much so far:

 app.datasources.Departments.query.filters.DeptID._equals = widget.datasource.item.DeptID;
 app.datasources.Departments.newQuery().run();
 widget.text = app.datasources.Departments.item.DeptDescription;

I know this is not correct, but am I close?

2
google-app-maker
Not sure why you would not want to set up a relation between the tables? It would make that so much simpler. Your current code would only run the query once against the first item in your Employees table and you are mixing client code with server code, so this would not work. I can think of a possible solution but it would take a little bit to gather my thoughts on this. - Markus Malessa
I am looking for the mechanism to accomplish this type of task because we are converting our entire system to Google Cloud. Establishing a relationship is not an option because we are using MySQL tables that are managed and maintained outside of the apps. AppMaker applies data model changes to the database. A table like Departments would potentially relate to hundreds of other tables and if you did it for everything that had a Department field then multiply that for every Cost Code, Vendor, Employee, part number, etc. that might be used for a dropdown or suggest box on hundreds of apps... - Jeff
In PHP, I would loop through the main datasource records and write my table rows. I would stop after each record read, do a quick query to another table, and write the Description to the Dept cell. I am looking for how I would accomplish this through the AppMaker interface. - Jeff

2 Answers

2
votes

This answer is untested, but I wanted to present a possible solution that would not require a lot of DB calls, especially ones that make repeated calls to a server script which might consume a lot of processing time when you do line item calls.

  1. Set up a separate datasource under the Department model. Change the default 'Query Builder' to 'Query Script' and add a parameter of type 'list(number)' or 'list(string)', this should match your Primary Key field type. Uncheck the 'auto load' option.

  2. In your 'Query Script' portion enter the following code:

    query.filters.Id._in = query.parameters.YourParameter;

    return query.run();

  3. Go to your Employees datasource that is supposed to generate your table and find your 'On Load' client script section. In this section enter the following code:

    var departmentsDs = app.datasources.YourDepartmentsDs;

    departmentsDs.properties.YourParameter = datasource.items.map(function(deptIds) {return deptIds.DeptID;});

    departmentDs.load();

  4. Now go the page that contains your table. If you have not already create a label widget do so now. In this label widget for the text binding enter the following:

    @datasources.YourDepartmentsDs.loaded && (@datasources.YourDepartmentsDs.items).map(function(Id){return Id.Id}).indexOf(@widget.datasource.item.DeptID) !== -1 ? @datasources.YourDepartmentDs.items[(@datasources.YourDepartmentsDs.items).map(function(Id){return Id.Id}).indexOf(@widget.datasource.item.DeptID)].DeptDescription : 'Unable to retrieve Dept Description'

As stated this is untested and I wrote the code from memory without App Maker in front of me so it may require some additional tweaking. Going with the first option presented by J.G. would also be a very viable solution though. And I apologize but the code formatter does not seem to be working for me.

2
votes

1 way) Create an aggregate table that joins your tables if you need to bypass using the relations feature. This way you can use sql to join the two tables in the datasource definition

2) if you don't want to make a new table. Change the text from a value binding to "more options"

=getDescription(@datasource.item.DeptId)

and then the code you wrote in a client side script

function getDescription(id){
  google.script.run
  .withSuccessHandler(function successHandler(result){    return result;})
  .withFailureHandler( function failureHandler(e){ console.log(" Failed" +e);})
  .queryValue(id); 

}

server side script:

function queryValue(id){ 
  var query = app.models.Departments.newQuery();
  query.filters.DeptID._equals = id;
  var results = query.run();
  return results[0]["DeptDescription"];
}

that last line might be results[0].DeptDescription