Example Code: Querying Records with a Where Clause

  • Article

This C# code uses the QueryRecords WhereClause to select all records that have a _Modified value greater than a specific date-time value. This example converts the Date-Time value to universal time and explicitly formats it using the SOAP date-time format. When a Date-Time value is an operation parameter, the proxy code formats, but in the WhereClause, the value is included in a string and the client application must format it.

When querying based on the value of the _Modified system field, it is important to understand that it is the time that the workspace member updated the record using the local system clock and not the time that the update was replicated to the local device. This has the following implications:

  • If a workspace member is offline, the _Modified value can be significantly earlier than the current local time that the modification is replicated to a system.

  • If multiple workspace members are modifying the same workspace and one or more of them is offline at the time, Groove may resolve the conflict by applying an update with an earlier _Modified value after one with a later value. Groove may also roll back updates so that it can reapply them in a different order.

If you attempt to find updates by doing successive queries with increasing values of _Modified based on the local current time, you could miss some updates because the record could be updated with a value of _Modified that was less than the last value you used in a query. In contrast, on the Groove Data Bridge, the Forms_Tool_EDBSeqTime system field is the time that the update is applied on the local system, and it is guaranteed to be monotonically increasing. Consequently, you can use Forms_Tool_EDBSeqTime in successive queries to find all new updated records.

 // Create a RecordQuery object and set it to query for records
 // that have a _Modified value greater than a specified value.
 // Do not include attachment content.
 GrooveForms2.RecordQuery recordQuery = new GrooveForms2.RecordQuery();
 recordQuery.FormURI = "";
 recordQuery.IncludeFileAttachmentContent = false;
 recordQuery.QueryMetadataOnly = false;
 recordQuery.UnreadRecordsOnly = false;
 recordQuery.ViewURI = "";
 // Set up WhereClause for records modified in previous seven days
 DateTime NowMinus7 = DateTime.Now.AddDays(-7.0);
 
 // Format date-time to yyyy-mm-ddThh:mm:ss.sssZ
 string NowMinus7String = 
   NowMinus7.ToUniversalTime().ToString(
   "yyyy'-'MM'-'ddTHH':'mm':'ss'.'fff'Z'");

// Create WhereClause delimiting date-time with '#
recordQuery.WhereClause = "_Modified > '#"
  + NowMinus7String + "#'";
  
// Query for records
GrooveForms2.Forms2RecordDataSet forms2Ds =
  forms2Svc.QueryRecords(recordQuery);

See Also

Reference

GrooveForms2.QueryRecords Operation

Concepts

Accessing Forms Tool Records
Querying Records