How to Remove or Add Record

[RMittelman]

I have a function that is supposed to remove a record if it exists or add one if it doesn’t exist.

The table has 2 fields, UserKey and StudyGroupKey. In Access I would say something like:

let sql = 'select count(*) from myTable where UserKey = \'xxx\' And StudyGroupKey = \'yyy\'';
let recs = five.executeQuery(sql, 0, 'xxx', 'yyy');
if(recs > 0){
  sql = 'Delete From myTable Where UserKey = \'?\' And StudyGroupKey = \'?\'';
}
else{
  sql = 'Insert into myTable (UserKey, StudyGroupKey) Values \'?\', \'?\'';
}
result = five.executeQuery(sql, 0, myUser, myGroup);

But I’m not sure how to do the first query, which should return a numeric value. In that case, will recs contain the count?

Thanks…

[mark]

Hi,
If you use the following code it should work for you:

let sql = ‘select count(*) from myTable where UserKey = ? And StudyGroupKey = ?’;
let recs = five.executeQuery(sql, 0, ‘xxx’, ‘yyy’);
if (!recs.isOk()) {
return five.createError(recs, ‘Failed to execute query’);
}
if (recs.recordCount() > 0) {
sql = ‘Delete From myTable Where UserKey = ? And StudyGroupKey = ?’;
}
else{
sql = ‘Insert into myTable (UserKey, StudyGroupKey) Values (?, ?)’;
}
result = five.executeQuery(sql, 0, myUser, myGroup);

The changes are summarised as follows:

1. You need to put a ? where you want the parameter replacement to occur
2. The isOk() function will check the query executed okay before you test the result
3. The recordCount() function will return how many rows were returned in the query
4. The Values clause in MySQL needs to be eclosed in brackets

Hopefully this all works for you. I haven’t tested it myself so apologies if I made a typo.

BTW I notice that you are using ‘xxx’ and ‘yyy’ for the select query but myUser and myGroup for the delete or insert query. I believe you would want them to be the same so that the record you test is the one you insert or delete. To handle that you would just change the first executeQuery to:
let recs = five.executeQuery(sql, 0, myUser, myGroup);

Best regards

Mark

[RMittelman]

Thanks for the answer Mark.

I was using ‘xxx’ and ‘yyy’ as placeholders.

I’m concerned about your test of recordCount since the query asked for Count(*) and would probably return a record even if the value was 0.

I figured out how to pull the actual count out of the JSON.

I will test this and post results back. Thanks again…

Thanks…

Ron Mittelman

[mark]

Hi Ron,

You could change the SQL to say ‘COUNT(*) AS RecCount’ and then access it via recs.record[0].RecCount if that helps.

Best regards

Mark

[mark]

Apologies Ron.

Use recs.records[0].RecCount

I missed the s for records

Best regards

Mark

[RMittelman]

Thanks Mark. This seems to work so far, but as usual it brought up another challenge. Currently I have a query to populate my DataView:

SELECT
  `StudyGroups`.`StudyGroupsKey` AS `StudyGroupsKey`,
  `StudyGroups`.`GroupName` AS `GroupName`,
  `ReportGroups`.`ReportKey` AS `ReportKey`,
  `ReportKey` IS NOT NULL AS `IsSelected`
FROM
  `StudyGroups`
  LEFT JOIN `ReportGroups` ON (
    `StudyGroups`.`StudyGroupsKey` = `ReportGroups`.`StudyGroupKey`
  )
ORDER BY
  `GroupName` ASC

This gives me a list of all study groups, with the ones also present in the ReportGroups join table being indicated as IsSelected. This is fine if I’m working in the SetupReports form, because it saves default groups for each report in the join table.

However, I need a similar query (and DataView) to use with the GenerateReports form. This form must use the SelectedGroups table rather than the ReportGroups join table. Also, the information from here is not saved back to the database, but instead used when I actually render the report. This SelectedGroups table has 2 fields, UserKey and StudyGroupKey. I can build the query, but there are some questions:

1: Is there a way to save the parameter for the UserKey in the query design? I would need to know how to add a parameter to the query and populate it with something that evaluates to the currentUserKey when the query is being used. I know how to use currentUserKey() in a function when building SQL statements, but not in a pre-defined query. Can I include a “?” in the query, then add a parameter which will evaluate to the user at run-time?

