DTH Development

Hi - I am writing a SQL report extracting information from DTRegister. I am using the excellent forum material contained in the SQL2Exel discussion. The problem I have is reporting data from dropdown, radio etc fields. All I get is the numeric lookup value rather than actual text dropdown value. I could write CASE statements to link the numeric value back to a the required text value but I have some large lookup lists and this would be laborious. So question - does anyone know how to link the numeric values back to the lookup value for these field types as this relationship does not appear to be stored in a database table.

Thanks

Bernard

this is the code I use to get the value of a multivalue field.

GROUP_CONCAT(IF(v.field_id = 19, SUBSTRING_INDEX(SUBSTRING_INDEX(f.values, '|', CAST(v.value AS UNSIGNED)+1),'|',-1),NULL)) AS Division,

table v is the _dtregister_user_field_values and the id value is a foreign field for the _dtregister_fields table

The full code for individual registrations is below:
(Select TRIM(e.summary) AS Tournament, u.userId AS UserID, u.type AS RegType, u.memtot AS GroupSize, IF(u.type, "", "") AS MemberID, p.fee AS Fee, p.paid_amount AS Paid,
CASE
WHEN p.payment_method = 2 THEN "Mail"
WHEN p.payment_method = 3 THEN "Phone"
WHEN p.payment_method = 4 THEN "On Site"
ELSE p.payment_method
END AS PayType,
GROUP_CONCAT(IF(v.field_id = 2, v.value, NULL)) AS FirstName,
GROUP_CONCAT(IF(v.field_id = 3, v.value, NULL)) AS LastName,
GROUP_CONCAT(IF(v.field_id = 10, v.value, NULL)) AS Email,
GROUP_CONCAT(IF(v.field_id = 11, v.value, NULL)) AS Phone,
GROUP_CONCAT(IF(v.field_id = 13, SUBSTRING_INDEX(SUBSTRING_INDEX(f.values, '|', CAST(v.value AS UNSIGNED)+1),'|',-1),NULL)) AS Class,
GROUP_CONCAT(IF(v.field_id = 14, v.value, NULL)) AS YearofBirth,
GROUP_CONCAT(IF(v.field_id BETWEEN 15 AND 18, SUBSTRING_INDEX(SUBSTRING_INDEX(f.values, '|', CAST(v.value AS UNSIGNED)+1),'|',-1),NULL)) AS Category,
GROUP_CONCAT(IF(v.field_id = 19, SUBSTRING_INDEX(SUBSTRING_INDEX(f.values, '|', CAST(v.value AS UNSIGNED)+1),'|',-1),NULL)) AS Division,
GROUP_CONCAT(IF(v.field_id = 20, SUBSTRING_INDEX(SUBSTRING_INDEX(f.values, '|', CAST(v.value AS UNSIGNED)+1),'|',-1), NULL)) AS Membership,
GROUP_CONCAT(IF(v.field_id = 22, SUBSTRING_INDEX(SUBSTRING_INDEX(f.values, '|', CAST(v.value AS UNSIGNED)+1),'|',-1), NULL)) AS TeamCandidate,
GROUP_CONCAT(IF(v.field_id BETWEEN 24 AND 25, v.value,NULL)) AS College,
GROUP_CONCAT(IF(v.field_id = 8, v.value, NULL)) AS ZipCode,
u.register_date AS RegDate,
u.confirmNum AS Confirm#
FROM cal_dtregister_user u
INNER JOIN cal_dtregister_user_field_values v ON v.user_id = u.userId
INNER JOIN cal_dtregister_fee p ON p.user_id = u.userId
INNER JOIN cal_jevents_vevdetail e ON u.eventId = e.evdet_id
INNER JOIN cal_dtregister_fields f ON v.field_id = f.id
WHERE u.eventID = #
GROUP BY u.userId)
ORDER BY RegType DESC, UserID, GroupSize DESC, FirstName

Thanks very much. That works well.
Regards Bernard