Beginner needs help!

[Mark Appleby]

I am trying to creeate a new stock database for my company but I am having a simple problem.(I hope that the answer is simple.)

All my stock can have up to three catagry listings, put simply this is Department>Catagory>Subcatagory. My problem is that I can not limit the lists that appear on a pop up list for entering new product lines. This will make entry a very long business I have over 80 subcatagorys in all which is too many from which to make a choice. Ideally I would like to be able to filter my list based on department and only show catagories that are in that department and sub catagories that are in my Department and catagory. This would bring my maximum number of choices down to 7.

There must be a way to do this but I just can not see it.

Mark

[FUBAR]

The database that I'm working had the exact same problem except with my fields being 'Application', 'Classification', and 'Equipment'. So for the example Im about to give just substitute Application with Department, Classification with Category, and Equipment with Sub-Category.

The solution I used involved self relationships and a seperate search layout.

On my "detailed" search layout I have three global fields (gWanted App, gWanted Clas, gWanted Equip)

gWanted App is a list based on the field Application.

gWanted Class is a list based on a self relationship with Application and gWanted App.

The somewhat tricky part is doing the final step for filtering Equipment. What I did was add a new field called AppClas which is a calculation that looks like... Application & " " & Equipment. When the user selects an Application and then a Classification they have to push a button (since I don't have the plug-in that allows scripts to be triggered when a field is exited) that will copy there selection into a field 'gWanted AppClass'

So finally, gWanted Equip is a list based on a self relationship with AppClass and gWanted AppClass.

Hope this helps

[Mark Appleby]

Thanks for getting back to me about this as I have been sprnding a lot of time trying to get to the bottom of this. Unfortunately as I am only getting started with filemaker everything is taking forever. I have tried this but if at all possible I must not have a button as this adds another procedure. I am now trying using your calculation though while using relational value list which I hope is going to cut out the need for a script. Let's see how this goes I will post another message if it works.

[The Bridge]

quote:

---

Originally posted by Mark Appleby:

Ideally I would like to be able to filter my list based on department and only show catagories that are in that department and sub catagories that are in my Department and catagory. This would bring my maximum number of choices down to 7.

---

One option is to create another database called Categories.fp5

Fields:

Category_ID (standard unique record key)

Department (indexed text)

Category (indexed text)

Sub_Category (text)

Department_Category_Key (indexed calculation, result is text) = Department & Category

Global_Access_Key (indexed calculation, result is number) = 1

Enter all of your Subcategories into this database with their corresponding Categories and Departments.

In your original database, add two fields:

Global_Access_Key (indexed calculation, result is number) =1

Department_Category_Key (unstored calculation, result is text) = Department&Category

Create three relationships from your main database to Categories.fp5:

Global_Access_Key=Global_Access_Key (will provide access to every record in Categories.fp5 from any record in your main database.)

Department=Department (will access all records in Categories.fp5 with matching Departments)

Department_Category_Key=Department_Category_Key (will access all records in Categories.fp5 with matching Departments *and* Categories)

Define three valuelists in your main database:

vlDepartments - values from a field in another file (Department in Categories.fp5)

vlCategories - from another file (Categories.fp5), related values only (Department relationship)

vlSubCategories - from another file (Categories.fp5), related values only (Department_Category_Key relationship)

Be sure to pay attention to which of the above suggested fields are stored (indexed) and which are unstored. It will affect your results.

Hope this helps!