Moving logic from Javascript to the database

ASP.NET, PHP, Tutorials

In this article we will discuss how to minimize the amount of code that handles Javascript on Add/Edit pages. Making your code data-driven will help you easily manage forms with a huge numbers of fields. We will be taking care of functionality like showing/hiding fields, making fields readonly, required, disabled etc.

This approach will involve the following steps
1. Creating and populating database tables to store triggers ( when to apply the logic ) and actions ( what happens when trigger goes off )
2. Server-side PHP and C# code that passes this data to Javascript
3. Javascript code itself that listens to “change” event and implements the logic defined in the database.

Database tables

Triggers table. The structure is fairly simple. You can see that we store table name, page type, field name and what event we are listening to. In this article we will only show how to implement the most common “change” event as it covers 95% of required functionality.

Actions table is a details one while triggers is a master. They are linked by trigger_id field. This table knows what condition to check and what action to perform. Lets take a look at the first row and try to decipher it.

The action is tied to trigger #1 ( change event of Country field on the Edit page of customers table). The condition is equal and the condition_value is ‘USA’. Which means that when Country field equals ‘USA’ we should proceed with our action. And the action itself is showing of the Region field.

And here is the SQL script that will create both tables for you with sample data.

CREATE TABLE IF NOT EXISTS `actions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `condition` varchar(250) DEFAULT NULL,
  `target` varchar(250) DEFAULT NULL,
  `action` varchar(250) DEFAULT NULL,
  `condition_value` varchar(250) DEFAULT NULL,
  `trigger_id` int DEFAULT NULL,
  KEY `Index 1` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `actions` (`id`, `condition`, `target`, `action`, `condition_value`, `trigger_id`) VALUES
	(1, 'equals', 'Region', 'show', 'USA', 1),
	(2, 'empty', 'ContactTitle', 'hide', NULL, 2),
	(3, 'notepmty', 'ContactTitle', 'readonly', NULL, 2),
	(4, 'notempty', 'ContactTitle', 'show', NULL, 2),
	(5, 'notequals', 'Region', 'hide', 'USA', 1),
	(6, 'notempty', 'Fax', 'disable', NULL, 2),
	(7, 'empty', 'Fax', 'enable', NULL, 2),
	(8, 'equals', 'Region', 'clear', 'USA', 1),
	(9, 'equals', 'Region', 'focus', 'USA', 1),
	(10, 'equals', 'Region', 'require', 'USA', 1);

CREATE TABLE IF NOT EXISTS `triggers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `table` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `page` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `event` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `field` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `triggers` (`id`, `table`, `page`, `event`, `field`) VALUES
	(1, 'customers', 'edit', 'change', 'Country'),
	(2, 'customers', 'edit', 'change', 'ContactName');

Server-side code

Server-side code goes to BeforeDisplay event of each page where this functionality needs to be implemented. In our situation this code goes to BeforeDisplay event of Customers table Edit page. The code itself is fairly straightforward. It just dumps all the data from actions and triggers tables and makes this data available in Javascript via proxy object.

Note that for better code structure you need to create an external PHP or C# file, define a function there and place this code into that function. Then in all BeforeDisplay events

PHP code

$table = $pageObject->tName;
$page = $pageObject->pageName;

// get all the triggers for the current page and send it to Javascript
$sql = DB::PrepareSQL("select * from triggers WHERE `table`=':1' and page=':2'",
    $table, $page);

$triggers=array();

$rs = DB::Query($sql);
while( $data = $rs->fetchAssoc() )
{
    $triggers[] = $data;
}

// do the same for actions table

$sql = DB::PrepareSQL("SELECT * FROM actions WHERE trigger_id IN (select id from triggers WHERE `table`=':1' and page=':2'",
    $table, $page);

$actions=array();

$rs = DB::Query($sql);
while( $data = $rs->fetchAssoc() )
{
    $actions[] = $data;
}

$pageObject->setProxyValue("triggers", $triggers);
$pageObject->setProxyValue("actions", $actions);

