There's nothing wrong with your idea of associating a company from the contact layout. In fact, it makes a lot of sense if you have a lot of information you need to add to the contact, it's easier to do this in it's own layout rather than cram all the information in a portal row. There's plenty of ways to skin a cat (if you're into that sort of thing), but one of the easiest ways to relate a contact to a company would be to format the company id field in the contact layout as a drop down list or pop-up menu. The value list should then be set up to "Use values from field" with the "Use values from first field" set to the id field in company. You should also display a second field, usually the company name. If you set up the company id field in the contact layout as a drop down list, then you should also add the company name related field to make it visually easier for the user to tell which company the contact is associated with. If you use a popup menu, you don't need to add this field to the layout, just change the value list to "Show values only from the second field".
Thanks Mfun. I actually did exactly that prior to my post and I could not get it to work. I created a new Contact Layout from scratch and set it up again using your suggestions and it worked. I think something must have been wrong with my original Layout. Either way, your suggestions is exactly what I needed. Thanks for much for the quick reply.
While this topic's question may be "flagged as answered," if I may pose a related question...
I'm wondering and am curious...
Why not have the Company and Contact data in the same table (since they contain similar, if not the same data attributes) by relating the table to itself using a CompanyID and ContactID key?
It improves database optimization and increases search/lookup speeds.
The ideaa of using a pop-up menu will very soon turn out to be less than practical.
format the company id field in the contact layout as a drop down list or pop-up menu
It wil work if you are having 20-50 or so companies. But when you increase this number to 100 or 1,000,000 or even more you will see that using the popup menu for this purpose is not a good user interface.
But your model is still absolutely OK. I would consider going one of two ways:
- Have a button on the company layout:"Create related person" (you could call it something better than that).
- This will put the ID of the company into a variable and go to the persons layout, create new record and then enter the ID from the variable.
- Have a button on the Person layout called "attach company to this person" (again, find a nicer name:-)
- This will open a modal window on top of the existing window. This will either be a list or a layout with a portal.
- In a global field you enter some of the name of the company you want, as you write the filter will shorten down your list of companies.
- When you have found the right company you click it, and a script put the ID into a varialbe and at the same time the script should close the window and set the ID from the variable into the foreign key field of the person.
This way is better than having a popup with a very large number of entries.
My two cents on that
I wouldn't go that route for a couple of reasons,
1. A company usually has financial data , Taxnumbers and so on, a person usually doesnt
2. Companies are often known by different names, abbreviations etc, Again persons usually only have a first or last name you'd want to search on
3 If you like going graphical, Companies have a logo wich you might want to show, ah well persons can have a picture
4 established date vs birthdate
and so on, small differences that IMHO make them seperate entities.
As for search speed, i guess it's better to seperate the two there too, more tables equals less data to sift through?
Greetings and Salutations to all!
There are very good arguments for having one or two tabels. And it is imposible to come up with just one answer.
The arguments for having one table is that searching on lets say postal numbers (zip) etc. will be easier with just one table.
One argument for having two tables is that a lot of the attributes for person/company is specific for each entity. Another argument is that you can use a simpler relational model with two tables.
But this is technicalities. My point is: Onless you are having very good reasons you should probably go for two tables.
We are, in many of our solutions having one table for company/person, but not where we do not need this structure.
True, as someone recently said, there's plenty ways to skin a cat,
In that sense i also have a seperate table for zipcodes, imho these are seperate entities wich are fixed but can be connected to multiple persons or even companies.
I do agree however that making a search more user friendly does get increasingly difficult as the number of tables increases.
However the quantity of data doesnt increase as fast as you can use a single record for say all people in a household or as a mailadres for multiple workers in a company.
To those who are starting out, good luck finding what works best for your application!
Carsten was right about limits to popups, and depending on your needs you can put both company and contacts into one table. I also had a solution where it made sense to have 3 tables, contacts (which was used everywhere) and people and companies related to it. Like I said, many ways to skin a cat, I personally think we shouldn't overly complicate things for a 'newbie', I'm sure he's happy to try and work things out for himself.
While I've been ramping up on researching as much as I can find on FM with the goal accelerating my learning curve, I had not heard of the FM Starting Point or Biz Tracker demo/template databases before. In taking a quick peek at them, wow, they truly will be educational for learning FM conventions and functionality so that I can slowly begin developing our GAAP Standard Double-Entry Accounting ERP app that will eventually replace SalesLogix and our legacy accounting system.
While I have downloaded all the free open demo's from FM and purchased the FM Training Kit, those samples do not compare with the two you shared above. Don't suppose you or anyone else might know if there are any other excellent free and open templates available, do you?
Thanks so much for posting those links.
Thank you all for your thoughts and comments on my above inquiry. Yes, there are plenty ways to skin a cat in database design.
For newbies that are just learning about both database design architecture and FM, perhaps the Abstract Normalization ("AN") model is not the place to start so please forgive me if I shouldn't have posted my inquiry in this newbie topic. The intention was to be helpful with a concept for having one "Names" table to contain both Company and Contact data.
FWIW and IMHO, Abstract Normalization is a cool model to employ because it resolves the redundancy of data subsets that seemingly can creep into a database. For example, Vendors are a subset of Company where Contacts would be a subset of both Company and Vendors. By using AN, our design construct only employs one table, Names, where the relationships and a ParentID, ChildID and "NameType" field drive much of the data table accessing requirements.