2: Is there a way to make the table names and field names parameterized also? That way I could use a single query / DataView instead of having one for SetupReports and another for GenerateReports forms. I think the answer to this one will be no, but please correct me if I’m wrong.

Thanks!

[RileyAnderssen]

Hello,

Answer for question 1: Yes, you are able to include a “?” in the query and then parse a parameter through at run time. If you are using a button to generate the report, you will need to use a JavaScript function in which sets the variable in order for the SQL query to be able to use it.

Answer for question 2: Sorry but this answer for this one is no.

Let me know if you require any further assistance with question 1!

Thanks,
Riley.

[RMittelman]

Thanks Riley!

The question was around what to put into the “expression” box when I’m setting up the parameter. Unlike the functions I’ve worked on before, I can’t put something in at run-time, I must put the parameter expression in at design-time for the query.

So what expression can I put in at design-time that will evaluate to the current user key at run-time?

Thanks…

Ron Mittelman

[RileyAnderssen]

Hello,

If the variable has been set within a JavaScript Function, you are able to use:

{{five.variable.VARIABLENAME}}

So if you set a variable like so:

… five.setVariable(“Userkey”, userkey);

Then you could call it within the SQL Query using the parameter:

{{five.variable.Userkey}}

Thanks,
Riley

[RMittelman]

Thanks for a quick answer, Riley. Not sure I was able to implement it properly. Here is the query for my DataView:

SELECT
  `StudyGroups`.`StudyGroupsKey` AS `StudyGroupsKey`,
  `SelectedGroups`.`UserKey` AS `UserKey`,
  `StudyGroups`.`GroupName` AS `GroupName`,
  `UserKey` IS NOT NULL AS `IsSelected`
FROM
  `StudyGroups`
  LEFT JOIN `SelectedGroups` ON (
    `StudyGroups`.`StudyGroupsKey` = `SelectedGroups`.`StudyGroupKey`
  )
WHERE `UserKey` = ?
ORDER BY
  `GroupName` ASC

Here is the parameter definition for the query:

Here is the code I’m using in the GenerateReportOnShow event:

function InitReportSubforms(five, context, result)  {
    
    // called from on_load event in SetupReports & GenerateReports
    
    five.log('InitReportSubforms');
    
    // context: { SelectTable: xxx, SelectField1: xxx, SelectVal1: guid, SelectField2: xxx, JoinTable: xxx, 
    //            JoinField1: xxx, JoinVal1: guid, JoinField2: xxx, }
    
    // setup common fields
    const _five = five;
    const selField1 = 'UserKey';
    const selVal1 = five.currentUserKey();
    const joinField1 = 'ReportKey';
    const joinVal1 = five.field.ReportsKey;

    // save userKey
    five.setVariable("UserKey", selVal1);
    
    // update study group fields
    let selTable = 'SelectedGroups';
    let selField2 = 'StudyGroupKey';
    let joinTable = `ReportGroups`;
    let joinField2 = 'StudyGroupKey';

    five.executeFunction('InitSubformServer', { SelectTable: selTable, SelectField1: selField1, SelectVal1: selVal1, 
                        SelectField2: selField2, JoinTable: joinTable, JoinField1: joinField1, JoinVal1: joinVal1, JoinField2: joinField2 }, 
                        null, '', '', function (result) {
        if (result.serverResponse.errorCode === 'ErrErrorOk') {
            five.refreshTable(joinTable);
            five.reload();
            return;
        }

        let functionMessage = result.serverResponse.results;
        if (functionMessage !== '') {
            _five.showMessage(functionMessage);
        }
    });

    // update portfolio fields
    selTable = 'SelectedPortfolios';
    selField2 = 'PortfolioKey';
    joinTable = `ReportPortfolios`;
    joinField2 = 'PortfolioKey';

    five.executeFunction('InitSubformServer', { SelectTable: selTable, SelectField1: selField1, SelectVal1: selVal1, 
                        SelectField2: selField2, JoinTable: joinTable, JoinField1: joinField1, JoinVal1: joinVal1, JoinField2: joinField2 }, 
                        null, '', '', function (result) {
        if (result.serverResponse.errorCode === 'ErrErrorOk') {
            five.refreshTable(joinTable);
            five.reload();
            return;
        }

        let functionMessage = result.serverResponse.results;
        if (functionMessage !== '') {
            _five.showMessage(functionMessage);
        }
    });

    
    return five.success(result);
}

