I have a SetupReports form, with fields from the Reports table being on the General page.
My Join page is based on the ReportGroups table which has ReportGroupsKey, ReportKey and StudyGroupKey fields. First field is a generated GUID, and is (I think) immaterial to this question.
I’ve created Action Buttons on the form called AllGroups and NoGroups. Clicking either causes a client-side function to run a server-side function called JoinTableAllOrNoneServer, which is my attempt at a generic function to flip all items in the join form either on or off. My logic was to delete all records in the join table for the particular report, then if I’ve clicked “All” groups, it will add a new record in the join table for the particular report, and each group. So far I’m only testing the first part, which is removing all records in the join table where ReportKey is the particular report being modified. This should be the same as flipping all switches to “Off”, right? But it’s not working. Here is the code from the generic server function:
function JoinTableAllOrNoneServer(five, context, result) {
// this function is used with join tables where setup form uses a JOIN page
// it is called from a client function so form info is available
// context: { JoinTable: join table name, JoinField1: Field1Name, JoinField1Value: Field1Value, JoinField2: Field2Name,
// RelatedTable: table name, RelatedTableKeyName: key field name in related table, IsSelected: true }
// first, all records from the join table for context.JoinField1Value are removed
// if context.IsSelected is true, it adds all records from JoinField2Table into the join table,
// using context.JoinField1Value, in essence selecting ALL
// first clear all records from JoinTable for this Field1
let sql = 'DELETE FROM ' + context.JoinTable + ' WHERE ' + context.JoinField1 + '=?';
five.log(sql);
five.log('field1: ' + context.JoinField1Value);
let queryResults = five.executeQuery(sql, 0, context.Field1Name);
// if setting all true, add all JoinField2 records to join table
if(context.IsSelected) {
// sql to cycle all records in second table, writing them into join table
const sql1 = 'INSERT INTO ' + context.JoinTable + ' (' + context.JoinField1 + ', ' + context.JoinField2 + ')'
const sql2 = ' SELECT \'' + context.JoinField1Value + '\' AS ' + context.JoinField1 + ', ';
const sql3 = context.RelatedTableKeyName + ' AS ' + context.JoinField2;
const sql4 = ' FROM ' + context.RelatedTable;
five.log(sql1);
five.log(sql2);
five.log(sql3);
five.log(sql4);
sql = sql1 + sql2 + sql3 + sql4;
five.log(sql);
// queryResults = five.executeQuery(sql, 0);//, context.ReportsKey);
}
return five.success(result);
}
When I run the code using the inspector, I see log entries with the ReportKey GUID, and the SQL Statement, but it doesn’t do anything.
I think this messes things up, because even though it does nothing, when I flip any switches on the join page, then click Save, I get the following error:
runtime error: invalid memory address or nil pointer dereference
It also logs me off the running session.
I notice now the error happens even if I don’t use the new functionality. If I log on to the runtime, bring up the report, then flip any switches, the error occurs always when I click SAVE.
Any idea what I’ve done wrong here?
Thanks…