Forums (Obsolete)

Old Forums

Portal Engine Questions on portal engine and web parts.

Version 7.x > Portal Engine > UserID and ItemCreatedBy

View modes:

Member

Jerreck - 11/19/2013 3:19:31 PM

UserID and ItemCreatedBy

I'm trying to populate a dropdown list in a report with a SQL Query on a custom table that retrieves the UserName of the user who created a record in the custom table.

Here's the query to populate the dropdown:

SELECT DISTINCT ItemCreatedBy, ItemCreatedBy
FROM customtable_time_tracking
WHERE DepartmentLabel = 'Web Development'

Unfortunately, instead of returning the UserName the query returns the UserID, and there isn't an ItemCreatedBy field in the CMS_User table to join the two fields together so I can retrieve the UserName.

Is there a way for me to force the ItemCreatedBy field in my custom table to map to the UserName field in the CMS_User table, or is there another, easier, way to do this?

Kentico Consulting

Accepted solution

Kentico_RichardS - 11/20/2013 2:36:55 AM

RE:UserID and ItemCreatedBy

Hi,

Thank you for your message.

May I ask where you are using this query? Also which fields there are in the custom table?

Generally speaking this could be accomplished by joining the tables so that the return of your query holds fields from both custom table and the user table.

The following example is untested and you may need to adjust it to your needs.

SELECT DISTINCT ItemCreatedBy, ItemCreatedBy
FROM customtable_time_tracking join CMS_User on 
customtable_time_tracking.UserID = CMS_User.UserID WHERE 
DepartmentLabel = 'Web Development'

Let me know for further assistance.

Kind regards,
Richard Sustek

Member

Jerreck - 11/20/2013 8:47:35 AM

RE:UserID and ItemCreatedBy

I am using this query in a custom report (CMS Desk > Tools > Reporting). It's to populate a dropdown form control in a parameter. I think you have solved my problem, though.

I misunderstood how joins worked. For some reason I thought fields had to have the exact same name in order to map to each other, but apparently they don't :)

I did modify the query that you provided, though. Here's what I changed it to:

SELECT DISTINCT CMS_User.UserName, CMS_User.UserName
FROM customtable_time_tracking JOIN 
CMS_User on customtable_time_tracking.ItemCreatedBy = CMS_User.UserID
WHERE DepartmentLabel = 'Web Development'

I was trying to get the UserName field from the CMS.User table and populate my dropdown with a list of all of the users whose UserID matched the ItemCreatedBy field in my custom table. Just changing the SELECT statement made everything work fine.

Thank you for your help :)

Top