Constraining results for further choices

Constraining results for further choices

Post by dln » Fri, 04 Jul 2003 01:21:57



I have a complicated question that might just wind up baffling anyone
that reads it. Here is what I seek help in doing:

I have lists with metadata about administrative features: Nations
(ADMLevel 1), Provinces (ADMLevel 2), Prefectures (ADMLevel 3), Counties
(ADMLevel 4), Townships (ADMLevel 5), etc. What I would like to do is
when I am in the entry interface creating an entry for a County, I'd
like to choose the Nation from a list, and then be given another list of
only the Provinces that are within that Nation, and then be able to
choose the appropriate Prefecture from that Province and then place the
County within that. This database has several hundred Counties, so if
when I get down to the Township level, I at least only get only the
Counties within a given Prefecture, that will make my pop-up list a lot
more manageable.

The metadata is very similar regardless of the Level of the feature -
whether a Nation, Province, etc is described, so I am thinking it can be
done with a singular file and not separate files for each feature type.
The calculation that I am thinking is something like this:

1. Find all the records where the ADMLevel = 1 (i.e. a Nation) and the
FeataureName=the value I've chosen from the list of 5 Nations.
2. Find all the ADMLevel 2 features (i.e. Provinces) that fall within
that ADMLevel 1 feature.
3. Then take the FeatureName field of all those ADMLevel 2 features
(i.e. Provinces) and put the values into a value list.
4. Select the value for that Province and then similary constrain the
search to only the Prefectures within that Province.

If any has even made it through this message, much less offerred some
advice, i say props to you!

Gratefully,
David N

 
 
 

Constraining results for further choices

Post by Glenn Schwand » Fri, 04 Jul 2003 04:34:42


What version of FileMaker are you using?  You need something called
Conditional Value Lists and implementation is different depending upon your
version.


Quote:> I have a complicated question that might just wind up baffling anyone
> that reads it. Here is what I seek help in doing:

> I have lists with metadata about administrative features: Nations
> (ADMLevel 1), Provinces (ADMLevel 2), Prefectures (ADMLevel 3), Counties
> (ADMLevel 4), Townships (ADMLevel 5), etc. What I would like to do is
> when I am in the entry interface creating an entry for a County, I'd
> like to choose the Nation from a list, and then be given another list of
> only the Provinces that are within that Nation, and then be able to
> choose the appropriate Prefecture from that Province and then place the
> County within that. This database has several hundred Counties, so if
> when I get down to the Township level, I at least only get only the
> Counties within a given Prefecture, that will make my pop-up list a lot
> more manageable.

> The metadata is very similar regardless of the Level of the feature -
> whether a Nation, Province, etc is described, so I am thinking it can be
> done with a singular file and not separate files for each feature type.
> The calculation that I am thinking is something like this:

> 1. Find all the records where the ADMLevel = 1 (i.e. a Nation) and the
> FeataureName=the value I've chosen from the list of 5 Nations.
> 2. Find all the ADMLevel 2 features (i.e. Provinces) that fall within
> that ADMLevel 1 feature.
> 3. Then take the FeatureName field of all those ADMLevel 2 features
> (i.e. Provinces) and put the values into a value list.
> 4. Select the value for that Province and then similary constrain the
> search to only the Prefectures within that Province.

> If any has even made it through this message, much less offerred some
> advice, i say props to you!

> Gratefully,
> David N


 
 
 

Constraining results for further choices

Post by dln » Fri, 04 Jul 2003 05:10:25


I'm using FileMaker Pro 5 for Windows. I can relatively easily move up
to FMP 6, and if need be move to a Mac if that brings enhancements.

Thanks much,
David


> What version of FileMaker are you using?  You need something called
> Conditional Value Lists and implementation is different depending upon your
> version.



>>I have a complicated question that might just wind up baffling anyone
>>that reads it. Here is what I seek help in doing:

>>I have lists with metadata about administrative features: Nations
>>(ADMLevel 1), Provinces (ADMLevel 2), Prefectures (ADMLevel 3), Counties
>>(ADMLevel 4), Townships (ADMLevel 5), etc. What I would like to do is
>>when I am in the entry interface creating an entry for a County, I'd
>>like to choose the Nation from a list, and then be given another list of
>>only the Provinces that are within that Nation, and then be able to
>>choose the appropriate Prefecture from that Province and then place the
>>County within that. This database has several hundred Counties, so if
>>when I get down to the Township level, I at least only get only the
>>Counties within a given Prefecture, that will make my pop-up list a lot
>>more manageable.

>>The metadata is very similar regardless of the Level of the feature -
>>whether a Nation, Province, etc is described, so I am thinking it can be
>>done with a singular file and not separate files for each feature type.
>>The calculation that I am thinking is something like this:

