Welcome, Guest

TOPIC: Using SQL2Excel, need custom field IDs

Using SQL2Excel, need custom field IDs 14 years 4 months ago #8933

divalsafety
Topic Author
Offline
Senior Boarder
Posts: 45
Thank you received: 0

Hello my fellow DT Register users and/or admin!

I'm trying to generate a spreadsheet using the SQL2Excel component and I'm trying to call up my custom fields within the SQL statement. I'm sure there is a very easy solution, I'm sure that I'm close using the 'jos_dtregister_fields' statement but that's where I'm stuck. My custom fields are SALES_REP and EXPO_LOC. How would I specify this in the SQL statement?

Thanks in advance for your help!

Chris

Please Log in or Create an account to join the conversation.

Using SQL2Excel, need custom field IDs 14 years 4 months ago #8939

dthadmin
Offline
Administrator
Posts: 5470
Thank you received: 3

You will need the field ID number. So check jos_dtregister_fields and the field 'name' will show the name like SALES_REP. If you just need to pull the data of a certain field, the same field name is used in jos_dtregister_user and jos_dtregister_group_member (for group members)... so the field name is all you need to finding corresponding data for the field.

If you need to check what fields are assigned to an event, grab the 'id' of the desired field. Then the table jos_dtregister_field_event contains the 'field_id' (corresponding with the ID in jos_dtregister_fields) and 'event_id'.

Hope that helps

Please Log in or Create an account to join the conversation.

Using SQL2Excel, need custom field IDs 14 years 4 months ago #8940

divalsafety
Topic Author
Offline
Senior Boarder
Posts: 45
Thank you received: 0

check jos_dtregister_fields and the field 'name' will show the name like SALES_REP

ok, I have the field IDs, now how would the SQL statement look? Here's what I have so far...

SELECT jos_dtregister_user.userId AS Num, If(jos_dtregister_user.userType='G',jos_dtregister_group_member.firstname,jos_dtregister_user.userFirstName) AS FirstName, If(jos_dtregister_user.userType='G',jos_dtregister_group_member.lastname,jos_dtregister_user.userLastName) AS LastName, jos_dtregister_user.userOrganization AS Company, jos_dtregister_user.userAddress AS Address, jos_dtregister_user.userCity AS City, jos_dtregister_user.userState AS State, If(jos_dtregister_user.userType='G',jos_dtregister_group_member.email,jos_dtregister_user.userEmail) AS Email, jos_dtregister_user.userPhone AS Phone
FROM jos_dtregister_user LEFT JOIN jos_dtregister_group_member ON jos_dtregister_user.userId = jos_dtregister_group_member.groupUserId
WHERE (((jos_dtregister_user.eventId)="16"))
ORDER BY jos_dtregister_user.userId DESC