Path: csiph.com!aioe.org!.POSTED!not-for-mail From: Helpful Harry Newsgroups: comp.databases.filemaker Subject: Re: Dynamic ( nested) value list Date: Tue, 08 Mar 2016 18:56:54 +1300 Organization: Aioe.org NNTP Server Lines: 314 Message-ID: <080320161856541251%HelpfulHarry@BusyWorking.com> References: NNTP-Posting-Host: em1v4tzoIUmG/cT5E6Lh1A.user.gioia.aioe.org Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Complaints-To: abuse@aioe.org User-Agent: Thoth/1.8.4 (Carbon/OS X) X-Notice: Filtered by postfilter v. 0.8.2 X-Face: vIZzK[X\z-._]-_:U?l@cBtwg<{a>ggn\mH}Vs>38E,#R%7h%U0'=4;1Q=erO %[T#a{YJfGvAbWmE@f_h], Mushabab Al-Murayeh wrote: > Hi everyone :) > > I would appreciate the help! > > I want to build a solution for a medical warehouse and the database will > contain thousands of records of different materials (consumables) : > > I start building the solution but I faced a problem with nested value lists: > I will have 5-6 tables in a file to describe these materials: > > 1. Category (many items) > 2. Class (to be nested to Categories) e.g. many classes belong to one > category. > 3. Type > 4. Size > 5.... > > Every table of the above belongs to the table above > > I want to have a table where once I choose a category only the related > classes pop up in the class field and the same with once I choose a class the > related types popup in the type field etc. > > > Thank you in advance. > > Please consider attaching a file as a demonstration to your kind explanation > and support. > > Thank you in advance. > > > Mushabab Al-Murayeh, MD Okay, this didn't work out as bad as I thought it was going to have to be. I figured out a way for using five nested tables that is fairly compact and easy (and simple to add more sub-tables to) ... but complicated to explain in a text message It does also make a few assumptions about how your system is designed - the main one being that the Relationship links to the next sub-Table records are stored in a normal Text Field, with each separate Relationship Link on a new line. It also requires the pop-up menus for the user to display both a ID number and item name. You didn't say what version of FileMaker Pro you're using, but this should work in any version from at least FileMaker Pro 4 onwards (probably any version with Relationship abilities). Sample files to be tidied up and uploaded ... if I can find somewhere to upload them! SETTING-UP First you've got the five nested data Tables, with each sub-Table related from the Table above it in a One:Many system. Category - Class -- Type --- Size ---- Brand These need to have at least four Fields: a unique ID Field, the name Field, a Field that combines these two, and a Field for links to the next sub-Table (not needed in the bottom-most Table). They each also obviously need a Relationship to the next sub-Table. e.g CategoryTable Fields: CategoryID Text Auto-enter Serial Number starting with Cat001 CategoryName Text c_CategoryID_CategoryName Calculation (Text Result) = CategoryID & " - " & CategoryName ClassLinks Text Relationship: rel_Classes ClassLinks = ::ClassID ClassTable ClassTable Fields: ClassID Text Auto-enter Serial Number starting with Cls001 ClassName Text c_ClassID_ClassName Calculation (Text Result) = ClassID & " - " & ClassName TypeLinks Text Relationship: rel_Types TypeLinks = ::TypeID TypeTable TypeTable Fields: TypeID Text Auto-enter Serial Number starting with Typ001 TypeName Text c_TypeID_TypeName Calculation (Text Result) = TypeID & " - " & TypeName SizeLinks Text Relationship: rel_Sizes SizeLinks = ::SizeID SizeTable SizeTable Fields: SizeID Text Auto-enter Serial Number starting with Siz001 SizeName Text c_SizeID_SizeName Calculation (Text Result) = SizeID & " - " & SizeName BrandLinks Text Relationship: rel_Brands BrandLinks = ::BrandID BrandTable BrandTable Fields: BrandID Text Auto-enter Serial Number starting with Bnd001 BrandName Text c_BrandID_BrandName Calculation (Text Result) = BrandID & " - " & BrandName The main UserTable is the one for the user data entry (the one that will have the pop-up menus). This needs to have at least 13 Fields, eight Relationships linking to the five data Tables above (one each for the top and bottom Tables, and two for the each of the middle Tables), and five Value Lists (one for each pop-up menu Field). UserTable Fields: UserCategory Text formatted as Pop-up Menu using vl_UserCategoryList UserClass Text formatted as Pop-up Menu using vl_UserClassList UserType Text formatted as Pop-up Menu using vl_UserTypeList UserSize Text formatted as Pop-up Menu using vl_UserSizeList UserBrand Text formatted as Pop-up Menu using vl_UserBrandList c_UserCategoryID Calculation (Text Result) = Left(UserCategory, 6) c_UserClassID Calculation (Text Result) = Left(UserClass, 6) c_UserTypeID Calculation (Text Result) = Left(UserType, 6) c_UserSizeID Calculation (Text Result) = Left(UserSize, 6) c_ClassLinksLookup Calculation (Text Result) = rel_LookupCategory_Classes::ClassLinks c_TypeLinksLookup Calculation (Text Result) = rel_LookupClass_Types::TypeLinks c_SizeLinksLookup Calculation (Text Result) = rel_LookupType_Sizes::SizeLinks c_BrandLinksLookup Calculation (Text Result) = rel_LookupSize_Brands::BrandLinks Relationships: rel_LookupCategory_Classes c_UserCategoryID = ::CategoryID CategoryTable rel_LookupClass_Types c_UserClassID = ::ClassID ClassTable rel_LookupType_Sizes c_UserTypeID = ::TypeID TypeTable rel_LookupSize_Brands c_UserSizeID = ::SizeID SizeTable rel_Class_Popup c_ClassLinksLookup=::ClassID ClassTable rel_Type_Popup c_TypeLinksLookup=::TypeID TypeTable rel_Size_Popup c_SizeLinksLookup=::SizeID SizeTable rel_Brand_Popup c_BrandLinksLookup=::BrandID BrandTable Value Lists: vl_UserCategoryList From Field c_CategoryID_CategoryName CategoryTable vl_UserClassList From Field c_ClassID_ClassName only related values: rel_Class_Popup vl_UserTypeList From Field c_TypeID_TypeName only related values: rel_Type_Popup vl_UserSizeList From Field c_SizeID_SizeName only related values: rel_Size_Popup vl_UserBrandList From Field c_BrandID_BrandName only related values: rel_Brand_Popup That's the set-up done ... now on how it works when entering data in the UserTable using the five pop-up menu Fields. HOW IT WORKS Basically it copies the Relationship links from each pop-up menu's chosen parent Table record so that data can be used in a Relationship link from the UserTable to the next child Table. The UserCategory Field uses the vl_UserCategoryList Value List to retrieve *ALL* the category records from the CategoryTable. These are displayed as both the ID number and the name. e.g. Cat001 - Fruit Cat002 - Vegetables Cat003 - Confectionery etc. You need ID so that the UserTable can work out which record to retrieve the appropriate sub-Table links from. You could potentially do this in other ways, but it can get messy if they aren't uniquely identifiable. When the user picks one of these Category menu options, the c_UserCategoryID Calculation Field extracts the first six characters (i.e. the CategoryID) which can then be used via the rel_LookupCategory_Classes Relationship to retrieve the appropriate Class Links from the CategoryTable's ClassLinks Field. These related link IDs are stored in the c_ClassLinksLookup Calcluation Field.The c_ClassLinksLookup Calculation Field can then be used via the rel_Class_Popup Relationship to allow the vl_UserClassList Value List to retrieve only the appropriate related possibilities for the UserClass pop-up menu Field from the ClassTable. e.g. If the user chooses UserCategory option "Cat002 - Vegetables" then the UserClass pop-up menu would display Cls001 - Carrot Cls002 - Cauliflower Cls003 - Cabbage Cls004 - Lettuce etc. When the user picks one of these Class menu options, the process repeats with the "Type" set of Fields, Relationships, and Value List to retrieve the appropriate related possibilities for the UserType pop-up menu Field from the TypeTable. And so on, down through to the fifth pop-up menu. If you need to add another sub-Table {NEWITEM}Table to the bottom of the nested sub-Tables, then the {PREVIOUSITEM}Table needs these additions: Fields: {NEWITEM}Links Text Relationship: rel_{NEWITEM} {NEWITEM}Links = ::{NEWITEM}ID {NEWITEM}Table the {NEWITEM}Table needs these: Fields: {NEWITEM}ID Text Auto-enter Serial Number starting with {NEWCODE}001 {NEWITEM}Name Text c_{NEWITEM}ID_{NEWITEM}Name Calculation (Text Result) = {NEWITEM}ID & " - " & {NEWITEM}Name and finally the UserTable needs these additions: Fields: User{NEWITEM} Text formatted as Pop-up Menu using vl_User{NEWITEM}List c_User{PREVIOUSITEM}ID Calculation (Text Result) = Left(User{NEWITEM}, 6) c_{NEWITEM}LinksLookup Calculation (Text Result) = rel_Lookup{PREVIOUSITEM}_NEWITEM::NEWITEMLinks Relationships: rel_Lookup{PREVIOUSITEM}_{NEWITEM} c_User{PREVIOUSITEM}ID = ::{PREVIOUSITEM}ID {PREVIOUSITEM}Table rel_{NEWITEM}_Popup c_{NEWITEM}LinksLookup=::{NEWITEM}ID {NEWITEM}Table Value Lists: vl_User{NEWITEM}List From Field c_{NEWITEM}ID_{NEWITEM}Name only related values: rel_{NEWITEM}_Popup Hopefully that makes some sort of sense. Helpful Harry :o)