>>1. Find all the records where the ADMLevel = 1 (i.e. a Nation) and the
>>FeataureName=the value I've chosen from the list of 5 Nations.
>>2. Find all the ADMLevel 2 features (i.e. Provinces) that fall within
>>that ADMLevel 1 feature.
>>3. Then take the FeatureName field of all those ADMLevel 2 features
>>(i.e. Provinces) and put the values into a value list.
>>4. Select the value for that Province and then similary constrain the
>>search to only the Prefectures within that Province.

>>If any has even made it through this message, much less offerred some
>>advice, i say props to you!

>>Gratefully,
>>David N

 
 
 

Constraining results for further choices

Post by Glenn Schwand » Fri, 04 Jul 2003 05:31:00


I don't have 5, but see if "About displaying only a subset of field values
in a value list" shows up in your help file.


> I'm using FileMaker Pro 5 for Windows. I can relatively easily move up
> to FMP 6, and if need be move to a Mac if that brings enhancements.

> Thanks much,
> David


> > What version of FileMaker are you using?  You need something called
> > Conditional Value Lists and implementation is different depending upon
your
> > version.



> >>I have a complicated question that might just wind up baffling anyone
> >>that reads it. Here is what I seek help in doing:

> >>I have lists with metadata about administrative features: Nations
> >>(ADMLevel 1), Provinces (ADMLevel 2), Prefectures (ADMLevel 3), Counties
> >>(ADMLevel 4), Townships (ADMLevel 5), etc. What I would like to do is
> >>when I am in the entry interface creating an entry for a County, I'd
> >>like to choose the Nation from a list, and then be given another list of
> >>only the Provinces that are within that Nation, and then be able to
> >>choose the appropriate Prefecture from that Province and then place the
> >>County within that. This database has several hundred Counties, so if
> >>when I get down to the Township level, I at least only get only the
> >>Counties within a given Prefecture, that will make my pop-up list a lot
> >>more manageable.

> >>The metadata is very similar regardless of the Level of the feature -
> >>whether a Nation, Province, etc is described, so I am thinking it can be
> >>done with a singular file and not separate files for each feature type.
> >>The calculation that I am thinking is something like this:

> >>1. Find all the records where the ADMLevel = 1 (i.e. a Nation) and the
> >>FeataureName=the value I've chosen from the list of 5 Nations.
> >>2. Find all the ADMLevel 2 features (i.e. Provinces) that fall within
> >>that ADMLevel 1 feature.
> >>3. Then take the FeatureName field of all those ADMLevel 2 features
> >>(i.e. Provinces) and put the values into a value list.
> >>4. Select the value for that Province and then similary constrain the
> >>search to only the Prefectures within that Province.

> >>If any has even made it through this message, much less offerred some
> >>advice, i say props to you!

> >>Gratefully,
> >>David N

 
 
 

Constraining results for further choices

Post by dln » Sat, 05 Jul 2003 06:48:57


Yes, that is in the Help menu for FMP5 and your advice has really helped
a lot.

Thanks,
David


> I don't have 5, but see if "About displaying only a subset of field values
> in a value list" shows up in your help file.



>>I'm using FileMaker Pro 5 for Windows. I can relatively easily move up
>>to FMP 6, and if need be move to a Mac if that brings enhancements.

>>Thanks much,
>>David


>>>What version of FileMaker are you using?  You need something called
>>>Conditional Value Lists and implementation is different depending upon

> your

>>>version.



>>>>I have a complicated question that might just wind up baffling anyone
>>>>that reads it. Here is what I seek help in doing:

>>>>I have lists with metadata about administrative features: Nations
>>>>(ADMLevel 1), Provinces (ADMLevel 2), Prefectures (ADMLevel 3), Counties
>>>>(ADMLevel 4), Townships (ADMLevel 5), etc. What I would like to do is
>>>>when I am in the entry interface creating an entry for a County, I'd
>>>>like to choose the Nation from a list, and then be given another list of
>>>>only the Provinces that are within that Nation, and then be able to
>>>>choose the appropriate Prefecture from that Province and then place the
>>>>County within that. This database has several hundred Counties, so if
>>>>when I get down to the Township level, I at least only get only the
>>>>Counties within a given Prefecture, that will make my pop-up list a lot
>>>>more manageable.

>>>>The metadata is very similar regardless of the Level of the feature -
>>>>whether a Nation, Province, etc is described, so I am thinking it can be
>>>>done with a singular file and not separate files for each feature type.
>>>>The calculation that I am thinking is something like this:

