Child Form Select SQL
Key elements of Child Form Select SQL include:
- SELECT - The SQL command must select the primary key of each record; this is essential for the edit, goto and delete icons to work as it needs to know which record it is editing, going to, or deleting. You will also select any other columns that will display on the child form, or which are needed for goto override links and for other purposes (see other articles for more details).
- FROM - You must choose the table(s) from which you are selecting data. Use JOIN expressions for related tables.
- The WHERE clause of the SQL command must filter which records are appearing on the child form. For example, on the Event profile page, the Sessions child form must display only the Sessions for the selected Event. This can be done in the WHERE clause by parsing the value of the parent record's own primary key (in this case it would be evt_key). You'll generally also want to exclude hidden records.
- ORDER BY - Sort the results if needed with a default sort order; generally you'll sort them by the left-most column.
See Child Form Standards for more stylistic and performance guidelines when writing Child Form Select SQL.
Tips
We recommend writing and testing the SQL in Query Analyzer and then copy-and-paste it into the child form page. You will not be able to run the last line of the WHERE clause statement in SQL Query Analyzer because it is not proper run-time SQL syntax; you can omit this line when testing in Query Analyzer.
SELECT
x02_key,
x02_code AS [SHOW name],
x02_lookup_code AS [SHOW type],
x02_add_date AS [ADD date]
FROM cli_tv_network_show (nolock)
WHERE x02_delete_flag=0
AND x02_x01_key={x01_key}
ORDER BY x02_code
Note: The line--AND x02_x01_key={x01_key}--is what ensures that you only select records on the child form that are affiliated with the parent. netFORUM automatically sees the braces {} and substitutes the correct value during run-time. We refer to these as parsed values.
After you have done this, you must click the Has Child Forms and Designed Form checkboxes on the parent dynamic form.
Underscore Character
netFORUM displays any columns returned in your SELECT statement except if there is an underscore (_) in the column name or if the data type is uniqueidentifier (for example, a key column). Therefore, you must assign an alias to any columns you wish to appear in the child bar. This is done to avoid displaying confusing column headers to the user.
Why would you want to select a column, but not display it? You might need the column for Child Form Visibility SQL or for a Recursive Child Form. Also, for the key column and goto override column. Or you might have a column in the select statement that is used to create an ORDER BY, and you'll use that column in the ORDER BY clause.
Images in Child Form
With a little inventiveness, you may display images and add clickable hyperlinks to the records in your child form. For examples, see the "Active" checkbox in the Committee Participant tab of the Committee Profile page and the clickable "renew" hyperlink on the Membership tab of the Individual or Organization Profile window. Here are the syntax samples.
Syntax for adding an image from the Committee Participant child form of Committee Profile:
SELECT
cmc_key,
cmc_cst_key,
_active=case when ((cmc_end_date>=getdate() OR cmc_end_date IS NULL) AND isnull(cmc_status,'')<>'pending') then '1' else '2' end,
Active=case when ((cmc_end_date>=getdate() OR cmc_end_date IS NULL) AND isnull(cmc_status,'')<>'pending') then '<img src="../images/img_chkmk.gif" border="0"></img>' else '' end,
Name=cst_sort_name_dn,
Position=cmc_cpo_code,
Rank=cpo_rank,
STATUS=cmc_status,
Admin=case when cpo_admin_flag = 1 then '<img src="../images/img_chkmk.gif" border="0"></img>' else '' end,
[Start Date]=CONVERT(NVARCHAR(10),cmc_start_date,101),
[End Date]=CONVERT(NVARCHAR(10),cmc_end_date,101),
case when cmc_voting_flag = 1 then '<img src="../images/img_chkmk.gif" border="0"></img>' else '' end AS "Voting?"
,Funded=case when cpo_funded = 1 then '<img src="../images/img_chkmk.gif" border="0"></img>' else '' end,
Renewable=case when cpo_renewable= 1 then '<img src="../images/img_chkmk.gif" border="0"></img>' else '' end
FROM mb_committee_x_customer (NoLock)
JOIN co_customer (NoLock) ON cst_key=cmc_cst_key
LEFT JOIN mb_committee_position (NoLock) ON cmc_cpo_code = cpo_code
WHERE cmc_cmt_key = {cmt_key} AND cmc_csu_key IS NULL AND cmc_delete_flag=0
ORDER BY _active,cpo_rank, name
Hyperlink in Child Form
Syntax for adding a clickable hyperlink in a child form:
SELECT
dyc_key,
dyc_dyn_key_child,
[Description]=dyc_description,
[Child Form]='<a href="DynamicEdit.aspx?FormKey=4cdb590a-ed8b-4e42-894c-338cd03d799b&key='+convert(nvarchar(36),dyn_key)+'">'+dyn_description+'</a>',
[Form Title]=dyc_grid_title,
[ADD]=dyc_allow_add,
[Edit]=dyc_allow_edit,
[DELETE]=dyc_allow_delete,
[Goto]=dyc_allow_goto,
[ORDER]=dyc_order,
[Deleted?]=dyc_delete_flag
FROM md_dynamic_form_child (nolock)
JOIN md_dynamic_form (nolock) ON dyn_key = dyc_dyn_key_child
WHERE dyc_dyn_key = {dyn_key} AND dyc_dyc_key IS NULL
ORDER BY [Deleted?], [ORDER]
Mailto Link in Child Form
To add a html "mailto" link in a child form, enter the following syntax, assuming you are joining to the co_customer table in your Select SQL:
[Email] = '<a href="mailto:' + cst_eml_address_dn +'">' + cst_eml_address_dn + '</a>',
Stored Procedure in Child Form
Sometimes the data you wish to display in a child form is too complicated to assemble from a direct SQL SELECT statement, or perhaps you can write a direct SQL SELECT statement to retrieve the data but your statement is too large to fit in the TextArea (which is 4000 characters long).
In this instance, you can write a database stored procedure to return child records instead of a SQL SELECT statement.
Another scenario that might call for a stored procedure instead of direct SQL is if you will have two, three or more child forms that will return the exact same kind of recordset, only with different parameters in different scenarios. In this case, a stored procedure can make development of child forms and long term maintenance easier.
On the nearby image, you will see an example of this instance in the Membership child form of the Individual form.
Note how the select sql textarea has a stored procedure instead of a direct SQL SELECT statement. The value you enter in the select sql textarea must begin with the word exec so that the netFORUM engine will realize that it needs to execute a stored procedure instead of a direct SQL SELECT statement.
If you choose to have a stored procedure, then the stored procedure must meet the following requirements. The stored procedure must:
- Return a recordset including the column specified in the child key column, which in the case above is mbr_key
- If you specify an override goto form and a goto key column, then your recordset must include the goto key column which in the case above is asn_key. The datatype of this column must be uniqueidentifier.
- Any columns you want to appear in the child bar must be aliased in such a way that there is no underscore character in the column name of the recordset you return. netFORUM will only output columns in the child bar if the column name does not have an underscore in it, and if the column datatype is not a uniqueidentifer.
- The stored procedure must have a parameter that will be used to filter the records in a WHERE clause internal to that stored procedure so that you only display records that are linked to the parent record on which the child bar exists. In the case above, the parameter value is being passed in by the value of {ind_cst_key}. The datatype of the parameter must in this case be uniqueidentifer.
- If you are writing a stored procedure as a custom requirement for a client, we ask that you name the stored procedure with the client abbreviation in front in order to distinguish the database object from baseline netFORUM database object. For example, you might name the stored procedure asae_mb_membership_child_form.
- Be sure to grant EXECUTE permission on the stored procedure to the core netFORUM groups whose users will be running the procedure.
- We recommend that you test the stored procedure first in SQL Query Analyzer to ensure that it works as intended.
Although the actual stored procedure mb_individual_membership_child_form is more advanced, here is a simplified version of what it looks like and how it follows the rules specified above:
CREATE PROCEDURE mb_individual_membership_child_form
@cst_key uniqueidentifier
AS
SET nocount ON
CREATE TABLE #temp
(
[ind_cst_key] uniqueidentifier NULL,
[mbr_key] uniqueidentifier NULL,
[asn_key] uniqueidentifier NULL,
[Association] nvarchar(50) NULL,
[Member Type] nvarchar(50) NULL,
[Member STATUS] nvarchar(50) NULL,
[Member Through]nvarchar(50) NULL,
[JOIN] varchar(10) NULL,
[Re-JOIN] varchar(10) NULL,
[Effective] varchar(10) NULL,
[Expire] varchar(10) NULL
)
INSERT INTO #temp
SELECT @cst_key AS [ind_cst_key],
mbr_key,
asn_key,
mbt_asn_code AS [Association],
mbt_code AS [Member Type],
mbs_code AS [Member STATUS],
REPLACE(cst_name_cp,'''','') AS [Member Through],
convert(varchar(10), mbr_join_date, 101) AS [JOIN],
convert(varchar(10), mbr_rejoin_date, 101) AS [Re-JOIN],
convert(varchar(10), isnull(mbr_renew_date,''), 101) AS [Effective],
convert(varchar(10), isnull(mbr_expire_date,''), 101) AS [Expire]
FROM
co_customer_x_customer
JOIN co_relationship_type ON cxc_rlt_code = rlt_code
JOIN co_customer ON cxc_cst_key_1 = cst_key
JOIN mb_membership ON cxc_cst_key_1 = mbr_cst_key
JOIN mb_member_type ON mbr_mbt_key = mbt_key
JOIN mb_association ON mbt_asn_code = asn_code
LEFT JOIN mb_member_status ON mbr_mbs_key = mbs_key
WHERE
cxc_delete_flag = 0
AND rlt_type = 'Individual'
AND cxc_cst_key_2 = @cst_key
SELECT * FROM #temp order by [Association],[Expire]
Different Data Sources in Child Form
Note: This only works if you can set up a linked server.
Suppose you wish to display data from another database in a child form. For example, suppose you have an accounting database that stores certain customer data that does not exist in the netFORUM database. As long as you have a key joining your data between two databases, and your databases can be linked by a linked server or another technique, you can actually display data from an entirely different data source in a child bar.
From a design perspective, you might want to create a SQL View or a stored procedure that contains any complex querying, and have your select sql textarea run a stored procedure or a SELECT SQL statement against your more complicated VIEW. This way, you can tweak your stored procedure or VIEW while the calling select sql fieldcan remain a simple command.
Obviously, the data you are returning and displaying from another database will not be editable within netFORUM without significant customization and development. The data will be only for displaying. If you want to actually update data from another database, this could possibly be done by creating a SQL VIEW into the other database. Then you will build out the object and the accompanying form for that VIEW and when you insert or update the VIEW through the netFORUM engine, the database will resolve the commands within the VIEW itself and insert/update/delete the appropriate underlying data in the VIEW. In most cases, the rules for processing data in the other database will be complex and may not be appropriate or advisable for this kind of simplistic interface. Moreover, this may cause performance to suffer depending on the complexity.
Abila has performed this as a prototype, but this feature has not been fully tested or implemented in practice and therefore cannot be guaranteed.
Copy Icon in Child Form
You can include clickable hyperlinks in childforms. Here is an example of a clickable hyperlink that opens a DynamicEditModal form that tries to copy from a record in the child form.
Here is what the child form looks like. When you click the "copy" icon image, the new window pops up with certain values on the new form defaulted from values in that child form record. Notice that the FormKey is hard-coded to the form you want to pop up.
Here is the child form sql:
SELECT
a53_key,
a42_key,
[ ] = ' <a href="javascript:OpenNewWindow(''../forms/DynamicEditModal.aspx?' +
'Action=Add' +
'&FormKey=51e5c4c5-d08d-4871-8ba0-906ab01ce4cf' +
'&org_acronym=' + org_acronym +
'&org_name=' + org_name +
'&a40_project_number=' + a40_project_number +
'&a40_project_name=' + a40_project_name +
'&a42_a53_key=' + lower(convert(varchar(36),a42_a53_key)) +
'&a42_work_type=' + a42_work_type +
'&a42_billing_method=' + a42_billing_method +
'&a42_ind_cst_key=' + lower(convert(varchar(36), a42_ind_cst_key)) +
'&a53_a40_key=' + convert(varchar(36),a53_a40_key) + ''')">' +
'<img border="0" height="16" width="16" alt="copy time" src="../images/img_copy.gif"></a>'
FROM <<various TABLES...etc. etc. etc.>>
Limitations
This does not work on the Add Price form because a Form Extension automatically defaults GL accounts on that page.
Mouseover Preview
You can use the av_mouseover_preview_childform UDF to add a mouseover preview in a child form as shown below:
Checkbox in Child Form
You can use the av_checkbox_html UDF to output a checkbox image in a child form as shown in the image on the section above, on the far right column.
SELECT x02_key, x01_code AS [Code],
dbo.av_checkbox_html ( x02_completed_flag , NULL) AS [Completed]
FROM ...
Red X in Child Form
If you want to have a prominent red X in a child form, you can use the UDF av_checkbox_x_html.
[Object Supports Workflow?] = [dbo].[av_checkbox_x_html] (obj_workflow_flag),
If 1 is passed, then a checkmark appears, the same as with Checkbox in Child Form. But if 0 is passed, then a red x appears which makes this more prominent. Use this when you want to call attention to the fact that the value is NOT checked.
Multicurrency
The 2008.01 release of netFORUM supports Multicurrency.
Beginning in that build, certain child forms that display currencies will show columns containing additional international currency information if the netFORUM site is configured for multicurrency (via the EnableMulticurrency system option).
To accomplish this, these child forms will contain an additional SQL section like this:
SELECT inv_key,
[Date]=inv_date,
[Amount]=inv_amount
FROM ac_invoice...
WHERE inv_delete_flag = 0
/* special multicurrency SQL command: */
{BeginMCSQL}
SELECT inv_key,
[Date]=inv_date,
[Currency Code] = ccc_code,
[Base Amount]=inv_amount,
[Transaction Amount]=inv_amount_mc
FROM ac_invoice JOIN ...
WHERE inv_delete_flag = 0
{EndMCSQL}
The special tags {BeginMCSSQL}{EndMCSSQL} will tell the child form SQL rendering engine to execute the SQL within these tags if the system is multicurrency, instead of the "main" SQL command.
If your system is not multicurrency, then it will ignore this section.