Storing field labels and visibility rules in the database

PHP, PHP Code Generator, Tutorials

You need to change some field labels in your project but don’t want to rebuild and upload the whole project. In this tutorial, we’ll show how to do that by storing field labels in the database and displaying them dynamically. As a bonus, we’ll also show how to hide fields based on visibility rules stored in the database, and make fields read-only or required.

First, we need to create a table in the database to store all those settings. We would need fields like table name, field name, field label and visibility. Here is the sample table script for MySQL.

CREATE TABLE `settings`(
`id` int NOT NULL AUTO_INCREMENT, 
`tablename` varchar(50) NULL, 
`field` varchar(50) NULL, `label` varchar(100) NULL, 
`visible` tinyint NULL DEFAULT 0, 
`readonly` tinyint NULL DEFAULT 0,
`required` tinyint NULL DEFAULT 0,
PRIMARY KEY (`id`))
CHARACTER SET utf8;

Here is how it works in generated application. Go to “settings” table first. Add a new record specifying table name (carscars), field name (Horsepower), field label (anything you want). Make field visible if you want to see how field label being applied. Turn visibility off to see field disappear from all pages. Once record added and saved go to “carscars” table and see those rules being applied.

Code

PHPRunner

Code needs to be applied to each table where you need this functionality. No changes in the code are required.

AfterTableInit event

global $field_labels, $tables_data;
$sql="select * from settings where tablename='".$table."'";
$rs = CustomQuery($sql);
while ($data = db_fetch_array($rs)) {
		$field_labels[$table][mlang_getcurrentlang()][GoodFieldName($data["field"])]=$data["label"];
		if ($data["readonly"]==1)
			$tables_data[$table][$data["field"]]["EditFormats"]["edit"]["EditFormat"]="Readonly";
		if ($data["required"]==1)
			$tables_data[$table][$data["field"]]["EditFormats"]["edit"]["validateAs"]["basicValidate"][0]="IsRequired";
}

Edit/Add/View pages, BeforeDisplay event

global $strTableName;
$sql="select * from settings where tablename='".$strTableName."'";
$rs = CustomQuery($sql);
while ($data = db_fetch_array($rs)) {
	if ($data["visible"]!=1) {
		$pageObject->hideField($data["field"]);
	}
}

List page, BeforeDisplay event

global $strTableName;
$sql="select * from settings where tablename='".$strTableName."'";
$rs = CustomQuery($sql);
while ($data = db_fetch_array($rs)) {
	if ($data["visible"]!=1) {
		$xt->assign($data["field"]."_fieldheadercolumn", false);
		$xt->assign($data["field"]."_fieldcolumn", false);
	}
}

Print page, BeforeDisplay event

global $strTableName;
$sql="select * from settings where tablename='".$strTableName."'";

$rs = CustomQuery($sql);
while ($data = db_fetch_array($rs)) {
	if ($data["visible"]!=1) {
		foreach( $xt->xt_vars["body"]["data"] as $page=>$pagedata ) {
			$xt->xt_vars["body"]["data"][$page][$data["field"]."_fieldheadercolumn"] = false;
			$xt->xt_vars["body"]["data"][$page][$data["field"]."_fieldcolumn"] = false;
		}
	}
}

ASPRunnerPro

AfterTableInit event

dim sql, rs, data
sql="select * from settings where tablename='" & table & "'"
set rs = CustomQuery(sql)
do while bValue(DoAssignment(data,db_fetch_array(rs))) 
         
	setArrElementN field_labels,CreateArray3(GoodFieldName(table),mlang_getcurrentlang(),GoodFieldName(data("field"))),data("label")
 
  if data("readonly")=1 then
		setArrElementN tables_data,CreateArray5(GoodFieldName(table),data("field"),"EditFormats","edit","EditFormat"),"Readonly"
	end if
  if data("required")=1 then

		setArrElementN tables_data,CreateArray6(GoodFieldName(table),data("field"),"EditFormats","edit","validateAs","basicValidate"),CreateArray2(0,"IsRequired")
	end if

loop

Edit/Add/View pages, BeforeDisplay event

dim sql, rs, data
sql="select * from settings where tablename='" & strTableName & "'"
set rs = CustomQuery(sql)
do while bValue(DoAssignment(data,db_fetch_array(rs))) 
    if data("visible")<>1 then  
        pageObject.hideField(data("field"))
    end if
