I’ve adoptted a process of hiding records that are marked as deleted rather than actually deleting them. I can then let admin users select marked records for re-entry back into the main data set if it was deleted incorrectly. When the user selects Delete the record specified has the column marked_deleted set to 1, and currently, the form is refreshed with a SQL query and loadRecords(). When its brought back into the main population marked_deleted is set back to null and the controller refreshed.
This works fine - but I have trouble deciding the best way to deal with Find() and Serach() on those forms with hidden records?
Using this format:
var vSQL = ‘SELECT fc_id FROM fabric_condition WHERE fc_marked_deleted = NULL’
controller.loadRecords(vSQL);
then the hidden records are not displayed (as expected), however on those forms where I have a Find() and Search() button the returned foundset does not know about the returned column marked_deleted - and hence all the records from the search are returned, including those marked deleted.
One ploy I’ve considered is a hidden field on the form for the marked_deleted column and setting that during the onSearch() to null. My obvious flaw there is that during search() an empty or null field is not considered during the search process = not searched for null?
Second thought was to set marked_deleted to 2 for hidden and 1 for not hidden, then set the form field to 1 in the onSearch() event. This is a little more complex, but more importantly, demands that all current data have a flag set in marked_deleted as default - and I was hoping to avoid that during data transfer and set-up (something else to get missed / go wrong).
How do others handle this process of hiding records marked for deletion - and the search() functions?
that way every query that Servoy does, takes the marked_deleted = null automatically into consideration.
In Servoy5 you can remove that filter, if needed.
from experience with a multitenant environment I can tell you that the link Jan posted above is definitely the most easy way and best way to go.
Some things to take into account:
you can define/set more than one tableFilter on a table, just by setting filters again and again.
every query handled from inside Servoy will be extended with the tablefilters (so find/search, relations, etc.)
getDatasetByQuery will NOT automatically add the filter. Although: when you try to load this dataset onto a form, the filter will be applied onto this dataset.
As for remark 3: this will stand in your way of easy showing records which are marked for deletion.
You can solve this in a couple of ways:
temporarily delete the filter
use the gridbean or solutionModel to show a dataset/datasource.