Why are reports organized by first digit instead of whole number?
sschreiber2785
Registered User ✭
Is there anyway to have reports organized by whole number instead of the first digit? When we run our "Past Due Balances" report and sort by "unit" the report will go - 06,09,104,122,23,32,38... The report is improper order and should be organized by whole number. Does anyone know who I can contact to correct this error? Also I tried to select "How to?" for the category of question and it wouldn't let me.
0
Answers
-
themage Registered User, Daily Operations Certified, Advanced Operations Certified, Administrator Certified, myHub Certified ✭✭✭✭✭Sitelink is using alpha-numeric sorting, since many of the sites use alpha numeric unit designations (A01, C45, etc).
The only way I can think of right now to sort by whole number is to export it to excel and sort it there.0 -
@sschreiber2785 - A bit of background on this one, this is not a SiteLink-specific issue but rather how alphanumeric string sorting works. Unit names are not numbers/integers but character strings -- even if all of a sites' unit names represent a number.Database logic is that it sorts everything by the first digit, then second, etc. The only way to overcome this is to make all their unit names the same length. For example, 06 should be 006 and 23 should be 023.. Assuming your max unit number length is 3 digits. If you have unit number length of 4 digits, then make them all 4 digits.
Hope that helps!0 -
"Database logic is that it sorts everything by the first digit, then second, etc. The only way to overcome this is to make all their unit names the same length."
Hi Storable, the request by @sschreiber2785 is SO simple to implement. Here, I will do it for you as a courtesy to all the customers. Your friendly neighborhood Database Admin.
The following query will work for everyone no matter what their numbering format is.
With this mix of formats: C45, A01, 2 , 1, 03, 01, 003
The query result would be: 1, 2, 01, 03, 003, A01, C45
-- Finally by string value
<br>SELECT * FROM units ORDER BY CASE WHEN id REGEXP '^[0-9]+$' THEN 1 ELSE 2 END, -- Numbers first, then letters LENGTH(unit_id), -- Then by string length id;
* By the way, I am available if you want to hire me.
0
Categories
- All Categories
- 2 What's New
- SiteLink Community Discussions
- 1.9K SiteLink General Discussions
- 79 SiteLink myHub
- 48 Lead to Lease
- 15 Price Optimization
- 36 SiteLink Certified Professional
- 185 How-To's
- 97 Training Videos
- 7 Other Resources
- 79 SiteLink, SpareFoot & storEDGE merge
- Self-Storage Operations
- 65 Starting Out in Self-Storage
- 3 3rd Party Management
- 223 Regional Discussions
- 188 Miscellaneous Discussions
- 39 Industry Webinars
- SiteLink Marketplace
- 62 Self-Storage Website Design & Listing Services
- 32 Credit Cards & Payment Processing
- 31 Self-Storage Call Centers & Kiosks
- 46 Notifications, SMS & Phone Integrations
- 93 Self-Storage Insurance, Legal & Auctions
- 11 Self-Storage Revenue Management & Analytics
- 24 Gates & Access for Self-Storage Facilities
- 10 Ancillary Services for Self-Storage Facilites
- Self-Storage Times
- 62 News
- 30 Opinion
- 146 Lifestyle