Working with Subforms

A Subform is a secondary table that allow you to add multiple line items to a primary table. The cmdlet allows you to retrieve Subform information, add lines to an existing subform, modify subform entries, and delete entries.

Select

In order to access the Subform data, you must filter on the Id of the parent:

SELECT * FROM ParentTable WHERE Id = 21142143232903

This will return the entire Subform as a JSON aggregate.

Deep Insert

It is possible to insert a new parent object along with its full subform record all in one operation. This is done by formatting the Subform as a JSON aggregate and setting the name of the Subform field to this string in your INSERT statement. The following query will insert a parent record with a subform containing two line items.

 INSERT INTO ParentTable (ParentName,ChildForm) 
   VALUES (
     'New Parent', 
     '[ 
       {
        "subformprop1": "row1val1", 
        "subformprop2": "row1val2"
       }, 
       {
        "subformprop1": "row2val1", 
        "subformprop2": "row2val2"
       }
      ]')

Modifying Subform records

Any modifications done to an existing Subform requires an UPDATE request on the parent record. When building the JSON aggregate of the Subform in your query, you must specify the Id of the lines that you wish to leave untouched. If the Id is not included in the JSON, that line will be deleted from the Subform. New lines can be added by appending a line in your JSON that does not contain an Id. Existing lines can be modified by specifying the Id and any properties to be modified. Properties not specified will be unchanged. Below are examples of how to add lines, modify existing lines, and delete lines from a Subform record that already exists.

Add line items to an existing Subform

To add a line to an existing Subform, you will need to issue an UPDATE request with a JSON aggregate that contains all of the existing Ids specified along with the new lines specified without Ids. The service will recognize that it needs to insert the new line because the record does not have an Id. See below example.

 UPDATE ParentTableTable SET ChildForm = 
     -- first include existing child Ids so that they do not get deleted, then append the new lines
     '[
       { "id":"2732548000001192003" },
       { "id":"2732548000001192004" },
       { "id":"2732548000001192005" },
       {
        "subformprop1": "newline1val",
        "subformprop2": "newline1val2",
       }, 
       {
        "subformprop1": "newline2val",
        "subformprop2": "newline2val2"
       }
      ]'  WHERE id=2732548000001192001

Modify a Subform line

To modify a line, specify the Id of that line and any properties you wish to modify. Properties not specified will remain unchanged. In the example below, records with Ids ending in 3 and 4 will be untouched, and the record ending in 5 gets one property modified. Other records that exist are deleted.

 UPDATE ParentTableTable SET ChildForm = 
     '[
       { "id":"2732548000001192003" },
       { "id":"2732548000001192004" },
       {
        "id":"2732548000001192005",
        "subformprop1": "updatedval"
       }
      ]'  WHERE id=2732548000001192001

You can perform any combination of deletions, inserts, and updates to line items in a single request depending on how you format the JSON aggregate of the Subform.