Exporting Ex Libris Tickets

Table of Contents

Background

When we encounter an Alma or Primo VE issue, we submit a ticket with Ex Libris via their Salesforce support center. Sometimes it’s hard to see a quick summary of the issues in the current user interface. In addition, OT is interested in SILS Cohort members easily being able to see the tickets that SILS Ops Center has submitted on behalf of the consortia, even of the member doesn’t have a Salesforce account.

For the past several months, SILS Op Center has been compiling a monthly list of open tickets and sharing them in the SILS Google Drive. This is particularly useful for keeping track of tickets that have been open for an excessive amount of time and need to be escalated in the monthly support meeting.

Below is information on how we created this spreadsheet.

Updating the Spreadsheet

Once the initial setup has been done (see below), the process of updating on a regular basis is fairly quick.

1. Open up your Google Sheets file for keeping track of tickets.

2. Create two new sheets: one for the final data and the other to use as scratch paper.

3. Login to Salesforce and click the drop down under cases to select the first view (Part 1). Make sure it’s sorting by Case Number ascending (the arrow should be pointing up).

4. Starting at the first row, left click with your mouse and drag to the bottom right corner of the table. All the displayed table data should be selected.

5. Copy the data and paste it into the Google Sheets tab you set aside for scratch paper. If you have more cases than display on one page, go to the next page in Salesforce and repeat.

6. In Google Sheets, delete the extraneous columns and make any other necessary modifications so the columns line up correctly.

7. Copy this data to the tab set aside for final data.

8. Add a row at the top with the appropriate labels for each column.

9. Add another row at the top. In A1 enter “Last Updated” and in B1 enter the date.

10. Erase the data in the scratch paper tab.

11. Go back to Salesforce and click the drop down under cases to select the second view (Part 2) you made. Make sure it’s sorting by Case Number ascending (the arrow should be pointing up).

12. Copy the data into the scratch paper tab.

13. Delete the extraneous columns and make any other necessary modifications so the columns line up correctly.

14. Copy all columns, excluding the case number, to your final data tab. Add appropriate column labels.

15. Rename your sheet to something useful (like the month) and delete the scratch paper sheet.

16. At the far right on your sheet, add a label for Opened (Days).

17. Highlight the column, click on the Format menu, choose Number, and click on Duration.

18. In the first data row, enter the formula =($B$1-N4)/24. You may need to adjust this if you have added a different number/combination of columns. $B$1 should contain the current date. N4 should be the column that the ticket was opened.

19. Copy the formula down to the end of the row.

20. At the far right on your sheet, add a label for Last Mod (Days).

21. Highlight the column, click on the Format menu, choose Number, and click on Duration.

22. In the first data row, enter the formula =($B$1-M4)/24. You may need to adjust this if you have added a different number/combination of columns. $B$1 should contain the current date. M4 should be the column that the ticket was last modified.

23. Copy the formula down to the end of the row.

Initial Setup

Creating Salesforce Views

We have not found an easy way to export information from Salesforce to a CSV or Excel format. In addition, Salesforce only allows users to view 15 fields at a time. Creating views, and then copying them is one way to get around this limitation.

1. Go to the Support Center, click the Login link, and login with your regular credentials.

2. Click on the Manage Cases link.

3. Click on the Gear icon for the List View Controls and click New.

4. Enter a view name and end with “Part 1”. For example, the SILS Op Center view is called “Monthly SILS Op Open Cases Part 1”.

5. Click the Save button.

6. Click the Gear Icon for the List View Control menu again and click on Edit List Filters.

7. By default it should show Filter by Owner set to My cases. Click on that filter and change to All cases and then click Done.

8. Click Add Filter and under Field choose My Cases. The Operator should be equals and the Value should be True. Click Done.

9. Click Add Filter again and under Field choose Closed. The Operator should be equals and the Value should be False. Click Done.

10. Click Save.

11. Click the Gear Icon for the List View Control menu again and click on Select Fields to Display. Under the Visible Fields Column, remove all the defaults, except Case Number, by highlighting the field and then clicking the left arrow.

12. Add the following fields by clicking the fields under Available Fields and clicking the right arrow.

    1. Case Number (should already be there)

    2. Ex Libris Case Number (this may no longer be necessary when all old cases have been closed)

    3. Parent Case Number

    4. Product/Service Name

    5. Category

    6. Sub-Category

    7. Case Subject

    8. Case Type

    9. Priority

    10. Case Status

    11. Version for ‘Pending Release’

    12. Last Modified Date

    13. Date/Time Opened

    14. Owner Name

13. Click Save.

14. Click the Gear Icon for the List View Control menu again and click on Clone.

15. Name the same as above except change Part 1 to Part 2. Then click Save.

16. Keep the existing filters but click the gear icon to Select Fields to Display. Under the Visible Fields Column, remove all the defaults, except Case Number, by highlighting the field and then clicking the left arrow.

17. Add the following fields by clicking the fields under Available Fields and clicking the right arrow.

    1. Case Number (should already be there)

    2. Department

    3. Escalated on

    4. Escalation

    5. Escalation Comments

    6. Last Public Comment

    7. Status

    8. Sub Status

    9. Email Reference ID (this is useful because you can use it to make direct links to the ticket)

    10. Any other fields you find useful.

18. Click Save.

Creating the Initial Google Spreadsheet

1. Open up a new Google Sheets and give it an appropriate name. Note: Depending on the type of tickets you create, you may need to make sure it is saved in a location where no PII would be compromised. (For example, if you need to put patron names and birth dates in your tickets, only authorized people should be able to see it.)

2. Go back to Salesforce and click the drop down under cases to select the first view (Part 1) you made above. Make sure it’s sorting by Case Number ascending (the arrow should be pointing up).

3. Starting at the first row, left click with your mouse and drag to the bottom right corner of the table. All the displayed table data should be selected.

4. Copy the data and paste it into Google Sheets. If you have more cases than display on one page, go to the next page in Salesforce and repeat.

5. In Google Sheets, delete the extraneous columns and make any other necessary modifications so the columns line up correctly.

6. Add a row at the top with the appropriate labels for each column.

7. Add another row at the top. In A1 enter “Last Updated” and in B1 enter the date.

8. Create a new tab.

9. Go back to Salesforce and click the drop down under cases to select the second view (Part 2) you made. Make sure it’s sorting by Case Number ascending (the arrow should be pointing up).

10. Copy the data into the new Google Sheet tab.

11. Delete the extraneous columns and make any other necessary modifications so the columns line up correctly.

12. Copy all columns, excluding the case number, to your first sheet. Add appropriate column labels.

13. Rename your first sheet to something useful (like the month) and delete the second sheet.

14. At the far right on your sheet, add a label for Opened (Days).

15. Highlight the column, click on the Format menu, choose Number, and click on Duration.

16. In the first data row, enter the formula =($B$1-N4)/24. You may need to adjust this if you have added a different number/combination of columns. $B$1 should contain the current date. N4 should be the column that the ticket was opened.

17. Copy the formula down to the end of the row.

18. At the far right on your sheet, add a label for Last Mod (Days).

19. Highlight the column, click on the Format menu, choose Number, and click on Duration.

20. In the first data row, enter the formula =($B$1-M4)/24. You may need to adjust this if you have added a different number/combination of columns. $B$1 should contain the current date. M4 should be the column that the ticket was last modified.

21. Copy the formula down to the end of the row.