Is there a scoping or lifetime for the variable “UserKey”? I’m setting it on showing the GenerateReports form.

Unfortunately, since I’m setting the variable at run-time, I can’t go back to the design-time tab of my browser, then open up the query definition, then tap the “run” button to see results of the query the same way I can with other queries, since it’s relying on a parameter that is not set at design-time.

So I don’t know if I’ve loaded the variable properly, or there is a scoping or lifetime issue with the variable. Any ideas? Thanks…

[RileyAnderssen]

Hello,

In order to test the query at design-time, you are able to log the UserKey variable value using Five’s Inspector. This way, you are able to copy and paste the key into the query at design time, run it, and check the results line up with what is happening at run time. Be sure to change this back to the placeholder value if you are not testing the query however.

In order to log the value and view it using the inspector:

1. Add a line of code after the variable has been set … five.log(selVal1)
2. Run the application with the inspector (the run button with the magnifying glass)
3. Generate the report as normal
4. View the inspector on the right hand side of your screen

The value will then be seen in the inspector, where you can then copy and paste it into your query for testing purposes (be sure to add quotes around the key when adding it to your query as well as replacing it with the ‘?’ placeholder value once you have finished testing).

I would also suggest moving the setting of the UserKey variable to the ‘DoLogon’ event of the application, this way, it is set when the user logs on to the application and then can be used at any point within the application without having to worry about run time issues. However, keep in mind this value will not be logged in the inspector if you attempt to log it from a function attached to the ‘DoLogon’ event.

Let me know if there’s any issues,

Thanks,
Riley.

[RMittelman]

Thanks for all the help so far.

I changed the data view query slightly for debugging:

As you can see, I commented out the WHERE with ? and added a WHERE with UserKey hard-coded. See the results, which are as expected.

However, when I fix the query to use the ? again, the display doesn’t properly reflect the 2 records properly at run-time. So I’m doing something wrong. This is a LIVE query, so it should update. It used to update. Did I enter the parameter value wrong at design-time?

Here is a screenshot of how the form displays. Based on the values returned from the data view query, the first 2 groups should show checkmarks.

Is there something wrong with the data view query itself? I have recently changed it a bit. Please note that even with 2 records coming back, the DataView doesn’t show 2 groups selected, only the first group.

Thanks…

[RMittelman]

Update:

I had the DataView query wrong, it was only showing the groups that were selected rather than all groups. I changed the WHERE statement and here is results:

Please bear in mind that what appears in the DataView itself is somewhat transient, and resets itself each time I load the form. I expect to see all groups on the StudyGroups page, but only those that are in the more permanent join table (ReportGroups) will show up checked. Basically the init function clears all selected study groups from the SelectedGroups table FOR THE CURRENT USER, then adds study group records into SelectedGroups for the current user IF the group also appears in the ReportGroups join table. So each time I run the GenerateReports form, the checked groups start over. So the screenshot for the form page is correct, because I only have 1 record in the ReportGroups join table for that report.

What I EXPECT to happen when I click the second group (DIVAS 2), is that a record gets added to the SelectedGroups table and the display should update to a check mark on that line. The record IS getting added to SelectedGroups, but the DataView is not reflecting that change.

Could this be related to the fact that in SelectedGroups table there is no IsSelected field, but instead the above DataView query is creating a virtual field which gets its value depending on whether the LEFT JOIN table field is null or not? The last time this worked was when I was updating an actual IsSelected database field in a table. Is the dynamic nature of that query field causing a problem?

[RMittelman]

Here is my application, in case you need it to answer prior question.

BrandeisConejo-20240825-000413461799506.fdf (5.3 MB)

[RMittelman]

Sorry to keep bothering, I know you’re busy. But I can’t proceed with my new project until we get this solved. Just want to clarify the situation:

