Database Layout

[Glasstream]

I have question and it could be pretty easy but as a novice I am struggling with putting it all together.

Scenario:

I have 3 companies that each have a listing of positions. example: Director, manager, technician, trainer etc.. and the positions are identical for the 3 companies. So I have made a table for each company and the fields are the titles of the positions with info like: entry, days, amount etc.... They are also only able to access the table that is for their company.

A person from each company will enter in the data for each position and update it once a year if anything changes.

I want another table to have the director position listed on it from all 3 companies, and another with the manager from all 3 etc.. So it maybe could be used as a comparison by a 4th party or whatever and even have some calculations like averaging the amount of all 3 Director positions etc... That the companies would not have access to.

I have the 3 tables for the companies but I am struggling to pull the single fields from each of the 3 to the 1 table. Not sure if I am going about this all wrong, but was thinking of adding a field that could hold a number like 1 for director, 2 for manager etc.. and use that number to pull the records into the table and if that is something that work, how would I go about setting that up?

If I have left out any information please do ask!

Again I am still learning FM and am very thankful in advance to any response!

Using FileMaker 10 advanced

[bcooney]

Sounds like you need the following tables:

Companies

People

Roles

Assignments

You'd have a portal to Assignments on the Company Form. The Assignments table would have the foreign Company, Person and Role IDs. A portal to Assignments from a Roles layout could show you all the people with that Role for each Company (and you could filter that portal by Company, if you wish). You don't say how much of the system a person can view/edit depending on their company, but that is handled by FM Security features and scripted navigation.

[Glasstream]

What would the relationships look like? Also, how could you add new fields to say, average the 3 records from the 3 companies in a portal?

The companies could only view their table and nothing else.

Thank you very much for the reply!

[bcooney]

Perhaps this'll help. The Assignments table would have the foreign keys for _kF_CompID, _kF_PeoID, _kF_RoleID. Perhaps a date assigned, and a salary figure?

On a Role form, the calc field, Avg_Salary, would be Average ( role_Assignments::Salary). This field would appear outside the portal of Assignments. Of course, the relationship is by RoleID.

[comment]

Perhaps three tables - Companies, Positions and People - would be sufficient, assuming that one person holds one position only and that no history of person's jobs is required. BTW, I see no mention of people in the original post, and I have no idea what "entry, days, amount etc...." mean in the context of a position.

Note: if users are allowed access to one company's data only, then they cannot view averages involving other companies - at least not easily.

[bcooney]

The average is from the Role context, and the OP mentioned needing this in his first post and implied this is not restricted by account. Yes, I assumed People where needed, but if he's not talking specific assignments, perhaps he just needs the Role. I also went for Assignments, as the most open-ended design.

However, there are many more questions, such as the entire Security model.

[Glasstream]

Thanks for the replies!

The entry, days, amount would be like Title=Director, Entry=22,000, Amount=26,000 - basically a listing of what these positions basic pay scale,days worked etc... The 3 companies would not have access to the other tables because I am basically pulling the info to the web from FileMaker via php. Not a php programmer either, so was wanting the info on a layout that I could easily tie to.

What I am struggling with is on all 3 company tables I will have like 63 different positions which are identical between the 3 tables. I want to take the director position from all 3 tables and show the difference and add fields to calculate averages between the 3, so one might have Entry=22,000 and another company might have Entry=34,000 etc...

So I am trying to setup a portal and for the life of me it is not pulling the records no matter how i do the relationship. Have a Primary key and foreign key with the primary key auto enter serial and the foreign key using a look-up. Am I missing something easy here that I do not see?

Hope I am explaining this correctly,

Thanks again for the replies and help!!

[comment]

What I am struggling with is on all 3 company tables I will have like 63 different positions which are identical between the 3 tables.

You should have only one Companies table (3 records). Then you should have a Positions table (63 records) and a CompanyPositions table (3*63 = 189 records per year). The data that is specific to a position in a company (e.g. the salary of a director in company ABC in 2009) goes into the CompanyPositions table.

[Glasstream]

You should have only one Companies table (3 records). Then you should have a Positions table (63 records) and a CompanyPositions table (3*63 = 189 records per year). The data that is specific to a position in a company (e.g. the salary of a director in company ABC in 2009) goes into the CompanyPositions table.

So when all 3 companies enter there data online and it drops it into FM, into the CompanyPositions table, how would I sort which company the info was from? would it be tied from the Companies table? What would the relationships look like?

Thanks so much, as a novice I still trying to wrap my head around FM and relationships.

[comment]

What would the relationships look like?

Companies::CompanyID = CompanyPositions::CompanyID

Positions::PositionID = CompanyPositions::PositionID

See a very similar example here:

http://fmforums.com/forum/topic/50942-portal-grouping-problem/page__p__239210#entry239210

Change Organizations to Companies, Contacts to Positions, and Affiliations to CompanyPositions.

[Glasstream]

Companies::CompanyID = CompanyPositions::CompanyID

Positions::PositionID = CompanyPositions::PositionID

See a very similar example here:

http://fmforums.com/forum/topic/50942-portal-grouping-problem/page__p__239210#entry239210

Change Organizations to Companies, Contacts to Positions, and Affiliations to CompanyPositions.

Ok, I have tried to make a file from scratch to try and replicate the join-table example. Could you by chance point out what I have did wrong in my relationships? I did schools in place of companies, but it shouldn't matter what the name is.

Posting the file.

Thanks very much!

test portal.zip

[comment]

There is nothing wrong with your relationships - but you need to populate the IDs in the join table. Note also that the position title belongs in the Positions table.

test portal2.zip

[Glasstream]

There is nothing wrong with your relationships - but you need to populate the IDs in the join table. Note also that the position title belongs in the Positions table.

Ok, sorry if I'm not getting this right off. Populate the IDs in the join table, by indexing them? or what didn't I do?

Also, is there a way that I can average the fields in the portal? like at the bottom of the portal or below it, to show the average of Entry, then next to it show the average of days?

By the way, thank you so much for your help!

[comment]

It depends on how (where) you enter the data. You will notice that in my demo, if you are in the join table (Affiliations), you populate the ContactID and the OrganizationID by selecting from a drop-down list. If you are in one of the other tables, you make a similar selection in the portal (the other ID is populated automatically via the relationship).