Basic Reporting Part V FAQ: How to Sort a Report with Both Individuals and Organizations Alphabetically

Basic Reporting Part V FAQ: How to Sort a Report with Both Individuals and Organizations Alphabetically

1. This FAQ will cover how to sort a report alphabetically that includes both Individuals and Organizations. For example:

   

2. For a Report That Just Sorts by Name WITHOUT the Letter (A, B, C, etc.) Headers/Separators (for example, for a mailing list report):

   

3. First, you must add an “if” statement to your Sorts that tells the report to sort by Individual Last Name if the contact record type is Individual Contact and to sort by Organization name if the contact record type is an Organization.
4. To do this, make sure the “Contacts” table is included on your Tables tab (click the "Manage Table" button and select the table from the Contact category).

   

   

5. Next, go to the Sorts tab (click the sort option under Filters and Sort toolbar).

   

6. Add the field “Individual or Organization” from the Contact table.

   

7. Click the “fx” next to the field name in the Sort Order column

   

8. Add the following formula (you can type it in, select the “if” function from the function list, or copy and paste from below):

   
   If({Contact.Individual or Organization} = "I", {Contact.Last Name}, {Contact.Organization Name})

   

9. Click OK
10. Next, you will want to have one column that displays “Name” and which displays the individual's First Name and Last Name if the contact record type is Individual and the Organization Name if the contact record type is organization.
11. On the report Layout, click in the cell where you want the “name” field to appear.

    

12. Copy and paste the following formula (or type it in/use the fx/formula editor to add it):

    =If({Contact.Individual or Organization} = "I", {Contact.Contact Name}, {Contact.Organization Name})

13. Now when you run the report it will sort Organizations and Individuals together and display the “name” in one column.

    

14. If you want each name to appear just once, then you can add a group header on the sort formula created in Step #7.

    

15. Then we added our formula in Step #10 for the display name to the group header area.

    

    

16. For a Report That Sorts by Name WITH Letter (A, B, C, etc.) Headers/Separators:

    

17. You will follow all of the above steps, but we will add an additional Sort option that pulls just the first letter of the Contact Last Name or Organization Name and then we will add an additional Group Header to the report for that first letter sort.
18. First, you must add an “if” statement to your Sorts that tells the report to sort by the first letter of Individual Last Name if the contact record type is Individual Contact and to sort by the first letter of Organization Name if the contact record type is an Organization.
19. To do this, make sure the “Contacts” category/table is included on your Categories tab (click the gear icon and select Categories).

    

    

20. Next, go to the Sorts tab (click the gear icon again and select “Sorts).
21. Add the field “Individual or Organization” from the Contact table.

    

22. Click the “fx” next to the field name in the Sort Order column

    

23. Add the following formula (you can type it in, select the “if” function from the function list, or copy and paste from below):

    If({Contact.Individual or Organization} = "I", left({Contact.Last Name}, 1), left({Contact.Organization Name}, 1))

    

24. Next, you must add a second Sort Order to your Sorts tab that contains an “if” statement that tells the report to sort by the Individual’s Last Name if the contact record type is Individual Contact and to sort by Organization name if the contact record type is an Organization.
25. Add the following formula (you can type it in, select the “if” function from the function list, or copy and paste from below):

    If({Contact.Individual or Organization} = "I", {Contact.Last Name}, {Contact.Organization Name})

    

26. Your Sorts should now look like this:

    

27. Click OK
28. Next, you will need to add a Group Header to your report to display the Alphabet/Letter Character Section Dividers/Headers.
29. To do this, on your report Layout, right click anywhere in the left column/Section area of the report layout and select “Add Section” or directly click Add Section and then “Group Header”

    OR

    

30. Select your first Sort order (the one containing the “Left” command).

    

31. Next, in the report Layout, add the following formula in the Group Header row, where you want the Letter Dividers to appear.

    =If({Contact.Individual or Organization} = "I", left({Contact.Last Name}, 1), left({Contact.Organization Name}, 1))

    

32. You can also add a second group header on the contact name (Individual or Organization) if you want the name to only appear once. Repeat the above step to add a second Group Header and select the second formula.

    

33. Finally, on the final report output/display, you will want to have one column that displays “Name” and which displays the individual’s First and Last Name if the contact record type is Individual and the Organization Name if the contact record type is organization.
34. To do this, on the report Layout, click in the cell where you want the “name” field to appear (in the second group header if you have added it, otherwise, in the details area). Copy and paste the following formula (or type it in/use the fx/formula editor to add it):

    =If({Contact.Individual or Organization} = "I", {Contact.Contact Name}, {Contact.Organization Name})

    

35. Now when you run the report it will sort Organizations and Individuals together and display the “name” in one column and the sections will have Alphabet Letter Headers/Dividers.