Load Records

Classic Servoy
1

I would like to load a dataset that queries a column that is not the pk.

This is the method

var query = "SELECT DISTINCT totals_query FROM donation_detail WHERE detail_date = '" + utils.dateFormat(donation_date, 'MM/dd/yyyy') + "'"
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, -1);
var recCount = dataset.getMaxRowIndex()

forms.Enter_Donation_Detail.controller.loadRecords(dataset)
forms.rpt_Current_Totals.controller.showPrintPreview()

I get the following error message

Error loading primary key data, for input string:

Is there a way to return the pk’s fo the dataset

Thanks

2

Is it possible to convert a dataset to a foundset?

Thanks

3

Couple of things…

-Make sure your date format matches what your database is expecting.
-Consider using a ‘?’ in place of your date string within the query, then use an array to pass the date value. This adds a little security to your system (to avoid sql injections) and also makes it a little easier when you don’t have to figure out the matching quotes.

Consider rewriting like this, using one dataset to get the results you want (distinct totals_query) and another dataset to get the PK’s for those same records:

// This query will get the records you want in your dataset
var query = "SELECT DISTINCT totals_query FROM donation_detail WHERE detail_date = ?"

// This query will return the PK's of the records you're wanting
// which will pass to .loadRecords() below
var query_pk = "select pk from donation_detail where exists " +
"(SELECT DISTINCT totals_query FROM donation_detail WHERE detail_date = ?)"

var args = new Array()
args[0] = utils.dateFormat(donation_date, 'MM/dd/yyyy')

var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, args, -1);
var ds_pk = databaseManager.getDataSetByQuery(controller.getServerName(), query_pk, args, -1);
var recCount = dataset.getMaxRowIndex()

// Load the desired records into a form's foundset using the PK's from ds_pk
forms.Enter_Donation_Detail.controller.loadRecords(ds_pk)
// put some code here to do what you want with dataset

Hope that helps.

4

Thanks Loads Jason

I’m still green at this sql stuff. I wasn’t aware that you could nest one query inside of another. Interesting.

For test purposes I have six records in the “donation_detail” table. Five of the six have unique data in the “totals_query” column. The sixth is a duplicate. The “dataset” returns five records. The “ds_pk” returns six records. When these records are loaded it still shows all six records instead of constraining the list to only the unique records.

Also, would you please explain what “exist” does in the “query_pk”.

If you have any more suggestions or help it would be greatly appreciated.

5

I could try to explain the WHERE EXISTS clause, but this link will do it more justice:

I thought that query would work off the top of my head, but forgot that EXISTS ignores the field list in the sub-select statement, so it didn’t do what I originally thought. Try this code to see if it gets you the records you’re looking for. I tested this one and it seemed to work, if I understand what you want.

This one groups the records by totals_query, then gets the smallest PK value out of each group, thus returning the equivalent of the DISTINCT statement.

See below:

    // This query will get the records you want in your dataset
    var query = "SELECT DISTINCT totals_query FROM donation_detail WHERE detail_date = ?"

    // This query will return the PK's of the records you're wanting
    // which will pass to .loadRecords() below
    var query_pk = "select pk from donation_detail where pk in " +
    "(SELECT min(pk) FROM donation_detail WHERE detail_date = ? group by totals_query)"

    var args = new Array()
    args[0] = utils.dateFormat(donation_date, 'MM/dd/yyyy')

    var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, args, -1);
    var ds_pk = databaseManager.getDataSetByQuery(controller.getServerName(), query_pk, args, -1);
    var recCount = dataset.getMaxRowIndex()

    // Load the desired records into a form's foundset using the PK's from ds_pk
    forms.Enter_Donation_Detail.controller.loadRecords(ds_pk)
    // put some code here to do what you want with dataset

Let me know if it works okay.

6

Sweet!

What a solution. Thanks Jason. Everything looks great.

Just one more question. Can I add a sort to the end of the sql statement?

7

Can I add a sort to the end of the sql statement?

Yes. Just put “order by asc” or “order by desc” at the very end of the query, outside the subquery.

Like so:

var query_pk = "select pk from donation_detail where pk in " +
    "(SELECT min(pk) FROM donation_detail WHERE detail_date = ? group by totals_query) " +
     "order by totals_query asc"

Using SQL is my favorite way to find records if there’s any complexity in the search. You can do much more with SQL than you can with FOUNDSET.find() / FOUNDSET.search().

A few weeks spent learning JOINS and other more complex SQL is time very well spent in Servoy.

Enjoy!

8

Thanks Jason

This has been a very fruitful and enjoyable conversation as well as pointing me in the direction that I wanted to go anyway.

Maybe as more challenges arise i can present them with the hope that we can have another one of these enlighting conversation.

Thanks again

9

Glad I could help. Don’t know where I’d be without the forums!