Hello,

I have a couple questions.

Problem #1.

I have a working conditional pop-up list; based on the value selected
in a category field1, only the related subcategories appear in the
corresponding subcategory field2. It looks like this:

CategoryField - value list (typed list of the Categories) - stored in
Main table.
e.g.,
Audio repair
Motor repair

Subcategory field (pop-up list, where the relationship is from the
main table "category" to a small table (Repair Code Lookup Table,
N=60) of all my categories, subcategories, and their codes. The
relationship is among the fields "category", so that when I select
from the typed value list, only the corresponding possible
subcategories apprear in the pop-up. The pop-up pulls from the small
table holding my categories, subcategories, and their codes.

e.g.,

When "Audio repair" is selected from the typed value list for category
- the following options for subcategory appear in the pop-up list:

speaker
audio wires
input jack

So far so good. But I want to store the repair code that is associated
with each subcategory.


Category: Audio

Subcategory Repair Code
speaker 50
audio wires 51
input jack 53

Since the relationship in the tables (for the conditional pop-up to
work) is bewteen category - I can't simply direct the field to the
Repair Code Lookup Table to store the repair code - FM sees the repair
codes for all of the associated categories, rather than the single
repair code for that particular subcategory. So a lookup field doesn't
work (as I've tried it, anyway).

I tried making another relationship between Subcategory - but then my
beautiful conditional pop-up list didn't work!

I actually have 3 lookup tables, because there are 3 sets of possible
repair codes for each machine. The layout looks like this:

***
RepairCategory1 RepairCategory2 RepairCategory3 - stored in
the Main table, typed value lists.

RepairSubcategory1 RepairSubcategory2 RepairSubcategory3 - value
list pulling from 3 seperate lookup tables (it was the only way i
could get them all to work!)

RepairCode1 RepairCode2 RepairCode3 - so far I don't know how
to fill these. They sit right next to Subcategory - so I don't want
another pop-up list. I just want to store the associated code that
matches each subcategory. In fact, I can even select these codes to be
viewed as a second field in the value list dialog options, so there's
just got to be a way to plunk 'em in a field of their own...

My last resort is to write "case" functions...ugh - alot of them.

***

Problem # 2

I also need to be able to just type in the code only, and have the
category and subcategory fields populate.
I know I can do this if I create a relationship from Category Code -
but doing so messes up my conditional pop-up fields. I already have 3
lookup tables - do I really need to make 3 more? It's getting
complicated and I want to step back and see if there is a guru out
there who can recommend a smooth solution for me before I go forward
with all these detailed steps...


Much thanks, much much!

EnJoy,
-cat

On May 30, 8:15 am, cat <cathyty... (AT) gmail (DOT) com> wrote:
Hi Cat

I am not sure if I've understood completely how your system is working
at the moment. I think I do understand what you are attempting to do.
As I've done similar things in the past, I'd like to reasure you that
what you are doing is achievable.

Everthing I've done is code based ie not the category or sub-category
name, but the code name. Users select from a list and the CODE is
entered into the field. All relationships are based on the CODE. The
value list definitions for sub-category must be defined to display
only the related values based on the main category code.

To prevent your users seeing the ugly/incomprehensible codes, overlay
the code fields with another field which is the category description.
The description will be pulled through using a relationship based on
the CODE the user has just entered.

The only time the user sees the CODE itself is when she clicks on the
popup and the code is displayed instead of the description. In your
case this may be a benefit as you want users to be able to have the
option of entering the code instead of the description. The code may
be entered manualy in the field. This can be done by selecting the
drop-down list option "include arrow to show and hide the list."

Please reply to tell what you'd like to be elaborated on. Or maybe the
real gurus on the list might like to suggest something better.

Regards
Kevin Smith

On May 30, 3:06 am, KevinSmith <pleasedonotusethisaddr... (AT) gmail (DOT) com>
wrote:
Thanks for replying Kevin,

What you've suggested makes sense, and I can do this is if I rearrange
the relationship to connect through 'code'. But if I do that, my
conditional pop-up list based on the relationship among 'category'.

My relationship looks like this now: (which allows only the related
fields in subcategory to appear in the pop-up after the category is
selected from the typed value list)

Main table Lookup table
Category = Category
Subcategory Subcategory
Code Code
etc

But in order to allow the other fields to populate based on the user
typing in a code, I need to change the relationship to this:

Main table Lookup table
Category Category
Subcategory Subcategory
Code = Code
etc

So basically I can make one or the other work, but not both. As I'm
just learning FM, I tried to simply create another relationship, like
this:

Main table Lookup table
Category = Category
Subcategory Subcategory
Code = Code
etc

Which doesn't work, of course.

So I'm not sure what to do, and the deadline is past due! I need both
functions to work:1) the ability to select broadly and then narrow via
the conditional pop-up list

Such as a selection of "audio" producing the next field pop-up of only
audio-realted choices...and then (problem 1) how to get that code to
appear without the user having to select from another pop-up (after
all, FM knows which code goes with which subcategory - it is a 1 to 1
relationship. But I don't know how to get the code to appear, since in
this case, the relationship is not based on code, but on the broader
field of category. And problem 2, how allow an user to just enter a
known code (some will be used commonly in data entry, and the user
would like to avoid going through the funnelling series of conditional
pop-ups and simply type in the code, thus populating the fields for
category and subcategory.)

Hopefully this helps explain better - any help greatly appreciated.

On May 30, 9:22 pm, cat <cathyty... (AT) gmail (DOT) com> wrote:
Hi Cat

I do think I understand a bit better. You need data to go in using
popups OR by entering the code directly. When the code goes in, the
category and sub-category must fill in automatically.

I've got this working in a sample file. It uses two tables. One for
main category and one for sub-category. It's possible to be clever and
use one file for all the value list info. You'll just need a few table
occurrences of the same table in your relationship graph to support
all the functions.

If user enters the code only, then a third relationship direct to the
sub-category will pull through the info back into the main category.
This ensures that the main category is correctly displayed on the
layout.

What I've said above is probably not clear enough to adequately
explain the structure. I'm sending a sample file I've just modified
for the purpose to your email address. I hope that will help.

Regards
Kevin Smith

On May 30, 2:57 pm, KevinSmith <pleasedonotusethisaddress (AT) gmail (DOT) com>
wrote:
Exactly right - this is what I need. I'll wait for your file - I can't
thank you enough. My eyes are about to pop from staring out the window
so hard for a solution!