Does anyone know whether it is possible to use addFoundSetFilterParam on valuelists and if so, how can it be achieved.
Thanks for your help.
The reason one uses AddFoundSetFilterParam is generally because one wants to limit what a particular user can view for privacy, efficiency or whatever. I presume you want to limit a valuelist for similar reasons, i.e. to ‘limit’ the valuelist for some reason either to do with the record a user is on, who the user is or whatever. The best way I have found to do that is by using the ‘setValueListItems’ function (located under ‘Application’ in the method editor).
You name and create a ‘Custom Valuelist’ as per normal but leave it empty of values. Then you create either a conditional array or just use a ‘dataset’ derived from an SQL query that is your ‘Filter’. Have the ‘setValueListItems’ triggered then by whatever is appropriate (RecordSelection, onShow, etc.) and you have your conditional, filtered Valuelist based on whatever you want.
Thanks John for your response. I have now managed to produce a valid valuelist, however, the method takes about 2 minutes to run!! Probably due to my limited knowledge of SQL producing an inefficent code.
Do you have any suggested improvements to the code I am using. (The use of the distinct is because ‘Tony Knight’ might have 5 records in the table each having a different sgb_branch_nbr.
Thanks in advance for any help.
var user_id = globals.UserID;
var queryStatement = "SELECT DISTINCT sgb_branch_contact_name FROM sgbbranchcontacts_branchaccess WHERE sgb_branch_nbr IN (SELECT sgbbranchcontacts_branchaccess.sgb_branch_nbr FROM sgbbranchcontacts_branchaccess WHERE (sgbbranchcontacts_branchaccess.security_user_id = '" + user_id + "'))";
var query = queryStatement;
var maxReturnedRows = 400;
var dataset = databaseManager.getDataSetByQuery(forms.sgbbranchcontacts_branchaccess.controller.getServerName(),query,null,maxReturnedRows);
var branch_contact_names = dataset.getColumnAsArray(1);
application.setValueListItems( 'userSGBNames', branch_contact_names);
Clive Sanders:
Do you have any suggested improvements to the code I am using. (The use of the distinct is because ‘Tony Knight’ might have 5 records in the table each having a different sgb_branch_nbr.Thanks in advance for any help.
Try to avoid using an IN SELECT statement where you can do the same with a JOIN. This is less costly.
This is your code:
SELECT DISTINCT sgb_branch_contact_name
FROM sgbbranchcontacts_branchaccess
WHERE sgb_branch_nbr IN
(SELECT sgbbranchcontacts_branchaccess.sgb_branch_nbr
FROM sgbbranchcontacts_branchaccess
WHERE (sgbbranchcontacts_branchaccess.security_user_id = <user_id>));
Looking at your SQL statement more closely shows me that you don’t need the extra select statement at all since you are querying in the same table(!).
So your query should be:
SELECT sgb_branch_contact_name
FROM sgbbranchcontacts_branchaccess
WHERE sgbbranchcontacts_branchaccess.security_user_id = <user_id>
GROUP BY sgb_branch_contact_name;
So to make it easy for you this is the code you have to paste in your method:
var queryStatement = "SELECT sgb_branch_contact_name FROM sgbbranchcontacts_branchaccess WHERE sgbbranchcontacts_branchaccess.security_user_id = '" + user_id + "' GROUP BY sgb_branch_contact_name;";
Thanks for that John, unfortunately it doesn’t give the desired result. What it brings back is just me (i.e. the user).
In my code I was selecting all DISTINCT sgb_branch_contact_name that had a sgb_branch_nbr contained in the array produced by the sub-select.
If I had a better knowledge of SQL perhaps I could have worked out from your code what I should be doing!! Help…
It certainly was a lot quicker.
Hi Clive,
There can be a number of reasons that your method is taking so long. First of all is it definitely the query part? Using Robert’s code - even though it wasn’t what you need - you said it ran a lot faster. You also mentioned that the whole method took about 2 minutes to run. Using Robert’s code how much was that reduced by? In other words there might be other things going on too. As for the query part:
It sounds like a particular user_id will have access to certain branches and certain contact_names will be associated with each branch. As a side note normally that data alone would be split among three tables, wouldn’t it? If there is a ‘branch’ table, then that table would have two child tables, one for user_ids (if each branch can have multiple user_ids) and one for contact_names (if each branch can have multiple contact_names). But considering it as is, most SQL databases would do this most quickly as a self-join query with the join being on ‘sgb_branch_nbr’. I would run it as:
"SELECT DISTINCT n.sgb_branch_contact_name " +
"FROM sgbbranchcontacts_branchaccess n, sgbbranchcontacts_branchaccess id " +
"WHERE n.sgb_branch_nbr = id.sgb_branch_nbr " +
"AND id.security_user_id = '" + user_id + "' " +
"ORDER BY n. sgb_branch_contact_name";
ahh…now I see what you want.
Yes, the query of John should give you your result.
By the way DISTINCT or a GROUP BY would give you the same result.
One might be faster than the other though.
Clive,
If you want to learn SQL check this SQLcourse site out.
Thanks very much for your help John and Robert, John’s solution worked a treat, and it ran very quickly.
This forum certainly does work very well, in no small part due to the willingness of those who contribute to give free advice and guidance.
You name and create a ‘Custom Valuelist’ as per normal but leave it empty of values. Then you create either a conditional array…
Do you have examples of this?
I want to have a valuelist load in a field depending on who’s looking at the record.
Thanks in advance!
Check out the excellent tutorial “Dynamic Valuelists” by Bob Cusick
It will give you a good basic understanding!