load() & with(): Editable Grid with filtering

Question

Hi Community, 
 
 I need some ideas of what is the best practice to solve a problem I just encounter. 
 I did an editable grid with my own edits to: https://docs.appian.com/suite/help/18.4/recipe_add_validations_to_an_inline_editable_grid.html 
 Main change is that I retrieve the data from the DB with an expression rule that can use filtering (unless null). The Editable Grid works fine until this point, meaning that when I call the DB, save the data in a load() variable I can remove & add data. 
 Then I needed to do a filtering on the editable grid. For that, I save the filtering - from dropdowns and search boxes - parameters in a load() variable. Inside the load, I have a with() - with the same content as I had on my load before - that call the DB with the filtering options. That works fine, as when the user enters a value for filtering it directly display the information I am looking for. The problem is that the with() breaks the remove/ add functionality, as it would be expected, as with() cannot be saved to store values. 
 I get this error: 
 
 Interface Definition: Expression evaluation error: An error occurred while executing a save: Expression evaluation error: The save target must be a load() variable, process variable, or node input (or a rule input passed one of those three), but instead was... 
 What would be the practice here? - Concatenate loads and with variables? - Make a button to summit the filtering options and remove some user-friendly part of the interface? 
 
 Thanks! 
 Manuel

ManuelHTG · Accepted Answer

Hi, 
 
For anyone interested. I found my own way of doing the filtering on an editable grid with a load variable. 
 
I used a load variable, as with the with variable i could call the a!queryentity and do the filters but not save the values on the GRID. As well, having on-line data could mismatch the ri! variables that I used for tracking the changes. 
 
The key to solve this problem was to load all the data but not display it all. Depending on the user filters, I would just display or not. 
 
i,e,. 
 
load( 
local!filter1, 
local!filter2, 
... 
local!allmydata: expressionrulegetdata(), 
 
a!integerField( 
label: "abc", 
value:local!filter1, 
saveInto: loval!filter1 
), 
 
.. 
a!gridlayout( 
 
.. 
 
rows: a!foreach( 
 
items: local!mythedata, 
 
expression: if(or(fv!item.id = local!filter1, isnull(local!filter), 
{ 
// code to show content 
}, 
{ 
 // empty array for the false condition (no filter matched) 
} 
 
) 
)

Dave Lewis · Answer

load( local!allTheData(queried when you load form), local!pagingInfo(default for when it first loads) , local!filters) 
 
with( local!datasubset: toDataSubset(rule!QueryLocalData(local!allTheData, local!pagingInfo/*which you can save to at will*/, local!filters/*which you can save to at will*/)) 
 
a!grid(columns: local!datasubset(with), pagingInfo(local!paging info(load), filters(load)) 
 
your data is basically a constant, but the datasubset you use for columns is paged from it with paging info and filters that change, and the datasubset you use for columns gets updated every time you click on anything. This is the standard in building a grid from DB data, because you gather absolutely EVERYTHING from your DB, but you only do it once. You can mess with the grid all you want and still not hit your Database connections again. Applying a filter to get the smallest amount of data applicable to what the user is doing is a must, otherwise you could spend several minutes waiting for the form to load.