Conditional Value List with 2 Conditions

[oakbridge]

I've got a conditional value list that is currently working. It is set up through a relationship to show only the products that are part of a specific category.

Basic schema is this:

product_id

description

category

However we are now getting to the point where there are many old products that no longer are valid. I don't want them to be deleted from the database, as they may be needed for reporting purposes so I have added a field to the product database to mark them as inactive. The revised schema is like this:

product_id

description

category

inactive

My question is this, is there a way of creating a value list that only pulls items that are from a specific category AND are not inactive?

Please let me know.

Thanks,

[LaRetta]

Hi Steve,

Some would say to include the Inactive flag in the join but I've found there are better ways. Take that inactive field (in Products) and rename it Active. Since new product records created will be Active, set its Auto-Enter by calculation with: ProductID.

Use this Active field as the basis in your display relationships (and value lists) instead. In this way, your original ProductID will still relate to all LineItems generated over your history but the new Active field will control all display of current products (as a separate relationship). Toggle script, which could be button or attached directly to the Active field itself (with Allow Entry turned off in Behavior) would be:

Set Field [ Active ; If ( not Active ; ProductID ; "" ) ]

You can't use Specify Button for this; it must be a script if you display the field because Specify Button won't work if Allow Entry is disallowed. Make this Active field the same data-type as your ProductID. I am aware that the calculation APPEARS reversed ... but it isn't. Text is also an integer and the calculation will simply remove or add the ProductID each time clicked (boolean toggle). So the field name is correct - if it holds the ProductID, it's active. Switch your conditional VL to this field (and Category) instead of ProductID.

UPDATE: For those with 7/8 - if the ProductID is number, you can set the number format (right-click on field) to boolean and 'Show non-zero as' with word Active. Leave the zeros blank.

LaRetta :wink2:

Edited March 15, 2006 by Guest
Added afterthought ...

[oakbridge]

Thank you, I'm trying to work through the response you provided. I just wanted to confirm that all this is available through FM6.

I want to compare both methods, but I have a question about including the inactive field in the join. How do you join against two fields in FM6?

Please let me know.

Thanks,

[LaRetta]

To join against two fields in vs. 6, you must connect the fields by concatenating them. It would look thus:

ProductID & " " & Inactive

I need to correct something as well ...

In versions prior to 7, boolean would also return true with Y, Yes, T and True. so your calculation might fail. Instead, you would want to explicitly test with:

If ( IsEmpty(Active) ; ProductID ; "" )

Also, Set Field[] WILL work with specify button if Allow Entry to field is turned off ... it's Go To Field[] which will fail in that instance. Not sure where my mind was on that statement -

L :wink2:

[oakbridge]

I'm going to use the 'inactive' route, as they had already begun to mark some products as inactive. If this had been a 'design from scratch', I think your suggestions would have been better to implement.

In the Product db, I've created a calculation field with this logic:

If(inactive = 1, inactive, 0) & "|" & Product Type

In my Sales Quote system, where we are creating the dynamic popups, I've added a calculation field called active_plus_producttype with this logic:

0 & "|" & Product Type

and I've established a relationship between sales quotes::active_plus_producttype and Product::inactive_plus_producttype.

It seems to be working, hopefully I haven't got any gotchas that will pop up later but because this is used only for creating the dynamic popups, I figure I'm pretty safe.

Thanks for your help with this.