NeedCategoriesSeperatedIntoPulldownsInOtherTables

[fastest]

I have 6 categories Artist,Client,Contact,Customer,Payor and Writer(s)

in People table.

In a Catalog table and a License table I need to pull up each People table category seperately. One pullup with Artist, One pullup with Client, etc.

Have category field in people table.

Have a Category table using categoryID for relationship to People Table.

User clicks button that enters "Artist" value in category field then enters namefirst then namelast.

What would be the most efficient way to do this and how?

Thanks to all the people who support such a great site!

[Mark L]

fastest-

fastest-

I'm trying to understand what you are saying - so a couple of questions:

1) When you say that you have 6 categories - do you have 6 separate fields dedicated on this table or are those the possible choices that go in the Category field you mention?

2) When you say "User clicks button that enters "Artist" value in category field then enters namefirst then namelast." - Is that what it currently goes - or you hope to do? Can you clarify this a bit.

-Mark

[fastest]

Thanks for responding Mark!

#1 Have 1 field named "category". (I actually have seperate ones but wasn't working)

#2 It all ready does that, so that each person has their particular category assigned to them.

[Mark L]

I'm still not 100% - sorry if I'm slow - I'm very busy right now.

1) You have one record for each person - correct?

2) Each person needs to be assigned to one of your six Catagories is - this true too?

ADDED EDIT <If both are true it sounds like you just need a value list based on your Catagories file.>

[fastest]

Yes to both questions

[Mark L]

Here are instructions on creating a Value List based on an existing table:

Click on

[fastest]

I probably don't understand you, but just to clarify:

In the People table the user clicks a button that says "Artist"

that enters that value "Artist" into the "Category" field.

There are other buttons for the various other categories that do the same.

Then the user fills in all the other info for that person. When they go to fill in a client they have to click on client button to set the value, etc.

(THIS IS WHAT I NEED IT TO DO)

Now when the user goes into the Catalog table and the License table

they need a seperate pulldown list that shows only the Artists from the

People table. Then another pulldown for lets say Client.

The pulldown lists currently show all of the categories

The buttons offers a quick way to set the value in the list however

I have the edit option on the list for miscellaneous entries like "Accountant"

in case they have the need.

[Mark L]

So the confusion continues.

I asked if "Each person needs to be assigned to one of the six catagories" was true - and you said yes; I took this to mean they get one assigned catagory; but it sounds like you are looking to assign people to more than one catagory.

Why not use a Check Box which would allow someone to be anywhere from none to all six categories (or whatever number you create?)

[fastest]

No, they are only assigned one category

[Mark L]

OK - I think it's my fault in misunderstanding. The part you need help on is one getting the specific PEOPLE into a drop down within their respective catagories - Is this correct?

If so, I believe it would be a variation of this: http://www.fmforums.com/threads/showflat...&PHPSESSID= where I only wanted Staff that was in the "P" catagory to show up in a drop down. The only functional difference here is you would need five more drop downs for the other catagories.

Does that help?

-Mark

[fastest]

"YES"

I believe it is very similiar to your post, however all is not clear so I thought since you did it you could clarify it. One thing I would love to see on these posts are the final full solutions on the last posting.

thanks for your time by the way.

[sbg2]

Not exactly sure what you wanted, but I have attached an example of what I think you are trying to do. The filtered list only works for Artist and Client at the moment but should show you one way to accomplish filtered values.

People.zip

[fastest]

the attachment is a big help, THANKS I'll try all out and let you know

[fastest]

Thanks journeyman...however the pulldown list that I have now shows (only for clarification of their category, until I fix this) "Name - Category - ID#"

It is one field that the user clicks on and they are able to type in up to 2 letters (filemaker limitation?) to bring them closer to the person they are looking for, then they click enter.

In your example the user clicks on the pulldown showing a number and then you see the name but you cannot type in the 1st 2 letters of the name, you have to manually scroll down which on a large list is impossible. I tried reversing the value list to use the name first then the number, but it didn't work.