loop

List page, BeforeDisplay event

dim sql, rs, data
sql="select * from settings where tablename='" & strTableName & "'"
set rs = CustomQuery(sql)
do while bValue(DoAssignment(data,db_fetch_array(rs))) 
    if data("visible")<>1 then  
				xt.assign data("field") & "_fieldheadercolumn", false
				xt.assign data("field") & "_fieldheader", false
    end if
loop

Print page, BeforeDisplay event

dim sql, rs, data
sql="select * from settings where tablename='" & strTableName & "'"
set rs = CustomQuery(sql)
do while bValue(DoAssignment(data,db_fetch_array(rs))) 
    if data("visible")<>1 then  
				xt.assign data("field") & "_fieldheadercolumn", false
				xt.assign data("field") & "_fieldheader", false
    end if

		set body = xt.getvar_p1("body")
		a = body("data").Items

		for i=0 to body("data").Count-1 
				a(i)(data("field")&"_fieldheadercolumn")=false
				a(i)(data("field")&"_fieldheader")=false
	  next    
loop

ASPRunner.NET (C#)

AfterTableInit event

string t = table;
string sql=String.Format("select * from settings where tablename='{0}'", t);
XVar rs = tDAL.CustomQuery(sql);
XVar data; 

while (data = CommonFunctions.db_fetch_array(rs)) {
		GlobalVars.field_labels[t][CommonFunctions.mlang_getcurrentlang()][MVCFunctions.GoodFieldName(data["field"])]=data["label"];
		if (data["readonly"]==1)
			GlobalVars.tables_data[t][data["field"]]["EditFormats"]["edit"]["EditFormat"]="Readonly";
		if (data["required"]==1)
			GlobalVars.tables_data[t][data["field"]]["EditFormats"]["edit"]["validateAs"]["basicValidate"][0]="IsRequired";
}

Edit/Add/View pages, BeforeDisplay event

string t = GlobalVars.strTableName.ToString();
string sql=String.Format("select * from settings where tablename='{0}'", t);
XVar rs = tDAL.CustomQuery(sql);
XVar data; 

while (data = CommonFunctions.db_fetch_array(rs)) {
	if (data["visible"]!=1) {
		pageObject.hideField(data["field"]);
	}
}

Some ideas on how to extend this functionality:

hide fields or change labels based on logged in user or day of week
hide fields on some pages and show on others
make this code work with multiple languages. You will have to add “language” field to “settings” table and modify code to take into account current language ($_SESSION[“language”] in PHP). For instance, if Russian language is selected you can ask users how many balalaika playing bears have they met today on their way to work. Apparently this question makes no sense if any other language is selected.
apply similar approach to table labels. You will probably need a different table to store table labels, i.e. ID, TableName, Label. Then you can use MenuItemModify event to run a SQL query, retrieve current table label (if exists) and replace it using setTitle() function.

Lets us know what else you’d like to store in the database and apply dynamically.

Happy coding!

12 thoughts on “Storing field labels and visibility rules in the database”

wpl says:

December 6, 2015 at 7:07 am

Interesting article on interesting topic. But what about Export page?
Jacques says:

December 6, 2015 at 1:52 pm

In a project I use a second connection to retrieve the correct table and field names

This way it saves you time and it prevents NAME mismatches.
perhaps you can combine it with above

global $dal;
$tblSettings = $dal->Table(“settings”);
$tblCOLUMNS = $dal->Table(“COLUMNS”,””,”information_schema at localhost”);
$rsCOLUMNS = $tblCOLUMNS->Query(“TABLE_SCHEMA=’Your_database'”,”TABLE_SCHEMA ASC”);
while ($data = db_fetch_array($rsCOLUMNS))
{
$tblSettings->Value[“tablename”]=$data[“TABLE_NAME”];
$tblSettings->Value[“field”]=$data[“COLUMN_NAME”];
$tblSettings->Add();
}
}
}
wpl says:

December 7, 2015 at 1:28 am

Jacques,

thanks a lot. I was also thinking about hiding/showing fields on Export page. Any hint to that topic?
jerry says:

December 10, 2015 at 6:46 pm

No ASPRunner.Net code ???????????????????????????????
admin says:

December 10, 2015 at 10:27 pm

I’ll craft C# code tomorrow. Just wanted to publish it on Thursday to give more people chance to read it.
Mario says:

