Child Form Visibility SQL

By default, child forms and their rows are always visible and editable (if the allow edit and allow add checkboxes are checked) to all users.

There may be times, however, when you want a child form to be made visible or read only in some cases. Or, you may want some rows in a child form to be read only or invisible in some cases but not in all cases.

For example, you might want a child form to be invisible on an Organization if the Organization Type is not a certain code. Or, you may want to have a child form that displays all records but makes some of those rows be read-only if there is a date field that has occurred in the past.

The way to accomplish these requirements is to use Visibility SQL or Read Only SQL for your child form.

Visible/Read-Only child form conditions will run a SQL command that you enter. The SQL command must return a recordset. If the recordset contains at least one row, then:

• If you entered the command in the visible sql or read only sql, then the child form will be rendered visible or all of its rows will be read only and the "add" button will not appear.

• If you entered the command in the allow add visible sql, then the child form will be shown, but the "add" button will be removed

• If you entered the command in the row visible sql or the row read only sql, then any this command will be run against every row that your child form’s select sql returns, and then render that row visible or read only, respectively.

Each of the following four fields requires a SQL statement that will determine a behavior. The SQL Statement will return a recordset. If the recordset has at least one row, then the behavior will be true, otherwise it will be false. When writing the SQL statement, the only fields available to parse are the fields selected in the results of the child form. I.e., when writing visibility SQL for a child form that selects 'rlt_code as [Relationship]', the value {Relationship} will parse but {rlt_code} will not.

Child Form Visible and Read Only SQL

Visible SQL: A SQL statement that determines whether or not to show the child form at all. If the SQL statement returns one or more rows, then the child form will be visible; if the SQL statement returns zero rows, then the child form will not appear at all.

Read Only SQL. A SQL statement that determines whether all the rows in the child form should be read-only. If one or more rows are returned by the SQL statement, then the child form and its rows will be read only; if the SQL statement returns zero rows, then the child form will have edit, delete, add and goto buttons (assuming those checkboxes were checked).

For Visible SQL and Read Only SQL select a field that is in your main data object.

In the example above, it is assumed that the object belonging to the child form’s destination form contains the control x02_code:

SELECT x02_key
FROM cli_tv_network_show (nolock)
WHERE {x02_code} = 'NBC'

If this statement returns more than one record, then the child form will be read-only or visible (depending on whether this command was entered in Visible SQL or Read Only SQL, respectively).

The command may also run a UDF of Stored Procedure if the logic is more complex or is repeated in multiple places.

Here is a much more complex version that determines the read-only/visible condition of a child form based on whether a user belongs to a key group (based on the values in the "ABCDEKeyRoleGroup" system option) and whether the Customer has a role based on the relationship codes defined in the "ABCDEKeyRoles" system option.

If the user is allowed to edit this customer’s data, then the UDF returns a row with a value of 1; else it returns a 0. If it returns a 0, then the Visible/Read-Only SQL will return a record in the recordset and the child form will be rendered visible/read-only.

SELECT isPrivileged 
FROM dbo.client_abcde_key_role_permission(
{cst_key},
{CurrentUserName},
{ABCDEKeyRoles},
{ABCDEKeyRoleGroup},
{CurrentDate})
WHERE isPrivileged=0

Here is the definition of this UDF:

CREATE FUNCTION dbo.client_abcde_key_role_permission(
	@cst_key av_key,
	@usr_code av_user, 
	@RoleList nvarchar(1000), 
	@GroupList nvarchar(1000), 
	@Today av_date)
 
returns @isPrivileged TABLE (isPrivileged bit DEFAULT 0)
AS
begin
 
	/*
		UDF is used for child form read-only 
security on Individual profile.
 
	*/
	declare @mayEdit bit
	SELECT @mayEdit = 0 -- default, assume user may NOT edit
 
	-- Look to see if the customer has a "key" (important) 
-- Role that is primary.
	-- note: the pipe characters (|) are used to ensure exact match 
 
	IF EXISTS(SELECT ixo_key 
		FROM co_individual_x_organization  (nolock)
		JOIN co_customer (nolock) ON cst_ixo_key = ixo_key
		WHERE ixo_ind_cst_key = @cst_key
		AND charindex('|' + ixo_rlt_code + '|', 
'|' + REPLACE(@RoleList,';','|')+'|') > 0 
		AND (ixo_end_date IS NULL OR ixo_end_date > @Today))
 
 
	begin
		-- customer has a top role, need to 
-- make sure User is in key group
 
		IF EXISTS(SELECT uxg_usr_code FROM fw_user_x_group (nolock) 
			WHERE charindex('|' + uxg_grp_code + '|', 
'|' + REPLACE(@GroupList,';','|')+'|') > 0  
AND uxg_usr_code=@usr_code)
		begin
 
			-- user IS in top group, therefore may edit
			SELECT @mayEdit = 1
		end
	end
	else
	begin
		-- Customer is not a top role, therefore anyone may edit	
		SELECT @mayEdit = 1
	end
 
	INSERT @isPrivileged (isPrivileged) VALUES (@mayEdit)
 
	RETURN 
end
go

Child Form Row Visible and Read Only SQL

Row Visible SQL: A SQL statement that can make specific child form rows be conditionally invisible based on certain conditions.

Row Read Only SQL: A SQL statement that can make specific rows be read-only based on certain conditions.

For Row Visible SQL and Row Read Only SQL select a column that is contained in the SQL statement as illustrated below. In the example below, the control [show type] is presumed to be contained in the select sql for this child form.

Example 1:

SELECT x02_key
FROM cli_tv_network_show (nolock)
WHERE {[SHOW type]} = 'Sit Com'

In this example, each row in the recordset that the child form’s select sql returns must have a column called [_mbr_through]. Each row is evaluated based on the value of this column. If the column’s value is Self, then the row will be editable; otherwise the row will be read only.

Tip: Select only 1 column for performance reasons. It is preferred that you don’t include a "from" clause in your statement if it is not required. In other words, you do not technically need to query a true database table if all your "data" is contained in the recordset returned by the child form’s select sql command. You should make your select sql command return all the data that is needed to prevent the framework from having to make an unnecessary database hit to evaluate every single record.

For Example:

SELECT [yes] = 'yes' WHERE {[SHOW type]} = 'Sit Com'

If the value is ’Sit Com’ then a record will be returned and the row will consequently be invisible or read only depending on whether the command was issued in the Row Visible SQL or the Row Read Only SQL respectively.

Example 2:

This child form SQL contains a non-viewable column called _editable:

The column _editable will be set to ’yes’ if the end date is in the future or is NULL; otherwise it will be ’no’.

Next, the row read only sql is this:

SELECT [readonly] = 'yes' WHERE {_editable} = 'no'

This command will return a row only if _editable is equal to ’no’. If at least one row is returned, then the row being evaluated will be read only.

When the form is rendered, you will see that the top row is read only, the row with the end date in the past:

Child Form Allow Add Visible SQL

Allow Add Visible SQL: A SQL statement that can make the add button of a child form be conditionally invisible based on certain conditions.

This feature works exactly the same as other examples provided above, with the exception that it will hide the "Add" button from the child form.