In FileMaker Pro it was possible to use multiple keys on the left side of a relationship to match with 1 key on the right side.
This was done by concatenating any keys on the left side with (soft) returns.
Servoy can handle multikey relations but I can’t seem to work it like FMPro did.
Any smart workarounds?
Well if you could put those keys into a global array and use that on the left side of the relation, maybe that would do the trick? You might find this post interesting/helpful: Storing arrays as globals - Classic Servoy - Servoy Community
Well that wouldn’t work because you can’t relate a media field with an integer field. Which the FK’s are.
I might have to resolve to a query instead and show the result list in an unrelated tabpanel. Ofcourse it means I have to make it (re)load at every possible action that can affect this.But when other users change the data it won’t update because my method doesn’t react on those broadcasts.
That’s my main reason to find a solution like FMPro had.
Yep, I’ve been down that road too… and why it would be so nice if we could use a media field with an array of integers to do joins with int fields. Better would be a global field that naturally holds an array of integers and that also can be used in a relation with an int field. That would definateley “one-up” FMP as doing the trick with the FMP text field is a workaround itself.
Another idea (also requiring a new feature) would be to make addFoundSetParam more dynamic - so it is event based rather than so curiously persistant - and allow it to take a regular array of ints for an argument. THat would be cool.
Probably he easiest way to get what you need is to script a find/search method triggered as needed to load the needed data. Not elegant, slow to run, and more cumbersome to develop and maintain, but it should get the end effect you need.
Would the ‘getFoundSetDataProviderAsArray()’ function work here in combination with a loadRecords() function ?
This would store the pks as an array and then load the related records by passing that array to the loadRecords() function ?
Cheers
Harry
Hi Guys,
Let me help save you hours of trying: NEVER GOING TO HAPPEN!
The basic problem (as discussed other places) is that Servoy uses the SQL predicate IN when doing loadRecords().
For example:
SELECT pk FROM table WHERE pk IN (101, 102, 103, 104, 110) ORDER BY pk
The limitation is a SQL limitation on the number of items in the IN clause. It’s limit is 200. Period. The end. Ever. Not going to happen to have more.
One way to do what you want is to update a “keywords” or “group” field for each customer in a group and then FIND by that criteria.
Doing it with SQL <> Doing it with FMP.
Cheers,
Bob
One way to do what you want is to update a “keywords” or “group” field for each customer in a group and then FIND by that criteria.
Yes, that seems to be the best way in Servoy. However, I do miss the trick in FMP that started this thread. It was great for making finds unnecessary and filtering portals, etc. It’s been awhile, but I think I just used a global field and added pk’s with a carraige return after each one making a kind of FMP index. Due to the way FMP stored its index this worked. You could use this in a relationship to do lots of interesting things.
The elegant solution in Servoy would be to allow a global field of type of array (ints) and allow that to be used in relations. You can store array in globals of type media, but then you don’t get to use it in a relation.
I imagine the SQL query could be done by joining on a “view” table of the pks stored in the global. The view would be maintained without the developer’s direct intervention and the SQL would also be hidden as per the norm.
Another approach might be a dynamic sort of addFoundSetFilterParam(). Yikes, does that mean an even longer function name like refreshAddFoundSetFilterParam()?
Hi Bob,
You’re right about storing the data in a separate table - or in the same table as the data, etc.
addFoundSetFilterParam() works by simply by appending data to the “where” clause (again, limited). So that will never work either.
So is it technically feasible to have a global array field in Servoy that contains pks in such a way that we could use them on the left side of relations and of course not have to see any of the ugly SQL behind the scenes?
Did I stutter?
NOT EVER GOING TO HAPPEN FOR OVER 200 RECORDS - EVER. NEVER. NOT EVER. NO. NEIN. NEIGHT. NAY.
This is not FileMaker Pro!
NO NEED TO SHOUT, Bob
I don’t think the 200 limit is the issue. JJust show me the next 200 records I want and I can deal with that, but don’t make me limit what I ask for to only 200. Forget FMP. We can do what we need to do with Servoy now. However, with a little (I lied) a lot of work by the Servoy team, we can have a tool that allows us to develop killer solutions faster. Development speed is essential.
I think we want an easy and fast way to create a relation with a multi-valued key on the left side of the relation as ROCLASI initially requested here. For example,lyou are a typical user and have a customers form and on it a portal showing orders. You want to filter that set of orders where only those with Joe was the sales person, then you want to also show the orders where 10 or more SuperWidgets were ordered, then you want to add some other orders to the list and omit some others based on whatever arbitrary nonsense you can imagine. You know what I’m talking about. Now you have the list you want for that customer and next you go to the next customer to go through this all over again. Sometimes you don’t even follow the same “logic” on the next one.
You can support this in Servoy by creating a new table called customer_orders_filtered and in it storing the order_ID and customer_ID. Then whenever the selected orders for a customer is updated, you just update the records in that table. The portal just shows those orders from customer_orders_filtered. This works, but it is time consuming and increases solution complexity. I hate giving an edge to FMP on this.
Servoy could better FMP by doing this for us in the background. Rather than put keys in a fake array field like FMP does, it would be cool to be able to use a real array. We could then store the selected order_IDs for a customer in a global field of type array that can be used in a relation with the orders table for display in the portal. With this feature, a developer could set this up in seconds and scripting it would be easy with javascript’s baked in array handling.
To do this, Servoy would just create a VIEW in the backend db for this “array field” and hide it from the developers’ dataproviders list (I suppose if this required persistence, a real table would be required, but I haven’t thought through that). Anyway, the developer would select this “array field” for use in the relation to show the orders records in the portal as described. The developer would be able treat this field as an array in the methods to change the portal records dynamically. When the user adds an order_ID to this “array field”, what really happens is that a record with order_ID and customer_ID is added to the view (or table) described.
I might be missing something here or just being lucky - wouldn’t be the first time! - but I think you CAN do some of these things. For instance the loadRecords() function takes an argument of a dataset. The convertToDataSet() function takes an array as an argument. I have been very successfully using those for a number of days to store and reload a foundset of records. The method below has somewhere between 327 and 447 pk’s in it and it runs in a flash with thousands of related child and grandchild records. The pk is obviously abbreviated here!
var pk = new Array(13794,61503,…,187340,190902,204704);
var dataset = databaseManager.convertToDataSet(pk);
forms.patient_controller.controller.loadRecords(dataset);
forms.patient.controller.show();
globals.CurrentFoundCount = dataset.getMaxRowIndex();
var x = forms.patient.elements.tabs_70.tabIndex;
forms.patient.elements.tabs_70.tabIndex = x-2;
The other way to do it is as getDataSetByQuery in which the WHERE clause is dynamically created with your pk/dataset. I don’t think there is a 200 limit on that function. I believe that depends much more on the type and settings of the particular SQL database. I am running this on a Sybase ASE database (not ASA) so it is possible that that is the difference. I am planning on getting a found set of records either by an ‘outside’ query or the user gets it from within Servoy. Then the user(s) will use that foundset, eliminating records as they go along that they find on closer inspection don’t fit the criteria. (We run a research database in which we are constantly working with different subsets of patients). Of course if the individual database does have an upper limit of 200 pk’s then it would still be possible to split them into datasets of 200 and work through them that way.
At the end of their session I plan on comparing the original foundset with their current foundset. If there is a difference they then have a choice as to which one they keep which will then be stored in a table with a user_id and ‘search_id’, search_name, etc. I don’t have that yet but I think that will work based on everything I have done so far.
Naturally I am saying all this in relation to the working/marking/omitting/restoring a foundset of records part of this thread as well as:
P.S. I will be trying something similar in MS SQL database too in the next couple of days.
As for the 200 limit withing the “in” clause: To my knowledge, the limitation differs between different databases.
As for an SQL workaround: “UNION” and “UNION ALL”
These commands will let you “paste” the results of two queries together. Offcourse, the resultssets have to be the same number and type of columns.
The difference between Union and Union All is that Union gives you the Unique records of the combined results sets, Union All gives you also the duplicates.
Just my 5 cents…
Paul
A dataset isn’t limited to 200 keys if no IN clause is used, right? If the dataset is just a query on a table of a zillion keys and you define it as ```
SELECT pk FROM myview
That would get everything, right?
To do what you can do with FMP, we need a global field type that can hold a dataset, that does not have a 200 key limit, AND that can be used in the left side of a relationship. Maybe a smarter media-like field could store the keys? If the keys were stored a SQL view (a computed table) and the dataset included all the kesy all the time, it would be fast and require little memory. And now that we have this nice view of keys in place, we can do the joins needed too.
You would just create a global field of type dataset with a relation based on it. Then you drop in a portal or tab panel using this relation and add and omit records to the portal using some simple functions and the drecords you want would be automatically displayed.
Where's the vegemite?Hi bob cart,
Here’s an example that kinda simulates the FMP multi right key
note: this will work only with tabpanels. Not with portals since I’m not using any relations here. Just plain SQL.
It’s ment as a possible different approach to this multi key issue
(Not sure if it would fully solve your obstacles)
Assume a company list with a “companyFilter” on top (global textarea) where you can enter multiple lines of text.
Create a script that get’s triggered onDataChange companyFilter
or simply a button action.
//split the filter entries separated by enters into an array
var companyFilterArray = globals.companyFilter.split("\n")
//initialise a variable that will hold the final query
var query = "SELECT companiesid from companies WHERE ";
//loop through all the entries and concatenate the query variable
for(var i=0 ; i<companyFilterArray.length ; i++)
{
query += "company_name like '%"+companyFilterArray[i]+"%' OR "
}
query = query.substring(0, query.length-3) //remove last OR
// query will look like
// SELECT companiesid from companies
// WHERE company_name like '%Ab%' OR
// company_name like '%E%' etc...
//load a primary key Dataset object from the query result(which is a list of Pk's)
var dataset = databaseManager.getDataSetByQuery("crm", query, null, 100000 );
//load this dataset object into the loadRecords function
controller.loadRecords(dataset);
Basically you can create any WHERE clause you want, combining multiple globalFilters in AND/OR fashion , starting with , ending with, case insensitive etc…
You could probably also turn this script into a global function for use on multiple forms
(see David’s article on “globalising” scripts )
http://www.servoymagazine.com/home/2005/01/quiz_week_11005_1.html
That is the method I am using now.
But like I said before, this needs extra work on my part to make sure this query is triggered everytime a record is changed.
Also broadcasts of changes by other users are not supported this way.
Indeed.
However, changes on record data are still being broadcasted.
Only foundsets won’t be auto updated.
Example:
Client 1 has a filter on companyType set to X
and sees 10 records
Client 2 decides to change a company Type from X to Y
Client 1 will still have 10 records in his foundset
but will see one record being changed from X to Y.
I can imagine a user would rather see this change instead of unexpectedly seeing his foundset drop from 10 to 9 records.
But then again, you can probably come up with an example where this is wanted behaviour?
(like client2 changing a record from Y to X )
Maarten, thank you for this code and explanation. By storing the keys as text in a global and moving it to a from an array as needed, you get the benefit of storing arrays in globals with only one added step. This global can then be used to show the selected records with loadRecords AND there isn’t a 200 key limit. So far so good.
The only thing this doesn’t provide is the kind of dynamic filtering we can get through a relation. I know a global field full of ints and \n’s is a type problem when trying to join with an int pk. Would it be hard to allow a field of this type to work in a relation? Couldn’t you store its values in a view and do a join somehow?
In any event, this is a very helpful technique for allowing a user to select a bunch of records by whatever reason they want and then using those selected records to do anything.
I can see this as a nice way to implement user interfaces like the servoy sort dialog with a list on the left to choose from so you can add them to the right or move them back. You can even highlight them at the same time. Once you get the list on the right how you want it, then you can run the method that does what you want all without yet changing any foundsets.
While there might be better ways to do some of these things, (though I don’t know them, yet) this technique lets me do things how I’'m used to but with new tools. This is easier than learning the new tools and a new approach all at once.
Thanks!
Bob,
Don’t use portals - use a listview with a tabless tabpanel (tabpanel with tabs hidden). That way you can perform a search in the list view to “filter” however you want.
bcusick:
Don’t use portals - use a listview with a tabless tabpanel (tabpanel with tabs hidden). That way you can perform a search in the list view to “filter” however you want.
Thanks, Bob. I do use far more tabless tab panels than portals, but then I usually have to add another form to my growing list… Searching doesn’t work in the cases we’re discussing here. There are times when a selection must be done manually. Events are then available to trigger methods for doing what is needed, but that also takes more work and is more work to maintain, extend & generalize.
You could also do searches and skip using addFoundSetFilterParam, but I prefer the ease and elegance of the simple function (esp. after seeing your nice tutorial! - I got da power!). We just need a way to do something as simple as that, but that can be dynamically updated.