1 Reply Latest reply on Nov 19, 2012 4:51 PM by philmodjunk

    How do I create a sub-group??? (complex relationship)



      How do I create a sub-group??? (complex relationship)


           Ok, so I have a database that has a table for "Companies". But there are a few different types of companies to keep track of that behave different ways. I don't need to track a whole lot of different info about the different types of companies other than their types. For instance, Contractors and Vendors are two types of companies in the list. A company can be both a contractor AND a vendor, or it can be either one, or neither. So I have a checkboix set in the COMPANIES layout with the different options. I would like to have a different list for each type of company so that I can dial in information quicker.

           If I am making a work order, I need to see a list of only companies that are listed as VENDORS. Likewise, I want a layout that only shows the VENDORS so I can see all work orders for a specific VENDOR.

           Do I write a script with a CASE statement? Or an IF statemenet? And then create some sort of self join table?

        • 1. Re: How do I create a sub-group??? (complex relationship)

               What you are describing are conditional value lists where you get a list of values from a table but only those from records that meet some kind of specified criteria--such as the type of company.

               See this thread: Custom Value List?

               Both "option 1" and "Option 2" can be made to work for this. Option 1 gives you permanently "hardwired" lists of values and don't require adding more relationships in order to get this to work. Option 2 makes for a 'selectable' value list where if you put a value such as "vendor" or "contractor" in a field and then only companies from records of that type appear in the value list. This is more flexible, but requires adding a new relationship to a new occurrence of companies.

               A calculation such as:

               If ( Not IsEmpty ( FilterValues ( CompanyType ; "Vendor" ) ; CompanyName )

               can be used with Option 1.

               WIth option 2, you can use your check box formatted field as a match field in the relationship and a record will match across the relationship for any value selected in the check box field. If "vendor" and "contractor" are both selected, the record will match to "Vendor" or "Contractor" in the match field on the other side of the relationship.

               More links on conditional value lists:

               Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

               Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

               Hierarchical Conditional Value lists: Conditional Value List Question

               Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.