Filtering value lists

[DPS]

I use a value list taken from a table called "Job numbers" but it is getting extremely long. I would like the value list to include only "Active jobs" so I have created a field to label the job as "active" or "inactive" but how do I make the value list only display the active job numbers?

[Brian C]

You can do this using the following method:

You can do one of 2 things here:

1) If you want to only see active codes and never see inactive codes, create an unstored calculation

"c_CodeStatus with your "active" value in it.

OR

2) Create a global field "g_CodeStatus" in the table where you are doing the selecting.

Create a new value list to display the active/inactive code selections from your Job Numbers table.

Value lists automatically filter to show only unique entries, so no worries of shown duplicates.

Next for either of the 2 options above create a relationship:

"Code Status From Job Numbers" g_CodeStatus OR c_CodeStatus ---> Job Numbers::CodeStatus

Now just change your existing value list for showing the Job Numbers to show only entries based on a relationship. Select the "Code Status From Job Numbers" relationship from the popup list. And now just reselect what you had displayed in your value list.

End result: You should now have a value list that will always see only active codes, or you will have a value list that will show nothing until you select either active or inactive codes, depending on what you select in the g_CodeStatus global field. If you go this route, upon showing the window/layout, you may want to have a script auto populate the global field with a default choice.

Hope this helps!

[-Queue-]

Another option:

Create a calculation field in Job Numbers equal to Case( Status = "active"; JobNumber ). Then create your value list based on this field.

[iNik]

I'm having trouble implementing this trick.

I have a table called "Tasks". Tasks have a numeric field called "ActiveState" and a text field called "Context." ActiveState is either 0, 1 or 2 (calculated based on checkboxes and the due date). ListContexts is a value list based upon the field Tasks::Context.

Simple enough.

What I want to do is set things up so that that ListContexts value list only contains the contexts of tasks with an ActiveState of 1 or 2. I attempted to create this relationship by creating a new field called "ActiveCutOff" (with a global or calculated value of "1") and relating that back to ActiveState in a second instance of the Tasks table (I'm in FM7), like so:

Tasks::ActiveCutOff =< Tasks2::ActiveState

With ActiveCutOff either as an unstored calculation or as a global field in Tasks. In both cases, the value list comes up empty due to an invalid relationship.

What am I missing here?

[-Queue-]

Is ActiveState a stored calculation? If not, then it cannot be used as the child key. If it is, please post your file for inspection.

[iNik]

Nope, it's an unstored calculation, because it's derived by a cross-relationship calculation.

How do I do this otherwise? I tried a suggestion from another thread of setting up a calc field equal to Case (Activestate > 1; ItemContext), but I can't use it for a value list, for the same reason.

[-Queue-]

Perhaps you can make it an auto-enter calculation with 'do not replace existing value' deselected. When the record is modified, the field will update. It will not update, however, when related data is modified. You will need to either use a script to set a normal field with the related data when it changes or perhaps a plug-in such as EventScript to trigger the script when the field changes.