Assigning relations

[Rothko]

Hello there,

I'm new to relational databases, so any help is greatly appreciated. I think I've gotten my clumsy head round it, but there is one problem that's baffling me.

I have two tables: One is a list of materials, the other a list of vendors. So a many-to-one relationship, right.

It's a database of materials, and where to get them. I would like to enter a new material, and then assign a contact person (the vendor). I think part of the problem is that I start with the 'child' and then assign its 'parent'.

Anyway, I would love to have a nicely formatted drop-down list of names to choose from, ideally followed by the company name. Or even a pop-up window of a list of of the contacts table, with a button that assigns the match key. At the moment all I have is a drop-down list of numbers (the contactID_pk) field.

A nudge in the right direction might be all it needs. I guess I really need a script, no?

The file is attached (Filemaker 8.5)

Many thanks,

Christian

CDatabase.fp7.zip

[aldipalo]

Is this what you are after?

[Rothko]

Thanks. I think may be getting there. I wasn't so sure about the Value List approach, but I've thought about it some more and I've slightly altered the Value List you picked out. See screenshot. (The calculated field simply does [color:green]Company& ", " & First Name & " " & Last Name

The user can now pick from a list of Companies and Names. However, once he made his pick, he still only sees (the field displays) the contactID_fk value. Not the name. Any idea?

Ideally I still think a pop-up window with a list of contacts to choose from is ideal. But the sample files I could find are simply too complex. Way over my head. Maybe someone knows an example file somewhere...

[aldipalo]

See attached file. I think this is what you may be after. What I did was superimpose the Contacts::Namecalc field over the ContactsID_fk field. Set the background of the ContactsID_fk to transparent and set the field behavior of Contacts::Namecalc so that you cannot enter it in browse mode. You then click in the field, find all records in the value list that are related to that material and then when you chose the contact only his/her name appears on the layout. I hope this is what you are trying to accomplish.

Al

CDatabase_Copy.zip

[Rothko]

Very pretty, thank you!

Christian

[bcooney]

I predict that you will probably need a many-to-many relationship. Usually, a material is sourced by many vendors. So, you need another table, a "join" table, that has a record for each combination of vendor and material. You could then add to the join table, the cost of the material from that vendor.

Given that, add a portal to the join table on the material layout, so you can see all the vendors, their costs for this material. It'll probably get more complicated. Units per measure/costs vary by #units purchased/etc., but this is a start.

Topic_191001.zip

Edited October 27, 2007 by Guest

[aldipalo]

Thanks, but, I can not take the credit. I learned this from others on this forum. Only paying it back.