Archives

Resorting M2M List Boxes

Recently I came across the following problem: A company I assist had over 100 different shipping options in Made2Manage. You might question why a company would have that many shipping options, but when you consider there are many different options for a single carrier, they add up. For example here are a few of the options for UPS:

  • UPS Express Critical
  • UPS Next Day Air Early A.M.
  • UPS Next Day Air
  • UPS Next Day Air Saver
  • UPS 2nd Day Air A.M.
  • UPS 2nd Day Air
  • UPS 3 Day Select
  • UPS Ground

The list box is from the Sales Order (SO) Screen. The problem is that when you have over 100 shipping options and they are put in by your sales or customer service people as they are required, they aren’t in any specific order. It becomes an annoying hunt through the list every time you want to enter a new order. It’s interesting to note that the shipping options aren’t in order on a brand new install by default.

Default List Box

Original List

Luckily, M2M has a screen to manage this and you can spawn it by right-clicking on the list box while in edit mode. You then get the following window:

Default Edit Screen

List Box Edit Screen

I highlighted the area where you change the display order. A new order number must be entered for each item you wish to shift in the list. If you have more than a few items, this becomes tedious and wastes a great deal of time. Furthermore, when someone enters a new address after a re-assignment marathon, a new order number must be entered for each item requiring another shift. I should mention however, that if there are several list items with the same Display Order Number, they will be grouped so every item need not be changed.

I was asked to re-assign all of the shipping options alphabetically. Being a member of the genus species homo logicus, I knew I could code a better way to do this. So, I created the following code to automate the process. Before I go any further, let me issue my standard disclaimer.

Important: All information on this site is provided “as is” for informational purposes only.
In particular, all code samples are provided “as is” without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

If you should decide to use any of the code from this site, make sure you test it thoroughly on a test company (or better yet a test server) before you ever use it on your live data.

IF EXISTS (SELECT *
           FROM   SYS.OBJECTS
           WHERE  OBJECT_ID = OBJECT_ID(N'[dbo].[cspopupTEMP]')
                  AND TYPE IN (N'U'))
  DROP TABLE [DBO].[CSPOPUPTEMP]

SELECT *
INTO   M2MDATA12.DBO.CSPOPUPTEMP
FROM   M2MDATA12.DBO.CSPOPUP
       
UPDATE C
SET    C.FNORDER = (SELECT COUNT(* )
                    FROM   [M2MDATA12].[DBO].CSPOPUP
                    WHERE  FCPOPTEXT < C.FCPOPTEXT
                           AND [FCPOPKEY] = 'SHIPVIA')
FROM   [M2MDATA12].[DBO].CSPOPUP C
WHERE  [FCPOPKEY] = 'SHIPVIA'

--drop table m2mdata12.dbo.cspopupTEMP

The first 8 lines are a simple backup for the table before I begin. The next several lines consist of a relatively simple SQL Update statement which derives its value from a nested sub-query. SQL coding isn't like "normal" coding in that when done right, everything is executed in sets. A set is a group of data, and it must be acted upon as 1 unit. 'C' is simply an alias I chose for [M2MDATA12].[DBO].CSPOPUP in the main query. It shortens the query, makes it easier to read, and differentiates the fields in the nested query from the main query. Note my first WHERE statement where I compare FCPOPTEXT (the actual text that shows in the list box) from the nested query to the main query to determine the order number. I am setting the FNORDER equal to the count of items in the list that are alphabetically before the current item in the list. As it goes through my set, it then changes the FNORDER to match the order that item is in alphabetically in the set. The last line of code is commented out as it drops your temporary backup table. After you are confident that the script worked, you can run that line separately.

This code can be adapted to change most of the list boxes on the SO screen. Here are the results of my testing:

Re-sorted List Box

Re-sorted List

Feel free to ask questions, or suggest other issues to tackle with SQL, in the comments.

9 comments to Resorting M2M List Boxes

  • Jason Griffith

    *kicks his computer* Show me pictures!

    *grabs his foot as he remembers he didn’t wear his steel-toes today*

    I’m not sure if this is a problem for us or not. One of the side effects of being the new guy in the company, I suppose. I can definitely see the potential use if we do need it though. But that might just be the organization freak inside me talking.

  • Fred Crawford

    Very nice example of how to copy a file, update data in a file, and resort lists. Keep up the good work!

  • First off thank you for this nice example!

    How would one go about doing a resort just one time? Could you just run this code in the query analyzer?

    Thanks

  • I’m sorry James, I never even realized that I failed to mention that. Yes, you copy and paste the code into the query analyzer (If you’re on SQL 2000) or SQL Server Management Studio for 2005+. However, as I mentioned above, you should test this out on a test database before using it on your production database. I’ve used it several times over the past year without problems though.

  • David,

    Thank you for the quick answer! Sorry for my slow reply. Been working on setting up a test server and I believe Iā€™m ready.

    Sorry to bug you on this but I have a question.

    I should be able to run:

    UPDATE C
    SET C.FNORDER = (SELECT COUNT(* )
    FROM [M2MDATA06].[DBO].CSPOPUP
    WHERE FCPOPTEXT < C.FCPOPTEXT
    AND [FCPOPKEY] = 'SHIPVIA')
    FROM [M2MDATA06].[DBO].CSPOPUP C
    WHERE [FCPOPKEY] = 'SHIPVIA'

    I should not need to add the table CSPOPUPTEMP before running this right?
    Just a little over my head here. Any help would be great!

  • Alex

    James,

    By the looks of it, the CSPOPUPTEMP table is created just as a backup of the data in case something gets messed up. If you are not worried about backing up the data (if it’s a test system or test server) then you should not need to use the first few lines there. Basically, what it’s doing is checking if there is a table called ‘CSPOPUPTEMP’ and if there is, it drops it. It then proceeds to fill it again allowing you a backup in case of problems.

    Hope that helped.

    Thanks,
    Alex

  • I’m sorry James, I completely missed your question when you posted it. I didn’t notice it until Alex replied to it. Technically you are both correct. However, I ALWAYS make a temporary back up of a table when I modify it. In fact, I’m writing a blog article now about a user who made a pretty big mistake when he failed to do so.

  • Alex

    David,

    I understand what you are saying and completely agree. I was just basically telling James that TECHNICALLY he did not need those lines and was not advocating not backing up your data before making a big change. Trust me, I know how important it is to back up your data haha.

    Thanks,
    Alex

  • is there a good way to do aif exists ( secletStatementA )begin secletStatementAend?It seems really clumsy to repeat the same seclet statement. or may be i’m not really supposed to use an if exists in this place o.ocan i haz help?

Leave a Reply to Alex

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>