HI,
I am not able to find any information on a many to many related form.
The scenario is this:
Form 1: Shelfs
Form 2: Inventory
What I have is a list of shelves on one form. The user then needs to see a list of inventory for ALL of those shelves in the next form. After some investigation in the forums I came accross 3 scenarios. The only scenario that I have been able to find that works is scenario 3. This is really slow however. Is there a better way to do this? thanks
var dataset = databaseManager.convertFoundSet(foundset, shelves_to_inventory)
foundset.loadRecords
var query = “Select shelve.shelve_id from shelve where shelve.shelve_id = ?'”
var pks = databaseManager.getFoundSetDataProviderAsArray(forms.shelves.foundset, ‘shelve_id’);
foundset.loadRecords(query,pks);
putting the form into find mode and running a search with these pks
foundset.find();
var pks = args = databaseManager.getFoundSetDataProviderAsArray(forms.shelves.foundset, 'shelve_id');
foundset.shelve_id = pks.join('||');
var results = foundset.search();
foundset.loadRecords(foundset);
Scenario 2 is buggy, the query needs to have “… where shelve.shelve_id in (?,?,?,?,?,?,?,?,?..)” with as many questionmarks as there are PK values in your pks array.
Don’t forget you have to load the dataset which you retrieve in scenario 1 on your inventory form, otherwise nothing happens!
If you follow scenario 2 with the extension Paul mentioned, please be aware that the number of arguments you pass into your query are not unlimited.
We found (using Sybase DB with a real lot of arguments) that at some point Sybase is throwing the error that you require ‘wide table support’…
After further exploring, we have been able to utilize the query method to retrieve the dataset.
var dataset = databaseManager.getDataSetByQuery(server, sqlstring, null, -1);
foundset.loadRecords(dataset);
As pbakker and mboegem have both mentioned, we are running into sql problems with lists larger than 1000 due our use of an in clause.
Scenario 1 just doesn’t seem to work. Not sure why as the relationship is utilize in other forms, as a one to many, in other forms without issue.
could you maybe explain which foundset and which relationship would need to be passed?
i have checked out the sample code but it isn’t very informative and it I believe the example does not show a multiple key based relationship either.
I have been able to utilize the databaseManager.getDataSetByQuery and passing a select statement that returns only the primary keys, and then passing this dataset into the loadRecords method.
The performance isn’t too bad, but because I am using a very large dataset, I am running into an issue with the # of parameters allowed in the “IN” clause portion of the query.
I would probably prefer to utilize the convertFoundSet method because I assume I won’t run into the issue mentioned above.
Any thoughts on how I could get around my “IN” clause problem? or how to get the actual convertFoundSet to work?
paulc:
Any thoughts on how I could get around my “IN” clause problem?
Did you try getting the data based on the sql of your current foundset?
Something like this:
var _sql = databaseManager.getSQL(foundset);
var _pars = databaseManager.getSQLParameters(foundset);
var _qry = 'SELECT pk FROM table WHERE fk IN (' + _sql + ')';
var _ds = databaseManager.getDataSetByQuery('YOURSERVER', _qry, _pars, -1);
paulc:
or how to get the actual convertFoundSet to work?
I don’t get why it doesn’t work for you…
This is what the sample code is like and should work:
// Convert in the order form a orders foundset into a orderdetails foundset,
// that has all the orderdetails from all the orders in the foundset.
var convertedFoundSet = databaseManager.convertFoundSet(foundset,order_to_orderdetails);
// or var convertedFoundSet = databaseManager.convertFoundSet(foundset,"order_to_orderdetails");
forms.orderdetails.controller.showRecords(convertedFoundSet);
Otherwise: could you post your code so we can have a look at it? + which version of Servoy are you using?