Display User Birthdays

Go to Modules>Membership>Classes>User settings>Fields and you'll find the UserDateOfBirth. This is the name of the field you are looking for. Be careful to not modify that field name but add new fields there if you want.

I guess I don't understand the separate Memberships tab when editing a User. There is a Settings tab in the User profile that has a Birthday, among other things. Then at the bottom there is the Memberships tab. But I have to add a Membership. We aren't really using Memberships, I don't think. In that users have to purchase a membership. I just want to pull the Birthdate in the User's settings.

It looks like we can't even modify what those settings are. I go to Modules->Users->Classes and it's saying "Classes cannot be created or deleted in installed modules."

A memberships a user belongs to are different than the Membership module. The membership module holds all the user classes. The Users module is a placeholder to set permissions on and does not hold the actual user classes. So go to the place I was talking about to find the user object info.

No matter if I put UserDateOfBirth in the WHERE or ORDER BY of the Users Data Source it stops pulling back any results. and I know one user has this field...

Nevermind I had to have the WHERE clause be exactly "UserDateOfBirth is not null" for the ORDER BY not to break it...

Kayla, if you want to include users without a date of birth, try this WHERE caluse: ORDER BY ISNULL(UserDateOfBirth, 0). That will give you the euivalent of DateTime.MinValue.

If you want to add custom values, go to modules > Memberhips > Classes > user. The general tab will tell you that you cannot modify the general data of installed modules as you identified, but you should be able to select the Fields tab. You can add additional fields in here. When you then got to edit the User, you will see an extra tab under Settings called Custom fields where any new fields you add will be stored.

For any object that you want to work with in Kentico, you can look in System > Object types to quickly identify the underlying database table and avoid having to work out which module it is from. One you know the table, you can just look at the database to find the column names. It would be nice if System > Object types would list the columns too, or perhaps event he module, but sadly it does not.

I've realized I need to do a custom query to query upcoming birthdays. I have a Repeater with custom query set up, with a query based on this: https://stackoverflow.com/questions/7343807/mysql-query-to-sort-upcoming-birthdays-based-on-current-date

But I'm getting a System Error when I refresh the front page: "An error occurred and your request couldn't be completed." Which is really vague and unhelpful. Not sure what's wrong with my query. Also I don't see an ID field in either CMS_Users or CMS_UserSettings that will allow me to do a JOIN so that I can also query their name.

SELECT
UserDateOfBirth,
UserDateOfBirth + INTERVAL(YEAR(CURRENT_TIMESTAMP) - YEAR(UserDateOfBirth)) + 0 YEAR AS currbirthday,
UserDateOfBirth + INTERVAL(YEAR(CURRENT_TIMESTAMP) - YEAR(UserDateOfBirth)) + 1 YEAR AS nextbirthday
FROM CMS_UserSettings
WHERE UserDateOfBirth is not null
ORDER BY CASE
WHEN currbirthday >= CURRENT_TIMESTAMP THEN currbirthday
ELSE nextbirthday

Below is my Transformation

 <div class="post-preview">
    <%# Eval("UserDateOfBirth") %>
    <p><%# //Eval("FullName") %></p>
 </div>

CMS_User.UserID = CMS_UserSettings.UserSettingsUserID is the join you're looking for.

When I run your query in SQL I get an error:

'INTERVAL' is not a recognized built-in function name.

I'm running SQL 2016, what version are you using?

Kayla,

The primary key of the cms_user table is UserID. The primary key of the cms_usersettings table is UserSettingsID. The foreign key in the cms_usersettings table is UserSettingsUserID which is linked to the cms_user.UserID field.

Also a Timestamp is not the same datatype as a DateTime so you might want to simply use the DateAdd function.

One last thing, I believe this is incorrect syntax:

<%# //Eval("FullName") %>

If you want to comment out something in the transformation you need to use <%-- --%> or simply remove that eval statement.

I have no idea, the client set this site up in their environment. I guess I'll have to look for a different format query.

OK, you could try some SQL similar to this and join up to the CMS_User table:

SELECT 
    FirstName, LastName, UserName, Email, UserSettingsUserGUID, UserDateOfBirth, Today, CurrBDate,
    CASE 
        WHEN joinedresults.CurrBDate <= joinedresults.Today THEN NextBDate 
        ELSE joinedresults.CurrBDate 
    END AS BirthDay 