>>>>1. Find all the records where the ADMLevel = 1 (i.e. a Nation) and the
>>>>FeataureName=the value I've chosen from the list of 5 Nations.
>>>>2. Find all the ADMLevel 2 features (i.e. Provinces) that fall within
>>>>that ADMLevel 1 feature.
>>>>3. Then take the FeatureName field of all those ADMLevel 2 features
>>>>(i.e. Provinces) and put the values into a value list.
>>>>4. Select the value for that Province and then similary constrain the
>>>>search to only the Prefectures within that Province.

>>>>If any has even made it through this message, much less offerred some
>>>>advice, i say props to you!

>>>>Gratefully,
>>>>David N

 
 
 

Constraining results for further choices

Post by Chris Brow » Sat, 05 Jul 2003 08:34:45


Hi dln,

I have something very similar that uses a 5 level hierarchy structure,
although it describes quite different data (in FMP 5/6)  I found it much
more manageable to use separate files for each level.  In order to create
the intermediate dependencies 'efficiently',  it is a real advantage to use
individual files I would suggest. If you are importing pre-existing full 5
level data then that would be  a different matter.

The other thing that you might consider, although I admit this will make
life more complicated when you are starting to use conditional value lists,
but has a number of advantages,  is to use a numeric key for each level
e.g. three digits, the resultant concatenated keys become much more admin
friendly, given the inevitable variable lengths of the actual data, e.g.

004 020 114 002 110
levelA levelB levelC levelD levelE

Chris Brown
Neurosurgery
University of Adelaide


> I have a complicated question that might just wind up baffling anyone
> that reads it. Here is what I seek help in doing:

> I have lists with metadata about administrative features: Nations
> (ADMLevel 1), Provinces (ADMLevel 2), Prefectures (ADMLevel 3), Counties
> (ADMLevel 4), Townships (ADMLevel 5), etc. What I would like to do is
> when I am in the entry interface creating an entry for a County, I'd
> like to choose the Nation from a list, and then be given another list of
> only the Provinces that are within that Nation, and then be able to
> choose the appropriate Prefecture from that Province and then place the
> County within that. This database has several hundred Counties, so if
> when I get down to the Township level, I at least only get only the
> Counties within a given Prefecture, that will make my pop-up list a lot
> more manageable.

> The metadata is very similar regardless of the Level of the feature -
> whether a Nation, Province, etc is described, so I am thinking it can be
> done with a singular file and not separate files for each feature type.
> The calculation that I am thinking is something like this:

> 1. Find all the records where the ADMLevel = 1 (i.e. a Nation) and the
> FeataureName=the value I've chosen from the list of 5 Nations.
> 2. Find all the ADMLevel 2 features (i.e. Provinces) that fall within
> that ADMLevel 1 feature.
> 3. Then take the FeatureName field of all those ADMLevel 2 features
> (i.e. Provinces) and put the values into a value list.
> 4. Select the value for that Province and then similary constrain the
> search to only the Prefectures within that Province.

> If any has even made it through this message, much less offerred some
> advice, i say props to you!

> Gratefully,
> David N

 
 
 

1. To Constrain Or Not Constrain

In my experience with RDBMSs design with a wide range of companies in my
years as a consultant I've seen almost no one extensively use the relational
integrity constraints that come with any RDBMS product.  So, while it may be
a normalized design, they don't explicitly enforce it in the database with
foriegn key constraints.  Check constraints are not used to limit value
ranges in a column.  Primary key constraints, yes, but not the others.  The
reasons given for this are performance and a belief that the application
code should be enforcing these constraints.

In my experience with these "free for all" databases with no referential
integrity enforced I see many data problems.  Orphaned records, invalid
foreign keys, you name it.  Yes, the application logic should enforce
integrity, but we all know too often it doesn't.  And no one can figure it
out until a user calls with a problem.  If constraints were put in from the
beginning, it would have been spotted up front.

Of course any book on the subject will tell you you should use these
features.  But does this not apply to the "real world"?  What are the pros
and cons of using constraints?  I know the pros--data integrity.  But why
would you explicitly choose not to use them?

Ed May
Consultant
Schaumburg, IL

2. select count(distinct col1, col2) from table

3. Character sets: choices, choices, ...

4. Lost cursors keys

5. Hi. If I fill in a field how can the further choices be influenced by this one ...see example

6. Subtotal

7. Constrains

8. IMS Technical Supprt Engineers, Regresion Test Engineers, Assembler Developers

9. DMO-Script-Methode drop Constrains check if exists

10. UNVIEWABLE FOREIGN KEY CONSTRAINS

11. Bitmap column and trigger constrain

12. Unique constrain with more than one field