<!-- 
/*
Copyright 2017 apHarmony

This file is part of jsHarmony.

jsHarmony is free software: you can redistribute it and/or modify
it under the terms of the GNU Lesser General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

jsHarmony is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU Lesser General Public License for more details.

You should have received a copy of the GNU Lesser General Public License
along with this package.  If not, see <http://www.gnu.org/licenses/>.
*/
-->
<script type="text/x-tutorial-info">
{
  "ID": "list_of_values_custom",
  "Title": "Custom SQL List of Values",
  "Menu": ["Models","Codes / List of Values"],
  "Code": [
    "/models/FieldLOV_CustomSQL_SQLSelect_Grid.json",
    "/models/FieldLOV_CustomSQL_SQLSelect_Form.json",
    "/models/FieldLOV_CustomSQL_SQLTruncate.json",
    "/models/FieldLOV_CustomSQL_AlwaysGetFullLOV.json"
  ],
  "Demo": [
    { "url": "jsHarmonyTutorials/FieldLOV_CustomSQL_SQLSelect_Grid?action=browse", "title": "Custom SQL - Optimization - lov.sqlselect Grid" },
    { "url": "jsHarmonyTutorials/FieldLOV_CustomSQL_SQLSelect_Form?action=browse&sale_id=2", "title": "Custom SQL - Optimization - lov.sqlselect Form" },
    { "url": "jsHarmonyTutorials/FieldLOV_CustomSQL_SQLTruncate?action=update&sale_id=2", "title": "Custom SQL - Optimization - lov.sqltruncate - Update" },
    { "url": "jsHarmonyTutorials/FieldLOV_CustomSQL_SQLTruncate?action=insert&cust_id=1", "title": "Custom SQL - Optimization - lov.sqltruncate - Insert with Querystring Value" },
    { "url": "jsHarmonyTutorials/FieldLOV_CustomSQL_AlwaysGetFullLOV?action=browse&cust_id=1", "title": "Custom SQL - De-optimization - lov.always_get_full_lov" }
  ]
}
</script>

<h3>Introduction</h3>
While the code tables provide an easy way to create and manage list of values, applications often require additional custom list of values.<br/>
<br/>
Samples and introductory information about implementing custom SQL list of values can be found in the <a href="/tutorials/list_of_values_basic">Basic List of Values</a> and <a href="/tutorials/list_of_values_parent_child">Parent / Child List of Values</a> tutorials.<br/>
<br/>
The custom SQL list of values are defined as follows:
<pre>
"lov": {
  //1-dimensional "basic" list of values
  "sql": "select ----- code_val, ----- code_txt where 1=1 %%%TRUNCATE%%% %%%DATALOCKS%%%",

  //2-dimensional list of values
  "sql2": "select ----- code_parent, ----- code_val, ----- code_txt where 1=1 %%%TRUNCATE%%% %%%DATALOCKS%%%",
  "parent": "<i>field.name</i>", //Name of the parent field for the 2-dimensional list of values

  //Multi-dimensional list of values
  "sqlmp": "select ----- code_parent1, ----- code_parent2, ----- code_parent<i>N</i>, ----- code_val, ----- code_txt where 1=1 %%%TRUNCATE%%% %%%DATALOCKS%%%", //For <i>N</i>-dimensional list of values
  "parents": ["<i>field.name</i>"], //Ordered array of parent field names - must match order in lov.sqlmp

  "sql_params": ["<i>field.name</i>"], //(Optional) Array of parameters.  This is automatically generated by the system.
  "datalock": { ... },                 //Datalock that should be applied to the sql / sql2 / sqlmp expressions
  "nodatalock":["<i>field.name</i>"],  //Do not apply datalocks for these datalock ids, if applicable

  "sqlselect": "...", //SQL statement included as part of the MODEL SQL select statement, to get the display text for the current code value
  "sqltruncate": "...", //SQL condition to limit the list to one entry for exact match - @parameter = field.name
  "always_get_full_lov": false, //Whether to NOT truncate the list to the current value for: "B", readonly, or query-string defined field values

  "db": "<i>default</i>" //Name of the dbconfig to use for the SQL operations
}
</pre>

<h3>SQL Optimization</h3>
When using custom SQL list of values, the list can get very large.  For instance, if using a list of values for a Customer lookup with thousands of records, the delay of loading the list can take several seconds.<br/>
<br/>
In order to improve performance, the lov.sqlselect and lov.sqltruncate optimizations can be used.

