Hard Company Relationship

[Dr. Evil]

What is the best way to link the "operation company" to all tables in solution?

Here's kinda what I have built:

I have a table called "companies".

The company operating and using the database exist in this table along with clients, vendors, etc...

I also have a table called "home". On the home layout is where user assigns the "operation company" from a list of companies in the companies table.

So I need to know the the best way to let all the tables know what record is the operations company record. The only way I really know how to do this is to create a calculation field in each table to equal the set operations company ID from the home table. Seems like there should be a better way? Maybe not, some insight on this would sure be fantastic.

Thank you for your time!

[bcooney]

In one of my systems, we needed a default Company. To do so, we have a one record table, Prefs. On a company layout, we have a button, "Set as Default". This button runs a script that simply sets the Prefs::DefaultCompanyID to the Company::CompanyID. Since Prefs has only one record, this approach works. I also use conditional formatting to change the appearance of the company name on the form to indicate that it's the default Company.

In an Open Script, I set gCompanyID to the Prefs::DefaultCompanyID. Be aware that if a default company is changed during a user's session, it will not update gCompanyID unless the user runs a script which updates his global values. But having gCompanyID out there is very helpful.

hth,

Barbara

[Dr. Evil]

First off bcooney, I would like to thank you, you always seem to send some advise my way! Rockin!

Your "pref" table sounds like the same as my "home" table.

I'm doing something similar to what you described, but I am not using a script to set company as default rather then giving a pull down list in the home form to choose the default company.

So by doing this, how do I tell the rest of the tables in the solution know which record is the default company? You mentioned it is only a single record table, what is the importance here? My home table has no records, all fields are global.

Thanks again! :)

[bcooney]

Well, a pull down list in your home form doesn't really let the users specify a default company for the system, since your home table only has global fields. So what you're really doing is letting each user set their default company for their current session. Each user can have a different gCompanyID. Every time they login, they need to set it again. That may be fine.

The importance of a single record table (commonly called a Pref table) is that in a shared environment, it's the best way to store values from session to session. You publish these values to globals in an Open Script. If you only allow an Admin to change the default Company and broadcast any change to the default if changed, then you can be assured that each user is working with the same default company.

Perhaps you can expand on the need to "tell the rest of the tables which record is the default company?"

[Dr. Evil]

Hey! Been busy, finally got a chance to re-visit this topic.

"Tell the rest of the database who the Operating company record" 1) Well I mean when I generate reports, what ever table Im viewing records from needs to know what record in the companies table is the operation table so the report can display the Business's contact info, etc...

2) On different tables I have fields to allow user to set a Manager, Lead Artist, etc... to an assignment. So I have to populate a value list of contacts that only belong to the operating company.

I have accomplished this by setting a global in the home/preference table where relations are built to get the Operating Company's contacts. Then in other tables I have a calc field that = the global home company id.

This is a multi user solution and I could set it so only an admin account can change the operating company id in the home table.

I still don't understand a better way to do this. I can make a tip file if it would help illustrate what I need. Or if someone wants to provide a tip file on how to do this properly that would be awesome!

Thanks FileMaker'ers!

[bcooney]

Hi Randy,

I'm still not undestanding your goal. You mention that you have several companies in a table, one of which you wish to make the default. Right there, I'm confused. To what purpose do you make one company the default?

A report would derive the company info from its data. For example, if I want to see how many invoices were generated for ABC Co., I'd find for ABC Co., in Invoices and generate a report. I could put the company name field in the Header, and it would display ABC Co., since the records are for just that company.

2) If you are setting a manager, lead artist, etc., you would need set the CompanyID in that record and build a related value list to limit the manager, lead artist choices so that they display just that company's contacts. No global's involved.

So, I suppose you are thinking that the system needs a global companyID floating around that somehow drives what appears on reports or choices in value lists. I don't think you really need this. I think you just need to add CompanyID to whatever transaction records you are creating.

[Dr. Evil]

I have attached a demo file to help illustrate my goal.

My goal is simply to set the "Operating Company" in a preference table/layout and have all other tables in solution know who the Operating Company by this setting.

I am not trying to set a "Default" company. The operating company can NOT be changed on the fly and theoretically should only be set once upon first time use of solution.

I don't want the User to have to set the Operating Company every time they create a record, nor do I want to auto-fill sense it is not modifiable and is same for all records in the entire solution at all times. The operating company is a given once it has be set in the preferences.

Maybe I need to change my thinking on this? Im open to the best technique to accomplish this.

Thank you!

operating_company.fp7.zip

[bcooney]

The demo helped a lot.

When you create a Project, don't you need to know the Company that the project is for? And shouldn't this NEVER change for this project? You don't have that.

Therefore, you must store the CompanyID in the Project record. By all means have it default to the current company in the Pref table. Or, only allow new projects from a company form.

[Dr. Evil]

Glad the demo helped!

When user creates a Project, they don't need to specify who the operating company is, only who the client and vendor is (I don't have the vendor option in demo, only in full version).

The operating company info only comes into play on reports such as quotes, invoices, etc... So unlike a invoice where it needs to store information historically, the operating company could be changed from Company A to Company B and nothing would be hurt, just when User ran reports Company B's info would be displayed. Altho I don't really ever want to change operating company.

