Hey Dave –
here's a small sample database to show you two techniques for creating join records; first one's an entry method using a popup, the second is more advanced,
but far more flexible, and not that complicated once you “get it”. There are lots of comments (and pictures, too!) on the layout and in the (few) scripts.
Hope this helps. Nice weekend and “Gesundheit”.
Join_SimpleAndABitLessSo.fp7.zip 141.2 K
thanks for the reply. the attachment wouldn't open, has some problem with the filename fp7.zip.
That's odd. Try renaming the archive.
It opened fine for me...Nice job erolst. It may be your virus software blocking it. Try right-clicking and extract all.
Why don't you post a version of your existing database?
erolst, Steve, Mike;
Many thanks. I got the zip to open finally and as a newb the example is way over my head sorry to say.
I'm attaching a screen shot of the layout I'd like to use and a portion of the relationships showing the join table.
I have a backlog of data entry to do and my project teammates are getting a bit irritated at the delay.
The only thing holding me back from implementing further data entry and use is my confusion on setting up the tables, portals etc so I can:
-when entering a new record, fill person fields, then in the company portal add a company name and have its details fill in
-or if the company is not yet in the db then create the full company entry on a company tab or layout so it appears in the portal
Am I going about it the wrong way? should I not use a company portal but something else? or have the company portal point to the join table companyID, not the Company table companyID?
[well, I was about to attach the screen shots but I don't see an attachment button here... maybe it comes up after clicking "Add Reply"]
ok, I had to post it and then edit it and click Update.
1) You're correct; your "Company" portal needs to point to the join table, not the Company table. (That's why you keep inadvertently creating Company records.)
2) You can't create a new Company record by going to a different tab on the same layout. Every layout is based on a single table occurrence (TO). Tabs don't change that; all they do is allow you to organize the information. So if you want to create a new Company, you'll need to switch to a layout based on the Company TO.
3) You can put whatever fields you want in the join table portal, so long as they're related. You can't add the company name directly (because that field isn't in the join table), but you can add the ID and have the database reflect the name from the related Company table. erolst has already shown you two methods for doing that. Just keep in mind the only way you can create the join record is by adding the company ID and the person ID to a join table record. Everything else lives in the parent tables.
Hope that clears it up.
1) OK, I made a new company portal with companyID from the join table, then added fields from the Company table for company name, etc. When I enter a companyID, the other fields fill in properly, hurrah! Downside is, I need to know the companyID and I can't make new company records this way.
2) Prior to using the join table, I had one layout initiated with Person fields, then added all the Company fields and I was able to type-tab-type all the way through for data entry as hoped. With this join table in place now it seems I need to enter the Person data then mouse over or find the keyboard shortcut or button for the company layout, enter the data, find the companyID assigned and go back to Person and put in the companyID... ??
3) Thanks for pointing this out, that helps make sense of the join table and portal functionality. As noted, I'm struggling a bit with erolst's suggestion, I like the pop-up idea but have to look it over again.
So there's no way to have a Company portal and enter the company name and have ID and other fields fill in, drat!
Getting close to usability, dave.
1) The company ID can be presented in a value list, along with the name as the second field. That's the simplest way to do it (although not necessarily the most user-friendly or idiot-resistant way). New company records can be created via scripting, where you spawn a second window with a new Company record prepared for the user to fill out.
2) You can't just tab to the new Company data through the join table, true. (The reason it worked before is because the first related record is what shows in a related field. A join table doesn't give you that option.) In order to have the "new" option, you'll need to script the creation of a new company (such as the secondary window idea I mentioned above), then have it appear in the list of options. I often add a "New Company" option to the pick list so the user can create a new entity on the fly.
Conceivably, you could use the company name as a key field, but it's a bad idea from a relational standpoint. What happens if you have two Company records with the same name (e.g., two branches, or two locations)? Then the database doesn't know which one you're talking about and your relationship breaks. Keys need to be unique. (It's also a really bad idea to allow users to have access to edit the key field, because if they change it after a relationship is established, they'll break the joins and all sorts of bad things happen.)
Thanks for spending so much time and sharing so much thought, Mike, I think I'm about where I need to be, I've experiemented along with your instructions and it's coming together.
1) That's a nice presentation, now I know what that option means, and it gives me other ideas.
2) Darn. Not a veteran DB user, had hoped to do all the data entry in one window but I see why that won't work now. I plunked around in Manage Scripts and put three steps together to do just what you indicate there, and attach to a button at the end of the Person entry, so that should work.
Good point about the companies and locations, that is true on the cards I have, there could be one Person with two business cards, each with a HQ and other sites. Gads!
I also discovered that my Company layout was based on the Person table, which I'll fix even though I'm not clear on why it's a bad idea. No need to explain, as they say, 'my brain is full' for now!
Got to take the ideas here and in the test db and put them to work in the real one.
Again, Many Thanks for leading me to a workable solution, Mike n erolst.