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.


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

Edit/Add/View pages, BeforeDisplay event

List page, BeforeDisplay event

Print page, BeforeDisplay event

ASPRunnerPro

AfterTableInit event

Edit/Add/View pages, BeforeDisplay event

List page, BeforeDisplay event

Print page, BeforeDisplay event

ASPRunner.NET (C#)

AfterTableInit event

Edit/Add/View pages, BeforeDisplay event

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

  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. I’ll craft C# code tomorrow. Just wanted to publish it on Thursday to give more people chance to read it.

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

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

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

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

  8. 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”];

Leave a Reply

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