[mfaisel]

Hi,

I would like to populate a GRID with data from a database.

The GRID form detail is as follows:

Code: Select all

ID : exchangeRateGrid
Field 1 id : ID   type: textbox
Field 2 id : date   type: date
Field 3 id : from_currency   type: dropdown   value & text : 3-character currency code
Field 4 id : to_currency   type: dropdown   value & text : 3-character currency code
Field 5 id : rate   type: textbox

Below is my sample data:

Code: Select all

Table Name : PMT_FOREX
+----+------------+---------------+-------------+-------------+
| ID | EDATE      | FROM_CURRENCY | TO_CURRENCY | ERATE       |
+----+------------+---------------+-------------+-------------+
|  1 | 2019-11-26 | ABC           | DEF         | 0.326382000 |
|  2 | 2019-11-26 | DEF           | ABC         | 3.063900000 |
+----+------------+---------------+-------------+-------------+
Note :  FROM_CURRENCY & TO_CURRENCY are 3-character currency code

Here is my trigger code:

Code: Select all

$dbConnection = 'workflow'; //set to ID of the DB Connection if using an external DB
$tableName = 'PMT_FOREX'; //set to table name

$sql = " SELECT * FROM  $tableName WHERE 1 ORDER BY ID ASC"; 
$result = executeQuery($sql,  $dbConnection); 
@@exchangeRateGrid = array();
$idx = 0;
foreach ($result as $row){
    ++$idx;
    @@exchangeRateGrid[$idx]['ID'] = $row['ID'];
    @@exchangeRateGrid[$idx]['date'] = $row['edate'];
    @@exchangeRateGrid[$idx]['from_currency'] = $row['from_currency'];
    @@exchangeRateGrid[$idx]['to_currency'] = $row['to_currency'];
    @@exchangeRateGrid[$idx]['rate'] = $row['erate'];
}

With the above, I only manage to populate Field 1 (ID). I am very sure that I have missed something here.

Please assist.

Thank you.

[programerboy]

Hi,
Your code looks right and should work properly
Please also test the following code:

Code: Select all

$sql = " SELECT * FROM `PMT_FOREX` WHERE 1 ORDER BY ID ASC"; 
$result = executeQuery($sql); 
@=exchangeRateGrid = array();
$idx = 1;
foreach ($result as $row){
    @=exchangeRateGrid[$idx]['ID'] = $row['ID'];
    @=exchangeRateGrid[$idx]['date'] = $row['edate'];
    @=exchangeRateGrid[$idx]['from_currency'] = $row['from_currency'];
    @=exchangeRateGrid[$idx]['to_currency'] = $row['to_currency'];
    @=exchangeRateGrid[$idx]['rate'] = $row['erate'];
	$idx++;
}

https://pmlearning.info
Thanks

[mfaisel]

Hi,

Thank you for helping. Sorry to inform that the code does not work as expected. I am still getting the same result.

Below is the form after the trigger executed:


Img URL : https://drive.google.com/file/d/1HlqF-l ... WdRa0/view


I would also try to explore if the following could be done as an alternative to the above:
1) Use a trigger to load data and create a JSON object.
2) Iterate the JSON object and populate the GRID fields using JavaSccript

Thanks again.

[programerboy]

You must check your grid name and grid fields name and check your columns table and PMT_FOREX data exactly.
Your code is true, Please check that you have any data in PMT_FOREX table

https://pmlearning.info
Thanks

[mfaisel]

Hi,

Following your suggestion, I have rename the 'date' & 'rate' fields to follwo the fields name in DB. As shown below.

Code: Select all

$dbConnection = 'workflow'; //set to ID of the DB Connection if using an external DB
$tableName = 'PMT_FOREX'; //set to table name

$sql = " SELECT * FROM  $tableName WHERE 1 ORDER BY ID ASC"; 
$result = executeQuery($sql,  $dbConnection); 
@@exchangeRateGrid = array();
$idx = 1;
foreach ($result as $row){
    @@exchangeRateGrid[$idx]['ID'] = $row['ID'];
    @@exchangeRateGrid[$idx]['edate'] = $row['edate'];
    @@exchangeRateGrid[$idx]['from_currency'] = $row['from_currency'];
    @@exchangeRateGrid[$idx]['to_currency'] = $row['to_currency'];
    @@exchangeRateGrid[$idx]['erate'] = $row['erate'];
    $idx++;
}

Still the same result as shown in this picture (https://drive.google.com/file/d/1HlqF-l ... WdRa0/view)


Here, I would also try to explore if the following could be done as an alternative :
1) Use a trigger to load data and create a JSON object.
2) Iterate the JSON object and populate the GRID fields using JavaScript


Thank you

[programerboy]

You don't need use javascript for fill this grid.
Please exactly copy my code and test it again or use @= without @@ for exchangeRateGrid variable.

https://pmlearning.info
Thanks

[mfaisel]

The code has been updated

Code: Select all

$sql = " SELECT * FROM  PMT_FOREX WHERE 1 ORDER BY ID ASC"; 
$result = executeQuery($sql); 
@=exchangeRateGrid = array();
$idx = 1;
foreach ($result as $row){
    @=exchangeRateGrid[$idx]['ID'] = $row['ID'];
    @=exchangeRateGrid[$idx]['edate'] = $row['edate'];
    @=exchangeRateGrid[$idx]['from_currency'] = $row['from_currency'];
    @=exchangeRateGrid[$idx]['to_currency'] = $row['to_currency'];
    @=exchangeRateGrid[$idx]['erate'] = $row['erate'];
    $idx++;
}

However, no changes to the output.

[programerboy]

Please enable debug mode in your process and run your case again and check the ERROR message.

[mfaisel]

Debug mode enabled. Belwo is the exchangeRateGrid value

Code: Select all

Array (
    [1] => Array ( 
      [ID] => 1 
      [edate] => 
      [from_currency] => 
      [to_currency] => [erate] => 
   ) 
   [2] => Array ( 
      [ID] => 2 
      [edate] => 
      [from_currency] => 
      [to_currency] => 
      [erate] => 
   ) 
)

From the debugger, I found that only 'ID' has value, the rest of the fileds have no value. Somehow the trigger did not successfully pass all values into GRID fields.

Please advise.

[mfaisel]

Silly me. The DB field name is case sensative.

Many thanks.

[programerboy]

Good luck