Allowing users to save a query by finding on a form

[chris]

1) Please confirm: I can build a tabbed SQL builder that lets users construct and save custom queries to be used again and again?

2) MORE IMPORTANTLY - many of my users don't want to learn this method of saving queries. They request again and again to be able to go to a form, perform a find like they do in Filemaker, and to be able to save that find to perform again later. Since Servoy converts a find on a form to an SQL statement, is it possible to save this SQL statement to be executed again and again - I envision the user saving the name of the find and then viewing a tab or portal of all finds and just clicking on the one s/he wants (which would just execute the SQL statement). THAT WOULD BE AWESOME.

Thanks!

[Jan Blok]

1) yes
2) added to planning

[bcusick]

1) Please confirm: I can build a tabbed SQL builder that lets users construct and save custom queries to be used again and again?

Open the "CRM" Example that comes with Servoy - and click on the "SQL Report" button in the controller.

2) MORE IMPORTANTLY - many of my users don't want to learn this method of saving queries. They request again and again to be able to go to a form, perform a find like they do in Filemaker, and to be able to save that find to perform again later. Since Servoy converts a find on a form to an SQL statement, is it possible to save this SQL statement to be executed again and again - I envision the user saving the name of the find and then viewing a tab or portal of all finds and just clicking on the one s/he wants (which would just execute the SQL statement). THAT WOULD BE AWESOME.

You can do that in a more limited way at the moment - just use your own "find form" to capture the values of the fields - and store your own SQL.

Cheers,

Bob Cusick

[Guest]

You can do that in a more limited way at the moment - just use your own "find form" to capture the values of the fields - and store your own SQL.

Thanks Bob - so in doing this, could you let me know which option below works better?

OPTION 1: hard code each field name. clunky
I'm thinking: "if firstname != '' then add firstname to WHERE clause, and then the method says "if lastname !=" then add lastname to WHERE clause, on through each field name.

OR OPTION 2: easier
Or can we set it to start at the first field, whatever it is, and loop through the following: use some kind of status current fieldname to check if it has criteria, and if it does to copy the fieldname and criteria to the WHERE clause. Can we set this up without naming each field so the method is easy to setup and won't have to be modified if we add a field to the form? How would we ensure the loop ends?

Thanks - this is really a wonderful tool. The promise of easily saving queries has come up in every contract meeting I've had.

[bcusick]

OR OPTION 2: easier
Or can we set it to start at the first field, whatever it is, and loop through the following: use some kind of status current fieldname to check if it has criteria, and if it does to copy the fieldname and criteria to the WHERE clause. Can we set this up without naming each field so the method is easy to setup and won't have to be modified if we add a field to the form? How would we ensure the loop ends?

This is the way to go for sure!

Use a "for" loop to iterate through the elements (be careful - becuase it will also do portals, tabpanels, lines, etc. IF they have a name.

for ( var i = 0 ; i < elements.length ; i++ ) {

//..... YOUR CODE HERE

}

Cheers,

Bob Cusick