Custom Values Overview

{gdata title=|Missing screencast| app=|none|}

How to Access

Select Components → Contact Enhanced → Custom Values from the drop-down menu on the back-end of your Joomla! installation. This option is only available if you have a SQL Form Field type.

Description

The Form Fields Manager screen allows you to create advanced Form Fields using the SQL Form Field type, which displays a Select dropdown with values from the database. Some users might want to add the values to a new database table, that's why we've created the Custom Values;

Details

• Text: The text the user will see in the dropdown select list (required);

• Value: The text that you will get in the email (required);

• Type: You can enter any value to help you to keep things organized;

• Category: You can use this field as you wish; You can enter a value or select one from the list; IMPORTANT: This is not necessary related to the Contact Enhanced Categories;

SQL Field Examples:

• Nothing fancy:
  SELECT text, value FROM #__contact_enhanced_cv WHERE category = 'MY_CATEGORY_NAME_OR_ID' ORDER BY ordering
• Same content for the value and text:
  SELECT text, text as value FROM #__contact_enhanced_cv WHERE category = 'MY_CATEGORY' AND TYPE='Any type' ORDER BY ordering
• If you have a small SQL knowledge you can play around with it. Let's say that you need to show the part number and product name, you your SQL will be like this:
  SELECT CONCAT_WS(' :: ',value, text) AS text, value FROM #__contact_enhanced_cv WHERE category = 'MY_CATEGORY_NAME_OR_ID' ORDER BY orderingSELECT CONCAT_WS(' :: ',value, text) AS text, value FROM #__contact_enhanced_cv WHERE category = 'MY_CATEGORY_NAME_OR_ID' ORDER BY ordering
• Same content for the value and text:
  SELECT text, text as value FROM #__contact_enhanced_cv WHERE category = 'MY_CATEGORY' AND TYPE='Any type' ORDER BY ordering
• Get a backend userlist:
  SELECT username AS value, name AS text FROM #__users WHERE gid >=23 ORDER BY name ASC
• Get data when the user is logged in:
  Get user id:
  SELECT id AS value, id AS text
  FROM #__users
  WHERE id = {user_id}
  
  Get user name:
  SELECT username AS value, username AS text
  FROM #__users
  WHERE id = {user_id}
  
  
  
  
• Get Product and order Info from VirtueMart v3:
  
  

  SELECT 	CONCAT('Product: ', p.product_sku
  		,' Order#: ',	o.order_number
  		,' Total: ',o.order_total) AS text,
  	CONCAT('Product:',p.virtuemart_product_id,' - ', p.product_sku
  		,' :: Order ID:', o.virtuemart_order_id 
  		,' Date: ', FROM_UNIXTIME(o.created_on)
  		,', Status: ', os.order_status_name) AS value
  FROM #__virtuemart_orders AS o
  INNER JOIN #__virtuemart_order_items AS oi ON o.virtuemart_order_id = oi.virtuemart_order_id
  INNER JOIN #__virtuemart_products AS p ON p.virtuemart_product_id = oi.virtuemart_product_id
  INNER JOIN #__virtuemart_orderstates AS os  ON o.order_status = os.order_status_code
  WHERE o.virtuemart_user_id ={user_id}
  
  

• Get Product and order Info from VirtueMart v2:
  SELECT p.product_name AS text,
  CONCAT('Product:',p.product_id,' - ', p.product_name,
  ' :: Order ID:', o.order_id ,
  ' Date: ', FROM_UNIXTIME(o.cdate),
  ', Status: ', os.order_status_name) AS value
  FROM jos_vm_orders AS o
  INNER JOIN jos_vm_order_item AS oi ON o.order_id = oi.order_id
  INNER JOIN jos_vm_product AS p ON p.product_id = oi.product_id
  INNER JOIN jos_vm_order_status AS os  ON o.order_status = os.order_status_code
  WHERE o.user_id ={user_id}