filtered value list by field

[madman411]

Hi all

 

I'm a little stumped here and after trying numerous methods I figured I would ask for help.

 

I have an Asset table which contains records imported from an external source. There are three key fields in this table: Product ID (SKU), Asset ID, and Department.

 

I have a second table called Product IDs. This table includes: Product ID (SKU) and Product Description. The two tables are joined by the SKU.

 

Each asset department has a separate layout on which their respective records can be modified. Each layout has a search feature that will constrain the found results depending on their department.

 

For example, one of my departments is called Lenses, under which all my lens inventory falls. I want the SKU search feature on the Lenses layout to have a drop-down list option that would display only the SKU's that fall under the Lens department. Is there a way to achieve this without needing to separate all my records in to separate tables? Would some sort of self-join relationship be required?

[bcooney]

Have a look at this: http://www.nightwing.com.au/FileMaker/demosX/demoX07.html

[eos]

Is this a matter of security or convenience? And I'm not clear about your departments: are these a ) groups of staff, or b ) "product categories"? (“Lens department”?)

 

Either way, I think what you're missing is exactly that: a Departments table.

[LaRetta]

I want the SKU search feature on the Lenses layout to have a drop-down list option that would display only the SKU's that fall under the Lens department. Is there a way to achieve this without needing to separate all my records in to separate tables? Would some sort of self-join relationship be required?

 

A Departments table wouldn't be necessary unless you wish to track specific additional details about a Department.  What you wish, I believe, is called a 'conditional value list' and here are some links by Comment:

 

http://fmforums.com/forum/topic/55459-automatic-zipcode-data-entry-help/#entry261760

 

http://fmforums.com/forum/topic/57376-conditional-value-list/#entry271214

 

In the second link's demo file, switch Tasks for your Departments and it should make sense.  You can also search further here for "conditional value list" for other examples.  

[madman411]

A Departments table wouldn't be necessary unless you wish to track specific additional details about a Department. What you wish, I believe, is called a 'conditional value list' and here are some links by Comment:

http://fmforums.com/forum/topic/55459-automatic-zipcode-data-entry-help/#entry261760

http://fmforums.com/forum/topic/57376-conditional-value-list/#entry271214

In the second link's demo file, switch Tasks for your Departments and it should make sense. You can also search further here for "conditional value list" for other examples.

Thank you LaRetta. I will review the demo file once I get back to my computer.

[madman411]

These examples deal with conditional value lists in browse mode. How would one define a set of values in find mode? I think this is what is causing my value list to display nothing. I need to tell filemaker which department i'm intending to relate to while in search mode.

[comment]

These examples deal with conditional value lists in browse mode. How would one define a set of values in find mode?

 

When you enter Find mode, only global fields retain their values - all other fields are "empty", ready for input of search criteria. Therefore a conditional value list can only work in Find mode if the underlying relationship is using a global field to specify the records to include.

In your example, you would need to start by populating a global field with the selected department (or rather DepartmentID). If I understand your description*, you would do this at login. Once this field is populated, the value list will work in Find mode, too (see the attached sketch). Note that Filemaker will not let you populate the global field manually, once you enter Find mode.

 

CondVLinFind.fp7.zip

 

 

 

Note also that if the current user is limited by his/hers privilege set to view only certain products, the other products will be automatically removed from any value list. In such case you do not need to build a conditional value list - a value list of all products will provide the same functionality.

---

(*) Actually, I do not understand your description at all: why do you have two almost identical tables, with what appears as a one-to-one relationship between them? And why would you need a separate layout for each department?

 

[madman411]

Thank you Comment. 

 

 

(*) Actually, I do not understand your description at all: why do you have two almost identical tables, with what appears as a one-to-one relationship between them? And why would you need a separate layout for each department?
 

 

If you're referring to my Asset and SKU tables, they are separated because the asset table contains our general inventory, whereas the sku table contains each product type. For example, SKU 1111 may be for "Canon Type A Camera", and asset abc123 is "Canon Camera Type A Camera 1", abc124 is "Canon Type A Camera 2", etc. The SKU table is primarily used for another feature I have in my system that allows the user to generate packing lists using the SKUs rather than the actual assets.

 

Each department has separate layouts because they're responsible for different products, and as a result, different reporting features and or scripting are required to track those items like we do. I'm sure there's a better approach, but at the time I designed the database the way that made sense to me.

[comment]

the asset table contains our general inventory, whereas the sku table contains each product type. For example, SKU 1111 may be for "Canon Type A Camera", and asset abc123 is "Canon Camera Type A Camera 1", abc124 is "Canon Type A Camera 2", etc.

 

Ok, that does make sense now - except why is the Department field in the (child) inventory table, rather than in the (parent) product types table? Is it possible for "Canon Type A Camera 1" to be in one department while "Canon Type A Camera 2" is in another?

[madman411]

Ok, that does make sense now - except why is the Department field in the (child) inventory table, rather than in the (parent) product types table? Is it possible for "Canon Type A Camera 1" to be in one department while "Canon Type A Camera 2" is in another?

 

No, each product ID is exclusive to their respective departments. That was the way the data was originally supplied to me in an Excel table when the tables and fields were first being defined. It won't take much to get the department data transferred to the product table. I'm assuming the example file you supplied indicates the product table should be the one to contain the department. That might explain why I still couldn't get the value list to filter correctly.

 

Thanks for your help Comment!