Eliminate obsolete products from drop down list

[carlosnorvik]

Hi everybody:

I have a product drop down list that shows all products for a certain supplier when making a new order.

The list changes according to the supplier of the products.

 

This is easily done by indicating who the supplier is in the products Table and making the appropriate relationship.

 

Now, I want to shorten the list by eliminating obsolete products (I need to keep history, so I can't simply delete the obsoletes from the products table)

The products table has a field which I can click when a product becomes obsolete.

 

My solution (it works, but I don't like it) is adding a field called obsolete in the purchase orders table, which is automatically set to "no" and I added a second relationship for the value list, so that if product is obsolete, it is no loger valid via the relationship.

 

Any ideas of a better way to do it?

 

Thanks

Carlos

[LaRetta]

Hi Carlos,

 

One option:  In your Products table, create a calculation which inserts the ProductID only if the product is active and then use THAT ProductID in a new value list called Active Products.  The calculation should be indexed.

 

An example calculation in Products could be similar to:

 

If ( not IsObsolete ; ProductID )

 

... where IsObsolete is a number field with a 1.  Adjust as needed to fit your Obsolete field.

[carlosnorvik]

Hi LaRetta:

 

Sounds interesting, but if I understand correctly it would entail joining a calculated field, will it hamper performance?

 

Thanks

Carlos

[LaRetta]

The calculation will be on the child side and it will be stored (do not set set it to unstored).  So yes, it has a small hit but no more than any other technique and less than most. 

[comment]

So yes, it has a small hit

 

 I don't see why there would be any hit.

[LaRetta]

I suppose because I believe that everything has a cost, no matter how slight.  We work in nanoseconds and pts and the very minuscule and nothing is everything or absolute except uncertainty.  

 

But the hit would be the initial index, wouldn't it?  Thank you for affirming that it is very negligible.  I mean, nothing is for free, is it?  

Edited ... I should have said that the impact is less than anything else.  LOL.

[comment]

But the hit would be the initial index, wouldn't it?

 

Yes. So it does have a cost, but it has already been paid. And it has been paid by waiting at a time where waiting the extra millisecond has no practical significance.