How to autopopulate a field based on a transition

create parameter that will capture sysdate
create execution step that will run the sql to generate sysdate
create execution step that will take sysdate and populate a field

How to handle single quotes in a sql statement

In this sql statement below there is a section that looks like:

where x in (...statuses ..)

One of the statuses in this list already contained a single quote around it:
'pending completion of target 'Go Live' date'

This broke the portlet. The way to fix it is to use two single quotes around Go Live so that it looks like:
'pending completion of target ''Go Live'' date'

Full sql statement below:
SELECT a.request_id REQUEST_NUMBER, a.CREATED_BY_NAME CREATED_BY, a.creation_date CREATED_ON, a.priority_meaning BUSINESS_PRIORITY, c.visible_parameter20 BUSINESS_SPONSOR, c.visible_parameter4 BUSINESS_OWNER, b.visible_parameter5 PROJECT_MANAGER, b.visible_parameter19 PROJECT_SIZE, b.visible_parameter3 PREFERRED_GO_LIVE_ETA, b.visible_parameter6 REQUIREMENTS_ETA, b.visible_parameter22 CONTROL_GROUP_PREFERRED_, b.visible_parameter1 INTERNAL_PID_ETA, b.visible_parameter12 SUPPLIER_PID_ETA, b.visible_parameter7 COMMENCEMENT_CODING_ETA, b.visible_parameter20 CODE_PLACED_ON_TEST_ETA, b.visible_parameter21 POTENTIAL_LIVE_ETA, b.visible_parameter9 ACTUAL_DATE_ON_TEST, b.visible_parameter13 TARGET_GO_LIVE_DATE, b.visible_parameter11 ACTUAL_RELEASE_DATE, a.request_id REQUEST_NUMBER_LINK, s.status_name REQUEST_STATUSFROM kcrt_requests_v a, kcrt_request_details b, kcrt_req_header_details c, knta_users d, kcrt_statuses sWHERE a.request_id = b.request_idAND a.request_id = c.request_idAND a.request_type_id = b.request_type_idAND a.request_type_id = c.request_type_idAND a.status_id = s.status_idAND a.request_type_id = 30704AND a.created_by = d.user_idAND s.status_name in('pending supplier requirements review', 'pending PID ETA and Duration', 'pending further information - control group (PID ETA and Duration)', 'pending PID analysis', 'pending attachment of supplier PID', 'pending further information - review supplier PID (by PM)', 'pending commencement coding ETA', 'pending actual date on test', 'pending completion of target ''Go Live'' date')--This section comes from the filter fieldsAND b.parameter5 in ([P.PROJECT_MANAGER])AND b.parameter19 in ([P.PROJECT_SIZE])AND TRUNC(a.creation_date) = to_date(SUBSTR('[P.CREATED_ON]',1,10), 'YYYY-MM-DD' )AND s.status_id in ([P.STATUS])AND (('[P.INCLUDE_CLOSED]' = 'Y') OR (a.status_code not like 'CLOSED%' and a.status_code not like 'CANCEL%'))--End section from the filter fields

How to add "include closed" radio button

Part of building a portlet includes giving the end user the choice of displaying closed requests as well as open requests. This behaviour is not provided by default and therefore needs to be added by the developer.

Approach:
1) Add a new field called "Include Closed?" in the request type. There is no need to display this field so make it invisible across all statuses.
2) In the data source, there is no need to add the field in.
3) It is necessary to add a filter that will handle this. See the image below for details:

This filter is providing two options. Either display:

1) requests with a token (INCLUDE_CLOSED) value set to "Y"

2) OR all statuses apart from %CLOSED% and %CANCELLED%

How to set date filters in a portlet

AND TRUNC(a.creation_date) = to_date(SUBSTR('[P.CREATED_ON]',1,10), 'YYYY-MM-DD' )

This results in ability to filter portlets by date