@simardl
The field is a system lookup so the values would not be in the jptsys_web_lkup_data table as that is for custom lookups. System lookups are for system lookup tables which can currently be only one of items, contact users or cost centres. You can tell which by looking at the lookup_table_id field in jptsys_web_cust_prop. These are:
| lookup_table_id | Table | Schema Name |
| 26 | Cost Centres | cost_centre |
| 49 | Contact Users | usr |
| 143 | Items | item |
( You can see the mappings from number to table in the jptsys_tabledata table)
If REGION is an item then the SQL would be:
(select
lkup.item_n
from
jptsys_web_cust_prop_cont (NOLOCK) jc
inner join jptsys_web_cust_prop (NOLOCK) j on jc.jptsys_web_cust_prop_id=j.jptsys_web_cust_prop_id
inner join item (NOLOCK) lkup on jc.LOOKUP_VAL_ID = lkup.item_id
where
i.incident_id = jc.entity_id
and j.jptsys_web_cust_prop_sc= 'REGION'
) AS "Region / Région",
If it was a cost centre then:
(select
lkup.cost_centre_n
from
jptsys_web_cust_prop_cont (NOLOCK) jc
inner join jptsys_web_cust_prop (NOLOCK) j on jc.jptsys_web_cust_prop_id=j.jptsys_web_cust_prop_id
inner join cost_centre (NOLOCK) lkup on jc.LOOKUP_VAL_ID = lkup.cost_centre_id
where
i.incident_id = jc.entity_id
and j.jptsys_web_cust_prop_sc= 'REGION'
) AS "Region / Région",
If it was a Contact User then:
(select
lkup.usr_n
from
jptsys_web_cust_prop_cont (NOLOCK) jc
inner join jptsys_web_cust_prop (NOLOCK) j on jc.jptsys_web_cust_prop_id=j.jptsys_web_cust_prop_id
inner join usr (NOLOCK) lkup on jc.LOOKUP_VAL_ID = lkup.usr_id
where
i.incident_id = jc.entity_id
and j.jptsys_web_cust_prop_sc= 'REGION'
) AS "Region / Région",
I hope this helps you.
Paul Cooper
Report Designer
IFS ESM BU
I was able to determined the the lokup_value_id was the item_id in the item table.
If I recall correctly you are right and the lookup_table_id was 143.
@simardl there is more information on custom fields (and much more besides) in the report development documentation that is attached in this Knowledge Base article:
