0

Hi all App Maker Gurus,

I am able to create Model (e.g. Department) in App Maker > Cloud SQL. And i know how to create basic Model, Department with Fields Code, Name, EffectiveDate

e.g. Code/Name/EffectiveDate IT / Information Technology / 1 Jan 2018 IT / Information Tech & Sec / 1 May 2018

i am trying to mention, effectively 1 May 2018, IT department is called 'Information Tech & Sec'. How can i perform that as My "Code" is now Primary Key and it doesn't accept another row due to duplicate key? The unique key supposed to be combination of Code + EffectiveDate.

How can i configure in Google App Maker?

Thanks for the guide.

Pavel Shkleinik
  • 6,050
  • 2
  • 20
  • 35
Weilies
  • 318
  • 2
  • 15

1 Answers1

0

In case you have direct access to the database you can set unique constraint for multiple columns:

ALTER TABLE `Department` ADD UNIQUE `unique_index`(`Code`, `EffectiveDate`);

But unfortunately at this time App Maker doesn't support unique constraints on multiple fields, but you can enforce it with script. The simplest implementation that doesn't handle concurrency will be:

// onBeforeSave model event
var query = app.models.Department.newQuery();

query.filters.Code._equals = record.Code;
query.filters.EffectiveDate._equals = record.EffectiveDate;

var records = query.run();

if (records.length > 0) {
  throw new Error('Duplicate entry');
}
Pavel Shkleinik
  • 6,050
  • 2
  • 20
  • 35
  • Does the second approach work without race conditions ? I.e., I have something I want to do on pageload, but there's a redirect involved and I might do the same action a split second later on the redirected page (same page, added parameter). That means the time the 2nd save starts, the first is in flight and the query will probably come up empty. – Mr Printer May 20 '19 at 19:03
  • `The simplest implementation that doesn't handle concurrency will be` - from the answer `¯\_(ツ)_/¯`. For the peace of mind I would recommend to add the `UNIQUE` constraint on the database level. – Pavel Shkleinik May 21 '19 at 16:31