Using user points module, the users of my site are successfully being assigned points for any content they post or revisions they make and so on. Now when a user logs in, i want the user to be able to see his points and the reason for the points being assigned. The view output would be something like this:

  • 1.Blog Pic A, Blog Title A, Blog TeaserA (and then) Points

    2.Blog Pic B, Blog Title B, Blog TeaserB (and then) Points

Here then user gets points for their blog uploads. How can i create a view showing the above result. I tried but was not successful. I created a view of user points with Contextual filter Userpoints Transactions: User and fields as Userpoints Transactions: Points (Points) . However i am not getting any fields as content:body and so on. I understand i have to add a relationship but i am a bit confused here. I tried adding the relationship Userpoints Transactions: Entity ID but the i get an error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'userpoints_txn.nid' in 'on clause'. What is this. How can i create the view for the above shown usecase. It seems a simple solution. What am i missing?

Edit: I tried different views but none are showing what i need. Would appreciate if someone can export a view, or at least, and preferable, guide me so that i also understand where i am going wrong.

  • this really should not be quite tough but i am unable to solve it...had to offer 50 of my hard earned reputations
    – why
    Commented Feb 28, 2013 at 6:47
  • your SQL exception probably goes in a similar direction like this here - so you may need to adapt the userpoint views_data for your relation
    – rémy
    Commented Feb 28, 2013 at 10:23
  • @remy..thanks..will look into it...am i in the right direction though?
    – why
    Commented Feb 28, 2013 at 10:33
  • don't know userpoints .. but the approach seems fine ;)
    – rémy
    Commented Feb 28, 2013 at 12:54

1 Answer 1


I have userpoints active on one of my sites, and the display you are looking for is integrated with the module.

Look at the user account, there should be a tab (Points) (/user/$uid/points).

To create your own page, introduce a required relationship to the users table, then a contextual filter using that relationship on user:uid. When the configuration screen comes up, under 'When the filter is not in the URL' provide a default value > UID from currently logged in user. This should get your view working.

Note: The relationship must be created first, then it is available for the contextual filter.

Edit: Step by step.

  1. Add new view: (/admin/structure/views/add)

    In the 'Add View' screen, enter view name, and choose to Show 'Userpoints Transactions' of type 'all'. I chose a Table format to show fields. The rest is adjustable later.

    Click 'Continue and Edit' to go to the edit screen.

  2. Add a relationship for 'Userpoints Transactions:User'. Check 'Required' and 'Apply all displays'. This will bring the users table into the view.

  3. Create a contextual filter on 'users:uid' using the relationship you just created. Configure the default value provided as 'UID from logged in user', and Apply.

  4. Add whatever fields you wish and format the view.

This is the export:

$view = new view();
$view->name = 'users_points';
$view->description = '';
$view->tag = 'default';
$view->base_table = 'userpoints_txn';
$view->human_name = 'Users Points';
$view->core = 7;
$view->api_version = '3.0';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Master */
$handler = $view->new_display('default', 'Master', 'default');
$handler->display->display_options['title'] = 'Users Points';
$handler->display->display_options['use_more_always'] = FALSE;
$handler->display->display_options['access']['type'] = 'none';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'full';
$handler->display->display_options['pager']['options']['items_per_page'] = '10';
$handler->display->display_options['style_plugin'] = 'table';
/* Relationship: Userpoints Transactions: User */
$handler->display->display_options['relationships']['uid']['id'] = 'uid';
$handler->display->display_options['relationships']['uid']['table'] = 'userpoints_txn';
$handler->display->display_options['relationships']['uid']['field'] = 'uid';
$handler->display->display_options['relationships']['uid']['required'] = TRUE;
/* Field: User: Name */
$handler->display->display_options['fields']['name']['id'] = 'name';
$handler->display->display_options['fields']['name']['table'] = 'users';
$handler->display->display_options['fields']['name']['field'] = 'name';
$handler->display->display_options['fields']['name']['relationship'] = 'uid';
/* Field: Userpoints Transactions: Description */
$handler->display->display_options['fields']['description']['id'] = 'description';
$handler->display->display_options['fields']['description']['table'] = 'userpoints_txn';
$handler->display->display_options['fields']['description']['field'] = 'description';
/* Field: Userpoints Transactions: Points */
$handler->display->display_options['fields']['points']['id'] = 'points';
$handler->display->display_options['fields']['points']['table'] = 'userpoints_txn';
$handler->display->display_options['fields']['points']['field'] = 'points';
/* Contextual filter: User: Uid */
$handler->display->display_options['arguments']['uid']['id'] = 'uid';
$handler->display->display_options['arguments']['uid']['table'] = 'users';
$handler->display->display_options['arguments']['uid']['field'] = 'uid';
$handler->display->display_options['arguments']['uid']['relationship'] = 'uid';
$handler->display->display_options['arguments']['uid']['default_action'] = 'default';
$handler->display->display_options['arguments']['uid']['default_argument_type'] = 'current_user';
$handler->display->display_options['arguments']['uid']['summary']['number_of_records'] = '0';
$handler->display->display_options['arguments']['uid']['summary']['format'] = 'default_summary';
$handler->display->display_options['arguments']['uid']['summary_options']['items_per_page'] = '25';

