2

I have two content types: one for invoices, and the other for donations.

Each content type contain two fields, one for "User Name" (which is a user reference type) and the other is the "Amount($)" (an integer type).

I am trying to create a table with the Views module that returns 3 columns: user name, total donation (the sum for all the donation for the same user), and invoice sum (the sum of all the amount paid for the same user).

I have two users (Ahmed and Taha). Ahmed made 3 donations ($50, $30 and $70) and paid 2 invoices ($30, $30); Taha made 1 donation ($60), and paid 3 invoices ($30, $30, $30).

What I am trying to achieve with the Views module is something like the following.

    *----------------*------------------------*-------------------------*
    |   Username     | Total Donations        | Total Subscriptions     |
    *----------------*------------------------*-------------------------*
    |   Ahmed        |     $150               |      $60                |
    *----------------*------------------------*-------------------------*
    |   Taha         |     $60                |       $90               |
    *----------------*------------------------*-------------------------*

or Something like:

    *----------------*------------------------*-------------------------*
    |   Username     | Donations              | Subscriptions           |
    *----------------*------------------------*-------------------------*
    |   Ahmed        |     $50                |      $30                |
    *----------------*------------------------*-------------------------*
    |   Ahmed        |     $30                |      $30                |
    *----------------*------------------------*-------------------------* 
    |   Ahmed        |     $70                |       ---               |
    *----------------*------------------------*-------------------------*
    |   Taha         |     $60                |       $30               |
    *----------------*------------------------*-------------------------* 
    |   Taha         |     ---                |       $30               |
    *----------------*------------------------*-------------------------*
    |   Taha         |     ---                |       $30               |
    *----------------*------------------------*-------------------------*
  • they told me to use relationships that is build with views but i cant figure out yet how. – Mouad Aug 18 '12 at 22:39
  • Hi and welcome to Drupal Answers! In order to get the best experience here, you may want to consider editing and re-wording your question. Currently, you are explaining what you are trying to do, but you never really ask a specific question. Also, it is very helpful to explain the steps you have take thus far, how the result you have is different from what you intended and also which versions you are using of Drupal and/or relevant modules. – sheena_d Aug 18 '12 at 22:43
  • thanks sheena_d for reply, the question how can achieve that table using views for each row (i have 3 column) a "user name", their Sum of paid Invoices and the Sum of their Total donations. i am using the latest stable version of drupal and views and reference. i am sorry for my poor English please if there is anything unclear tell me and i will try to clear it – Mouad Aug 18 '12 at 23:08
2

To add Joins to your views using the Views UI you will need to open the Advanced settings and visit Relationships. (See graphic)

Views Relationships

Assuming the user is the author ($node->uid) of the donation's and subscription's payment types you should be able to use the "Content: Author" field to accomplish this join.

Join on Author

This should allow you to build the second table from your question. To see results displayed in a similar format to your first table you will need to use aggregation. 'Use Aggregation' can be found under the Advanced menu.

Use aggregation

Once use aggregation is enabled you will see a new link below your fields called "Aggregation settings."

Select SUM on the field you want to display the totals for and you should see something similar to your first table.

SQL SUM()

Lastly, while you are getting aquanted with the Views module I recommend turning on the "Show the SQL query" feature in Views settings. This can be found at admin/structure/views/settings

This will show you the SQL views is generating which can be useful while debugging or learning the Views UI.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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