Case study Model IT: Using parameter from the record in a query

In some cases we need to return in the query only records for particular record detail. In ObjectGears such parameter is not passed to the query but it is taken into account when filtering list of records.

Functionality description

There is query RFC implementation - open cases in the entity Change request(RFC). This query is launched from a particular record of Change request and displays entities associated with it.

Solution description

We will create a query displaying required data. The query will also return column chr_id which will be reference to the Change request. We will not display this column in the query. Therefore, we check off options Display in form, Display in list and Display in a detailed list in the query column detail.

SELECT chr.id AS [chr___change_request], r.name AS Request, tc.code AS Test_case, uat.name AS UAT, tcs.name AS Test_case_UAT_status, d.id AS Defect___defects, ds.name AS Defect_status,
case
 when uat2.id = uat.id then 'Ano'
 else null
end UAT_for_PROD_Release,
chr.id AS chr_id

FROM {{:class.change_request:}} chr
LEFT JOIN {{:class.request:}} r ON r.rfc = chr.id
LEFT JOIN {{:class.project-phase:}} pph ON pph.id = chr.[project-phase]
LEFT JOIN {{:class.project:}} p ON chr.project = p.id
LEFT JOIN {{:class.request:test-case:}} rtc ON rtc.DataRowId = r.id
LEFT JOIN {{:class.test-case:}} tc ON tc.id = rtc.LinkDataRowId
LEFT JOIN {{:class.test-case-for-uat:}} tcfu ON tcfu.[test-case] = tc.id
LEFT JOIN {{:class.test-case-status:}} tcs ON tcfu.status = tcs.id
LEFT JOIN {{:class.uat:}} uat ON tcfu.uat = uat.id
LEFT JOIN {{:class.defects:}} d ON tcfu.id = d.[test-case-for-uat]
LEFT JOIN {{:class.defect-status:}} ds ON ds.id = d.status

LEFT JOIN {{: class.release:change_request:}} rechr ON rechr.LinkDataRowId = chr.id
LEFT JOIN {{:class.release:}} re ON re.id = rechr.DataRowId

LEFT JOIN {{:class.uat:}} uat2 ON uat2.id = re.uat_for_prod_release
WHERE (tcs.id = 2 OR tcs.id = 4 OR tcs.id IS NULL) AND (ds.id IS NULL or ds.id = 1)

We want to call the query for a particular record of Change request. Therefore we will create button RFC implementation - open cases, which will by of type Script. In the button detail we will check options Display in the list in context menu, Display in the detail in context menu and Display by a record change. The script creates URL to the query containing variable DrId and value Id of the Change request record, from which the query is called. At last this URL gets open. 

var q = OG.Query.GetByCode( OGModel.Id, 'rfc_readiness_open_cases');
var url = 'Datas.aspx?QId=' + q.Id + '&DrId=' + OGActualDataRowId;
OGForm.RedirectTo(url);

In order we ensure displaying only records relating to the change request from which the query is called, we set a script for the List of records in the query detail. In the script we will look for string DrId in URL, and if it is found, we will filter list of records of the query in column chr_id, containing Id of the Change request, for the value passed over in URL variable DrId.
 

function OnCreateFilter()
{
  var drId = OGForm.QueryString.GetInt( 'DrId');
  if ( drId != null)
  {
      var c = OG.Column.GetByCode( OGQuery.Id, 'chr_id');
      OGDataRowFilter.SetColumnData(c.Id, drId);
  }
}