Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.databases.filemaker > #2586
| From | Helpful Harry <HelpfulHarry@BusyWorking.com> |
|---|---|
| Newsgroups | comp.databases.filemaker |
| Subject | Re: Dynamic ( nested) value list |
| Date | 2016-03-08 18:56 +1300 |
| Organization | Aioe.org NNTP Server |
| Message-ID | <080320161856541251%HelpfulHarry@BusyWorking.com> (permalink) |
| References | <dff63d83-8e99-46ba-9193-ccb07a66aecd@googlegroups.com> |
In article <dff63d83-8e99-46ba-9193-ccb07a66aecd@googlegroups.com>,
Mushabab Al-Murayeh <malmurayeh@gmail.com> 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)
Back to comp.databases.filemaker | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread
Dynamic ( nested) value list Mushabab Al-Murayeh <malmurayeh@gmail.com> - 2016-03-01 10:14 -0800
Re: Dynamic ( nested) value list Los_Indios <losindios.m@gmail.com> - 2016-03-01 17:49 -0800
Re: Dynamic ( nested) value list Mushabab Al-Murayeh <malmurayeh@gmail.com> - 2016-03-05 03:13 -0800
Re: Dynamic ( nested) value list Helpful Harry <HelpfulHarry@BusyWorking.com> - 2016-03-03 18:37 +1300
Re: Dynamic ( nested) value list Mushabab Al-Murayeh <malmurayeh@gmail.com> - 2016-03-03 04:22 -0800
Re: Dynamic ( nested) value list Helpful Harry <HelpfulHarry@BusyWorking.com> - 2016-03-04 18:22 +1300
Re: Dynamic ( nested) value list Mushabab Al-Murayeh <malmurayeh@gmail.com> - 2016-03-04 10:35 -0800
Re: Dynamic ( nested) value list Helpful Harry <HelpfulHarry@BusyWorking.com> - 2016-03-05 10:03 +1300
Re: Dynamic ( nested) value list Mushabab Al-Murayeh <malmurayeh@gmail.com> - 2016-03-04 20:10 -0800
Re: Dynamic ( nested) value list ursus <ursus.kirk@gmail.com> - 2016-03-05 20:46 +0100
Re: Dynamic ( nested) value list Helpful Harry <HelpfulHarry@BusyWorking.com> - 2016-03-07 13:59 +1300
Re: Dynamic ( nested) value list Helpful Harry <HelpfulHarry@BusyWorking.com> - 2016-03-08 18:56 +1300
Re: Dynamic ( nested) value list Mushabab Al-Murayeh <malmurayeh@gmail.com> - 2016-03-08 02:54 -0800
Re: Dynamic ( nested) value list Helpful Harry <HelpfulHarry@BusyWorking.com> - 2016-03-09 18:43 +1300
Re: Dynamic ( nested) value list dempson@actrix.gen.nz (David Empson) - 2016-03-09 21:13 +1300
Re: Dynamic ( nested) value list Mushabab Al-Murayeh <malmurayeh@gmail.com> - 2016-03-10 13:41 -0800
Re: Dynamic ( nested) value list Thibaud Taudin Chabot <tchabot@hccnet.nl> - 2016-03-11 11:22 +0100
Re: Dynamic ( nested) value list ursus <ursus.kirk@gmail.com> - 2016-03-11 19:22 +0100
Re: Dynamic ( nested) value list Thibaud Taudin Chabot <tchabot@hccnet.nl> - 2016-03-12 16:11 +0100
Re: Dynamic ( nested) value list Mushabab Al-Murayeh <malmurayeh@gmail.com> - 2016-03-10 13:40 -0800
Re: Dynamic ( nested) value list Mushabab Al-Murayeh <malmurayeh@gmail.com> - 2016-04-06 09:41 -0700
Re: Dynamic ( nested) value list Helpful Harry <HelpfulHarry@BusyWorking.com> - 2016-04-07 08:48 +1200
Re: Dynamic ( nested) value list Mushabab Al-Murayeh <malmurayeh@gmail.com> - 2016-04-08 04:45 -0700
Re: Dynamic ( nested) value list ursus <ursus.kirk@gmail.com> - 2016-04-08 16:06 +0200
Re: Dynamic ( nested) value list Helpful Harry <HelpfulHarry@BusyWorking.com> - 2016-04-09 13:20 +1200
Re: Dynamic ( nested) value list Helpful Harry <HelpfulHarry@BusyWorking.com> - 2016-04-09 10:03 +1200
Re: Dynamic ( nested) value list dempson@actrix.gen.nz (David Empson) - 2016-04-09 11:20 +1200
Re: Dynamic ( nested) value list Helpful Harry <HelpfulHarry@BusyWorking.com> - 2016-04-09 13:29 +1200
csiph-web