/* Display: Page */
$handler = $view->new_display('page', 'Page', 'page');
$handler->display->display_options['defaults']['hide_admin_links'] = FALSE;
$handler->display->display_options['path'] = 'users-points';

/* Display: Block */
$handler = $view->new_display('block', 'Block', 'block');
$handler->display->display_options['defaults']['hide_admin_links'] = FALSE;
$handler->display->display_options['defaults']['pager'] = FALSE;
$handler->display->display_options['pager']['type'] = 'some';
$handler->display->display_options['pager']['options']['items_per_page'] = '5';

Second Edit:

The descriptions shown in the screen print you showed me are not stored in the database, but are generated on the fly when the page is requested.

Here is how you complete the view.

First, install and activate the Views PHP module.

Second, go into userpoints.views.inc and change 'field' => 'nid'; on line 435 to 'base field' => 'nid';. Then insert the following code after line 466:

$data['userpoints_txn']['txn_id'] = array(
      'title' => t('Transaction ID'),
      'help' => t('The transaction ID for the points allocation.'),
      'field' => array(
          'handler' => 'views_handler_field',
      'filter' => array(
          'handler' => 'views_handler_filter_string',
      'sort' => array(
          'handler' => 'views_handler_sort',

This will make the Transaction ID accessible to views. (The necessary changes are included in a patch file linked at the end of this answer.)

Save your view in its current form to clear its temporary cache, then go to /admin/config/development/performance and clear your caches.

Return to your view, and add 'Userpoints Transactions:Transaction ID' as a field, and set it as excluded from the display.

Then add another field. In the 'Add Field' screen, filter on Global, and select 'Global:PHP'.

To configure the field, Create a label of your choosing, and in the Output box add the following:

$transaction = userpoints_transaction_load($row->txn_id);
$value = userpoints_create_description($transaction);
echo $value;

Note: Do not place anything in the Value box! It is used for another purpose.

Save your view, and you are good to go.

BTW. The changes necessary in userpoints.views.inc are in a patch attached to the Features Request issue I opened on D.O.

  • hello again...thanks for the export..however, the description field is empty..this is my main concern as before....
    – why
    Commented Feb 28, 2013 at 22:59
  • That is a configuration issue. Go to /admin/config/people/userpoints/transaction, choose one to edit, and manually create a description under the 'Reason' tab. It will show in the view.
    – Triskelion
    Commented Feb 28, 2013 at 23:05
  • please look at this i.imgur.com/ko23dt2.png...as you will see i already have added reason much before..however, in my view no description shows up
    – why
    Commented Feb 28, 2013 at 23:10
  • and when i add the relation: ` (Users) Userpoints Transactions: Entity ID ` so that i can fetch node teaser and image for whose upload the user got the points then i get the error : ** SQLSTATE[42S22]: Column not found: 1054 Unknown column 'users_userpoints_txn__userpoints_txn.nid' in 'on clause' **
    – why
    Commented Feb 28, 2013 at 23:15
  • This is a bug in Userpoints. The column in userpoints_txn is 'entity_id', and views is trying to find a column 'nid' to match the node table. (I just confirmed the problem.) It is a known issue with Userpoints. perhaps this reference will point you in the right direction. BTW, the ony time you use a relationship in another relationship is when you are chaining tables, which you are not doing here. You have the 'entity' using the 'user' relationship. It should not.
    – Triskelion
    Commented Feb 28, 2013 at 23:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.