Need to Trim value in a List

[craig_uk]

I have a list that retrieves documents in a directory. <!--- Pack Directory
---> <cfdirectory action='list' directory='C:\TNS_Docs\EXCEL_PACKS' sort='type
asc' name='directory_listing'> I then have a query that checks if a user has
access to documents dependant on the first 3 numbers of the documents, i then
want to match them up and show them to the user. I need to trim the NAME value
in the DIRECTORY_LISTING so i only look at the first 3 numbers. <!--- My Query
---> <cfquery name='mylist' dbtype='query'> select name, size,
datelastmodified from directory_listing where name =
'#GET_DATA.pack_location#PACK.xls' </cfquery> Everytime I use LEFT(name, 3) I
get an error. Any ideas?

[pukkel]

craig_uk, the Query of Queries doesn't support every SQL statement. In fact,
it does support very little of them. (the LEFT() statement isn't one of them!)
But in regard to your problem, what do you think about turning your routine
around? Instead of getting all the files and then checking if a user may see
them, you could get only those files which the user may see. You have a query,
GET_DATA, with pack_location to indicate what packs the user may see, am I
right? You could use that GET_DATA.pack_location as a filter in your
cfdirectory tag, so it only gets those files who meet this filter. Like so:
filter='#GET_DATA.pack_location#*' Hope that helps.

[craig_uk]

Thanks for the reply. I've created a test script based on what you said.
<CFQUERY NAME='GET_DATA' datasource='TNS'> SELECT p.pack_location FROM
Users_tbl u, Pack_data_tbl p where u.user_id = 164 AND u.user_id = p.user_id
order by p.pack_location </CFQUERY> <cfdirectory action='list'
directory='C:\TNS_Docs\EXCEL_PACKS' sort='type asc' name='directory_listing'
filter='#get_data.pack_location#*'> <cfloop query='GET_DATA'> <cfdump
var='#directory_listing#'> </cfloop> I should get 2 files showing yet I get
none. ? Cheers

[pukkel]

You have misplaced the cfdirectory tag, it should be within the cfloop..
Because I see now that users can have rights to multiple, different 'packs';
you should take into account that the cfdirectory tag only supports one filter
at a time (too bad, I think). Therfore it should be within the loop where you
output the packs.

[craig_uk]

That works, thanks for the pointers. <CFQUERY NAME='GET_DATA'
datasource='TNS'> SELECT p.pack_location FROM Users_tbl u, Pack_data_tbl p
where u.user_id = 164 AND u.user_id = p.user_id order by p.pack_location
</CFQUERY> <cfloop query='GET_DATA'> <cfdirectory action='list'
directory='C:\TNS_Docs\EXCEL_PACKS' sort='type asc' name='directory_listing'
filter='#get_data.pack_location#*'> <cfoutput query='directory_listing'>
#name#<br> </cfoutput> </cfloop>