having issue relating tables in a simple one to many DB

[tomivang]

hey guys, im pretty new to FM and i've been working with it tirelessly for the past month learning to use it. i love this program but there were some things that i cant seem to get done, i was hoping someone could help me out or at least point me in the right direction.

my company hosts a fair every year, and we rent out booth spots for vendors to come and sell their products and advertise and such. we started out with MS access early on but for the past few years our secretary and treasurers have been tracking our vendors and booths by hand with MS excel. its extremely error-prone and time consuming. im the only "general" IT guy here so they've tasked me with creating some sort of database system to make this process more efficient. our goals is very simple actually, im almost done with design of the database but i just need to finish the relating and portals issues. i have:

- 2 tables, "vendor information" and "booth information"

GOALS

1. relate vendors to booths in a "one-to-many relationship"

2. create a portal in the booth layout that displays the vendor who owns it

3. create a portal in the vendor layout that displays which booths he owns

4. use a script button to be able to create a new vendor from the booth layout with the information already filled in from the booth layout fields. vice versa.

ISSUES

1. i understand that database relationships require primary and foreign keys, which i've already created for my records. but the problem is my company wont be using the keys or know how to because we search and process using the predesignated booth numbers only. for example, B34, B32, B30, B29

2. there will be lots of vendors with the same name, dont know exactly how to relate the tables (with respect to primary keys)

QUESTIONS

1. currently vendors tables have a "first name" and "last name" field. will i be able relate booth to vendor this way? or have to change vendor to only 1 field of "full name"

thanks ahead of time for anyone whos willing to help me out.

[comment]

there will be lots of vendors with the same name, dont know exactly how to relate the tables (with respect to primary keys)

Your Vendors table should have a VendorID field (Number, Auto-enter serial number). The auto-entry will ensure each vendor has a unique ID and this will be your "primary key" in the relationship between the two tables:

Vendors::VendorID = Booths::VendorID

the problem is my company wont be using the keys or know how to because we search and process using the predesignated booth numbers only. for example, B34, B32, B30, B29

That's not a problem since the keys are only used internally for relating the tables, and do not need to be exposed to the users.

2. create a portal in the booth layout that displays the vendor who owns it

Since Vendors is the "one" in a one-to-many, you can simply place fields from Vendors on the layout of Booths - without requiring a portal..