I am working on designing a database where someone can custom build a computer. I have tables for each type of component. I have built some conditional value lists that only show compatible parts. Now what I am trying to accomplish is how to filter out these parts to only show items that are still available. I don't want it to show items that are compatible but discontinued. I have a field that I have a drop down list that I have selected if an item is "Active", "Unavailable", "Discontinued". I am not sure how to have my my value lists filter based on this condition. 

 

Any help would be greatly appreciated.

In your relationship that the value list is based on, you need to add an additional match field. One side should be the status field you mentioned, the other should be a 'constant' field that contains the value "active". By constant, I mean a calculated text field.

what I am trying to accomplish is how to filter out these parts to only show items that are still available. I don't want it to show items that are compatible but discontinued. I have a field that I have a drop down list that I have selected if an item is "Active", "Unavailable", "Discontinued".

 

Why don't you define a calculation field =

 

Case ( Status = "Active" ; ItemID )

 

and use this as the basis for your value list?

In your relationship that the value list is based on, you need to add an additional match field. One side should be the status field you mentioned, the other should be a 'constant' field that contains the value "active". By constant, I mean a calculated text field.

 

Can you elaborate on the constant field a little more? I think that knowledge might come in handy in the future as well.

Let's say you have two tables: Customers and Projects, and the project table has a status field that contains one of: quote, active, or complete. You have a layout based on Customers and want to have a portal on that layout display active projects for the customer...

 

If you create a 'constant' field in the Customer table that always contains the value "active", you can include that in a new relationship to the Projects table, which will cause that relationship to only return active projects. So, your relationship would look like:

    Customers::id -------[=]--------- Projects::id_Customers

    Customers::constant_active --[=]-- Projects::status

 

I have had issues using global storage on 'constant' fields, so lately I've been using indexed fields. In the example above the calculation would be: "active", and the calculation result type would be Text.

 

P.S. I hate to throw miss-information out there, so what I mean by "issues" with global 'constant' fields is this:  If the field is calculated, in a hosted environment, and the field is not viewed on a layout before accessing a relationship that is utilizing the field, the field's calculation was not triggered, so the field was empty and the relationship returned no records. So, a global field is fully capable of working as a 'constant' field, I just didn't want have to bother setting it's value when the file opened.

I have had issues using global storage on 'constant' fields, so lately I've been using indexed fields.

 

That's an awful waste of resources, isn't it? Why don't you simply make the field unstored? That is if you think you need it - along with a whole new relationship and a TO - in the first place.

That's an awful waste of resources, isn't it? Why don't you simply make the field unstored? That is if you think you need it - along with a whole new relationship and a TO - in the first place.

 

That's what I started doing after I had an issue with the global field, but then I think I had an issue with that too, although I can't remember what it was right now 

I rarely have constant fields that contain text. The most common one I use contains the number 1; which I use in relationships to boolean fields.

 

What do you use for 'constant' fields?

What do you use for 'constant' fields?

 

In the rare case where it cannot be avoided, an unstored calculation field. The only "issue" I can think of is that the relationship becomes uni-directional. But the other direction is not required - and besides, you would get the same thing with a global.