<!-- 
/*
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">
{
  "Title": "Parent / Subform",
  "Menu": ["Models","Forms"],
  "Code": [
    "/models/FormSubform_Form_PrimaryKey.json",
    "/models/FormSubform_Form_ForeignKey.json",
    "/models/FormSubform_FormM.json",
    "/models/FormSubform_Grid.json",
    "/models/FormSubform_Multisel.json",
    "/models/FormSubform_Exec.json",
    "/models/FormSubform_Nested.json",
    "/models/FormSubform_InsertLink.json",
    "/models/FormSubform_TwoInstances.json",
    "/models/FormSubform_ReadOnlyParent.json",
    "/models/FormSubform_SameTable.json",
    "/models/FormSubform_Unbound.json",
    "/models/FormSubform_Styling.json"
  ],
  "Demo": [
    { "url": "jsHarmonyTutorials/FormSubform_Form_PrimaryKey_Cust?cust_id=1&action=update", "title": "Subform - Form Layout - Via Primary Key" },
    { "url": "jsHarmonyTutorials/FormSubform_Form_ForeignKey_Cust?cust_id=2&action=update", "title": "Subform - Form Layout - Via Foreign Key" },
    { "url": "jsHarmonyTutorials/FormSubform_FormM_Cust?cust_id=1&action=update", "title": "Subform - Form-m Layout" },
    { "url": "jsHarmonyTutorials/FormSubform_Grid_Cust?cust_id=1&action=update", "title": "Subform - Grid Layout" },
    { "url": "jsHarmonyTutorials/FormSubform_Multisel_Cust?cust_id=1&action=update", "title": "Subform - Multisel Layout" },
    { "url": "jsHarmonyTutorials/FormSubform_Exec_Cust?cust_id=1&action=update", "title": "Subform - Exec Layout" },
    { "url": "jsHarmonyTutorials/FormSubform_Nested_Item?item_id=1&action=update", "title": "Multi-level Nested Subforms" },
    { "url": "jsHarmonyTutorials/FormSubform_InsertLink_Cust?cust_id=2&action=update", "title": "Custom 'Insert' Link" },
    { "url": "jsHarmonyTutorials/FormSubform_TwoInstances_Cust?action=update&cust_id=1", "title": "Two Subforms of the Same Model" },
    { "url": "jsHarmonyTutorials/FormSubform_ReadOnlyParent_Cust_Container?action=insert", "title": "Subform with Read-only Parent (Insert)" },
    { "url": "jsHarmonyTutorials/FormSubform_SameTable_Cust?action=insert", "title": "Subform targeting the same table as the parent (Insert)" },
    { "url": "jsHarmonyTutorials/FormSubform_Unbound_Cust?cust_id=1&action=update", "title": "Unbound Subform" },
    { "url": "jsHarmonyTutorials/FormSubform_Styling_Cust?cust_id=1&action=update", "title": "Subform Container Style" }
  ]
}
</script>

<h3>Overview</h3>
There are several types of subforms that can be embedded within a model:
<ul>
  <li>
    <b>Subform Controls</b><br/>
    Subforms that can be embedded at any location within a model
  </li>
  <li>
    <b>Tabbed Subforms</b><br/>
    Subforms organized into tabs, displayed one at a time.  More information is available in the <a href="/tutorials/forms_tabbed">Tabbed Forms</a> tutorial.
  </li>
  <li>
    <b>Duplicate Record Subforms</b><br/>
    A subform used to duplicate the current record.  More information is available in the <a href="/tutorials/duplicating_records">Duplicating Records</a> tutorial.
  </li>
  <li>
    <b>Popup List of Values Subforms</b><br/>
    A popup grid used to select a value.  More information is available in the <a href="/tutorials/list_of_values_popup_lov">Popup LOV</a> tutorial.
  </li>
</ul>
This tutorial focuses on subform controls.<br/>
<br/>
Subform controls can be embedded within the following model layouts:
<ul>
  <li>Form</li>
  <li>Exec</li>
  <li>Report</li>
</ul>

<h3>Subform Examples</h3>
Subform controls can link to models with form, form-m, grid, multisel, and exec layouts.

<h3 data-level="2">Subform Layout: Form</h3>
Subforms that link to another Form layout are used to model 1-to-1 relationships.  The easiest solution is to avoid the subform altogether and create an updateable view.  If that is not an option, "form" subforms can be either linked to a subform's Primary Key or a subform's Foreign Key.<br/>
<br/>
If using subforms with a Form layout, it is necessary to set the "action" on the subform control.  If the parent and subform both link to the same table, the subform should not be available on Insert, since otherwise two insert statements on the same table will be executed.

<h4>Primary Key</h4>
<%-getScreenshot('jsHarmonyTutorials/FormSubform_Form_PrimaryKey_Cust?cust_id=1&action=update&popup=1','Subform Form')%>
<pre>
"Cust": {
  "table":"cust",
  "layout":"form",
  "onecolumn":true,
  "caption":["Customer","Customers"],
  "fields":[
    {"name":"cust_name","caption":"Name"},
    {"name":"cust_sts","caption":"Status"},
    {"control": "subform","target": "CustExt","actions":"BIU"}
  ]
},
"CustExt": {
  "table":"cust_ext",
  "layout":"form",
  "onecolumn":true,
  "popup": [500,250],
  "caption":["Information","Information"],
  "fields":[
    {"name":"cust_ext_desc","caption":"Description","control":"textarea"}
  ]
}
</pre>

<h4>Foreign Key</h4>
<%-getScreenshot('jsHarmonyTutorials/FormSubform_Form_ForeignKey_Cust?cust_id=1&action=update&popup=1','Subform Form')%>
<pre>
"Cust": {
  "table":"cust",
  "layout":"form",
  "onecolumn":true,
  "caption":["Customer","Customers"],
  "fields":[
    {"name":"cust_name","caption":"Name"},
    {"name":"cust_sts","caption":"Status"},
    {"control": "subform","target": "CustAddr","actions":"BIU","bindings":{ "cust_id":"cust_id","cust_addr_type":"'BILLING'" }}
  ]
},
"CustAddr": {
  "table":"cust_addr",
  "layout":"form",
  "onecolumn":true,
  "popup": [550,300],
  "caption":["Address","Addresses"],
  "fields":[
    {"name":"cust_id","key":1,"control":"hidden","actions":"BI"},
    {"name":"cust_addr_type","key":1,"control":"hidden","actions":"BI"},
    {"name":"cust_addr_country","caption":"Country"},
    {"name":"cust_addr_line1","caption":"Address"},
    {"name":"cust_addr_line2","caption":""},
    {"name":"cust_addr_city","caption":"City"},
    {"name":"cust_addr_state","caption":"State"},
    {"name":"cust_addr_zip","caption":"Zip"}
  ]
}
</pre>
If using foreign keys for the bindings, it is necessary to specify the bindings manually, and to mark the foreign keys as primary keys on the subform.  It may also be necessary to designate the "BI" action on the foreign key fields, so that the columns are included in the insert statement.

<h3 data-level="2">Subform Layout: Form-m</h3>
<%-getScreenshot('jsHarmonyTutorials/FormSubform_FormM_Cust?cust_id=1&action=update&popup=1','Subform Form-m')%>
<pre>
"Cust": {
  "table":"cust",
  "layout":"form",
  "onecolumn":true,
  "caption":["Customer","Customers"],
  "fields":[
    {"name":"cust_name","caption":"Name"},
    {"name":"cust_sts","caption":"Status"},
    {"control": "subform","target": "Cust_Address"}
  ]
},
"Cust_Address": {
  "table":"cust_addr",
  "layout":"form-m",
  "onecolumn":true,
  "caption":["Address","Addresses"],
  "fields":[
    {"name":"cust_addr_country","caption":"Country"},
    {"name":"cust_addr_line1","caption":"Address"},
    {"name":"cust_addr_line2","caption":""},
    {"name":"cust_addr_city","caption":"City"},
    {"name":"cust_addr_state","caption":"State"},
    {"name":"cust_addr_zip","caption":"Zip"}
  ]
}
</pre>

<h3 data-level="2">Subform Layout: Grid</h3>
<%-getScreenshot('jsHarmonyTutorials/FormSubform_Grid_Cust?cust_id=1&action=update&popup=1','Subform Grid')%>
<pre>
"Cust": {
  "table":"cust",
  "layout":"form",
  "onecolumn":true,
  "caption":["Customer","Customers"],
  "fields":[
    {"name":"cust_name","caption":"Name"},
    {"name":"cust_sts","caption":"Status"},
    {"control": "subform","target": "Cust_Contact"}
  ]
},
"Cust_Contact": {
  "table":"cust_contact",
  "layout":"grid",
  "caption":["Contact","Contacts"],
  "fields":[
    {"name":"cust_contact_name","caption":"Name"},
    {"name":"cust_contact_title","caption":"Title"},
    {"name":"cust_contact_email","caption":"Email"}
  ]
}
</pre>

<h3 data-level="2">Subform Layout: Multisel</h3>
<%-getScreenshot('jsHarmonyTutorials/FormSubform_Multisel_Cust?cust_id=1&action=update&popup=1','Subform Multisel')%>
<pre>
"Cust": {
  "table":"cust",
  "layout":"form",
  "onecolumn":true,
  "caption":["Customer","Customers"],
  "fields":[
    {"name":"cust_name","caption":"Name"},
    {"name":"cust_sts","caption":"Status"},
    {"control": "subform","target": "Cust_Flag"}
  ]
},
"Cust_Flag": {
  "table":"cust_flag",
  "layout":"multisel",
  "caption":["Customer Flag","Customers Flags"],
  "fields":[
    {"name":"cust_id"},
    {"name":"cust_flag_type","caption":"Flag"}
  ]
}
</pre>

<h3 data-level="2">Subform Layout: Exec</h3>
<%-getScreenshot('jsHarmonyTutorials/FormSubform_Exec_Cust?cust_id=1&action=update&popup=1','Subform Exec')%>
<pre>
"Cust": {
  "table":"cust",
  "layout":"form",
  "onecolumn":true,
  "caption":["Customer","Customers"],
  "fields":[
    {"name":"cust_name","caption":"Name"},
    {"name":"cust_sts","caption":"Status"},
    {"control": "subform","target": "Cust_Activate"}
  ]
},
"Cust_Activate": {
  "layout": "exec", 
  "onecolumn":true,
  "title": "Activate Customer",
  "sqlexec": "update cust set cust_sts='ACTIVE' where cust_id=@cust_id", 
  "fields": [
    {"name":"cust_id","control":"hidden"},
    {"control":"button","value":"Activate","onclick":"xmodel.controller.RunDBFunc(function(){XPage.Refresh();});"}
  ]
}
</pre>

<h3 data-level="2">Nested Subforms</h3>
<%-getScreenshot('jsHarmonyTutorials/FormSubform_Nested_Item?item_id=1&action=update&popup=1','Subform Nested')%>
<pre>
"Item": {
  "table":"item",
  "layout":"form",
  "onecolumn":true,
  "caption":["Equipment","Equipment"],
  "fields":[
    {"name":"item_code","caption":"Code"},
    {"name":"item_name","caption":"Name"},
    {"name":"cust_id","caption":"Customer","lov": { "sql": "select cust_id code_val,cust_name code_txt from cust order by cust_name" } },
    {"control": "subform","target": "Cust","actions":"BU"}
  ]
},
"Cust": {
  "table":"cust",
  "layout":"form",
  "onecolumn":true,
  "caption":["Customer","Customers"],
  "fields":[
    {"name":"cust_name","caption":"Name"},
    {"name":"cust_sts","caption":"Status"},
    {"control": "subform","target": "CustContact"}
  ]
},
"CustContact": {
  "table":"cust_contact",
  "layout":"grid",
  "caption":["Contact","Contacts"],
  "fields":[
    {"name":"cust_contact_name","caption":"Name"},
    {"name":"cust_contact_title","caption":"Title"},
    {"name":"cust_contact_email","caption":"Email"}
  ]
}
</pre>

<h3>Bindings</h3>
Bindings define how to filter / select subform data based on the parent form.  Often bindings can be automatically generated.<br/>
<br/>
More information on bindings can be found in the <a href="/tutorials/model_bindings">Model Bindings</a> tutorials.

<h3>Subform "Insert" Link</h3>
When using a subform with a "form" layout, when the subform record does not exist, an "Insert" link will be displayed:
<%-getScreenshot('jsHarmonyTutorials/FormSubform_InsertLink_Cust?cust_id=2&action=update&popup=1','Subform Form - Insert Link')%>
By default, the insert button links to "insert:MODELID", where "MODELID" is the model ID of the subform.  Bindings from the parent form are passed in the querystring parameters.<br/>
<br/>
It is recommended to add the model.popup property to the child subform, so that the "Insert" link will open in a new window, and the existing record will stay open.<br/>
<br/>
The insert link URL can be customized using the field.controlparams.insert_link property on the subform control.  In the example below, the Description field is given a default value via the insert link:
<pre>
"Cust": {
  "table":"cust",
  "layout":"form",
  "onecolumn":true,
  "caption":["Customer","Customers"],
  "fields":[
    {"name":"cust_name","caption":"Name"},
    {"name":"cust_sts","caption":"Status"},
    {"control": "subform","target": "CustExt","actions":"BIU",
     "controlparams":{ "insert_link":"insert:CustExt&cust_ext_desc='Sample'" }
    }
  ]
},
"CustExt": {
  "table":"cust_ext",
  "layout":"form",
  "onecolumn":true,
  "popup": [500,250],
  "caption":["Information","Information"],
  "fields":[
    {"name":"cust_ext_desc","caption":"Description","control":"textarea"}
  ]
}
</pre>

<h3>Multiple Subforms of the same Model</h3>
If a model needs to be displayed twice on the same page, it is necessary to create a second instance of that model through inheritance:

<%-getScreenshot('jsHarmonyTutorials/FormSubform_TwoInstances_Cust?action=update&cust_id=1&popup=1','Two Subforms of the Same Model')%>

If the subform records have not yet been created, an "Insert" link is displayed:
<%-getScreenshot('jsHarmonyTutorials/FormSubform_TwoInstances_Cust?action=update&cust_id=2&popup=1','Two Subforms of the Same Model')%>
<pre>
"Cust": {
  "table":"cust",
  "layout":"form",
  "onecolumn":true,
  "caption":["Customer","Customers"],
  "actions":"BIU",
  "fields":[
    {"name":"cust_name","caption":"Name"},
    {"name":"cust_sts","caption":"Status"},
    {"control": "subform","target": "Cust_Address_Billing","bindings":{ "cust_id":"cust_id", "cust_addr_type":"'BILLING'" },"actions":"BU"},
    {"control": "subform","target": "Cust_Address_Shipping","bindings":{ "cust_id":"cust_id", "cust_addr_type":"'SHIPPING'" },"actions": "BU"}
  ]
},
"Cust_Address": {
  "table":"cust_addr",
  "layout":"form",
  "onecolumn":true,
  "popup": [550,300],
  "caption":["Address","Addresses"],
  "fields":[
    {"name":"cust_id","key":1,"control":"hidden"},
    {"name":"cust_addr_type","key":1,"control":"hidden"},
    {"name":"cust_addr_country","caption":"Country"},
    {"name":"cust_addr_line1","caption":"Address"},
    {"name":"cust_addr_line2","caption":""},
    {"name":"cust_addr_city","caption":"City"},
    {"name":"cust_addr_state","caption":"State"},
    {"name":"cust_addr_zip","caption":"Zip"}
  ]
},
"Cust_Address_Billing": {
  "inherits":"Cust_Address",
  "title": "Billing Address"
},
"Cust_Address_Shipping": {
  "inherits":"Cust_Address",
  "title": "Shipping Address"
}
</pre>

<h3>Database Transactions</h3>
When using subforms or tabbed forms, all operations are saved in one atomic commit transaction.  Top-level insert statements are executed first, and then the primary key from the newly inserted data is passed through bindings to the subforms.

<h4>Transactions: Client-side</h4>
In the client-side API calls, in the Form-m example above, the following data would be sent during insert:
<pre>
[
  {
		"model": "Cust",
    "method": "put",
		"query": "",
		"post": "cust_name=ACME+Industries&cust_sts=DEACTIVE"
  }, 
  {
    "model": "CustAddr",
    "method": "put",
		"query": "",
		"post": "cust_addr_country=USA&cust_addr_line1=123+Test+St.&cust_addr_line2=&cust_addr_city=Chicago&cust_addr_state=IL&cust_addr_zip=60103&cust_id=%25%25%25cust_id%25%25%25"
	}
]
</pre>
The value for cust_id in the "CustAddr" model is passed as "%%%cust_id%%%".

<h4>Transactions: Server-side</h4>
Internally, after the cust insert is complete, the resulting cust_id is passed back via a select statement and stored in transtbl (any data passed back from a SQL statement in a transaction is stored in transtbl).  Then the CustAddr insert is processed as follows:
<pre>
dbtasks['CustAddr'] = function(dbtrans, callback, transtbl){
  sql_params = _this.ApplyTransTblEscapedParameters(sql_params, transtbl);
  db.Row(dbcontext, sql, sql_ptypes, sql_params, dbtrans, function (err, rslt, stats) {
    ...
  });
};
</pre>
The AppSrv.ApplyTransTblEscapedParameters function replaces %%%cust_id%%% in sql_params with the cust_id from transtbl.

<h3>Actions / Roles / Insert vs Browse vs Update Mode</h3>
There are four types of Actions used in determining subform behavior:
<ul>
  <li><b>querystring.action</b>: Top-level querystring "action": insert, browse, or update</li>
  <li><b>parent_model.actions</b>: Parent model effective actions (after roles are applied)</li>
  <li><b>parent_field.actions</b>: Subform control effective actions (after roles are applied) - </li>
  <li><b>child_model.actions</b>: Subform model effective actions (after roles are applied)</li>
</ul>
The subform's effective actions depend only on the querystring.action, and not on the parent model or parent control.<br/>
<br/>
The parent model and parent control are only used to determine when to hide the subform.  The subform is displayed when:
<pre>
If querystring.action = "insert" and child_model.layout is "form", "form-m", or "grid"
  When parent_model.actions has "I"
    and parent_field.actions has "I"
    and child_model.actions has "B" or "I"

If querystring.action = "insert" and child_model.layout is "multisel", "exec", or "report"
  When parent_model.actions has "I"
    and parent_field.actions has "I"
    and child_model.actions has "B" or "U"

If querystring.action = "browse"
  When parent_model.actions has "B"
    and parent_field.actions has "B"
    and child_model.actions has "B"

If querystring.action = "update"
  When parent_model.actions has "B" or "U"
    and parent_field.actions has "B" or "U"
    and child_model.actions has "B" or "U"
</pre>

<h3 data-level="2">Read-only Parent with Editable Child</h3>
Since the subform's actions are independent of the parent model and parent control, it is possible to have a read-only parent with an editable child.  Parent fields are rendered empty on insert.
<%-getScreenshot('jsHarmonyTutorials/FormSubform_ReadOnlyParent_Cust_Container?action=insert&popup=1','Form / Subform - Read Only Parent')%>
<pre>
"Cust_Container": {
  "table":"cust",
  "layout":"form",
  "actions":"B",
  "caption":["Customer","Customers"],
  "fields":[
    {"name":"cust_name","caption":"Name"},
    {"control": "subform","target": "Cust","actions":"BIU"}
  ]
},
"Cust": {
  "table":"cust",
  "layout":"form",
  "caption":["Customer","Customers"],
  "fields":[
    {"name":"cust_name","caption":"Name"},
    {"name":"cust_sts","caption":"Status"}
  ]
}
</pre>

<h3 data-level="2">Parent Form and Child Form that reference the same table</h3>

If the Parent Form and Child Form reference the same table, the subform field should not have the "I" action, so that the subform will be hidden on insert:
<%-getScreenshot('jsHarmonyTutorials/FormSubform_SameTable_Cust?action=insert&popup=1','Form / Subform - Same Table - Insert')%>

The subform will then be automatically displayed once the record is saved:
<%-getScreenshot('jsHarmonyTutorials/FormSubform_SameTable_Cust?cust_id=1&action=update&popup=1','Form / Subform - Same Table - Update')%>
<pre>
"Cust": {
  "table":"cust",
  "layout":"form",
  "onecolumn":true,
  "caption":["Customer","Customers"],
  "fields":[
    {"name":"cust_name","caption":"Name"},
    {"name":"cust_sts","caption":"Status"},
    {"control": "subform","target": "cust_doc","actions": "BU"}
  ]
}

"cust_doc": {
  "table":"cust",
  "layout":"form",
  "caption":["Document","Documents"],
  "fields":[
    {"name":"cust_doc","type":"file","caption":"Document","controlparams": {"data_folder": "tutorials_cust_doc"} }
  ]
}
</pre>

<h3 data-level="2">Parent Form with Unbound Subform</h3>
A parent form can contain a subform that is linked without any bindings:
<%-getScreenshot('jsHarmonyTutorials/FormSubform_Unbound_Cust?cust_id=1&action=update&popup=1','Form / Subform - Unbound')%>
<pre>
"Cust": {
  "table":"cust",
  "layout":"form",
  "onecolumn":true,
  "caption":["Customer","Customers"],
  "fields":[
    {"name":"cust_name","caption":"Name"},
    {"name":"cust_sts","caption":"Status"},
    {"control": "subform","target": "Cust_Listing_Info","bindings":{}}
  ]
},
"Cust_Listing_Info": {
  "layout":"grid",
  "caption":["Key Customers"],
  "sort":["vitem_cnt"],
  "sqlselect":"select cust.cust_id,cust_name,count(item_id) item_cnt from cust left outer join item on item.cust_id=cust.cust_id where %%%SQLWHERE%%% %%%DATALOCKS%%% %%%SEARCH%%% group by cust.cust_id order by %%%SORT%%% limit %%%ROWCOUNT%%% offset %%%ROWSTART%%%",
  "fields":[
    {"name":"cust_name", "caption":"Customer Name" },
    {"name":"item_cnt", "caption":"# Equipment" }
  ]
}
</pre>

<h3>Styling Subforms</h3>
The subform components can be styled via the model styles available in the <a href="/tutorials/model_styling">Model Styling Tutorials</a>.<br/>
<br/>
Additionally, the subform container can be styled using the subform field's "controlclass" and "controlstyle" attributes:
<%-getScreenshot('jsHarmonyTutorials/FormSubform_Styling_Cust?cust_id=1&action=update&popup=1','Subform Container Style')%>
<pre>
"Cust": {
  "table":"cust",
  "layout":"form",
  "onecolumn":true,
  "caption":["Customer","Customers"],
  "fields":[
    {"name":"cust_name","caption":"Name"},
    {"name":"cust_sts","caption":"Status"},
    {
      "control": "subform",
      "target": "CustContact",
      "controlstyle": "border: 4px dashed black;",
      "controlclass": "fieldWithErrors"
    }
  ]
},
"CustContact": {
  "table":"cust_contact",
  "layout":"grid",
  "caption":["Contact","Contacts"],
  "fields":[
    {"name":"cust_contact_name","caption":"Name"},
    {"name":"cust_contact_title","caption":"Title"},
    {"name":"cust_contact_email","caption":"Email"}
  ]
}
</pre>
