Display User Birthdays

Kayla Johnson asked on October 16, 2017 22:04

I'm working on an Intranet page for a client site. They want to have an upcoming birthdays and anniversaries section.

I have a Users Data Source as described here https://docs.kentico.com/k8/developing-websites/loading-and-displaying-data-on-websites and a Basic Repeater. However anytime I put anything about the DateOfBirth in the WHERE or ORDER BY fields for the Data Source, it suddenly pulls no Users. I'm guessing it's an invalid field name. But I can't find the list of valid field names anywhere. Also if I have it pull all users and just try to display the User's birthday in the Transformation, it shows nothing.

Why don't I have access to this field? Is there a way in a transformation to display all available fields like a var_dump in PHP? I don't even know what the object's name is in a Transformation to begin to debug this.

Correct Answer

Kayla Johnson answered on October 17, 2017 16:50

MSSQL is not my strength either! I'm a PHP dev who uses MySQL. But thanks so much for the query. I had to make a few modifications to exclude users without birthdays and ORDER BY Date.

Here is my final query:

SELECT 
FullName, UserSettingsUserGUID, UserDateOfBirth, Today, CurrBDate,
CASE 
    WHEN joinedresults.CurrBDate <= joinedresults.Today THEN NextBDate 
    ELSE joinedresults.CurrBDate 
END AS BirthDay 
FROM 
(
SELECT 
    u.FullName, 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
    WHERE UserDateOfBirth IS NOT NULL
) AS usersettings
LEFT JOIN CMS_User u on u.UserGUID = usersettings.UserSettingsUserGUID
) AS joinedresults 
WHERE CurrBDate >= Today 
ORDER BY CurrBDate
0 votesVote for this answer Unmark Correct answer

Recent Answers


Brenden Kehren answered on October 16, 2017 22:20

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.

0 votesVote for this answer Mark as a Correct answer

Kayla Johnson answered on October 16, 2017 22:48

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."

0 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on October 16, 2017 22:51 (last edited on October 16, 2017 22:51)

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.

0 votesVote for this answer Mark as a Correct answer

Kayla Johnson answered on October 16, 2017 23:11

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...

0 votesVote for this answer Mark as a Correct answer

Kayla Johnson answered on October 16, 2017 23:13

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

0 votesVote for this answer Mark as a Correct answer

Matt Nield answered on October 16, 2017 23:58 (last edited on October 17, 2017 00:03)

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.

0 votesVote for this answer Mark as a Correct answer

Kayla Johnson answered on October 17, 2017 15:38

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>
0 votesVote for this answer Mark as a Correct answer

Matt Nield answered on October 17, 2017 15:53 (last edited on October 17, 2017 15:54)

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?

0 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on October 17, 2017 16:02

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.

1 votesVote for this answer Mark as a Correct answer

Kayla Johnson answered on October 17, 2017 16:02

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.

0 votesVote for this answer Mark as a Correct answer

Matt Nield answered on October 17, 2017 16:24 (last edited on October 17, 2017 16:32)

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 :) )

1 votesVote for this answer Mark as a Correct answer

Kayla Johnson answered on October 18, 2017 22:28

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
0 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on October 18, 2017 22:32

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.

0 votesVote for this answer Mark as a Correct answer

Kayla Johnson answered on October 20, 2017 23:37

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.

0 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on October 20, 2017 23:44

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.

0 votesVote for this answer Mark as a Correct answer

Matt Nield answered on October 20, 2017 23:48

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'.
0 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.