2 Replies Latest reply on Dec 3, 2016 1:28 AM by bobele

    creating a value list from 2 independent tables


      Hi, and thanks for your help

      I'm an amateur trying to build a knowledge base for my wife's research and her extensive collection of objects. i myself am interested to keep up with the field because many moons ago (I'm 70) I was a systems analyst and worked on the first attempt at a commercial relational database (IBM-IMS); it failed because back then we didn't have the necessary memory and processing power - IBM 360s!)


      Anyway my question:

      The collection has several categories of object (e.g. documents, figures, puppets etc)

      Each category has subcategories (e.g for documents, subcategories include prints, pictures, articles etc)

      I also have to organise things by geography (by world region like West Africa). Of course category and region are independent (i.e. not in a hierarchical relationship)


      The categories are common to all regions

      Some of the subcategories are also common (like the document subcategories I listed above), but most are region-specific

      The total list of sub-categories is too large for a convenient dropdown


      For each item in the base, I select (separately) the region and the category using drop downs

      Then I want to build a drop down list (value list) that restricts sub-category according to both region and category

      I could do it I suppose by creating a table with every combination of category region and subcategory but that gets very bulky and is complicated to update.


      I thought about creating table A (region/subcategory) and B (category/subcategory) and using a values list starting from.. but I cant get it to work

      I could write a script, but I can't see how to use it to populate a value list (I suppose I would have to populate a temporary table?)



      I would be very grateful for advice, but please be patient...


      NB I'm on Filemaker 13 Pro