<!-- 
/*
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": "fields_overview",
  "Title": "Fields Overview",
  "Menu": ["Models","Fields / Controls"],
  "Code": [
    "/models/FieldOverview_LineBreaks.json"
  ],
  "Demo": [
    { "url": "jsHarmonyTutorials/FieldOverview_LineBreaks?action=update&cust_id=1", "title": "Fields Overview - Line Breaks" }
  ]
}
</script>

<h3>Introduction</h3>
Fields have two purposes:
<ol>
  <li><b>In the API / Database Backend:</b> Define the columns that will be used in the model's database queries</li>
  <li><b>In the front-end:</b> Define the fields and controls displayed on the form or grid</li>
</ol>
Columns that are not defined in the fields section will not be retrieved from the database, unless manual SQL overrides are used.<br/>
<br/>
Since there is significant overlap between the columns selected from the database and the columns displayed on the form / grid in most database systems, jsHarmony combines them into one array called "fields":
<pre>
{
  "table":"cust",
  "layout":"grid",
  "title":"All Customers",
  "caption":["Customer","Customers"],
  "actions":"B", 
  "fields":[
    {"name":"cust_id","type":"bigint","actions":"B","key":1,"caption": "ID"},
    {"name":"cust_sts","type":"varchar","length":"8","caption":"Status","actions":"B"},
    {"name":"cust_name","type":"varchar","length":"50","caption":"Customer","caption_ext": "Customer Name","actions":"B","link":"update:Cust"}
  ]
}
</pre>
Automatic schema recognition enabled many of the above properties to be automatically generated based on the database schema.  The above model could also be written as:
<pre>
{
  "table":"cust",
  "layout":"grid",
  "title":"All Customers",
  "caption":["Customer","Customers"],
  "actions":"B", 
  "fields":[
    {"name":"cust_id","caption": "ID"},
    {"name":"cust_sts","caption":"Status"},
    {"name":"cust_name","caption":"Customer","caption_ext": "Customer Name","link":"update:Cust"}
  ]
}
</pre>

<h3>Field Name</h3>
The field name has three purposes:
<ol>
  <li>The field name is used as the column name in database queries</li>
  <li>The field name is used as the control class in rendering the front-end</li>
  <li>The field name is used as the internal ID to override field behavior in inherited forms</li>
</ol>
It is recommended to include a name for each field, even if the field is just static HTML code.  This will enable the field to be overridden in inherited models.<br/>
<br/>
Field names should be lower-case for cross-database compatibility.

<h3>Field Types</h3>
Each of a model's database fields require a type definition.  The following types are available:
<ul>
  <li>
    "type: "varchar", "length": <i>LENGTH</i>
    &nbsp; &nbsp; &nbsp; &nbsp; ex. "type: "varchar", "length": 50
  </li>
  <li>
    "type: "char", "length": <i>LENGTH</i>
    &nbsp; &nbsp; &nbsp; &nbsp; ex. "type: "char", "length": 50
  </li>
  <li>"type: "bigint"</li>
  <li>"type: "int"</li>
  <li>"type: "smallint"</li>
  <li>"type: "tinyint"</li>
  <li>"type: "boolean"</li>
  <li>
    "type: "decimal", "precision": [<i>PRECISION</i>, <i>SCALE</i>]
    &nbsp; &nbsp; &nbsp; &nbsp; ex. "type: "decimal", "precision": [10, 4]
  </li>
  <li>
    "type: "float", "precision": <i>PRECISION</i>
    &nbsp; &nbsp; &nbsp; &nbsp; ex. "type: "float", "precision": 53
  </li>
  <li>"type: "date"</li>
  <li>"type: "time"</li>
  <li>
    "type: "datetime", "precision": <i>PRECISION</i>
    &nbsp; &nbsp;  &nbsp; &nbsp; ex. "type: "datetime", "precision": 7
  </li>
  <li>
    "type: "varbinary", "length": <i>LENGTH</i>
    &nbsp; &nbsp; &nbsp; &nbsp; ex. "type: "varbinary", "length": 30
  </li>
</ul>
Varchar / Char/ Varbinary length can be -1 for unlimited length.  If the length is omitted, -1 is set by default.<br/>
<br/>
Other custom types are available on a per-database basis.  Full support for all PostgreSQL and SQL Server types is available.

<h3>Field Actions / Roles</h3>
The field's "actions" property can consist of any of the following characters:
<ul>
  <li><b>B</b> - Browse (Field is retrieved as part of the database Select statement)</li>
  <li><b>I</b> - Insert (Field value is set as part of the database Insert statement)</li>
  <li><b>U</b> - Update (Field value is updated in the database Update statement)</li>
</ul>
If the "B" action is not defined, the field is not selected from the database and is not rendered to the screen.  However, the field can still be used as part of a SQL "where" condition, or as a Key or Foreign Key.<br/>
<br/>
One way to hide fields in inherited models is to set the field's "actions" to "" (empty string).

<h4>Automatic Actions</h4>
If field.actions is not defined, it will be defaulted based on the model layout, model properties, field database definition, and field properties.<br/>
<br/>
If you come across a situation where the automatic action is incorrect, and could be improved by adjusting the default field.actions algorithm, please submit a suggested rule change via a support ticket on jsHarmony's GitHub page.

<h4>Roles</h4>
Roles can be used to change field behavior based on user roles.  For instance, the field can be set to read-only for certain users.<br/>
<br/>
Learn more about Actions / Roles in the <a href="/tutorials/model_actions">Model Actions / Roles</a> tutorial.

<h3>Key Fields &amp; Foreign Keys</h3>
In order to run a Select, Update, Insert, or Delete statement, a Key field is required.  This should ideally be a bigint or int field.  If a table uses two primary keys, it is still recommended to add an identity column that contains a unique sequence number, and use the sequence number as the key in jsHarmony.  Key fields are defined by setting the field.key property to 1:
<pre>
{
  "fields": [
    {"name":"cust_id", "type": "bigint", "key": 1}
  ]
}
</pre>
Foreign Keys are columns that reference keys in other tables, and can be used in parent/child relationships - such as in subform and tab bindings.  The <b>field.foreignkey</b> attribute should be set in the following situations:
<ul>
  <li>When using subforms, on the foreign key of the child form (child column binding)</li>
  <li>When using a multisel, on the foreign key id that the selected values will be linked to</li>
</ul>
<pre>
{
  "fields": [
    {"name":"cust_id", "type": "bigint", "foreignkey": 1}
  ]
}
</pre>

<h3>Read-only Fields</h3>
The field.readonly property can be used to make a field read-only.  This is different from changing the field.actions to "B", in that the field value can still be saved on Insert using Default Values or Querystring parameters, or updated using JavaScript.<br/>
<br/>
Additionally, as an optimization, dropdown read-only fields on update will not have the full List of Values returned by default, since they only need to display the current record.

<pre>
{
  "name": "cust_id",
  "readonly": true
}
</pre>

<h3>Locked By Querystring on Insert</h3>
During an insert operation, if the field's name is in the quersytring, the field's initial value will default to the value in the querystring.<br/>
<br/>
The field.locked_by_querystring property defines whether the field is still editable when its initial value is defaulted via a querystring parameter.<br/>
<br/>
The field.locked_by_querystring property is set to "true" by default, but can be overridden to false in the field definition:
<pre>
{
  "name": "cust_sts",
  "locked_by_querystring": false
}
</pre>

<h3>Field Line Breaks / Block Elements</h3>
The following properties are used to manage field line breaks:
<ul>
  <li>field.eol :: Add a line break after the field</li>
  <li>field.nl :: Add a line break before the field</li>
  <li>field.block :: Make the field a "block" element, and add a line break both before and after the field</li>
</ul>
Field.block has the additional benefit of squashing the next field's "field.nl" property, so that it does not add an extra line break between the fields.
<%-getScreenshot('jsHarmonyTutorials/FieldOverview_LineBreaks?action=update&cust_id=1&popup=1','Field Line Breaks')%>
<pre>
{ 
  "table":"cust",
  "layout":"form",
  "onecolumn":true,
  "caption":["Customer","Customers"],
  "fields":[
    {"name": "cust_id","eol": true},
    {"control":"html", "value":"Sample Text"},
    {"name": "cust_name","nl": true},
    {"name": "cust_sts","block": true}
  ]
}
</pre>
At the model level, the "model.onecolumn" property can be used to make all fields, except html controls, have a line break before each field.
<pre>
{
  "onecolumn": true
}
</pre>

<h3>Field Validation</h3>
Validation is performed both Client-side before submitting the form, and Server-side before processing the form.  Validation rules are automatically added for each field based on the datatype, however additional validation rules can be added to define required fields or specify ranges.<br/>
<br/>
Validation is covered in more detail in the <a href="/tutorials/field_validation">Field Validation Tutorials</a>.
