Getting the column names and search parameters in find

Questions, tips and tricks and techniques for scripting in Servoy

Getting the column names and search parameters in find

Postby john.allen » Wed Feb 01, 2012 6:33 am

Hi,

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

Thanks for any pointers (Servoy 6.0.5)
John Allen
Stanford University
john.allen
 
Posts: 515
Joined: Wed Jul 02, 2003 10:07 pm
Location: Stanford CA USA

Re: Getting the column names and search parameters in find

Postby david » Thu Feb 02, 2012 9:06 pm

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):

Code: Select all
// 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
David Workman, Kabootit

Image
Everything you need to build great apps with Servoy
User avatar
david
 
Posts: 1727
Joined: Thu Apr 24, 2003 4:18 pm
Location: Washington, D.C.

Re: Getting the column names and search parameters in find

Postby john.allen » Fri Feb 03, 2012 12:38 am

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.
Code: Select all
   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!
John Allen
Stanford University
john.allen
 
Posts: 515
Joined: Wed Jul 02, 2003 10:07 pm
Location: Stanford CA USA

Re: Getting the column names and search parameters in find

Postby david » Fri Feb 03, 2012 2:44 am

alldataproviders returns an array or null (instead of an empty array) so try this:

Code: Select all
var relProv = forms[controller.getName()].linvmaster_to_linv.alldataproviders
var len = (relProv) ? relProv.length : 0


We could probably still squish you in.... :)
David Workman, Kabootit

Image
Everything you need to build great apps with Servoy
User avatar
david
 
Posts: 1727
Joined: Thu Apr 24, 2003 4:18 pm
Location: Washington, D.C.

Re: Getting the column names and search parameters in find

Postby john.allen » Fri Feb 03, 2012 3:21 am

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.
John Allen
Stanford University
john.allen
 
Posts: 515
Joined: Wed Jul 02, 2003 10:07 pm
Location: Stanford CA USA

Re: Getting the column names and search parameters in find

Postby david » Fri Feb 03, 2012 4:00 am

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:

Code: Select all
var relProv = forms.myFormName.linvmaster_to_linv.alldataproviders


Once you do a dynamic reference:

Code: Select all
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:

Code: Select all
@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.
David Workman, Kabootit

Image
Everything you need to build great apps with Servoy
User avatar
david
 
Posts: 1727
Joined: Thu Apr 24, 2003 4:18 pm
Location: Washington, D.C.

Re: Getting the column names and search parameters in find

Postby john.allen » Fri Feb 03, 2012 4:45 am

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).
John Allen
Stanford University
john.allen
 
Posts: 515
Joined: Wed Jul 02, 2003 10:07 pm
Location: Stanford CA USA


Return to Methods

Who is online

Users browsing this forum: No registered users and 5 guests