So the ideal way would be: User clicks pulldown list or menu but is able to type in letters to find who, then enter. They see only one field with just the name showing (the id# can show in list but shouldn't paste into field)...thanks

[sbg2]

New attachment shows the name without ID# in drop down list. You can remove the " - Category" by removing from the calculation field in the People table.

*Edit: I would urge you to add the ID_People to the end of the NameFirstLast calculation in case you might have two people in a particular category with the same name. If you dont add a unique identification only one will show up in the value list.

People.zip

[fastest]

Journeyman, you the man! Works perfectly. Now hopefully I can get it to work on my stuff. I agree with the ID showing, no problem for me. I'll let you know if all is ok. THANKS AGAIN for all your help.

[fastest]

Journeyman in order for this to work on other tables do I have to duplicate everything, and how would I link them?

FYI: The Catalog table creates the info which is pulled from the people table.

Then the License table pulls info from the Catalog table.

[fastest]

Mark, I would also like to thank you for your help.

[sbg2]

Unfortunately filemaker doesn't allow you to filter value lists (much) in the the Value List setup. For a filter such as you proposed you need to establish a relationship for the filter, ie. the Artist_filter in the relationship graph. I created a global field to store the text "Artist" and linked that to the category field in people. Since I use that relationship for the Artist Value List it will only show people who have their category assigned to Artist. This would all be much easier, IMO, if you could simply define a filter in the Value List definition... Show all values from Field X filtered by Field Y = "MyFilter". They really make you work to create filters.

Since you will be adding the ID# to the end of the value list you will not need the Artist_People relationship to lookup the ID#. If you need the ID# stored seperately then add it to the NameFirstLast_IDPeople calculation in such a way that it is easily extracted:

NameFirstLast_ID_People = NameFirst & ", " & NameLast & " ##" & ID_People

this way you can use a calculation field in the Catalog file to extract the ID#:

ID_Artist = Right(Artist;Length(Artist) - Position ( Artist ; "##" ; 1 ; 1)-1)

[Mark L]

For all the help others have given me here - my attempt was the least I could do.

-Mark

[fastest]

Let me see if I understand you.

Since I will be showing the ID_People in the pulldown, I do not need to create the additional Artist_People and the Client_People,etc. table occurences?

Do I need the ID stored seperately?

and if I don't do I need the calculations on the bottom of your post?

[fastest]

I forgot - As mentioned in the #155433 - Fri Apr 08 2005 12:03 PM post

do I need to duplicate exactly all of what I did in the other table (License)?

FYI: The Catalog table creates the info which is pulled from the people table.

Then the License table pulls info from the Catalog table. Thanks

[sbg2]

Let me see if I understand you.

Since I will be showing the ID_People in the pulldown, I do not need to create the additional Artist_People and the Client_People,etc. table occurences?

Correct, you do not need the additional TO's

Do I need the ID stored seperately?

Most likely not. It would be much cleaner, and would help limit file size, to store only ID_People rather than NameLastFirst_ID_People. Unfortunately Filemaker does not make it easy to do this in the instance you propose.

and if I don't do I need the calculations on the bottom of your post?

No, if you dont need to extract the ID# you dont need the bottom most calculation. You can define the NameFirstLast_ID_People to your liking, it does not have to be setup as I proposed unless you would feel the need to have the ID seperate.

[sbg2]

If I'm understanding correctly that you want the same filtered value lists on the License Table, then no you do not have to duplicate everything. Create an Artist field on your License layout and set the Field Format to pop-up list and to display values from Value List "Artist".

[fastest]

All seems to be working I am trying to create a filter using 2 categories.

Artist and Writer is that possible? So far have tried all different operators to combine the 2 categories?:

[sbg2]

Create a new calculation field in the People table, calc result set to Text:

ArtistWriter = If(Category = "Artist" or Category = "Writer"; NameLastFirst_ID_People)

Then create a new value list that uses all values from the field ArtistWriter in the People table.

file attached if you have problems.

** for some reason you can only use AND to build complex relationships. If you want an OR type value then you accomplish this with a calculation field.

People.zip

[fastest]

Once again you have come thru sbg2, that works perfectly.