1 Reply Latest reply on Sep 8, 2011 1:17 PM by philmodjunk

    Another Conditional Value list

    IanSegers

      Title

      Another Conditional Value list

      Post

      Hi there

      Table
      Field

      Previous question was having 2 condiitions for one Value list. But this problem is slighty different.
      Consider next 2 tables. 'Invoice Items' and 'Products'. There is a One('Product')-To-Many('Invoice Items') relation between both tables.

      A Product can be from a certain 'Category'. Consider this as a 3th table that feeds a Value List 'Product Categories'.
      When I create a new record in the 'invoice Items' I want to be able to select first the 'product category' and afterwards the appropiate 'product code'.

      Logically I create the relation between 'Invoice Items' and 'Products' by using the 'Product Code' field in 'Invoice Items'.
      For achieving the Master-detail effect with dropdowns I used next article:
      http://help.filemaker.com/app/answers/detail/a_id/5833/~/creating-conditional-value-lists

      Hence the fact that in there example they use TYPE as relation. In my situation that would be Category.
      Eventually by using these tutorial the dropdowns behave like they should. I select a 'Product Category' and in next dropdown I can select only the 'product code' which are related to the category.

      But other tables/layout behave inappropiate now. This is because the 'Product Category' (table products) and 'category' (table invoice items) are the relationship > THIS MEANS NO UNIQUE VALUES FOR ONE OF BOTH TABLES. (derived from the tutorial). But when I add to the relationship the product code from table products and Invoice Items for making a correct relation with unique values. I cannot select any products anymore in my dropdown list. The dropdown list for my products wil show <No Values Defined>.

      I tried also other strategies by changing the database structure. But I am hitting on a brick wall here. Can someone help me with the correct approach (Domain Model design or Settings).

      I really would appreciate some help or hints. Have been trying for some hours with trial and error but I am not really progressing.
      I hope the description and question are understandable by everyone.

      Thank you for your Time

       

      Ian

      test.jpg

        • 1. Re: Another Conditional Value list
          philmodjunk

          In FileMaker you can have more than one relationship between two tables by using additional table occurrences for one of the tables.

          Click on Products and make a new occurrence of it by clicking the button with two green plus signs.

          Use:

          LineItems::ProductCode = Products::Code so your portal to line items can reference the needed data in products.

          Use

          LineItems::category = LineItemsBycategory::category

          for your conditional value list.

          You may want to read this tutorial on table occurrences as this is a key concept in how fileMaker functions:  Tutorial: What are Table Occurrences?