I have a BPM custom code widget that requires a foreach loop with an order by statement like this.
foreach (var nextcert in (from Cert_Row in Db.UD06 where Cert_Row.Company == Session.CompanyID && Cert_Row.Key2 == rowVDR.VendorID select Cert_Row).OrderBy(x=>x.ShortChar01).OrderByDescending(t=>t.Key5))
That works except that Key5 is a string values that stores an integer and so the sort does not sort numerically as needed. Poking around on the internet, I found and tried this…
foreach (var nextcert in (from Cert_Row in Db.UD06 where Cert_Row.Company == Session.CompanyID && Cert_Row.Key2 == rowVDR.VendorID select Cert_Row).OrderBy(x=>x.ShortChar01).OrderByDescending(t=>t.Key5.PadLeft(6,'0')))
(see the PadLeft added to the end of the command)
Though the syntax checks passes the syntax checker in the custom code widget, the BPM throws an error when this version is used.
Thanks to both @josecgomez and @timshuwy for their input. Though I could not make Tim’s suggestion work, both suggestions inspired me to look again. I found that this works to solve my issue:
foreach (var nextcert in (from Cert_Row in Db.UD06 where Cert_Row.Company == Session.CompanyID && Cert_Row.Key2 == rowVDR.VendorID select Cert_Row).OrderBy(x=>x.ShortChar01).ThenByDescending(t=>t.Key5.Length).ThenByDescending(t=>t.Key5))
Sorting by the length of Key5 then by Key5 is a neat little trick to get around the issue without padding. I like Tim’s idea better i.e. finding a way to convert the string to an int on the fly within the sort function, I could not figure out a syntax that would work.
What I have works for now, always looking to make it better.