List SQL

A List Page Detail enables you to type a SQL Statement and then output the results in HTML.

The Form setting of the page detail must align with the List Table setup for the form's Object. Any Parsed Value you need must be "known" by the form you have chosen.

Example:

{BeginListColumns}org_cst_key, org_name{EndListColumns}{BeginListWhere}org_name LIKE {org_name}+'%'{EndListWhere}{BeginPagerProperties}50{EndPagerProperties}<table><tr><th>Organization Name</th></tr>{BeginListDetail}<tr><td><a href="#{org_cst_key}">{org_name}</a></td></tr>{EndListDetail}</table>

List Controls can also contain ternary operations using a netFORUM-specific syntax:

{?CustomerKeyPricingControl=00000000-0000-0000-0000-000000000000?{non_prc_price}?{mem_prc_price}}

(for another example, see Correspondence Template Child Form HTML)

A list of the available built-in variables is available at netFORUM Variables.

Case Study - Sortable Column Headers

Here is an example of how to enable a user to click on a column header to resort in ascending or descending order.

{BeginPagerProperties}10{EndPagerProperties}
{BeginListSQL}
Declare @orderby varchar(50)
set @orderby = {orderby}
Declare @dir varchar(50)
set @dir = {dir}select [Key]=a05_key,[Course Name] = a05_cec_name, [Course Type] = a05_cet_type, [Provider] =a05_a04_ppr_code, [Program#] = a05_a04_ppr_number, [Reported by] = a05_reported_by from client_contact_hours_activity (nolock)
where a05_delete_flag = 0 AND a05_cst_key = {ind_cst_key} 
order by 
     CASE           
         When @orderby='coursename' AND @dir = 'D' THEN a05_cec_name 
         When @orderby='coursetype' AND @dir = 'D' THEN a05_cet_type
         When @orderby='provider' AND @dir = 'D' THEN a05_a04_ppr_code
         When @orderby='program' AND @dir = 'D' THEN a05_a04_ppr_number
         When @orderby='reportedby' AND @dir = 'D' THEN a05_reported_by
        End DESC,
     Case
          WHEN @orderby='coursename'  AND @dir !='D' THEN a05_cec_name
          When @orderby='coursetype' AND @dir != 'D' THEN a05_cet_type
          When @orderby='provider' AND @dir != 'D' THEN a05_a04_ppr_code
          When @orderby='program' AND @dir != 'D' THEN a05_a04_ppr_number
          When @orderby='reportedby' AND @dir != 'D' THEN a05_reported_by
         end ASC
{EndListSQL}
<div><span class="PageTitle">My Contact Hours</span><span class="WizardTitleSeparator"> | 
</span><span class="WizardHeader">{cst_sort_name_dn}</span>
</div><br>
<div align="right"><a href="javascript:OpenNewWindow('DynamicEditModal.aspx?FormKey=71032163-a7dc-4616-a752-6d6a98f09826&action=add&Modal=Yes')">add</a>
</div>
<div class=bodyTXT>
<table cellpadding=0 cellspacing=0 border=0 width=100%>
<tr>
<td class="transactions_title"><b><a href="DynamicPage.aspx?Site=eweb&WebCode=contacthours&orderby=coursename&dir=D" onclick="if('{orderby}'.length>0 && this.href.indexOf('{orderby}')>=0)this.href=this.href.replace(/&dir=[^&]*/,'')+'&dir='+('{dir}'=='D'?'A':'D');">Course Name</a></b></td>
<td class="transactions_title"><b><a href="DynamicPage.aspx?Site=eweb&WebCode=contacthours&orderby=coursetype" onclick="if('{orderby}'.length>0 && this.href.indexOf('{orderby}')>=0)this.href=this.href.replace(/&dir=[^&]*/,'')+'&dir='+('{dir}'=='D'?'A':'D');">Course Type </a></b>
</td>
</tr>
{BeginListDetail}
<tr>
<td class="transactions"><a href="javascript:OpenNewWindow('DynamicEditModal.aspx?FormKey=71032163-a7dc-4616-a752-6d6a98f09826&key={Key}&Modal=Yes')">{Course Name}&nbsp;</a></td>
<td class="transactions">{Course Type}&nbsp;</td>
<td class="transactions">{Provider}&nbsp;</td><td class="transactions">{Program#}&nbsp;</td>
<td class="transactions">{Reported By}&nbsp;</td>
</tr>
{EndListDetail}
</table>
<br>
</div>

To Do

The list content type is one of the most common. The List allows you to insert SQL and perform queries against your netFORUM database. The format of a List content detail is the query first and then HTML markup to present the results.

An SQL query can also be preformed using the List Control which adds a few more options. The List Control is a Control content-type which are described below. A List Control uses the same netFORUM script tags as a List-type content with the addition of a few extra (described in the table below).

The following table lists the netFORUM tags required to setup List details.

Tag	Used to Specify
{BeginPagerProperties} {EndPagerProperties}	Number of records to display per page - e.g. 10
{BeginPager} {EndPager}	Position of pager on list page (pager is by default numerical - 1,2,3,4...)
{BeginListSQL} {EndListSQL}	(SQL) query. This entry will override statements from the Build ListContent tool.
{BeginListDetail} {EndListDetail}	List detail. The values between this tag and its closing tag get repeated as many times as records are found that match the query criteria.
{BeginNoResults} {EndNoResults}	Used to display content if there are no results returned from a query (List Control only)
{BeginLoopProperties} {EndLoopProperties}	Defines the data element used in the primary loop (List Control only)
{BeginLoop} {EndLoop}	Used to define the presentation content for the primary loop (List Control only)

The following tags are used specifically with the Build ListContent tool:

Tag	Used to Specify
{BeginListWhere} {EndListWhere}	"Where" clause of SQL query (overridden by {BeginListSQL})
{BeginListOrderBy} {EndListOrderBy}	(SQL) list order
{BeginListColumns} {EndListColumns}	(SQL) columns (overridden by {BeginListSQL})

SAMPLE LIST CONTENT

{BeginPagerProperties}5{EndPagerProperties}{BeginListSQL}SELECTwps_title,wps_author,Date = convert&#40;varchar&#40;10&#41;, wps_post_date, 101&#41;,wps_key,wps_short_descriptionFROM md_web_postingJOIN md_web &#40;nolock&#41; ON wps_web_key=web_keyWHERE wps_post_date IS NOT NULLAND wps_post_date < getDate&#40;&#41;+1AND &#40;wps_remove_date IS NULL OR wps_remove_date > getDate&#40;&#41;&#41;AND wps_approval_date IS NOT NULLORDER BYwps_post_date DESC{EndListSQL}<H1>Press Releases</H1><div>{BeginPager}{EndPager}</div><br>{BeginListDetail}<div><b>{wps_title}</b></div><div style="color:gray">{Date}</div><div>{wps_short_description}</div><br>{EndListDetail}

List SQL

Example:

Case Study - Sortable Column Headers

See Also

To Do