/* Readme */
set its data type as VarChar of a suitable length, 50 or so, 
set it as a calculated field, and use this SQL query for calculating its value

/* code */
SELECT CONCAT(
    UPPER(SUBSTRING(`products`.`name`, 1, 5)),
    `products`.`dept_id`,
    'SN',
    `products`.`product_id`
) FROM `products` 
WHERE `products`.`product_id` = '%ID%



/* Readme */
set its data type as VarChar of a suitable length, 50 or so,
set it as a calculated field, and use this SQL query for calculating its value

/* code */
SELECT CONCAT(
    UPPER(SUBSTRING(`gtm`.`parties`, 1, 5)),
    `gtm`.`case_num_ref`,
    'GT',
    `gtm`.gtmid`
) FROM `gtm` 
WHERE `gtm`.`gtmid` = '%ID%