Thanks so much Elton!
This turned out to be a PEBCAK error (ask if you don’t know what that is).
Now I’m trying to actually build and run the SQL to do my inserts, updates and deletes. I’m having issues here. I’m getting invalid sql statements. Here is an example of an update statement which I copied out of the Five log:
UPDATE StudyGroupMembers SET StudyGroupKey = ''38eb45d7-6892-4842-a9db-263a96655dce'', MemberKey = ''864aad21-f3e7-4b46-94b7-20eb53321b05'', IsLeader = 0, IsCoordinator = 0, IsCoLeader = 0, IsAssistant = 0, IsSunshine = 0, GroupPaidDate = ''2025-10-01'', CheckNumber = ''12345''
WHERE StudyGroupMembersKey = ''3b5c64d6-a15c-4f6e-b82e-9fb493ba7474''
You should be able to see (or put cursor in the statement and move it around with arrow keys) that surrounding every string field there are 2 apostrophes. This obviously won’t work in SQL.
Here is where I load my form data into a variable and call the process:
function EditGroupMember(five, context, result) {
// need to save variable so we know what the action should be
five.setVariable('GroupMemberAction', 'edit');
// const parentForm = five.variable.MyForm; // Members or StudyGroups
// const parentKey = five.variable.MyKey; // MembersKey or StudyGroupsKey
let vars = {
Action: five.variable.GroupMemberAction,
ParentTable: five.variable.MyForm === 'Members' ? 'Members' : 'StudyGroups',
StudyGroupMembersKey: five.field.StudyGroupMembersKey,
MemberKey: five.field.MemberKey,
StudyGroupKey: five.field.StudyGroupKey,
IsLeader: five.field.IsLeader,
IsCoordinator: five.field.IsCoordinator,
IsCoLeader: five.field.IsCoLeader,
IsAssistant: five.field.IsAssistant,
IsSunshine: five.field.IsSunshine,
GroupPaidDate: five.field.GroupPaidDate,
CheckNumber: five.field.CheckNumber
};
five.setVariable("AddEditVars", vars);
try {
five.selectAction("AddEditGroupMemberProcess");
return five.success(result);
}
catch (e) {
five.showMessage("Error: " + e.message);
return five.failure(result);
}
}
The process calls this immediately:
function InitAddGroupMemberProcess(five, context, result) {
// load fields into process
const myVars = five.variable.AddEditVars;
five.field.Action = myVars.Action;
five.field.ParentTable = myVars.ParentTable;
five.field.StudyGroupMembersKey = myVars.StudyGroupMembersKey;
five.field.MemberKey = myVars.MemberKey;
five.field.StudyGroupKey = myVars.StudyGroupKey;
five.field.IsLeader = myVars.IsLeader;
five.field.IsCoordinator = myVars.IsCoordinator;
five.field.IsAssistant = myVars.IsAssistant;
five.field.IsCoLeader = myVars.IsCoLeader;
five.field.IsSunshine = five.variable.AddEditVars.IsSunshine;
five.field.GroupPaidDate = myVars.GroupPaidDate;
five.field.CheckNumber = myVars.CheckNumber;
// five.clearVariable('Vars');
return five.success(result);
}
Here is code run when I click Save button on my process:
function InsertOrUpdateGroupMember(five, context, result) {
// make sure we selected member or group
if (five.field.ParentTable === 'StudyGroups' && !five.field.MemberKey) {
return five.createError("You must select a member before running this process.");
}
if (five.field.ParentTable === 'Members' && !five.field.StudyGroupKey) {
return five.createError("You must select a study group before running this process.");
}
// if adding, make sure member is not already in group
if (five.variable.GroupMemberAction === 'add') {
const sql = `SELECT COUNT(*) AS ExistingCount FROM StudyGroupMembers WHERE StudyGroupKey = '${five.field.StudyGroupKey}' AND MemberKey = '${five.field.MemberKey}'`;
five.log(sql);
const checkResult = five.executeQuery(sql, 0);
if (!checkResult.isOk || !checkResult.isOk()) {
return five.createError("Could not check for existing member assignment");
}
const rows = checkResult.rows;
if (rows && rows.length > 0 && rows[0].ExistingCount > 0) {
return five.createError("This member is already assigned to this study group");
}
}
// build context and call server function
let parms = {
Action: five.variable.GroupMemberAction,
StudyGroupMembersKey: five.field.StudyGroupMembersKey ? `'${five.field.StudyGroupMembersKey}'` : 'NULL',
StudyGroupKey: five.field.StudyGroupKey ? `'${five.field.StudyGroupKey}'` : 'NULL',
MemberKey: five.field.MemberKey ? `'${five.field.MemberKey}'` : 'NULL',
IsLeader: five.field.isLeader ? 1 : 0,
IsCoordinator: five.field.isCoordinator ? 1 : 0,
IsCoLeader: five.field.isCoLeader ? 1 : 0,
IsAssistant: five.field.isAssistant ? 1 : 0,
IsSunshine: five.field.isSunshine ? 1 : 0,
PaidDate: five.field.GroupPaidDate ? `'${five.field.GroupPaidDate}'` : 'NULL',
CheckNumber: five.field.CheckNumber ? `'${five.field.CheckNumber}'` : 'NULL'
}
five.executeFunction('UpdateGroupMemberServer', parms, null, '', '', function (result) {
if (result.serverResponse.errorCode == 'ErrErrorOk') {
five.selectAction(five.variable.MyForm, five.variable.MyKey);
} else {
const functionMessage = result.serverResponse.results;
if (functionMessage !== '') {
return five.createError("Update failed: " + functionMessage);
}
}
});
}
I think this is where my problem is. I’m explicitly surrounding my string variables with a single apostrophe, then sending the context variable into the server function where a SQL statement is built, which also surrounds the variables with apostrophes.
I first thought somehow the JSON contact object was supplying extra apostrophes, but I don’t think this is the case. So I know what is going wrong, the question is WHERE should I put those apostrophes, when building the context variables or when using them to build a SQL statement?
I think either way should work, but is there a best-practice? Thanks…
ALSO: If I click the Delete action button, what is the best way to ask the user if they are sure? there is a client-side function that I can ask the question in. thanks…
