Report - Sorting

Carol-Anne Cerbone

Hello Community

I'm about to pull my hair out with Reports and sorting. I have 24 source sheets, 10 columns selected, no filters or grouping but I want to order my sheets by Order column. The Order column properties are a drop down list (I did try just a text property but that didn't work either). Below are the drop down values. When I initially created the report it sorted perfectly but now it has a mind of it's own. I have no idea why and wondered if anyone is experiencing this. If you did and figured out what it was to correct the issue, please let me know. I have included a screenshot of the report showing the current order that is taking place which includes the Sort By showing Order is selected by Ascending. I have confirmed that each number has a leading zero and not the letter O.

01 - One

02 - Two

03 - Three

04 - Four

05 - Five

06 - Six

07 - Seven

08 - Eight

09 - Nine

10 - Ten

11 - Eleven

12 - Twelve

13 - Thirteen

14 - Fourteen

15 - Fifteen

16 - Sixteen

17 - Seventeen

18 - Eighteen

19 - Nineteen

20 - Twenty

21 - Twenty One

22 - Twenty Two

James Keuning

What if you take note of the sheets that are in these mixed up records, like just two of them, and you create a new report with just the one field and the two sheets? Does that break also? Start adding columns. Not broken? Add sheets. Still not broken? Add more.

But note where it breaks and analyze the problem at its most simple unsuccessful stage.

Debbie Sawyer

@Carol-Anne Cerbone

Oh My! How frustrating!!

Have you copied and pasted the values of the dropdown lists across all 24 source sheets?

Maybe one or two sheets have spurious entries which is causing an underlying issue?

I'd love to offer a quick zoom to delve deeper into a couple of the sheets to see if we can find the reason for the strange behaviour.

Feel free to reach out.

Debbie Sawyer - Chief Smartsheet Solutions Officer (CSSO)

debbie.sawyer@smarterbusinessprocesses.com

 

Carol-Anne Cerbone

@James Keuning - Just created a brand new report and only added in 3 sheets, only 3 columns, tried to sort it based on the order and it didn't work😥

@Debbie Sawyer - I did create 1 list then copied / pasted into the other sheets.

Debbie Sawyer

There has to be an explanation!! ha ha

Does it sort correctly with just 1 sheet in the source?

Debbie Sawyer

I would say, if you can find 1 sheet where the order sorts correctly then go into the Order properties of that sheet and copy the dropdown list values.

Then go into each of the other source sheets, edit the dropdown list values and tick the restrict to list option, then paste the values in. If when you click on Save you get a message saying you have entries in your data that do not match your new list, you know that that is the sheet to work on to correct the entries. If you get no errors then you know the order should be ok.

(need to log out now, but will check back Monday to see if the issue has been resolved - good luck!)

Carol-Anne Cerbone

@Debbie Sawyer - Thanks for the suggestion. A piece of information I forgot to include was this is a Sheet Summary Report. When you select the Property type to be a drop down, there is no tick to restrict to a list option.

Debbie Sawyer

Oh so each entry in the report is a row from a different sheet?

These are summary fields?

Carol-Anne Cerbone

@Debbie Sawyer - that is correct

Debbie Sawyer

Literally need to run out the door! but since you mentioned the sheet summary I have recreated at my end and all is seemingly working fine :(

I can't recreate the issue...

If you have a mix of data type in the summary field (even with the same name) they will split across multiple columns. So it isn't that.

If you copied and pasted the list to all the sheets, then there is no reason at all why the sort is acting strangely.

Is this summary field just to put the sheets in order?

Can you add 01 to 24 to the start of the sheet name instead and put the list in sheet name order? Just a quick suggestion before running out the door! Or use A-V instead of a text version of a number?