Virtual Lookups
GG relies on up-to-date lookup tables to properly convert PKL id
values to a human-readable unique textual representation.
getAllLookupTableStats
This is a key SOAP endpoint that informs the CT about changes to data in the underlying data.
The data is however loaded using the existing getData()
method.
getData(*_lookup)
CT refreshes lookups with a standard set of parameters:
string selectParams = ":createddate=" + lastSyncDate.ToString("s") +
"; :modifieddate=" + lastSyncDate.ToString("s") +
"; :valuemember=; :startpkey=; :stoppkey=; :displaymember;";
code_value_lookup
gets a special treatment in the CT, the dataview name is hardcoded and referenced.
SELECT
cv.code_value_id AS code_value_id
,cv.group_name AS group_name
,cv.value AS value_member
,COALESCE(cvl.title, cv.value) AS display_member
,COALESCE(cvl.sys_lang_id, 1) AS sys_lang_id
FROM
code_value cv
LEFT JOIN code_value_lang cvl ON cv.code_value_id = cvl.code_value_id AND cvl.sys_lang_id = __LANGUAGEID__
WHERE
((cv.created_date > COALESCE(:createddate, '1753-01-01'))
OR (cv.modified_date > COALESCE(:modifieddate, '1753-01-01'))
OR (cv.value IN (:valuemember))
OR (cv.code_value_id BETWEEN :startpkey AND :stoppkey)
OR (cvl.created_date > COALESCE(:createddate, '1753-01-01'))
OR (cvl.modified_date > COALESCE(:modifieddate, '1753-01-01'))
)
Advanced lookup UI
When the lookup table contains any column name that matches a column name in the source dataview they are wired as filters:
Make sure this is an FK column and if so inspect the fields available in the lookup table that might match fields in the parentRow's table there are matches - wire them up as filters to restrict the number of rows returned to the dialog box that the user chooses from...
Booleans are handled differently, all is_*
fields in the lookup are rendered as checkboxes and are not included in the filters.
taxonomy_species_lookup
is a complex example of a lookup table, it includes several is_*
fields that get a special treatment by the CT LookupPicker
control.
SELECT
ts.taxonomy_species_id AS value_member,
CASE
WHEN EXISTS (SELECT * FROM taxonomy_species ts2 WHERE ts.name = ts2.name AND ts.taxonomy_species_id != ts2.taxonomy_species_id)
THEN CONCAT(ts.name, CONCAT(' ', COALESCE(ts.name_authority, '')))
ELSE ts.name
END AS display_member,
CASE
WHEN taxonomy_species_id = current_taxonomy_species_id THEN 'Y'
ELSE 'N'
END AS is_accepted_name
FROM
taxonomy_species ts
sys_table_field_lookup
is defined as
SELECT
stf.sys_table_field_id AS value_member,
stf.field_name AS display_member,
stf.sys_table_id
FROM
sys_table_field stf
WHERE
((stf.created_date > COALESCE(:createddate, '1753-01-01'))
OR (stf.modified_date > COALESCE(:modifieddate, '1753-01-01'))
OR (stf.sys_table_field_id IN (:valuemember))
OR (stf.sys_table_field_id BETWEEN :startpkey AND :stoppkey))
And sys_dataview_field_lookup
is defined with the SQL below, but it's a bit unclear as to why sys_dataview_id
is included:
SELECT
sdf.sys_dataview_field_id AS value_member,
sdf.field_name AS display_member,
sdf.sys_dataview_id
FROM
sys_dataview_field sdf
WHERE
((sdf.created_date > COALESCE(:createddate, '1753-01-01'))
OR (sdf.modified_date > COALESCE(:modifieddate, '1753-01-01'))
OR (sdf.sys_dataview_field_id IN (:valuemember))
OR (sdf.sys_dataview_field_id BETWEEN :startpkey AND :stoppkey))
geography_lookup
also uses a few booleans, but they're easily tackled:
SELECT
g.geography_id AS value_member,
LTRIM(RTRIM(COALESCE(cvl.title, g.country_code) +
CASE COALESCE(CONVERT(NVARCHAR, g.adm1), '') WHEN '' THEN '' ELSE ', ' + g.adm1 END +
CASE COALESCE(CONVERT(NVARCHAR, g.adm2), '') WHEN '' THEN '' ELSE ', ' + g.adm2 END)) AS display_member,
g.is_valid,
CASE WHEN adm2 IS NULL THEN 'Y' ELSE 'N' END AS is_hide_counties
FROM
geography g
cooperator_lookup
is also hardcoded and expects:
SELECT
DISTINCT co.cooperator_id AS value_member,
LTRIM(RTRIM(COALESCE(co.last_name, '') + ', ' + COALESCE(co.first_name, '') + ', ' + COALESCE(co.organization, ''))) AS display_member,
s.site_short_name AS site,
COALESCE(su.is_enabled, 'N') AS account_is_enabled,
CASE WHEN co.cooperator_id = co.current_cooperator_id THEN 'Y' ELSE 'N' END AS is_current_address
FROM
cooperator AS co
LEFT JOIN sys_user AS su ON co.cooperator_id = su.cooperator_id
LEFT JOIN site s ON co.site_id = s.site_id
WHERE
((co.created_date > COALESCE(:createddate, '1753-01-01'))
OR (co.modified_date > COALESCE(:modifieddate, '1753-01-01'))
OR (co.cooperator_id IN (:valuemember))
OR (co.cooperator_id BETWEEN :startpkey AND :stoppkey)
OR (su.created_date > COALESCE(:createddate, '1753-01-01'))
OR (su.modified_date > COALESCE(:modifieddate, '1753-01-01'))
OR (s.created_date > COALESCE(:createddate, '1753-01-01'))
OR (s.modified_date > COALESCE(:modifieddate, '1753-01-01'))
)
There's also a mailing_geography_lookup
:
SELECT
g.geography_id AS value_member,
COALESCE(g.adm1 + ', ', '') + COALESCE(cvl.title, g.country_code) AS display_member
FROM
geography g
order_request_lookup
:
SELECT
oreq.order_request_id AS value_member,
COALESCE(CONVERT(NVARCHAR, oreq.order_request_id),'') + ' - ' + COALESCE(c.last_name,'') + ', ' + COALESCE(c.first_name,'') + ', ' + COALESCE(c.organization,'') as display_member
FROM
order_request AS oreq
LEFT JOIN cooperator c ON oreq.final_recipient_cooperator_id = c.cooperator_id