C# code

dynamic actions = XVar.Array(), page = null, sql = null, triggers = XVar.Array();
table = XVar.Clone(pageObject.tName);
page = XVar.Clone(pageObject.pageName);
sql = XVar.Clone(DB.PrepareSQL(new XVar("select * from triggers WHERE `table`=':1' and page=':2'"), (XVar)(table), (XVar)(page)));
triggers = XVar.Clone(XVar.Array());
rs = XVar.Clone(DB.Query((XVar)(sql)));
while(XVar.Pack(data = XVar.Clone(rs.fetchAssoc())))
	{
	triggers.InitAndSetArrayItem(data, null);
	}
sql = XVar.Clone(DB.PrepareSQL(new XVar("SELECT * FROM actions WHERE trigger_id IN (select id from triggers WHERE `table`=':1' and page=':2'"), (XVar)(table), (XVar)(page)));
actions = XVar.Clone(XVar.Array());
rs = XVar.Clone(DB.Query((XVar)(sql)));
while(XVar.Pack(data = XVar.Clone(rs.fetchAssoc())))
	{
	actions.InitAndSetArrayItem(data, null);
	}
pageObject.setProxyValue(new XVar("triggers"), (XVar)(triggers));
pageObject.setProxyValue(new XVar("actions"), (XVar)(actions));
return null;

Javascript code

Javascript code goes to Javascript OnLoad event of the page where the action should happen, in our case this is Javascript OnLoad event of Customers table Edit page. The same idea with creating a Javascript function and calling from the external file is also valid here.

You can extend this code by adding more conditions types and more actions. Check inline comments for more info.

$('input, textarea, select, radio').on('change', function() {
        // 'this' refers to the element that triggered the change event
        const str = $(this).attr('id');
        const match = str.match(/^value_(.*?)_/);
        var name;
        if (match) {
            console.log('Value changed for:', match[1] , 'New value:', $(this).val());
            name = match[1];
            value = $(this).val();
        }
        
        // lets see if we have a trigger associated with the current field
        let triggers = proxy['triggers'];
        let actions = proxy['actions'];
        for (const trigger of triggers) {
            if (trigger["field"]==name ) {
                // loop through actions array to see what kind of actions we need to perform
                for (const action of actions) {
                    if ( trigger["id"] == action["trigger_id"]) {
                        
                        var proceed = false;
                        
                        // check conditions
                        // equals
                        if (action["condition"] == "equals") {
                            if ( action["condition_value"] == value ) {
                                proceed = true;
                            }
                        }
                        // not equals
                        if (action["condition"] == "notequals") {
                            if ( action["condition_value"] != value ) {
                                proceed = true;
                            }
                        }
                        // empty
                        if (action["condition"] == "empty") {
                            if ( value.length==0 ) {
                                proceed = true;
                            }
                        }
                        // not empty
                        if (action["condition"] == "notempty") {
                            if ( value.length!=0 ) {
                                proceed = true;
                            }
                        }
                        
                        // do we have mathcing conditions 
                        
                        if (proceed) {
                            // show action
                            if (action["action"] == "show") {
                                pageObj.showField(action["target"]);
                            }

                            // hide action
                            if (action["action"] == "hide") {
                                pageObj.hideField(action["target"]);
                            }
                            // clear action
                            if (action["action"] == "clear") {
                                Runner.getControl(pageid, action["target"]).setValue();
                            }
                            // focus action
                            if (action["action"] == "focus") {
                                Runner.getControl(pageid, action["target"]).setFocus();
                            }
                            // require action
                            if (action["action"] == "require") {
                                Runner.getControl(pageid, action["target"]).addValidation("IsRequired");
                            }
                            
                        }

                    }
                }
            }
        }    
    });

Enjoy!

Moving logic from Javascript to the database

Database tables

Server-side code

Javascript code

Leave a Reply

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

Resources

Company

Database tables

Server-side code

Javascript code

Related posts:

Leave a Reply