Skip to content

REST_GetRecords

Jump to bottom
Julio Carneiro edited this page Nov 21, 2017 · 8 revisions

The REST_GetRecords HTTP Request is used to retrieve a set of records from a 4D Database. It takes a query string and will send back the result of that query.

The POST request payload must include the following arguments/attributes:

  • TableName: name of the table to get records from
  • QueryString: a JS44D query string (see documentation here)
  • Columns: a JSON Array describing columns and data to retrieve for each record resulting from the query (Base64 encoded)
  • StartRec: starting record # to return, from the resulting record set
  • NumRecs: max # of records to return, from the resulting record set (if empty or set to -1, indicates the total numer of records selected)
  • FilterOptions: Record filter is also a JS44D query string, applied to the query results to further filter down the record selection to return
  • OrderBy: a JS44D order by string (see documentation here), indicating the fields to sort the resulting record set

The Table name must be a valid table in the host database structure.

The JS44D QueryString is used to select the records to return. To that selection the FilterOptions, if non empty, will be applied to eventually further reduce the selected record set.

The OrderBy instruction, again if not empty, is then applied to the final record set. StartRec and NumRecs are then used to delimit the records to be sent back in the request's response.

Both QueryString and FilterOptions are documented in The JS44D Query String page.

StartRec and NumRecs are useful if you want to implement paging when retrieving records from the database. It is used to define the portion of records to return from the selected record set.

The Columns array lists the fields, or calculated values, to be sent back in the response. Each entry in the array is a JSON object with the following possible attributes/properties:

  • name: the attribute's name to use in the response JSON object (usually the field name); this property is mandatory
  • field: the name of the field whose contents is to be sent back; it can be in the long dot notation format as "Table.Field", which allows one to retrieve data from related records (see below)
  • formula: instead of a field, it is possible to return a calculated value; this attribute can be any valid 4D Formula that returns a string value; when executed, the current record from the selected set is in memory, as well as any related one records, so the 4D expression or method called can assume that those records are available
  • joinFK & joinPK: those attributes indicate a field in the 'main' table (joinFK) and one in a related table (joinPK); those values must be in the long dot notation format, and are used to retrieve a related record (examples below)

The REST_GetRecords response is a JSON object with the following attributes:

  • table: the 4D Table number
  • total: the total number of records in the Table
  • selected: the total number of records selected, after applying the QueryString and the FilterOptions
  • records: an Array of JSON objects with all fields as indicated in the request's Columns argument; it will also include an additional attribute, "_recnum", with each record's record number in the database

Sample Request

Here is a sample request:

https://gyazo.com/344a369a89993de6b45d6677a97e6499

The Columns in the request above, which are sent Base64 encoded, correspond to the following JSON string:

[{"name":"RecordID","field":"Location.RecordID"},
{"name":"CreationDate","field":"Location.CreationDate"},
{"name":"LastUpdateDate","field":"Location.LastUpdateDate"},
{"name":"TimeStamp","field":"Location.TimeStamp"},
{"name":"LocationName","field":"Location.LocationName"},
{"name":"City","field":"Location.City"},
{"name":"Country","field":"Location.Country"},
{"name":"GeoLocation","field":"Location.GeoLocation"},
{"name":"Locale","field":"Location.Locale"}]

And the response to the request above would look something like:

https://gyazo.com/3c59ff5a8540718b3b3601a60f282c22

More Examples

A) Using Related Fields

https://gyazo.com/2de939e851dd83619921c6ce99ddb68f

The Columns in the request above, which are sent Base64 encoded, correspond to the following JSON string:

[{"name":"RecordID","field":"ViewerContent.RecordID"},
{"name":"FeatureID","field":"ViewerContent.FeatureID"},
{"name":"UserID","field":"ViewerContent.UserID"},
{"name":"IMDBTitle","field":"Features.IMDBTitle"},
{"name":"PosterURL","field":"Features.PosterURL"},
{"name":"JustWatchID","field":"Features.JustWatchID"},
{"name":"TMDBID","field":"Features.TMDBID"},
{"name":"MGCCI","field":"ViewerContent.MGCCI"},
{"name":"MGEQI","field":"ViewerContent.MGEQI"},
{"name":"MGPAI","field":"ViewerContent.MGPAI"},
{"name":"MGPEI","field":"ViewerContent.MGPEI"},
{"name":"MGPVR","field":"ViewerContent.MGPVR"},
{"name":"MGNQI","field":"ViewerContent.MGNQI"},
{"name":"Feedback_Content","field":"ViewerContent.Feedback_Content"},
{"name":"Feedback_Style","field":"ViewerContent.Feedback_Style"},
{"name":"Feedback_Theme","field":"ViewerContent.Feedback_Theme"},
{"name":"Feedback_Narrative","field":"ViewerContent.Feedback_Narrative"},
{"name":"Feedback_Execution","field":"ViewerContent.Feedback_Execution"}]

Notice that some of the columns being retrieved come from related tables. The request's table is ViewerContent, and some columns come from a Features table. The Component uses the relation defined in the structure to retrieve Features record that relates to a ViewerContent record.

The response JSON would look like this:

https://gyazo.com/3115a846292a926d7b539f656da41873

B) Using Calculated Values

TBD

Clone this wiki locally