Assyst 11.6.3 custom field data

@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:

( 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