I don't need to know who was the operating company was in 2001 or any historical info.

From looking at my demo file... I'm I setting this up for failure? What do I need to do to make this solid?

[Dr. Evil]

I wonder?..

Should I create a single record table named

"Operating Company"

with child table/ "Employees"

Then also have tables:

"Companies"

with child table/ "Contacts"

Does it make sense to have four tables when logically should only be two "Businesses" and "People"???

Plus I'm not real familiar on "single-record tables"... what are the advantages/disadvantages???

this topic just gets deeper...

[GeoSteven]

Hi Randy,

It may help to describe to us what the company is. You mention clients and vendors. What role does each play in your business model? Do your users work for different companies and you want the company to be set depending on what user logs in?

Steve

[Dr. Evil]

Geo

I have a table called businesses. In this table I store Clients, Vendors and the Company. By Company I mean the establishment using the FMP Solution. For example; Lets say Pepsi is using the solution to manage their books. Pepsi/Company sells cola to Jimmy/Client and buys cans from CanCo./Vendor.

So, there is only ONE company and it NEVER changes. User can NOT set company A this project and company B for that project. It is Company A for everything. For example; Every Project will be under Pepsi the company, but will have a separate client/Jimmy and a vendor/CanCo. as well.

I ponder creating a single record table to store Pepsi's company info.... but seems redundant sense I can already create companies, contacts, address, phones etc... in the business TOG (Table Occurrence Group). As I mentioned above, not too familiar with single record tables, don't know the advantages and disadvantages.

I hope I explained this better, poor terminology on my part.

[GeoSteven]

OK - I think I understand. I would design this with 3 separate tables, one for each role. Company (me), Clients (pay me for my services) and Vendors (I pay them for their services). Company becomes the 'prefs' or control table and is related to vendors and clients via CompanyID.

Now the only question left would be is this a distributed application or a web hosted one?

If it's distributed and only a single company will have access to it (ie - instaled on their servers on their premises) then when the package is initially installed you direct the user setting it up to enter the pertinent company information. This info is placed into a single record in the 'company' table along with a 'CompanyID' (auto generated serial number). Regardless of who logs in there is only one company. No choice needed.

In my designs this would also serve as your 'prefs' table, as mentioned earlier, storing things like file locations, default system control values, etc. Being a single record you can make the majority of the fields globals for easier access in your scripts and layouts.

If it's a web based solution so that multiple companies may access it (ie - hosted by a third party other than the 'company') then you have to back into the company record from your user table. Ex - user AlexD logs in, your autoexec/login script locates him in the user table along with the CompanyiD (tied back to company) that he works for and you populate global fields that set your relationships into vendors and clients.

I'm sure all that's just as clear as mud. Hope it helps. :bye:

[Søren Dyhr]

I would design this with 3 separate tables, one for each role. Company (me), Clients (pay me for my services) and Vendors (I pay them for their services). Company becomes the 'prefs' or control table and is related to vendors and clients via CompanyID.

But all these have a fair share of common attributes, your normalization have missed this blatantly - take Dr. Evils advice it's slightly more healthy than your approach!

your autoexec/login script locates him in the user table along with the CompanyiD (tied back to company) that he works for and you populate global fields that set your relationships into vendors and clients.

I would in your case restrain my desire to use globals and start trusting the search/relations engine instead.

--sd

[Dr. Evil]

Dyhr,

You said my advice is "slightly more healthy..." what would be the healthiest approach?

Also, you mentioned abandoning the use of globals and trust search relationship? Is this part of your healthier approach? If you don't mind, can you explain.

Thank you! :

[GeoSteven]

sd - I agree Randy that more explanation would be useful. There seem to be a lot of nebulous replies in forums that the inexperienced undoubtedly struggle with. My attempt to offer a complete explanation was not a defacto schema design but hopefully offered enough information to provoke a constructive train of thought.

I would also agree with you that the 3 tables have a lot of common attributes which could/should be placed in a 'contacts' table which would be tied to the 3 tables mentioned. I will point out though that the 3 roles I described are different enough that they should be separated as described. While it is certainly possible to combine all of them there are attributes for a 'customer' that have nothing in common with a client or vendor.

[Søren Dyhr]

It's a bit late here, but perhaps reading this - could explain my being socratic?

http://www.universaldatamodels.com/Portals/9/udm_Publications_Article_Universal_PatternsforDataModeling_Beye_Newsletter.pdf

lot of nebulous replies

Which is somewhat snotty I apologize - however should the warning be conveyed somehow ... "rethinking is urged here!" Since it all can be boiled down to this test:

Is Your Model Busted?

Can your application track information for someone who works for several companies in different capacities without having to enter the same individual more than once? Can you have several contact numbers for a company, which can be organized by the role or purpose for the number? Can you have several different addresses for a company, without having to make duplicate records? Can you hold more than a fixed number of telephone numbers for a contact? If you can't answer yes to all these questions, then your application may be built on a flawed model.

So would your model fail this test or have you really hit something unusual?

--sd