JSON Filter Parameter
- Trello (Unlicensed)
- Jason Burford (Unlicensed)
The filter parameter is complex. In the following example, which updates a sample inventory when the Done button is selected on the Signature page, you use the filter parameter to determine how to filter a dataset:
\"filter\":\"27.CUSTREF1 = 16.CUSTSAMPLE and 27.CUSTREF2 = 15.CUSTLOTNUMBER and15.CUSTPRODUCT = 16.uid and 15.uid in (select childid from me_datarelation where parenttype=7 and childtype=15 and (parentid=$7.uid or devicerowidparent=$7.uid ) )\"
In this example, a dataset is returned under the following conditions:
- When CUSTREF1 in Module 27 is equal to CUSTSAMPLE in Module 16
- When CUSTREF2 in Module 27 is equal to CUSTLOTNUMBER in Module 15
- When CUSTPRODUCT in Module 15 is equal to uid (rowid) in Module 16
The uid (rowid) in Module 15 is listed as a child record in the me_datarelation table where:
- Module 7 is the parent
- Module 15 is the child
- The parent uid (rowid) is stored in the Module 15 record (as a rowid from the data source or within the temporary rowid on a mobile device).
All conditions are joined with AND statements, so only rows from the dataset that meet all conditions are retrieved. For every parent or child relationship that is in the data, there is an entry in the me_datarelation table. The next table describes the columns in the me_datarelation table.
Column | Description |
parenttype = MODULEID | The MODULEID of a parent, which specifies the records that have a particular parent object. |
childtype = MODULEID | The MODULEID of a child, which identifies the data to which a particular child object is associated. |
parentid = | Specifies the parent row ID of the record. |
devicerowidparent = | You usually use devicerowidparent in conjunction with the parentid (explained in the previous row). The parentid only returns the the data source row ID, but devicerowidparent also includes any temporary row IDs stored on a device. This occurs if the device has not managed to synchronize with the the data source Server since the record was created. |
childid = | Specifies the row ID of a child record. |
devicerowidchild = | Specifies the temporary row ID stored on a device, if the device has not yet synchronized with the data source. |
referencetype | Determines the type of reference field that is being filtered. Possible values follow. If no value is selected, then the default value is 4.
|
To retrieve all contacts for an account from the me_datatable, then set the following in the query:
parenttype = 1, childtype = 2, parentid=[Accountrowid], devicerowidparent=[ accountrowid] and referencetype=4
The resulting children will be your dataset. The reason for checking both parentid and devicerowidparent is because the records might have been created offline, and might not have the full the data source row ID at the time.
This table describes an important flag that the filter parameter can also include.
Flag | Description |
\"isfromscreen\":\ "yes\" | If the isfromscreen flag is set to Yes, then data is read from the current module before the record is saved. Any data that is already typed on a device by a user is read as part of the current query. |