Originally, this worked just fine. The GenerateReports form has a page showing which study groups are included in the report generation. When the form opens, I would set the IsSelected field of all StudyGroups to false, then set those which are included in my ReportGroups join table to true. The DataView on the Groups page properly indicated this state. When I clicked on a group, the resulting code would flip the IsSelected to the opposite of what it was, and the DataView properly reflected this because the query was live.

Then I realized this was not a multi-user solution because somebody else could be doing the same thing with a different report. So I added a SelectedGroups table with UserKey and StudyGroupKey fields. Now the initial code clears all records from that table for the current user, then adds them back in if the ReportGroups join table has that group in it for the current report. Now the FlipIsSelected function removes the record from SelectedGroups if it exists, or adds one if it doesn’t exist.

This new logic seems to work fine, as after I flip the desired StudyGroup on or off, I can go back to the design tab and query the tables, and they are up-to-date with the choices I’ve made. The problem is that the live query doesn’t cause the DataView to be updated anymore.

I can’t figure out what I did wrong. If you could help on this, I could start working on the other tables and forms. Thanks so much for looking at this…

[RileyAnderssen]

Hello,

Sorry for the late reply, here is a fix I have put together.

Code Block 1 - from client side

function UpdateIsSelectedClient(five, context, result) {
let isSelected = five.field.IsSelected;
let studyGroupKey = five.field.StudyGroupsKey;
five.log("Is selected: " + isSelected);

variables = {};
variables['IsSelected'] = isSelected;
variables['StudyGroupKey'] = studyGroupKey;


five.executeFunction("TestServer", variables, null, null, null, function (result){
    
})


return five.success(result);

}

Code Block 2 - from server side

function UpdateIsSelectedServer(five, context, result) {
let isSelected = context.IsSelected;
let studyGroupKey = context.StudyGroupKey;

let sql = '';

if(isSelected === true){
    sql = 'INSERT INTO ReportGroups (ReportKey, StudyGroupKey) VALUES (?,?)';
    let sqlResults = five.executeQuery(sql,0,five.uuid(), studyGroupKey);
} else {
    sql = 'DELETE FROM ReportGroups WHERE StudyGroupKey = ?';
    let sqlResults = five.executeQuery(sql,0,studyGroupKey);
}


return five.success(result);

}

Attach client side function

Attach the client side function to the IsSelected field on the general page of the StudyGroups form.

For this solution to work, you will need to set IsSelected to false for all study groups first, and then it will work perfectly fine when you adjust between true and false for IsSelected. Note, in code block 2, a random key has been generated for the ReportKey, this will need to be replace with the appropriate report key if that is required.

End Result

Just to show what this achieved, I have set IsSelected to true in the StudyGroups form for 3 different study groups, when I switch back to the GenerateReports form, the corresponding study groups have a tick.

Hope this helps,

Thanks,
Riley.

[RMittelman]

Hi Riley,

Thank you SO much for taking the time to answer my issue. Unfortunately, this will not work. Perhaps I didn’t explain the situation clearly enough. I will try to clarify.

I have a Reports table, a StudyGroups table and a ReportGroups table, which is the join table between the first 2. When I use the SetupReports form, it wants to maintain the Reports and ReportGroups tables, and keep a permanent record of which StudyGroups are attached to which Reports BY DEFAULT. But I can’t use the IsSelected field of the StudyGroups table. That field shouldn’t even be there in retrospect. The only thing that keeps track of which StudyGroups should be associated with which Reports is the ReportGroups join table.

When I want to generate a report, I run the GenerateReports form, which uses a SelectedGroups table that keeps track of which StudyGroups want to be associated with the report. By default, it will be the same StudyGroups which are in the join table for that report. But I need to be able to add or remove groups from the SelectedGroups table, without changing which ones are in the join table. So if some groups are associated with a given report, they need to be put in the SelectedGroups table. So I have an InitReportSubforms client function which clears all selected groups for the user, then loads groups back in the table for that user IF those groups are in the ReportGroups join table during the INIT processing. Now I have a temporary list of the StudyGroups that are associated with the user. During the GenerateReports phase, I can remove some groups from this list and add other groups, but this will be transient, and not saved beyond the report processing phase. I will use it when I actually generate the report, but nowhere else.

The only way to accomplish this is by using the SelectedGroups table, which holds transient data.

