Method Community

I've got a grid of Sales Orders, that I'd like to order by descending value of their RefNumber field (aka Sales Order #). 

I'd assume that when I order these SOs by descending value, they would organize themselves based on the numeric value of their RefNumber. So the order should be something like 100002, 100001, 100000, 9999, 9998, 9997, etc. (My old QB Sales Orders are 4 digits, while the new Method SOs are set as 6 digits to avoid confusion). 

But this is not the case: my 6 digit numbers are actually ranked near the bottom, just above 1000 and below 1001. I'm guessing it is ordering itself alphabetically (or alphanumerically), because the RefNumber field type is 'text', not 'integer'.

I know that my Sales Order RefNumber's will all be pure integers, but I'm uncertain whether I should just delete the current RefNumber field (if QB would even let me) and replace it with an Integer field, because I'll lose all the previous RefNumbers. (I don't have any real Method SO's, so technically I could just resynch all my QB SO info back to Method after deleting the field.. is this a viable option?) 

What is the best way to change the data type from text to integer, and preserve the old data? Or, for the more direct solution, how can I get the grid ordering mechanism to order the field like they were numbers, and not text?

@smohyee - It needs to stay text, simply because it is syncing with a QuickBooks field which is text.  What if a QuickBooks user put a non-numeric value into their Sales Order field?  When it syncs with Method it would create a mess.

Instead, can you maybe just sort your grid by RecordID Desc, which is the order they are entered into Method?

Paul

OK, I agree that seems like a much easier solution!

Is it possible to do a type conversion? The RefNumber is of type 'text'. One of the Character functions is isNumeric which returns TRUE or FALSE. Is there also a function that would allow you to turn it into an integer.

Hi Johnny,

There is no function to turn it into an integer.  It would also still need to stay text in order to sync with the Quickbooks field.

- Adam

Johnny,

If you select the January version of Method (select "Switch to the January version" at the bottom of the login screen) you can actually use a SQL override to sort the RefNumber of the sales orders in the grid.  You can cast the field as integers.  Method will then sort accordingly.

A note about using this function is that any letters or spaces conatained in the grid will show as 0's and could affect the order.

- Adam

Thank you for the reply. I'll have to give this a shot. In the meantime, I've looped through the Estimate table and pulled out the maximum TxnNumber. I then increment this and stick it in the Estimate # object which links to the RefNumber. Method seems to have no problem with me sticking an integer into a text field, I just can't do it the other way around. 

Since my goal is to have an auto-increment of the Estimate #, this may actually be a better method. I can trust the TxnNumber to be fairly stable and add math to the maximum found in the table to get me to the estimate number that I want. Using RefNumber may actually be less stable b/c employees can manipulate this number to add characters as you said. This leaves open the possibility that someone will enter an outrageous number that becomes the new maximum RefNumber and screws up the serialization that I'm going for. 

Thanks for the help with this.