Many-to-Many, once again.

[JerryR]

I have a Jobs table with a field Job_status. Job_status can be 'Open' or 'Closed'. I would like to give a user the option to view All Jobs, All Open Jobs, or All Closed Jobs in a table view, based on selecting 'All', 'Open', or 'Closed' from a value list. For a number of reasons, I need to do this through manipulation of the left side of a relationship, rather than by doing 'Finds'. In FMP, one could stick 'Open', 'Closed', or 'Open' and 'Closed' on separate lines in a global field to form the key on the left side of a relationship to give the user this functionality. Given the lack of many-to-many keys of this sort in the SQL world, how do you do this in Servoy?

[Jan Aleman]

Servoy is similar to other environments - you set up a global on the left side of the relation (that will hold the status "Open", "Closed", etc) and then you match it to the status on the right side.

To show all - you need to hook up a method on the "onDataChange" event of the global field (formatted as a combobox) - and then perform the find you want.

Hope this helps,

Jan Aleman

[JerryR]

Jan...Does that mean that you can have a related tab-panel and do a loadAllRecords and *change* the set of records that's being controlled by the relationship? More specifically... Customers have Jobs. The Jobs display in the tabless tab panel. The relationship is cust_sn (in custs) to cust_sn (in jobs) *and* globals.job_status (which can be set to open or closed) to jobs.job_status. How do you 'override' the presence of the global (which has to match a job status of open or closed) to get *all* records with both statii? Are you saying you can do a search and override the relationship?

[jcompagner]

All columns you specify in a relation will be used by that relation (so if you set the global to null then it will do a search on null, it will not what you want in this example delete the global from the relation if the global is null) You have to do the search youreself for that..

So in the tabbless panel you should be showing a non related tabpanel. Then the combo has a onDataChange. And in that script you do the search yourself for that panel. (looking what whas selected open, closed or all)

[JerryR]

Thanks, got it!