elegant way to populate foreign key?

[ispytodd]

Apologies if this has been answered, but I can't seem to find it in a forum search.

I have a nice sized database with about 10 Tables and probably 50 table occurrences. Everything works fairly well. There is one thing I've never been able to well, and it seems so basic.

Is there an elegant way to populate foreign keys (aka match fields)?

All my primary keys are standard incrementing integers. Most of my relationships are based on said primary keys. That means than when I'm trying to relate two records I have to select from a field (or value list) of integers - not very functional for my users. I DO use value lists with "also display values from second field" but this only works with pop-up menus... I don;t want to use pop-up menus for the rest of my life.

Am I missing something? Should I be creating relationships based on other fields? I wish I could relate two records with an autocomplete editbox.

TIA!

[corn]

Popup windows with portals can be your new BFF if only for the performance increase typical compared to value lists. I use this technique because I find the value list option to be too constrained whereas portals (or list views alike) have much greater flexibility in what you can show to the user and how.

[ispytodd]

Thanks for the reply. I'll investigate that route a little more. For whatever reason I have considered popups as inelegant. A typical form may have 2-4 foreign keys that need to be populated - that could be a distracting number of popup windows. We also have a few IWP users and will be having more PHP integration.

For example, I have a 'call log' were I need to quickly select 1) company name 2) caller name 3) caller location.

thx..

[ispytodd]

I just had an ah-hah/duh moment after my last post.

I suppose I could script for one popup, with multiple related portals in it. Going left to right, each portal would limit next portals choice of related records..

-new call log record

-popup window to select foreign keys from:

portal 1 select a company from all companies,

portal 2 select a contact related to said company,

portal 3 select a location related to said company.

Still klunky for IWP users, but that's life. I could probably get a better work flow with the PHP API.

[Fenton]

Possible scenario:

1) company name*

Popup new window.** Dedicated layout of the Company table; with Find, and/or possibly filter by first few letters.

Click on name or [ + ] button in record/row to set to Call

2) caller name

Are these Contacts of a Company? Which would be a child table of Company. If so:

Filtered drop-down (or popup) of only those matching chosen company.

Value List, Show ONLY values from the 2nd field.

No duplicate names allowed for any given company (as only 1 will show).

3) caller location

Lookup of caller location, from Company_Contacts table.

or

Filtered drop-down (or popup) of only those matching chosen company, if none assigned to Contact.

*Or start the Call from a Company layout, after finding the company.

**Choice or Cancel closes window. Also test for window before opening a new one, to reuse and reduce window clutter. Oh yeah, IWP. It wouldn't really be a "popup window", it would be the only window.

[P.S. Your dedicated layout with all 3 choices makes perfect sense.]

Edited July 23, 2008 by Guest