Finding Field Values in a one to many relationship

[andersonk06]

Hello,

I have 2 tables (for these purposes) that look at employee records. The first, Contract_Records has one record per person per week and records extensive information about each employee week. The second, Contract_Weeks has one record per person with one field for each week to record either on or off contract. I need a calculation that finds a record in Contract_Records where Contract_Weeks::EmployeeID=Contract_Records::EmployeeID and Contract_Weeks::WeekID=Contract_Records::WeekID.

Right now I am using Case (  Contract_Records::week_ID="1"; Contract_Records::Type; "NA") and it is returning the correct value for Week_ID 1 but not for the subsequent fields such as Case (  Contract_Records::week_ID="2"; Contract_Records::Type; "NA") (this is returning NA). 

Is something preventing FMP from looking past the first record? Is there a way to use Lookup for this calculation? I do want to be able to use Contract_Weeks to change information on Contract_Records. 

Thanks!

[comment]

You neglected to explain what the relationship is between these two tables. You also did not explain what is your purpose here. I suspect you are going about it the wrong way. You most certainly should not have a field for each week. Weeks are objects and their number grows with the passage of time. Therefore, if it is necessary to track them (and that's a very big IF), it should be done by creating a record for each week. Your file's structure should be permanent and not change according to the data.

In general, any related field referenced in a calculation gets the value from the FIRST related record. If you want some other record, you need to use the GetNthRecord() function, or the Last() function. Or use one of the Aggregate functions to get an aggregate value from all related records.

[Lee Smith]

Anderson K06,

 I deleted your other post as a duplicate. Please do not multiple post your questions in the forum.

 I will send some additional information via private message in a few minutes.

[andersonk06]

Hi Comment,

 

Thank you for your response. In the heat of the moment it's hard to figure out what is necessary information. 

Using the table Contract_Weeks I am looking to create a chart that has my weeks down the side and employees across the top (or vice versa, not important). Each cell would be marked On Contract/Off Contract/ or On Furlough. This information is already recorded in the Contract_Records table but I am having trouble finding a way to display all of my 30 employees and 53 weeks in one easy to glance at chart/table that can be used for planning purposes. I know that duplicate information is a hazard but the only way I can think to create a chart such as this is to get all of a single employees 53 weeks in to a single record and since I would only be pulling this one field it wouldn't be too bad. 

I need to be able to update the Type field in Contract_Weeks and have it be reflected in Contract_Records. 

At this time we are starting a fresh database for each year so there will only be 53 weeks at a time. 

[comment]

I suggest you go further back and explain what your data represents in real life. So far the only thing I understood is that you have 30 employees - so I would expect to see a table of Employees with 30 records.

[andersonk06]

Table 1: Personnel Records: This table contains personal information for each of the employees as well as salary or wage information, it is connected to all other sheets via the PERSONNEL ID MATCH FIELD (this table holds the primary key)

Table 2: Contract_Records: This table has 1 record for each employee week, 30 employees, 53 weeks, 1590 records. It pulls salary/wage information via the PERSONNEL ID MATCH FIELD relationship. This table is my primary workhorse, I enter on contract/off contract/furlough information here as well as hours worked and other important weekly information

Table 3: Contract_Weeks: my hope was that this would have 1 record per employee and be a summary of all their weekly statuses

While this whole database is primarily designed around monitoring the active year I also need to use it for forecasting future years (we are in a field that hires people seasonally). We currently use a big spreadsheet (see attached sample) where my boss colorcodes each cell to represent the employees status, I then enter it into the database and it does the hard work of calculating expected pay per employee and department. That's all fine and dandy if you do it once a year, however we do this budgeting practice upwards of 10 times before we lend on a final budget. I need to recreate something that will allow my boss to easily play with the weeks each employee is working and have it automatically update the salary numbers. 

I currently have a portal that displays a brief sum of each week (see employee sample) but really need to see everyone together as it's a very tightly woven puzzle. 

Does that make more sense?

Prod Schedule Sample.pdf

[comment]

See if the attached skeleton file gives you any ideas.

Note :

1. No empty records are required;
2. Any number of years can be accommodated.

 

WeeklyOverview.fp7

[andersonk06]

This certainly gives me ideas. I will have to look into it in more detail tomorrow.

Thanks!