How to move a record from one model to another in Google App Maker

Question

I have created 2 SQL models in Google App Maker. For simplicity sake lets say Model 1 has all of the information that can be added and edited for each of the records. Model 2 works as a storage model where once a record in Model 1 is removed it moves over to Model 2. The idea is that the individual can click on a "removed" boolean which will open a dialog page to add in comments for the removal and once complete the record will be moved to Model 2 for storage and will no longer be visible in Model 1.

Is there any way to do this? If you need more information let me know and I will try to provide it but the reason I cannot post the existing app is because the information is confidential.

Thanks for you help!

Answer 1

Updated answer: move to another model

If you want to enforce users to enter a message, you need to forbid them to delete records through datasources:

// onBeforeDelete model event
throw new Error('You should provide message prior deleting a record');

Then you need to implement audit itself:

// server script
function archive(itemKey, message) {
  if (!message) {
    throw new Error('Message is required');
  }

  var record = app.models.MyModel.getRecord(itemKey);

  if (!record) {
    throw new Error('Record was not found');
  }

  var archive = app.models.Removed.newRecord();
  archive.Field1 = record.Field1;
  archive.Field2 = record.Field2;
  ...
  archive.Message = message;

  app.saveRecords([archive]);
  app.deleteRecords([record]);

}

// client script
google.script.run
  .withSuccessHandler(function() {
    // TODO
  })
  .withFailureHandler(function() {
    // TODO
  })
  .archive(itemKey, message);

If you need to implement auditing for multiple/all models then you can generalize the snippet by passing model's name and using Model Metadata: funciton archive(modelName, itemKey, message) {}

Original answer: move to another DB

Normally I would recommend just to add and set a boolean field Deleted to the model and ensure that records marked as deleted are not sent to the client. Implementation of moving data between databases could be tricky since transactions are not supported across multiple databases.

If you desperately want to make your app more complex and less reliable you can create record's backup in onBeforeDelete model event using JDBC Apps Script service (External Database Sample could be your friend to start with):

// onBeforeDelete model event
var connection = Jdbc.getConnection(dbUrl, user, userPassword);

var statement = connection.prepareStatement('INSERT INTO ' + TABLE_NAME +
              ' (Field1, Field2, ...) values (?, ?, ...)');
statement.setString(1, record.Field1);
statement.setString(2, record.Field2);
...
statement.execute();

Why do you need JDBC? Because App Maker natively doesn't support models attached to different databases.

Answer 2

I was able to do what I needed using a query filter as a client script This keeps the data on the back end when i export and only shows the active user whatever is not removed.

1. var datasource1 = app.datasources.WatchList_Data;
2. datasource1.query.filters.Remove_from_WatchList._equals = 'No';
3. datasource1.load();