Skip to main content
Solved

query events with system custom fields (items)

  • Dec 19, 2022 9:38 AM UTC
  • 2 replies
  • 234 views
Do Gooder (Customer)
Forum|alt.badge.img

HI all,

I need to write a query that will extract some events with system custom fields (items).

 

I found some exemples here on the wiki, but not regarding item system custom fileds

https://wiki.axiossystems.com/assyst11-5Wiki/index.php/Installation:Database_Schema_for_Additional_Custom_Fields#Retrieving_values_of_custom_fields

 

Can anyone help me?

 

thanks!

Best answer by MennovH

The following can be used to get the selected items from custom fields that are lookups to the item table. Just replace PLACE_HOLDER with the shortcode of the custom field you want to extract.

 

(SELECT i.item_n FROM item i, 
    jptsys_web_cust_prop_cont c, 
    jptsys_web_cust_prop jwcp
 WHERE i.item_id = c.lookup_val_id  
 AND jwcp.jptsys_web_cust_prop_id = c.jptsys_web_cust_prop_id  
 AND jwcp.jptsys_web_cust_prop_sc = 'PLACE_HOLDER' 
 AND c.entity_id = incident.incident_id)

 

Regards,

 

Menno

View original

2 replies

Hero (Customer)
Forum|alt.badge.img+12
  • Steve Miller
  • Hero (Customer)
  • Dec 19, 2022 5:33 PM UTC

@KevinM - This one might be in your wheelhouse.

Hero (Employee)
Forum|alt.badge.img+10
  • MennovH
  • Hero (Employee)
  • Answer
  • Jan 5, 2023 4:34 PM UTC

The following can be used to get the selected items from custom fields that are lookups to the item table. Just replace PLACE_HOLDER with the shortcode of the custom field you want to extract.

 

(SELECT i.item_n FROM item i, 
    jptsys_web_cust_prop_cont c, 
    jptsys_web_cust_prop jwcp
 WHERE i.item_id = c.lookup_val_id  
 AND jwcp.jptsys_web_cust_prop_id = c.jptsys_web_cust_prop_id  
 AND jwcp.jptsys_web_cust_prop_sc = 'PLACE_HOLDER' 
 AND c.entity_id = incident.incident_id)

 

Regards,

 

Menno

    Terms & ConditionsAccessibility statement