Hi Folks - I seem to be in a loop with this challenge and would apprciate any clarity you can offer.
I have a table with a column ‘fc_marked_deleted’, this is set to 1 when the record is deleted by the user (rather than actually deleting the record in the table).
I’m using addTableFilterParam’s on startup to show only those records which are NOT marked deteled and I use the following code to do so:
var vSQLFabric = 'SELECT fc_id FROM fabric_condition WHERE fc_marked_deleted != 1 OR fc_marked_deleted IS NULL'
databaseManager.addTableFilterParam(vGeneralServer, 'fabric_condition', 'fc_id', 'IN', vSQLFabric, 'fabric_filter_param')
That filtering seems to work fine, and shows only the records which are not marked deleted. However, when I mark a record deleted with this code:
function del_rec(){
fc_marked_deleted = 1
databaseManager.saveData()}
I get an error (with or without the databaseManager.saveData())
Cannot save form data
com.servoy.j2db.dataprocessing.DataException: You can’t specify target table ‘fabric_condition’ for update in FROM clause
save failed for 1 or more records
com.servoy.j2db.dataprocessing.DataException: You can’t specify target table ‘fabric_condition’ for update in FROM clause
====================
I’m not sure if this error is coming from the SQL feeding the filter param, or the form foundset? This delete code works fine when not applying the filter.
Any suggestions on where to look would be most appreciated.
When using tableFilterParams this filter will be added to any SQL you send to the backend database. It seems this is then also used in updates.
But I am not sure if this also applies to the rawSQL plugin. You might use that as a workaround.
In any case it seems this issue is something Servoy needs to address.
When using tableFilterParams this filter will be added to any SQL you send to the backend database. It seems this is then also used in updates.
But I am not sure if this also applies to the rawSQL plugin. You might use that as a workaround.
In any case it seems this issue is something Servoy needs to address.
Robert - I figured this was something I was doing wrong - as its difficult to conceive that one cant change a record on the filtered criteria when the filter param is in place?
A couple of thoughts about routes to address it:
Your suggestion of the rawSQL - though not sure if that will update the current record view based on the filter param.
Remove the filter param before changing the marked deleted column - re-set the filter after saving - again not sure how that will affect the current record view?
I’ve not seen any discussion of this in the past - have you?
we also, want to change ALL the Servoy deleteRecord()'s, to such sort of mechanism.
Why should Servoy use this filter, also in updates? that looks like a bug to me…
(or a not wanted feature! )
I’ve made a work-around by removing the filter - doing the update - and then reapplying the filter.
This flashes the UI a little as it finds the correct record to display (after deleting record 5 it seems to show record 12 then 10 and finally arrives at 6), but other wise works. It just really complicates what I had hoped would be a simple filtering process.
I also use a form to list the marked deleted records so the user can un-check them and re-show them again. This is currently using a global relationship but this also is filtered, so does not show the deleted records. I’ll try a similar (remove the filter) process with the onShow and onHide of that form.
I’ll probably create an apply / remove global function for the three tables I need to filter in this way.
This is introduced in Servoy 5, you can use more complex operators.
This filter translates to
where fc_id is null or fc_id = 0
The table filter is designed to make some records (like products of other ISV customers) invisible to the user.
To make sure these are also never updated we also add the filter to the update and delete statements.
You are running against a limitation that mysql poses on the the update statement.
Please create a case in our support system for that, i hope we can work around that for mysql.
I found out that addTableFilterParam() is applied to all clients on the server rather than the current user ( ).
This is something we don’t want, so I switched to using foundset.addFoundSetFilterParam().
But!
Whenever I use this:
…and there is more than 200 records in arrayOfPks, the filter fails to be applied at foundset.loadAllRecords().
With this exception:
com.servoy.j2db.dataprocessing.DataException: Can't find column: SERVCARDID_4087042147 in table TEMP_4087042145
(servcardid is a legitimate column name in one of the tables being used and the application in the background may have temporary tables with that sort of names but physically the table is not there at the time of the error)
The filters using other operators than “IN” work fine.
The same filter worked ok with addTableFilterParam().
Is this something I can work around? Our working solution is screwed because of this, please, help.
maria:
…and there is more than 200 records in arrayOfPks, the filter fails to be applied at foundset.loadAllRecords().
Each database has its own limits for how many parameters can be stuffed into the IN part of the statement. I believe MySQL has a max of 200. However, you should really consider a different usage instead of using hard coded array of PK’s. Consider using the IN statement with SQL, like:
foundset.addFoundSetFilterParam(columnName, "IN", "SELECT pk FROM table WHERE ...", filterName)
So, however your retrieving the array of PK’s, just use the SQL to get those PK’s instead of inserting them as an array.
maria:
I found out that addTableFilterParam() is applied to all clients on the server rather than the current user ( ).
Maria,
That is not the case, filters are purely per client, they are no way shared with other clients.
Rob
I’m sorry about that one Rob. Yes, table filters are not shared. In our scenario the client was reading a property and applying the filters accordingly.
It appears that that property was set for all clients instead of one Therefore the filters were triggered Sorry.
goldcougar:
Each database has its own limits for how many parameters can be stuffed into the IN part of the statement. I believe MySQL has a max of 200.
Scott,
Servoy automatically creates different sql when an in-clause with more than 200 values is used.
So using Servoy you do not run in that limit.