Excluding certain records from Value List?

[mfl]

Environment MAC 10.6 with FMPA 12.0v2

I have a situation that works but needs to be refined because its not doing what the user requires.

It starts out at style development where a user creates measurement charts for various stages of garment development.

The layout(Enter New Chart Name) to create a new chart has 2 drop downs where the user selects a default measurement chart depending on size range and gender.

The CUSTOMER drop down should display customer specific charts and the ALL CHARTS needs to display all charts minus ones that are designated RETIRED in the default chart.

The default measurement chart table is actually a TO from another DB.

Both MeasurementChart and MeasurementChart_Defaults_List_Gender_Customer(TO) exist in the STYLES_MAIN DB.

The ALL Charts is tied to a value list which is set to include all values from the default list and this is where the problem is.

Obviously I can't make the value list only select non retired records.

My other thought was to create a small layout with a portal filtering out any retired records.

Is there any other way that I can display all records except retired ones for the user to select.

I have included screen shots of the overall relationship between STYLE_MAIN, MeasurementChart, MeasurementChart Default.....

The Value list setup and the Enter New Chart name layout.

Hope someone can decipher what I trying to fix here.

Thanks

[Matt Malyschko]

Do you mean 'related' where you mentioned 'retired'?

Can't you just reverse the relationship. Ie, make = be !=

Would that the satisfy the opposite fields?

[mfl]

Matty,

Sorry should have clarified a bit more.

There is a field in the Default table called MCDL_Usage.

Normally its set to Current when the chart is created but the user can set to Retired.

Now when a user creates a new measurement chart it puts "Retired" in a field called MC_Word_Retired in the MeasurementChart table right away.

In the relationship between MeasurementChart and the MeasurementChart_Defaults_list..... it "sorts" out where MC_WORD_RETIRED != MCDL_USAGE.

Seems that this relationship doesn't play a roll in the drop down value list which is set to include all values.

[Matt Malyschko]

which is set to include all values.

you only want to display related values though don't you?

[mfl]

No they want to see all the records in the default table except for the ones that are designated "Retired".

Right now the drop down value list is bringing in everything including "Retired".

[Donald Clark]

Check out the Magic Value List example at FileMakerhacks.com. Using ExecuteSQL, you can easily make a dropdown list without a relationship and call exactly what you want. For example (insert your field names below):

MVL_Dropdown ( ExecuteSQL ( "SELECT ChartName FROM All Charts WHERE NOT Status = ?" ; "" ; "";"Retired") )

The example file does not give a great explanation, but with a bit of work, you can set it up for any file. Essentially, you use a combination of custom functions, SQL, a couple of scripts, script triggers, and a global field to generate drop down lists, checkboxes, and radio buttons, as well as conditional lists based upon input from other fields.

Take the time to learn this technique and you won't regret it.

[mfl]

Well you were correct about the explanation part.

I get the SQL part, it's when I get to the Filemaker part thats where I get lost.

What gets setup where etc.

Unfortunately time being what it is I'm not going to be able to work through this.

Thanks for the suggestion will have to take some time to work on it.