A better way? Lookups and valuelists

[Kingme]

I've got a very simple fairly-standard Contact Database with two tables, Company & ContactName. The two tables are related through CompanyID and unlimited persons can be added to a company.

I have a third file/table (let's call that "Invoices") that "pulls" information from the ContactDatabase via drop-downs and lookups.

First, in the "Invoices" table I use a value list of all company names from the contact database. After the selection occurs, (1) I use a "lookup" field to copy over the CompanyID into the Invoice table and then create a relationship with this ID field and the CompanyID field in the ContactDatabase (just it case the company name changes in the future). (2) Then, based on this relationship, another valuelist is shown with matching Names from that Company. The user selects the name they want and then (3) another lookup field copies the ContactNameID over to the Invoice table and finally another relationship is created between the NameID's (again, just in case information is changed)

So basically I have the following relationships in the Invoice table (to the ContactDatabase):

CompanyID (CompanyID_Invoices -> CompanyID_Main) [(1) from above paragraph]

CompanyID_Name (CompanyID_Invoices -> CompanyID_ContactName) [(2) from above paragraph]

ContactNameID (ContactNameID_Invoices -> ContactNameID_ContactName) [(3) from above paragraph]

Everything works fine and dandy but was wondering if there was a "better" way to do the above. Possibly reconfiguring my relationships a bit, it seems a bit messy to have so many relationships taking up so much space. I don't like using lookups, but don't really see any other way of doing this.

[bcooney]

Oh, you were so close.

Set the Invoices::_kF_CompanyID field to use the value list of your companies. Have the value list include the CompanyID and its Name. Use the option to only show values from the second field. This way, your are directly entering the CompanyID into the Invoice table. No lookups required. In fact, you may wish to not store the Company Name in the Invoices table, but always get it thru the Invoices=Company relationship.

Have a look at this thread and the demo file I posted. I put this together, bcs many people don't know how to create child records.

[Kingme]

Nice. I knew I was overlooking some of the valuelist options. I actually utilize the "second field" in some other valuelists, but never really thought of showing the second field only (then using the first field for entry.) I always thought it was weird that Filemaker doesn't allow you to use the second field for data entry, but it makes perfect sense with this method.

Thanks a bunch.

[bcooney]

Glad to help. What you might run into is that popup menus, although the simplest to implement, can become cumbersome if there are many values in the list. So, you may wish to use a Select button that calls up a popup window that has a list of your choices (list view) or a portal (filtered). A user selects their foreign key this way, it's passed via a script to the proper foreign key field.

See if you can find an example by searching for popup window.