<h3>SQL Optimization - lov.sqlselect</h3>
Downloading the list of values for a drop-down is not required when:
<ul>
  <li>Field.actions does not have "I" or "U"</li>
  <li>Field.readonly = true</li>
</ul>

In these situations, the drop-down will not be editable, so the field value and display caption can be downloaded together with the form or grid data as part of the "select" statement.<br/>
<br/>
The following example uses the "lov.sqlselect" property to improve performance:
<pre>
{
  "name":"cust_id",
  "caption":"Customer",
  "lov":{
    "sql":"select cust_id code_val,cust_name code_txt from cust where 1=1 %%%TRUNCATE%%%",
    "sqlselect":"select cust_name from cust where cust.cust_id=sale.cust_id"
  }
}
</pre>
If the form action is set to "browse", the lov.sql statement will not be executed, however the drop-down display text will still be properly populated:
<h4>lov.sqlselect - Grid</h4>
<%-getScreenshot('jsHarmonyTutorials/FieldLOV_CustomSQL_SQLSelect_Grid?popup=1&action=browse','Custom SQL - Optimization - lov.sqlselect Grid')%>

<h4>lov.sqlselect - Form</h4>
<%-getScreenshot('jsHarmonyTutorials/FieldLOV_CustomSQL_SQLSelect_Form?popup=1&action=browse&sale_id=2','Custom SQL - Optimization - lov.sqlselect Form')%>

<h3>SQL Optimization - lov.sqltruncate</h3>
As opposed to the "lov.sqlselect" property, the "sqltruncate" is applied:
<ul>
  <li>On a "form" layout in "Insert" mode, if the drop-down field is set in the querystring, and the "locked_by_querystring" attribute is true (default behavior)</li>
  <li>On an "exec" or "report" layout, if the drop-down field is set in the querystring, and the "locked_by_querystring" attribute is true (default behavior)</li>
</ul>
In this situation, the drop-down display text needs to be selected from the database, however there is no existing record to get the code from.  The drop-down value needs to be selected from the lov.sql, lov.sql2, or lov.sqlmp statement.<br/>
<br/>
In order to faciliate this, the lov.sql, lov.sql2, or lov.sqlmp statement can have a "%%%TRUNCATE%%%" expression, which will be replaced by lov.sqltruncate when applicable.<br/>
<br/>
For example:
<pre>
{
  "name":"cust_id",
  "caption":"Customer",
  "lov":{
    "sql":"select cust_id code_val,cust_name code_txt from cust where 1=1 %%%TRUNCATE%%%",
    "sqltruncate":"cust_id=@cust_id"
  }
}
</pre>
During a normal operation, the full list of values will be retrieved:
<%-getScreenshot('jsHarmonyTutorials/FieldLOV_CustomSQL_SQLTruncate?popup=1&action=update&sale_id=2','Custom SQL - Optimization - lov.sqltruncate - Update')%>
<pre>
LOV SQL Executed
================
select cust_id code_val,cust_name code_txt from cust where 1=1
</pre>
However, during an insert operation, when cust_id is in the querystring:
<%-getScreenshot('jsHarmonyTutorials/FieldLOV_CustomSQL_SQLTruncate?popup=1&action=insert&cust_id=1','Custom SQL - Optimization - lov.sqltruncate - Insert with Querystring Value')%>
<pre>
LOV SQL Executed
================
select cust_id code_val,cust_name code_txt from cust where 1=1  and cust_id=@cust_id
</pre>

<h3>SQL De-optimization - lov.always_get_full_lov</h3>
By default, when using code tables, lov.sqlselect will be automatically applied, and the list of values will not be returned from the database if the field or model is read-only.<br/>
<br/>
In some situations, it may be useful to disable the optimization to always select the full list of values, such as when backend JavaScript uses the list of values for other operations.<br/>
<br/>
The lov.always_get_full_lov property will cause the system to always return the full list of values, regardless of the list of values optimizations:
<%-getScreenshot('jsHarmonyTutorials/FieldLOV_CustomSQL_AlwaysGetFullLOV?popup=1&action=browse&cust_id=1','Custom SQL - De-optimization - lov.always_get_full_lov')%>
<pre>
{
  "name":"cust_sts",
  "caption":"Status",
  "lov":{
    "code":"cust_sts",
    "always_get_full_lov":true
  }
}
</pre>
