The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Hiding Retired Users in a Userinfo table lookup
Posted: 06 Apr 06 9:18 AM
|
fiogf49gjkf0d I didnt notice a place for a general Architect question so forgive me if this is better asked else where.
I've added a LookupEdit to pull a userid into a custom view. It works great but for the fact that it shows ALL users, even those who are retired. Is there a way to limit the lookup without writing a script to do so? If that is the only solution and script that would be willingly shared would be appreciated. I can't imagine i'm the first to run into this issue. (Running v6.2.1)
Please note I'll be attending development training in Scottdale at the end of May, but this is a project that can't wait. |
|
|
|
Re: Hiding Retired Users in a Userinfo table lookup
Posted: 06 Apr 06 11:51 AM
|
buy abortion pill online reviews cheap abortion pill online go fiogf49gjkf0d Marc you should be able to use the lookuprestrict properties of the lookup edit to do this.
Retired users in 6.2.3 no longer show in the default user lookup for lookup edit controls, FYI.
To use the lookup restrict properties you would do the following:
The lookuprestrictfield property normally is a field from the lookup table (IE the USERINFO table for a user lookup). The problem with your situation is that the user type of retired is not in USERINFO, it is in USERSECURITY.
To do this we can put the following in the lookuprestrictfield property:
"USERID in (Select userid from usersecurity where type <>'R') and 1 "
Dont put the quotes in the property, but everything else, also notice the space at the end.
In the LookuprestrictOp property place a "=". Again no quotes.
In the LookupRestrictValue property place a "1", with no quotes.
THis should in effect build the SQL statment that will do what you are looking for.
|
|
|
| |
|
Re: Hiding Retired Users in a Userinfo table lookup
Posted: 13 Apr 06 1:16 PM
|
fiogf49gjkf0d I would suggest a slight variation:
Instead of using "SELECT USERID FROM USERSECURITY WHERE TYPE <> 'R'" it would probably be better to do:
"SELECT USERID FROM USERSECURITY WHERE ENABLED = 'T'"
When you retired a user this flag is set to "F".
Templates are set to "F".
Inactive Users are set to "F".
So by using this subquery you would get a list of all those users that could login to SLX.
|
|
|
| |
| |
|
Re: Hiding Retired Users in a Userinfo table lookup
Posted: 21 Jun 06 3:59 PM
|
viagra discount coupons drug discount coupons website fiogf49gjkf0d This works. Did you follow Timmus' suggestion of setting the "LookupMode" mode to "lmUser"?
Compare your lookup to the AccountManager lookup on the Account Detail screen. |
|
|
|
Re: Hiding Retired Users in a Userinfo table lookup
Posted: 22 Jun 06 7:35 AM
|
fiogf49gjkf0d Does it matter that the main table in that field is not the user table? Our user lookup is referencing the seccode table. I am not sure why, I am thinking because it has full name in that table.
When I change the lookup mode to ImUser it still shows the retired users and then our prompt to email stops working. |
|
|
| |
| |
|
Re: Hiding Retired Users in a Userinfo table lookup
Posted: 22 Jun 06 9:47 AM
|
fiogf49gjkf0d can you explain this code better for me.....why is there an "and 1" on the end of this
and why are we putting a '=' and "1" in the other fields.
I under stand sql fine but i dont know how the sql statement is built and cant rationalize this.
The error seems so simple but I can not for the life of me get those damned retired users out of my lookup!!! |
|
|
|
Re: Hiding Retired Users in a Userinfo table lookup
Posted: 22 Jun 06 10:18 AM
|
fiogf49gjkf0d Originally posted by Jason
Yes the "Acct Mngr" Object show the retired users also.
I entered the code that was posted into there and it also did not work..... |
|
Sounds like someone has modified the Base Components. Check in the Architect to see if the earlier versions of the plugins are still around.
You might want to setup a blank or eval SalesLogix database so you can see how the stock compnents behave.
Here are the key settings from the Acnt Mrg object from my eval database.
FormatString: dddd
FormatType: ftUser
Lookup: (Contact:LASTNAME)
LookupMainTable: (CONTACT)
LookupMode: LmUser
Text: (Account:AccountManagerID)
BTW, I don't think that FormatString and FormatType are looked at when you sent the LookupMode to LmUser. |
|
|
|
Re: Hiding Retired Users in a Userinfo table lookup
Posted: 22 Jun 06 10:22 AM
|
fiogf49gjkf0d Originally posted by Jason
can you explain this code better for me.....why is there an "and 1" on the end of this |
|
What code are you referring to? Did you forget to post it? |
|
|
|
Re: Hiding Retired Users in a Userinfo table lookup
Posted: 22 Jun 06 10:46 AM
|
fiogf49gjkf0d This sql code.
"USERID in (Select userid from usersecurity where type <>'R') and 1 "
Dont put the quotes in the property, but everything else, also notice the space at the end.
In the LookuprestrictOp property place a "=". Again no quotes.
In the LookupRestrictValue property place a "1", with no quotes.
Thank you for helping, greatly appreciated.... |
|
|
|
Re: Hiding Retired Users in a Userinfo table lookup
Posted: 22 Jun 06 10:58 AM
|
fiogf49gjkf0d Question: do basic scripts override these fields....
WhenChange.Action = 'Basic'
WhenChange.Argument = 'Ticket:META_Ticket_Specialist_Assigned'
WhenChangeCommited.Action = 'Basic'
WhenChangeCommited.Argument = 'Ticket:META Ticket When Change Committed'
WhenEnter.Action = 'Basic'
WhenEnter.Argument = 'System:META Self Text to Global'
WhenExit.Action = 'Basic'
WhenExit.Argument = 'System:META ByID When Exit'
WhenPopupReturn.Action = 'Basic'
WhenPopupReturn.Argument = 'Ticket:META_Ticket_Assign_To_Validate' |
|
|
|
Re: Hiding Retired Users in a Userinfo table lookup
Posted: 22 Jun 06 11:00 AM
|
fiogf49gjkf0d See this post by Ryan Farley for a good explanation:
http://saleslogixblog.com/rfarley/archive/2004/02/10/329.aspx
Basically the conditions added to a lookup are used to build a where clause to the lookup. SalesLogix requires all three proprties to be filled in order to know what to do. You can use this to your advantage by basically breaking up your where condition to fit into the three control proprties SalesLogix needs.
What you are ultimately creating by filling in the properties is something like this:
WHERE Account = 'abbott wolrdwide' and status='active' and 1 = 1
THe final condition 1 = 1 is the part that the = and the 1 in the other fields are used for. |
|
|
|
Re: Hiding Retired Users in a Userinfo table lookup
Posted: 22 Jun 06 11:00 AM
|
fiogf49gjkf0d See this post by Ryan Farley for a good explanation:
http://saleslogixblog.com/rfarley/archive/2004/02/10/329.aspx
Basically the conditions added to a lookup are used to build a where clause to the lookup. SalesLogix requires all three proprties to be filled in order to know what to do. You can use this to your advantage by basically breaking up your where condition to fit into the three control proprties SalesLogix needs.
What you are ultimately creating by filling in the properties is something like this:
WHERE Account = 'abbott wolrdwide' and status='active' and 1 = 1
THe final condition 1 = 1 is the part that the = and the 1 in the other fields are used for. |
|
|
|
Re: Hiding Retired Users in a Userinfo table lookup
Posted: 22 Jun 06 2:44 PM
|
fiogf49gjkf0d Yeah I understand the whole process now thanks to everyone but i miust be screwing something up b/c it doesnt matter how many ways i put it in or what I change.........I cant get it to leave out the retired users.
I am giving up for now. I can get by with out it.
If anyone have any other hints or common issues let me know please. Thanks. |
|
|
|
Re: Hiding Retired Users in a Userinfo table lookup
Posted: 23 Jun 06 2:55 PM
|
fiogf49gjkf0d The problem with seccode is that retired users will always be there, and you literally have to map back to the userid to omit them. I made the assumption that a seccodeid of N(ID) is the same as a userid of U(ID) and that only works for a subset of my users.
I found the correct way to map it back by profiling the ticket screen (it uses seccode for assignedtoid but has to pull in the email address from userinfo). This is the SQL statement the profiler returned:
Select A3.EMAIL, A3.PHONE,A1.SECCODEID,A3.USERID,A2.USERID From SECCODE A1 LEFT JOIN USERSECURITY A2 On (A1.SECCODEID=A2.DEFAULTSECCODEID) Left Join USERINFO A3 On (A2.USERID=A3.USERID) Where A1.SECCODEID = ''
Basically you would alter the userinfo section so that it only shows the user types you want and the rest of the SQL should do the mapping back to the seccodeid that the lookup is using. That might be easier said than done but at least that SQL will give you the mapping if you ever needed it. |
|
|
|
Re: Hiding Retired Users in a Userinfo table lookup
Posted: 26 Jun 06 8:30 AM
|
fiogf49gjkf0d I need to insert this code into a specific script...correct??
Inserting anything in the Restict field does absolutely nothing.
Question is what script do I put it in....thare are abotu 5 and they all dont seem to restrict that lookup.
I need a sql statement that will only bring back users with a where statement A2.TYPE = 'C'
I am getting confused now....this shouldnt be so hard.....there has to be something I am leaving out or we are missing.
thanks |
|
|
|
Re: Hiding Retired Users in a Userinfo table lookup
Posted: 26 Jun 06 8:30 AM
|
fiogf49gjkf0d I need to insert this code into a specific script...correct??
Inserting anything in the Restict field does absolutely nothing.
Question is what script do I put it in....thare are abotu 5 and they all dont seem to restrict that lookup.
I need a sql statement that will only bring back users with a where statement A2.TYPE = 'C'
I am getting confused now....this shouldnt be so hard.....there has to be something I am leaving out or we are missing.
thanks |
|
|
|
Re: Hiding Retired Users in a Userinfo table lookup
Posted: 26 Jun 06 12:32 PM
|
cialis 5 mg generico cialis effetti exlim.net fiogf49gjkf0d If you have 5 scripts it sounds like you're in Legacy land. Scripts only matter when you're changing a field dynamically or restricting the lookup with some extra processing and even then must update the control properties or it won't know how to restrict itself. In cases where I use a script, I build the correct lookup restrictions dynamically based on a list of rules but in this instance you could set it once at design time and forget it.
All you have to do is change the Lookup properties in Architect to the following:
LookupRestrictField = "SECCODEID"
LookupRestrictValue = "IN (SELECT A1.SECCODEID FROM SECCODE A1 LEFT JOIN USERSECURITY A2 ON (A1.SECCODEID=A2.DEFAULTSECCODEID) WHERE (A2.TYPE = 'N') OR (A2.TYPE = 'M') AND "1" = "
LookupRestrictValue = "1"
This restricts the lookup to include only remotes (type M) or network (type N). You could use A2.TYPE <> 'R' but that would include the admin user which is type W and all of the other types like template, web, etc. |
|
|
|
Re: Hiding Retired Users in a Userinfo table lookup
Posted: 26 Jun 06 1:33 PM
|
fiogf49gjkf0d do you think it has to do that the fact that I am using the IemOwner LinkMode instead of the IemUser linkMode??
Whe I changed it the first time the email prompt stopped apearing so I changed it back. |
|
|
|
Re: Hiding Retired Users in a Userinfo table lookup
Posted: 26 Jun 06 2:19 PM
|
fiogf49gjkf0d The method I posted shows how to restrict LmOwner (stands for Link Mode: Owner, NOT IemOwner) to only show those users who are a network or remote user. To use the LmUser one would have to modify the SQL to return a userid and use the userinfo table.
There are cases where one link mode is preferred over the other. If you need to do processing on the seccode table, use LmOwner and restrict the seccodeid. If you need to do processing on the userinfo table, use LmUser and restrict the userid. Lookup modes set both the lookup base table and the resulting Lookup.LookupID value. If you're just looking to capture Lookup.Text, the lookup mode rarely has an effect but if you're looking to do processing on that Lookup.LookupID you have to make sure it returns the correct value otherwise you're going to incorrectly assume you have a seccodeid when in reality it's returning a userid. |
|
|
|