Use of Valuelist in cfscript is not working

Question

I'm using the iCalUS function from cflib.org which uses cfscript to generate iCal for a calendar. It is possible that an activity has more then one location. The locations are coming from a different table, so I want to use a Valuelist to get a comma separated list.

My code:

<cfquery datasource="#application.dsn#" name="getEvents">
select project.project_id,omschrijving, project.naam, project.start,project.eind,room, meeting_url
from project, lp_booking, lp_locaties
where export = <cfqueryparam value="1" cfsqltype="cf_sql_bit"> 
and keuren is null 
 and project.datum >= <cfqueryparam value="#createODBCDate(Now())#" cfsqltype="cf_sql_date"> 
and lp_locaties.room_id = lp_booking.room_id
and lp_booking.project_id = project.project_id
order by project.datum
</cfquery>


<cfscript>
eventStr = StructNew();
function iCalUS(stEvent) {
    var vCal = "";
    var CRLF=chr(13)&chr(10);
    var date_now = Now();
    vCal = vCal &  writeoutput('BEGIN:VCALENDAR'& CRLF);
    vCal = vCal &  writeoutput('VERSION:2.0'& CRLF);
    vCal = vCal &  writeoutput('PRODID:Name'& CRLF);
    vCal = vCal &  writeoutput('X-WR-CALNAME:SOMENAME' CRLF);
    vCal = vCal &  writeoutput('CALSCALE:GREGORIAN'& CRLF);
    vCal = vCal &  writeoutput('METHOD:PUBLISH'& CRLF);
    for(i=1; i LTE getEvents.RecordCount; i=i+1){
    vCal = vCal &  writeoutput('BEGIN:VEVENT'& CRLF);
    vCal = vCal &  writeoutput('SUMMARY:'&getEvents.naam[i]& CRLF);
    vCal = vCal &  writeoutput('UID:'&getEvents.project_id[i]& CRLF);
    vCal = vCal &  writeoutput('SEQUENCE:0'& CRLF);
    vCal = vCal &  writeoutput('STATUS:CONFIRMED'& CRLF);
    vCal = vCal &  writeoutput('TRANSP:OPAQUE'& CRLF);
    vCal = vCal &  writeoutput('DTSTART;TZID=Europe/Amsterdam:'&reReplace(getEvents.start[i],"[-:]","","all")& CRLF);
    vCal = vCal &  writeoutput('DTEND;TZID=Europe/Amsterdam:'&reReplace(getEvents.eind[i],"[-:]","","all")& CRLF);
    vCal = vCal &  writeoutput('DTSTAMP:'&reReplace(getEvents.start[i],"[-:]","","all")& CRLF);
    vCal = vCal &  writeoutput('LOCATION:'&getEvents.room[i]& CRLF);
    vCal = vCal &  writeoutput('DESCRIPTION:'&getEvents.omschrijving[i]&'\n\n\n'&getEvents.meeting_url[i]& CRLF);
    vCal = vCal &  writeoutput('URL:'&application.webUrl& CRLF);
    vCal = vCal &  writeoutput('END:VEVENT'& CRLF);             
    }
    vCal = vCal &  writeoutput('END:VCALENDAR'& CRLF);  
    return Trim(vCal);
   }
   </cfscript>
   <cfcontent type="text/calendar" reset="Yes">
   <cfheader name="Content-Disposition" value="inline;    filename=churchbookAgenda.ics">    <cfoutput>#iCalUS(eventStr)#</cfoutput>`

Now I want to use:

  vCal = vCal &  writeoutput('LOCATION:'&valuelist(getEvents.room[i])& CRLF);

When I do I get an error: variable [SomeRoomName] doesn't exist. When I remove the [i] it works, but I get all the locations found in the query.

 vCal = vCal &  writeoutput('LOCATION:'&valuelist(getEvents.room)& CRLF);

Answer 1

Too long for a comment.

This would be easier if you used tags instead of script. That would allow you to do this:

<cfoutput query = "getEvents" group = "a suitable field from your query">
code for stuff you only want to appear once
<cfoutput>
generate your list of rooms here
</cfoutput>
</cfoutput>

I don't know if there is a simple script equivalent of this.

Edit Begins Here

If you want to use script and valuelist, you can use queryExecute with dbtype="query" inside your loop. You can find a very simple example here. All you have to do is apply that general idea to your own situation.

Answer 2

You are referencing the room variable incorrectly. The cfdocs say (see valueList() at cfdocs.org): The cfml function valueList() returns "A delimited list of the values of each record returned from an executed query column" as a STRING. But you are trying to reference it with room[i] as being an array at index position i.

Take a look at the following query example and see the code comments in between the lines:

<cfscript>

// create and populate a query
myquery = queryNew("id,room","integer,varchar", [
            {"id":1,"room":"Room Oscar"},
            {"id":2,"room":"Room John"},
            {"id":3,"room":"Room Pablo"},
            {"id":4,"room":"Room Juliet"},
            {"id":5,"room":"Room Victor"}
]);


// get a list of all values of the room column
roomList=valueList(myquery.room);

/* because the list is a string, a single room can't be referenced 
 * with an index position, example roomList[i]. To make this work,
 * you'd need to convert the list to an array first */ 

roomArray=listToArray(roomList);
writedump(roomArray);
writedump(roomList);

//reference with index
i=4;
writeOutput( roomArray[i] );


</cfscript>

Instead, it would make more sense to use the function valueArray() then, like so:

vCal = vCal &  writeoutput('LOCATION:' & valueArray(getEvents.room)[i] & CRLF);

Unfortunately, I still can't follow what you are exactly trying to achieve. You've said you want to use vCal = vCal & writeoutput('LOCATION:'&valuelist(getEvents.room[i])& CRLF);, without specifying exactly where you want to use that line and what output you'd like to have. Also, it would have been better if you would have provided a query value dump as an data example. From what I understand, you just want to use an additional line in the same for-loop - but I'm not sure - then it would make more sense to just not use the valueList() function, like so: vCal = vCal & writeoutput('LOCATION:'& getEvents.room[i] & CRLF);

Answer 3

As the others stated, you need to provide a dump so we can see the data struct.

A LOOK INTO MY CRYSTAL BALL

You haven't said this directly, but my wild guess is that your query returns multiple rows per event, if the event has multiple locations.

I then assume that you're trying to produce one iCal entry per event ... and you were hoping that the valuelist function was somehow going to grab the room column from all rows in the query for that event.

However, your query isn't using a group by. If an event had 3 locations, you're going to end up with 3 iCal entries for that event -- one for each location.

SUGGESTIONS

Combine the Locations in the DB Query

Not sure what DBMS you're using ... but maybe you can deal with this in your query by using GROUP BY to get one query row per event and some function to concatenate the location values. Looks like MySQL has one called GROUP_CONCAT https://www.geeksforgeeks.org/mysql-group_concat-function/.

CFOUTPUT Group

Instead of doing this in CFSCRIPT, do this as a tried and true CFOUTPUT with the group attribute.

https://cfdocs.org/cfoutput

The last example on this page is pretty spot on. Of course, you'd likely need to define the function using the cffunction tag and do the output within a CFSAVECONTENT or something.

Come up with some reworked approach using either queryfilter or queries of queries

Not much to say here other than the title. There are all sorts of query manipulation functions available, depending on what version of ACF/LUCEE you're on.

Maybe use QoQ to pull a distinct list of events, and use that query as the basis of your for loop. Then when you need the locations, maybe a queryfilter against the original query to pull the locations that match that event.

The examples on the https://cfdocs.org/queryfilter page show this quite clearly.