Excluding values after first selection

[customfamily]

Hi all!

Over the past few weeks we've been building and fine tuning our new contact database and this is the first issue I can't resolve through research or doing countless trial runs.

I have a long list of job titles we categorize clients by. They obviously fall into larger groups based on profession. Thus we have a short list of Professions and a longer list of Job Titles. I already have the drop down value list for the professions working fine and could do the same for the job titles but what I'd really like to do is have the second list of job titles shorten based on whatever is selected from the Professions value list.

E.g. if somebody chooses Photography from the first value list as a Profession, they obviously don't have to to see Lawyer or Caterer as an option when choosing a job title. They only need to see Photography related job titles, such as photographer, assistant, lighting, etc..

Would I be able to set the individual values in the first list to trigger a specific list of values to be available to choose from in the second, Job Titles value list?

Hope I made it all clear...

Thanks so much for any and all suggestions!

Best,

Rob

[Paul de Halle]

Hi

The best way to handle this is through a relational value list across two fields. It was easier to write a small demo file than explain it - please see attached file.

Hope this helps.

Paul de Halle

www.linearblue.com

Value_List_Demo.fp7.zip

[customfamily]

OMG this is it. Thank you so much. I'll try and replicate this in our database and let you know how it works out. Thanks so so much!

[customfamily]

Uh, maybe its late and I should give this a nights sleep but I pretty much understand how this works and all, its the most obvious part I'm having trouble overcoming...

Post Edited

I realized I'm creating layouts instead of new tables....back to the drawing board.

Edited April 15, 2009 by Guest

[customfamily]

Also, since we have 7000 entries and not all of them have categories, job titles assigned to them, does this mean all would have to in order for this to work?

Because if I create a table such as the one you have the list values in, then I end up with 5000 blank entries...

Edited April 15, 2009 by Guest

[LaRetta]

There is more than one way to create conditional value lists (which is what you are requesting that the second Job Title pop-up restricts down to the Profession. Attached is another option. In this option, you would use field data itself to determine future selections.

To restrict what is added to the Job Title value list, you could put this value list (with the option to add 'other' to it) on a layout only for management. For regular staff, they can be restricted to legal prior selections. Once entries are made, future selections will be determined by prior field data.

In this way, you do not need to pre-populate all possible combinations in an independent table; you would use your data file to accumulate your options. You will still need to select something in both fields but the Profession can be 'Other' for all of those without a specific associated profession. It allows more 'on the fly' combinations.

Value_List_DemoREV.zip

[customfamily]

Thank you so much, I can't tell you how amazed I am at the effort you have both made to help me.

This second solution does seem like the simpler one and I almost have it set up, except the crucial part were the job title list would actually have to restrict itself to relevant values. How do you achieve this? In the first example that is clear from the table the values are in but in yours I can't see how it tells which job titles are restricted by which professions. Or is that something it would do based on prior entries? In which case instead of a custom value list I'd have to choose a specific field for the professions value list as well (since I already have most of the Professions in the database, as well as the job titles). I'll try that right now.

I can't say it enough: Thanks for your help so far.

Best,

R.

Edited April 16, 2009 by Guest

[customfamily]

I have been doing this for almost 24 hours and am becoming suicidal :(

[LaRetta]

Look in the relationship graph. The conditional value list is determined by the relationship. And look at the value list setup. If you still run into problems, please post your file. :wink2:

[customfamily]

The value lists seem to be fine because they pull up the correct information, only the job titles aren't restricted by the other list.

My relationships also seem to be fine, although I have tried every variation I can think of to get it to work but nothing.

The one difference I can see is that you have your data in 2 tables whereas I have a single table that contains both the Job and Profession information (Prof. is Category in my database). Could this be the problem?

[LaRetta]

Are you saying that some records don't have a Profession? If so, that profession must all be entered as Other. The Profession value list can be based upon values from field or from custom value list (just change Profession from custom value to be based upon field values (all values) based upon your Rolodex table). Then the Job Description is based upon Rolodex2!

Please provide a picture of your value list settings.

The one difference I can see is that you have your data in 2 tables whereas I have a single table that contains both the Job and Profession information (

I only have one table as well. The List table was residual from the prior demo file - it is not used at all.

[customfamily]

Nope, still nothing. I assigned a category (profession) to every record and set up everything else but to no avail. Here is a set of pics for everything...I just don't know...it must be something simple I'm missing....

[LaRetta]

it must be something simple I'm missing....

It is indeed simple ... I asked to see your value list settings: File > Manage > Value Lists

Profession should be 'Use Values from Field' > Specify Field > Use Values from First Field Rolodex Profession and down below check 'use all values'

Your Job Title should be: Use Values from Field > Specify Field > Use Values from First Field ROLODEX Job Title and below, check 'Include Only Related Values' and specify ROLODEX2. And if you now look at my demo file for Job Title, you should see how it relates.

Also, in specifying your drop-down on the Job Title, you are attaching it to the field from Roldex2. Change that back to attaching the value list to Rolodex Job Description. If you are still stuck, you will need to create an empty clone of your file, zip it and attach it. :wink2:

[customfamily]

Sorry, I thought that the pics I last attached contained the information you'd need. Maybe now.

Almost. I adjusted everything according to your recommendations and it almost did the trick. What happens now is after I select a category, the job lists are restricted but to a single option. The drop-down list will have only one option, when in fact it should have much more.

I'm so grateful for all your help and am really sorry its this complicated...I understand how it should work but don't see why it isn't.

Thanks again

[LaRetta]

Your value_list.jpg cannot be downloaded. You have your Job Title value list wrong, I'm pretty sure. Please see attached (which is the same file I presented before except that this time, I changed the names to match you perfectly).

Please look at the relationship graph and make sure it matches.

Please look at the File > Manage > Value Lists and look at your Job Title Value List. It should be RELATED VALUES ONLY as listed below (and what I explained in detail in my last post).

Please check your fields and make sure they match my fields exactly on which value list is attached and which table occurrence is listed in the field list to the right (they both should point to ROLODEX and not ROLODEX2).

If this doesn't do, you will need to attach your file. A conditional value list is very simple and there are thousands of examples here on FM Forums. I simply can't make it any easier. :idunno:

Rolodex.zip

Edited April 17, 2009 by Guest

[customfamily]

I let it rest for the weekend. Came in today, everything fell into place, its working. I could cry but few of my colleges would understand...

Your name will echo in my prayers for as long as I live. My children shall know of the greatness that was achieved in this forum thread.

Thanks you so-so much When it comes to patience, Buddha has nothing on you.

Thanks again,

All the best,

R.