I have a fairly simple form that a few users are going to use to help clean up a database. Basically they will be running some searches to find records that are missing data and/or need to view/print an output of their frozen tank inventory by tank/stack/pie. I couldn’t find any direct Servoy function that would grab the column names that were being searched on and the parameters being fed to those columns so tried to do some regexp using ‘databaseManager.getSQL(foundset)’ and ‘databaseManager.getSQLParameters(foundset)’.
The REGEXP is pretty long but this works well as long as I am not searching for any null values. At that point a) getSQLParameters does not return any values at all (e.g. if one is searching for ‘2’ in the ‘tank’ column and ‘^’ in the ‘amount’ column it simply returns ‘[2]’). And b) the getSQL function does put in ‘amount is null’ but also puts in that the id for that table ‘is not null’. (It can be a related search on one additional table). Having the ‘is null’ of course complicates the REGEXP but I could work that out; however I’m not sure why and when this extra ‘is not null’ check on the related table PK is thrown in.
Is there a simpler way to get that information? As they are doing searches and working on sets of records my plan was to automate a ‘header’ for their printouts that would tell them what their search was that yielded the found set. And of course I don’t want to give them the whole SQL as that wouldn’t mean much to them. I was thinking of simply something like:
Searched fields: tank = 4, stack = 13, pie = 2, amount = null
How about something a lot easier. The following code will return all search fields and terms when placed in a form’s onSearch() event (before doing the search):
// capture all search fields and values
var searches = []
var providers = alldataproviders
for (var i = 0; i < providers.length; i++) {
if ( forms[controller.getName()][providers[i]] ) {
var item = { field : providers[i],
term : forms[controller.getName()][providers[i]] }
application.output(item.field + ' = ' + item.term)
searches.push(item)
}
}
// do something with searches
return searches
That’s big time! Thanks David. Now I really want to hang out with Geeks on the Beach… Having skipped 4 and 5 in so many ways I’m starting over again. I either never knew or had forgotten that one could actually circle through the fields while in find mode and capture the contents. I had to extend this to some related fields but that was easy.
The one thing that was kind of weird was that when I set it up to cycle through the related providers, I’d get an error message that the property length was undefined.
var relProv = forms[controller.getName()].linvmaster_to_linv.alldataproviders;
var len = relProv.length
Nonetheless it correctly gave the length value for cycling through the for statement but couldn’t figure out why it would give that message.
The property length is undefined in relProv
Didn’t affect things but I ended up hard-coding the actual possible fields that the user would search on partly to keep the cycling to the minimum but mostly to get rid of the annoying warning!
No it’s weird. You still get the ‘property length is undefined’ even though there obviously is a property length and the for loop works fine with it. Something is throwing it off when it goes to a related allproviders I think. It computes the length OK (so that var len actually gives a number) but the warning is there regardless. Try it sometime.
Ah yea, Servoy 6. We have a love/hate relationship with Servoy 6’s warning system. One of the reasons we still do 80% of our work in Servoy 5 (more because the editors are so much slower in Servoy 6 as a result).
What you’re seeing is that this would work fine with no warnings:
var relProv = forms.myFormName.linvmaster_to_linv.alldataproviders
Once you do a dynamic reference:
var relProv = forms["myFormName"].linvmaster_to_linv.alldataproviders
Servoy no longer “knows” what the data type of relProv is and so when you use an array function/property on it the warning system kicks in.
Read up on JSDocs. Adding the following to your method signature let’s Servoy know what type the variable is:
@param {Array} relProv My variable description
Now for a cool benefit of this system: Servoy 6 gives you code completion when you type a declared variable name. Up to and including all of the array functions/properties in this case.
Thank you. Great explanation. I wasn’t going down that road here because I was stupidly thinking that “var providers = alldataproviders” didn’t have a problem so why should the related alldataproviders have a problem. It tastes like an array, it looks like an array, it gets treated as an array but it’s not DEFINED as an array And I guess “var providers = alldataproviders” doesn’t get a warning because the context of 'alldataproviders is given directly as I am on that form… I’ll get it in the end (I hope).