Value list from specified field

[keepemup]

I have two tables joined by volunteerID and InterestID. On the Volunteer form I put the interestID field and Interest name fileld and set them up as value list from the fields but it won't let me access those fields on the form. What am I doing wrong? There is information in the interest table.

 

 

Tables: Vounteer and Interest

 

Fields _kp_volunteerID _kp_interestID

_kf_interestID _kf_volunteerID

first name interestname

Last name

and so on......

 

joined by the ID fields. Value list Interest from Field Fields "interest::interestname and interest::interestID.

 

Then I have a portal on the input form with these fields but nothing is there?

[AHunter3]

I only followed about half of that, but are you saying that the fields you're not able to make any use of are related fields from the standpoint of the layout that you put them on?

 

Is the relationship set up to allow creation of new records in the other table via this relationship? Because otherwise you can't just go typing in one of that other related table's fields unless there is already a related record over there for you to edit.

[keepemup]

I only followed about half of that, but are you saying that the fields you're not able to make any use of are related fields from the standpoint of the layout that you put them on? Yes that is what I was talking about.

 

Is the relationship set up to allow creation of new records in the other table via this relationship? Because otherwise you can't just go typing in one of that other related table's fields unless there is already a related record over there for you to edit.

Oh you are right I totally forgot about allowing creations. I will see if I did that and let you know. Thanks!

[keepemup]

That was my problem. But I have another question, Do you have to input the interestID and then the interestname or is there a way to just have the interestname show up and it fills in the interestID on its own? Hope I explained that ok?

[mfif]

Reverse the order of the value list: interestID should go first, then interestname. You can sort by the second field and you can even avoid displaying the ID altogether.

 

The InterestID will be stored when you make your choice, and the interestname can be displayed in a separate field (interest::interestname).

[keepemup]

Still not working. I probably have things set up wrong. Should my interest table be set up with the interestID and interestname in a different table than where the information is stored when I enter a record? Right now when I enter a record they are entered in the interest table.

[mfif]

If you're not dealing with a portal, you need to put volunteer::_kf_InterestID on your layout and make it a drop-down based on your value list. The value list itself, however, should be based on the interest table and not filtered.

 

It sounds like you're confusing layouts and tables, or perhaps portals with tables, and it's hard to tell what you mean. Do try to use consistent FileMaker terminology.

 

Also, remember we can't see your database. We don't know what kind of record you're referring to when you talk about "entering a record" (I assume it's Volunteer records, but can't be altogether certain).

[keepemup]

This is what I want:

 

I need a database of volunteers that can have multiple interests. So I am guessing I need a many to many relationship database. Can you help me set it up?

[mfif]

The more reliable way to do it is to use a join table. We'll call it volunteerinterests

 

volunteerinterests

-----------------

__kp_volunteerinterests

_kf_volunteerID

_kf_interestID

 

You then set up a one-to-many relationship between volunteers and volunteerinterests, and a many-to-one between volunteerinterests and interests.

 

Allow creation of volunteerinterests records from volunteers, and creation of interests records from volunteerinterests.

 

Now we'll go for your layout. Use a layout based on the volunteers table occurrence.

 

Set up a portal of volunteerinterests in a layout based on the volunteers table occurrence. Make it display more than one row and give it a scroll bar; we're not aiming for pretty for now, just making sure everything that needs to work is working and everything that needs to be seen can be.

 

On the portal - that is, on the very first row of the portal - place a field _kf_interestID formatted as a drop-down list, using the value list you set up before. Next to it, place the field interests::interestname (i.e. it has to be the field from the related interests table).

 

(Note that you can have two or more "table occurrences" of a single table. Make sure you pick the ones that are related.)

 

That ought to do it. There are fancy calc fields that can display the interests in a more printer-friendly manner, e.g. "music, reading, writing and Sudoku" instead of

music

reading

writing

Sudoku

 

---

 

The "simpler" method uses what is called a "multikey relationship" but for some reason I find it much harder to with, especially in terms of interface design. I've also been advised against it.

 

What you need to understand to implement it - and FileMaker doesn't do a great job of explaining in its intros - is that whenever you have a return character in a text field, you have a value. If a field contains a list like

ID07

ID09

ID10

ID13

 

and you use that field as a key field, you will get a hit whenever there is a value - any value - that matches any of those IDs in the other key field.

 

I won't walk you through setting up your interface with this approach because I don't know enough to do it well myself.

[keepemup]

Where do I put my information for the interestID and interest name?

[mfif]

What do you mean by "put" and "where"? smiley-smile

 

Seriously, I don't know whether you're asking about data entry or database design.

 

Your underlying data for interests are supposed to be in the interests table: that's where you will store all the interests that could ever possibly come up.

 

When I referred to interestID earlier, I wasn't being consistent with your field naming convention: I really meant _kp_interestID. Sorry about that. (I must say your naming convention led me to believe you had prior experience with relational databases smiley-wink. Congratulations on faking it! smiley-wink)

 

Three suggestions:

a) join the Cafe as a full member and you will be able to post attachments; you can post your database and people will look at it and tell you what's wrong.

b) download and read David Kachel's White Paper from the homepage.

c) buy a good manual and read it. The Missing Manual comes with examples to work on that you download from their website. Special Edition Using FileMaker 9 covers things more systematically but takes you less by the hand.

 

They're not mutually exclusive!

[keepemup]

Yes, I am trying to fake it as much as possible.smiley-laughing I have worked in a database program so sorta have an idea how it should work but this is driving me nuts. I think that I have thought about things so much that I am confusing myself.

 

Thats were I was getting confused as to where do I put the list of interests. Thought that they should go in the interest table but it wasn't working very well.

 

I am finally going to breakdown and go see a computer guy today that maybe able to help get this figured out. Maybe if I see it I can grasp it. I will probably feel like a dummy when I do.Oh Really! I will let you know how it goes.

 

I do have a book and have read the white paper but I need to go back and read it again.

 

Thanks for your help and I am sure I will be back asking some real dumb questions again.

[keepemup]

Things went well and I figured out what I was doing wrong. So I am on a roll now at least I think I am. Now I am stuck making a report by interest. I have a table interestname that list all the interest and value list made of interestname::interestname. When I run the report and a volunteer has several interest, only the first interestname shows up. It will group by interestname and list all the interestnames on the report but only show the name of the volunteer under the first interestname that was marked in the valuelist. I have it set up as a checkbox valuelist.

[mfif]

Add a calc field to Volunteers.

 

DisplayInterests = List (interestrelatedtable::interestname)

 

Use a merge field for that calc in your report layout, and make sure you make the text object containing the merge field big enough for all the interests that may be listed.

 

The interests will be shown as a list, one item on each line. You could fix that calc to have them comma-separated.