Populate List through custom data source - Help

[mikeallenbrown]

What I need to do: First check to see if a user is a SYS_ADMIN (security group). If so populate the list without any filters. If the user is a non-SYS_ADMIN then populate the list using the :clientid argument as a filter.

My code isn't working and I'm unsure where I went wrong. Below is the code and the screen shot of the error the debugger gives me.

function populateListData as c (e as p)

dim cn as sql::Connection
dim args as sql::Arguments
dim pUser as p
dim sGroups as c
dim sql as c

debug(1)

a5ws_logged_in_user_values(pUser,request,session)
sGroups=a5ws_get_user_assignments(pUser.userId)

cn.Open("::Name::conn")

'Check to see if user is a SYS_ADMIN.
if is_one_of("SYS_ADMIN",sGroups) 
	'no filter
	sql = "SELECT ID, ClientID, Facility_Name FROM Facility_Information"
	cn.Execute(sql)
else
	'filter applied for non-SYS_ADMIN
	clientId = e.arguments.find("clientId").data
	args.Set("clientid",clientId)
	sql = "SELECT ID, ClientID, Facility_Name FROM Facility_Information WHERE Clientid = :clientid"
	sql = replace_arguments_in_string(sql,args)
	cn.Execute(sql,args)
end if

cn.FreeResult()
cn.Close()

end function

ScreenShot_2015.05.26_005_.png

ScreenShot_2015.05.26_006_.png

[mikeallenbrown]

Re: Populate List through custom data source - Help

Fixed ...I figured out an alternative.

I had forgotten that I have/use a session variable that is set to true/false after login depending upon whether or not the user is a SYS_ADMIN. I used this session variable along with jSON to populate my list. For those interested here is the code.

function populateListData as c (e as p)

'If a SYS_ADMIN is viewing this component then no filter is applied otherwise
'apply a filter

dim cn as sql::Connection
dim args as sql::Arguments
dim sql as c
dim sysadmin as l
dim jSON as c

'debug(1)

'Uncomment out the below line to work on list properties and for debugging
'session.__protected__SysAdmin = .F.

sysadmin = session.__protected__SysAdmin

cn.Open("::Name::conn")

'Check to see if user is a SYS_ADMIN.
if sysadmin = .t. 
	'no filter
	sql = "SELECT ID, ClientID, Facility_Name FROM Facility_Information"
	cn.Execute(sql)
else
	'filter applied for non-SYS_ADMIN
	clientId = e.arguments.find("clientId").data
	args.Set("clientid",clientId)
	sql = "SELECT ID, ClientID, Facility_Name FROM Facility_Information WHERE Clientid = :clientid"
	sql = replace_arguments_in_string(sql,args)
	cn.Execute(sql,args)
end if

jSON = cn.ResultSet.ToJSONObjectSyntax()

cn.FreeResult()
cn.Close()

jSON = stritran(jSON,crlf(),"," + crlf())
jSON = rtrim(jSON,",")
jSON = "[" + crlf() + jSON + crlf() + "]"

populateListData = jSON

end function

[GGandhi]

Re: Populate List through custom data source - Help

jSON = stritran(jSON,crlf(),"," + crlf())
jSON = rtrim(jSON,",")
jSON = "[" + crlf() + jSON + crlf()+ "]"

mike,
good that you have solved the issue. but the code above, is this needed for list control? you are adding lot of white spaces in a web which are not needed.
jSON = "[" + stritran(jSON,crlf(),"," ) + "]"
a single line, isn't that all needed for the list control?
(since you are not looking at the result, but the list control is. speed may be improved without all those white spaces)

[mikeallenbrown]

Re: Populate List through custom data source - Help

I don't know enough about jSON to say yes or no so I tried it (I the code from another component that some other developer helped me with. I just assumed all three lines were needed.)

Sure enough your one line of code does the same thing with out the spaces. Thanks!!

[GGandhi]

Re: Populate List through custom data source - Help

glad to hear it worked.
good. mike.