FROM 
  (
    SELECT 
        u.FirstName, u.LastName, u.UserName, u.Email, usersettings.UserSettingsUserGUID, usersettings.UserDateOfBirth,  
        usersettings.Today, 
        DateAdd(Day,(CASE 
            WHEN DAY(usersettings.UserDateOfBirth)=29 AND DAY(usersettings.CurrBDate)=28 THEN 1 
            ELSE 0 
        END), CurrBDate) AS CurrBDate, 
        DateAdd(Day,(CASE 
            WHEN DAY(usersettings.UserDateOfBirth)=29 AND DAY(usersettings.NextBDate)=28 THEN 1 
            ELSE 0 
        END), NextBDate) AS NextBDate 
  FROM 
    (
        SELECT 
            UserSettingsUserGUID, UserDateOfBirth,
            CAST(Convert(CHAR(8), GETDATE(),112) AS DateTime) AS Today, 
            DateAdd(Year,DateDiff(year,UserDateOfBirth, GETDATE()),UserDateOfBirth) AS CurrBDate, 
            DateAdd(Year,DateDiff(year,UserDateOfBirth, GETDATE()) + 1,UserDateOfBirth) AS NextBDate 
        FROM 
            CMS_UserSettings
    ) AS usersettings
    INNER JOIN CMS_User u on u.UserGUID = usersettings.UserSettingsUserGUID
) AS joinedresults 

T-SQL isn't my strength, but I think it's doing what you need. (I do hope so anyway :) )

I'm having more issues. It's only pulling back 2 users even though I know I have more than 5 with birthdates. Also I'm having an issue with another field.

I added 2 custom fields - Country and HireDate. I can add Country to the query no problem and pull it back in the Transformation.

But when I change the query to search by HireDate instead of UserDateOfBirth, nothing gets returned. I know HireDate is in CMS_UserSettings. What about this is wrong??

SELECT TOP 10
FullName, UserSettingsUserGUID, Country, HireDate as EventDate, Today, CurrBDate,
CASE 
    WHEN joinedresults.CurrBDate <= joinedresults.Today THEN NextBDate 
    ELSE joinedresults.CurrBDate 
END AS BirthDay 
FROM 
(
SELECT 
    u.FullName, usersettings.UserSettingsUserGUID, usersettings.HireDate,  
    usersettings.Today, Country,
    DateAdd(Day,(CASE 
        WHEN DAY(usersettings.HireDate)=29 AND DAY(usersettings.CurrBDate)=28 THEN 1 
        ELSE 0 
    END), CurrBDate) AS CurrBDate, 
    DateAdd(Day,(CASE 
        WHEN DAY(usersettings.HireDate)=29 AND DAY(usersettings.NextBDate)=28 THEN 1 
        ELSE 0 
    END), NextBDate) AS NextBDate 
 FROM 
(
    SELECT 
        UserSettingsUserGUID, HireDate, Country,
        CAST(Convert(CHAR(8), GETDATE(),112) AS DateTime) AS Today, 
        DateAdd(Year,DateDiff(year,HireDate, GETDATE()),HireDate) AS CurrBDate, 
        DateAdd(Year,DateDiff(year,HireDate, GETDATE()) + 1,HireDate) AS NextBDate 
    FROM 
        CMS_UserSettings
    WHERE HireDate IS NOT NULL
) AS usersettings
LEFT JOIN CMS_User u on u.UserGUID = usersettings.UserSettingsUserGUID
) AS joinedresults 
WHERE CurrBDate >= Today 
ORDER BY CurrBDate

Since your original question was about the name of a field and now you're on to a complex SQL query, you may want to ask the question on Stack Overflow tagged with 'sql' or 't-sql' vs. Kentico since this is more of a SQL question now.

I'm not sure it is though. I added a new field to the same table that the default UserDateOfBirth is located. So there shouldn't be anything wrong with the query. I have no way of knowing what's wrong. I tried enabling SQL Debug. But it's not showing anything. I can't figure out how to display errors on the page.

You're attempting to get users who have a birthday and ordering them by that datetime field. It's an issue with null/empty values on both retrieval of the data as well as with ordering. So best to maybe check to see if that datetime field has a value and then attempt to order them. Your query could be something as simple as this nested query to make sure you're only dealing with users who have a birthday filled in:

select * from (
    select *
    from cms_usersettings
    where userdateofbirth is not null) t1
order by userdateofbirth

You could also set an invalid birthday too if you wanted. Not really ideal but handles the null issue pretty easy.

Kayla, do you have access to the database server, if you can run the query directly, you may get a clue as to what the error might be. If you don't, you can try creating this as a view in Kentico using the Database objects.

There are two places that you can look for issues in the query you're using:

1. Check in the event log using Configuration > Event log to see if any errors have been logged there.
2. You can do this using the Development > Database objects application. Insert your query as a new view and you should then be able to validate that it's working as expected. For example, if I try to create a view using your SQL, I receive the following error as I don't have the same fields: Invalid column name 'HireDate'. Invalid column name 'Country'. Invalid column name 'HireDate'. Invalid column name 'HireDate'. Invalid column name 'HireDate'. Invalid column name 'HireDate'.