December 11, 2015 at 7:05 am

It’s fantastic!!!!! Another idea to add automation:
In AfterTableInit and BeforeDisplay events, make calls to a prebuild functions placed in AfterApplicationInit giving the necessary parameters. Doing that, you write the code in one place.
Thank you !!!!!!
Alberto says:

December 17, 2015 at 7:57 am

It is possible to use with view’s ?, because is a great idea to produce reports dinamically. May be using a check in list page to define if each field will be printed.
i tried in before display event at print page but not worked.
Ken Lyle says:

April 26, 2016 at 3:32 pm

There’s a SQL DDL converter at http://sql.phpmix.com/ if you need to store the data in a database other than MySQL.
Daniel says:

July 6, 2021 at 5:47 am

Hi,

Using ASPRunner Version 10.5 I can’t get the “isRequired” bit working in C#
if (data[“required”]==1)
GlobalVars.tables_data[t][data[“field”]][“EditFormats”][“edit”][“validateAs”][“basicValidate”][0]=”IsRequired”;

My code:

string ur = MVCFunctions.postvalue(“ID”);

string sq = String.Format(“select * from HeaderTable where EMSHeaderID='{0}'”, ur);
XVar rt = tDAL.CustomQuery(sq);
XVar dt;

while (dt = CommonFunctions.db_fetch_array(rt)) {

string tb = “dbo_Mytable”;
//string t = “111”;
string t = dt[“CountryID”];
string sql=String.Format(“select * from VW_Settings where CountryID='{0}'”, t);
XVar rs = tDAL.CustomQuery(sql);
XVar data;

while (data = CommonFunctions.db_fetch_array(rs)) {
GlobalVars.field_labels[tb][CommonFunctions.mlang_getcurrentlang()][MVCFunctions.GoodFieldName(data[“FieldName”])]=data[“Alias”];

//if (data[“isRequired”]==1)
GlobalVars.tables_data[tb][data[“FieldName”]][“EditFormats”][“edit”][“validateAs”][“basicValidate”][0]=”IsRequired”;

}

}

Only thing I can see that is different is that my settings is a view instead of a table
Mustafa says:

February 10, 2022 at 1:55 am

Live Demo is not working anymore!
James says:

March 4, 2022 at 9:39 am

I’m not sure the PHPRunner code works in 10.7. I have a project trying this out and it will hide the column but not change the label.
James says:

March 11, 2022 at 9:27 am

For anyone having issues getting the PHPRunner script to work there are two things to check.
1) Make sure your table names match what PHPRunner has and not your Database. PHPRunner can modify the table names. An example of this is that PHPRunner will bring in SQL tables with the dbo. before the table name even though that’s technically not part of the Table name.

2) If you are able to get the Visibility, ReadOnly and Required scripts to work but the Field Names do not change, check your table names for special characters. The Scripts for the 3 listed above use the table names as they are in PHPRunner. However, the script for the Field name change uses a converted version of the table name where special charactors and spaces are changed to _. If this is happening try replacing line 5 of the After Table Initialized with the below script and change the replace strings with your special characters.
$field_labels[str_replace(” “,”_”,str_replace(“.”,”_”,$table))][mlang_getcurrentlang()][GoodFieldName($data[“field”])]=$data[“label”];

Storing field labels and visibility rules in the database

Code

AfterTableInit event

Edit/Add/View pages, BeforeDisplay event

List page, BeforeDisplay event

Print page, BeforeDisplay event

AfterTableInit event

Edit/Add/View pages, BeforeDisplay event

List page, BeforeDisplay event

Print page, BeforeDisplay event

AfterTableInit event

Edit/Add/View pages, BeforeDisplay event

12 thoughts on “Storing field labels and visibility rules in the database”

Leave a Reply

Sign up for the free email newsletter for news about our
database publishing software, tips, and more.

Resources

Company

Code

AfterTableInit event

Edit/Add/View pages, BeforeDisplay event

List page, BeforeDisplay event

Print page, BeforeDisplay event

AfterTableInit event

Edit/Add/View pages, BeforeDisplay event

List page, BeforeDisplay event

Print page, BeforeDisplay event

AfterTableInit event

Edit/Add/View pages, BeforeDisplay event

Related posts:

12 thoughts on “Storing field labels and visibility rules in the database”

Leave a Reply