Storing field labels and visibility rules in the database

Categories 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 storing field labels in the database and displaying them dynamically. As a bonus we'll also show how hide fields based on visibility rules stored in the database, make fields readonly 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;

Live demo

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!

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

  1. 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();
    }
    }
    }

  2. Jacques,

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

  3. 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 !!!!!!

  4. 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.

  5. 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

Leave a Reply

Your email address will not be published. Required fields are marked *