Moving logic from Javascript to database

In this article we will discuss how to minimize the amount of code that handles Javascript on the Add/Edit page. Building your code based on data will help you manage forms with many fields easily. We will take care of functionalities like showing/hiding fields, making fields readonly, required, disabled, etc.

This approach will involve the following steps
1. Create and populate a database table to store triggers (when to apply the logic) and actions (what happens when the trigger goes off)
2. Server side PHP and C# code that passes this data to Javascript
3. The Javascript code itself that listens for “change” events and implements the logic specified in the database.

Database tables

Trigger table. The structure is quite simple. You can see that we store the table name, page type, field names and what events we listen for. In this article we will only show how to implement the most common “change” events as they cover 95% of the required functionality.

The action table is a detail table while the trigger is the master. They are linked by the trigger_id field. This table tells what conditions to check and what actions to take. Let’s look at the first line and try to decipher it.

This action is related to trigger #1 (change of Country field event on the Edit customer table page). The conditions are the same and the condition_value is ‘AS’. This means that when the Country column equals ‘USA’, we must continue with our action. And the action itself shows the Region field.

And here is a 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 into Events Before Display of each page where this function needs to be applied. In our situation, this code goes into the BeforeDisplay event on the Edit Customer table page. The code itself is quite easy. It simply dumps all the data from the actions and trigger tables and makes this data available in Javascript via a proxy object.

Note that for better code structure, you need to create an external PHP or C# file, define functions there and place this code into those functions. Then at 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

The Javascript code goes to the Javascript OnLoad event on the page where the action is to be performed, in our case it is the Javascript OnLoad event on the Edit Customer table page. The same idea of creating a Javascript function and calling it from an external file also applies here.

You can expand this code by adding more condition types and more actions. Check the 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!

Teknologi Terkini

Agen Togel Terpercaya

Bandar Togel

Sabung Ayam Online

Berita Terkini

Artikel Terbaru

Berita Terbaru

Penerbangan

Berita Politik

Berita Politik

Software

Software Download

Download Aplikasi

Berita Terkini

News

Jasa PBN

Jasa Artikel

News

Breaking News

Berita

Xrep

Moving logic from Javascript to database – Xlinesoft Blog