I used to use the IsSelected column of the StudyGroups table, but then realized that more than one user may need to simultaneously run reports, hence the SelectedGroups table which holds records for different users. I understand your suggested code will work, but it’s updating IsSelected in the StudyGroups table, and that won’t work for a multi-user solution.

What I can’t figure out is why my code is properly updating SelectedGroups, but the live query for the DataView is not really causing the DataView to update at run-time. The SelectedGroupsDVQuery query is using the StudyGroups and SelectedGroups tables to list ALL StudyGroups, but indicate which ones are indeed “selected” for the active user. The query works fine, and indicates which groups are selected and which are not. It just doesn’t update the DataView properly.

If I manually load the ReportGroups with 2 records for the particular report, I get the following in my subform page:

Here is the results of the SelectedGroupsDVQuery query:

It is exactly correct. I just can’t get it to update properly if I click the third StudyGroup (Movie Buffs).

Why the query behind the DataView works properly the first time after running the INIT function, and causes the DataView to show properly, but when adding a new StudyGroup to SelectedGroups table by clicking its button on the form, the table is properly updated but the DataView doesn’t refresh, escapes me.

I could properly show you if we could do a zoom call, but I realize you folks are too busy for that. Could you explain why the DataView works the first time but does not refresh properly thereafter?

Thanks…

[RMittelman]

Hi Riley,

I’m doing my best to simplify things so my application will work properly. As I mentioned earlier, when I open the GenerateReports form, it runs an INIT function to clear the SelectedGroups table, then fill it using a query involving the StudyGroups and ReportGroups tables. The screen shot of the table records above is still correct. The SelectedGroups table got updated properly.

I don’t know what I did, but the SelectedGroupsDV DataView (yes, I changed its name) no longer reflects the state of the SelectedGroups table. I can’t figure out what I did wrong. All Study Groups on the DataView show X under IsSelected, even though the table shows 2 of them should be check marks.

Could you PLEASE take a look at the application and let me know what I did wrong? Please only use the GenerateReports form, not SetupReports, as I haven’t updated that one yet. I also made changes to the FlipStudyGroupIsSelected and FlipIsSelectedServer functions, but I’m not ready to test those yet. I just need to know why the DataView doesn’t properly reflect what the underlying table contains.

BrandeisConejo-20240829-224906966277597.fdf (5.4 MB)

By the way, I changed the Control password, it now matches the run-time admin password for simplicity. I don’t want to post it on the forum, but you can ask Jo or shoot me a quick email if you need it.

Thanks so much for helping me with these issues!

[RMittelman]

UPDATE:
I got the data view to work. I actually had to create a new data view and add it to the GenerateReports form page, and it started working again. My simplified function FlipIsSelectedServer now only sets the IsSelected variable to NOT what it was before. This still works to the extent that if I immediately query the table back in the design tab of my browser, the record that I “flipped” is now correct. Still, the data view doesn’t refresh itself, even though I have the following in my client function, which should explicitly refresh the form. Note this is just a snippet of the function.

    let tableName = (form === 'GenerateReports') ? 'SelectedGroups' : 'ReportGroups';
    
    // context: { Form: xxx, Subform: xxx, Table: xxx, MainField: xxx, MainKey: guid, SubformField: xxx, SubformKey: guid }
    five.executeFunction('FlipIsSelectedServer', 
                { Form: form, Subform: subform, Table: tableName, MainField: mainField, MainKey: mainKey, 
                  SubformField: subformField, SubformKey: subformKey }, null, '', '', function (result) {
        five.log('result: ' + JSON.stringify(result));
        if (result.serverResponse.errorCode === 'ErrErrorOk') {
            five.log('server response: ' + result.serverResponse.errorCode);
            // return;
        }
            five.refreshTable(tableName);
            five.reload();

So now I just need to figure out why the DataView isn’t refreshing even though the underlying table is changed.

Here is fdf if you need:
BrandeisConejo-20240831-174246791465361.fdf (5.4 MB)

Thanks…

[mark]

Hi Ron,

I had a quick look at your FDF and you don’t have the query set to live. If you change that on the query the dataview based on the query will automatically update when any of the tables used in the query change as per your code.

Best regards

Mark