Fine tune sorting (1 Viewer)

[tmyers]

I have a dilemma I hope someone can offer insight on. I am trying to sort items in my report in a way that on the outside looking in, doesn't make sense. However to those you will use it, it will.

I have a column (TypeName). These are my designations for light fixture. In a typical job, we also have controls (switches, dimmers etc). Those also typically have a type (think something like "DIM" for dimmer, or "OS" for occupancy sensor. Sometimes they are also like $D for a dimmer. They are generally pretty straight forward. Controls are typically at the bottom of our quote. It is that way across the industry to my understanding.

I am sure you can already see where my problem lies. If I sort A-Z on TypeName, the controls get mixed in throughout the report. It would make it hard to read and end up being ugly. If they don't have a type, they all end up at the top of the report (at least in the few test I ran). I have not tried adding the "$" to it, as that won't consistently be used, so I don't know where they would end up in the report in that regard.

So far the only thing I have thought of is to have a separate flag for controls. Have a sub-report on my main report that populates with those the user flags as a control and selects to appear on the quote. That however just seems clunky when I go through the process in my head.

Any chance anyone has a different idea?

 

[theDBguy]

Hi. For a requirement like this, the usual approach is to have a separate numeric field for sorting.

For example, sorting military ranks or corporate titles can't be sorted using their titles or descriptions. So, you designate the sorting order using numbers.

 

[CJ_London]

agree with DBG - create a 'type' table with three fields

TypePK
TypeName
SortOrder

in your table, substitute typePK for your typeName field

 

[tmyers]

agree with DBG - create a 'type' table with three fields

TypePK
TypeName
SortOrder

in your table, substitute typePK for your typeName field

Just to make sure I am following, essentially break the two into "categories". Have my fixtures be type 1 and controls be type 2. That seems simple enough.

The next problem is figuring how to automate which product gets what number. The data is typically bulk imported from a vendors quote (in excel format). I don't really have a way to identify what they are as they are coming in as they are just part of the chunk of data. They are not separated nor are they typically designated anything unique enough (and consistently enough) to come up with a way to catch them as they come in.

It may be unavoidable that I will have to have the user flag them in some manner once they are imported. Just add a IsControl yes/no field.

 

[MajP]

If you have a long list that needs a manual sort you may want a form to allow you to quickly sort a list by moving items up and down and then keeping that sort order. Not sure if that is what you need, but here is a class module that makes this real easy. See demo

 

[CJ_London]

The data is typically bulk imported from a vendors quote

if they are quoting - presumably you have a 'request for quote' type document. Perhaps they can be identified from that?