Jump to content

Finding Field Values in a one to many relationship

Recommended Posts


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. 


Share this post

Link to post
Share on other sites

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.

Share this post

Link to post
Share on other sites

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.

Share this post

Link to post
Share on other sites

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. 

Share this post

Link to post
Share on other sites

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.

Share this post

Link to post
Share on other sites

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

employee sample.PNG

Share this post

Link to post
Share on other sites

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.



Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Similar Content

    • By Asu
      Hello FM Mavens, 
      I am likely missing something. 
      I have a script step that calculates the dimensions of an image in a container field. There are many such fields so the script should work with any arbitrary one. 
      I get the name of the field by using script parameter Get(ActiveFieldName). 
      Let's say I have a container field "cont1". The script is triggered by entering the field.
      I am trying to calculate the height of the image by using 
      GetContainerAttribute (Get ( LayoutTableName ) & "::" & Get ( ScriptParameter ); "height" ) The results are question marks - I am not sure why. If I explicitly identify the fields then the expression works. 
      Tried the same expression (Get ( LayoutTableName ) & "::" & Get ( ScriptParameter )) in the script step "Set Field by Name" and it worked, which means that the expression correctly identifies the field the script is attached to. Then why is it not getting it the first step? What's wrong with the calculation? (FM17)
      (I hope I posted it in the correct section)
    • By ash1474
      Hi, I am using this calculation in portal which calculates days in portal  Date - (GetNthRecord ( Date ; Get ( RecordNumber ) - 1 ) )
      how can i modify or do something else to avoid (?) in the first row.  Sample file attached for any modification/help.
      Thanks for any help
    • By Quant
      Could someone help me with this question?
      I have a row in a portal in wich i would like to make a calculation: Bedrag= Cases*Prijs,  but if Hoeveelheid is filled in then it should calculate Bedrag= Hoeveelheid*Prijs.
      The fields are all numeric. The calculation should be possible on one row, in this example i have made it on two.

      Yours sincerely
    • By beckham
      Help Please, I am trying to figure out how to extract field contents via a calculation as follows:
      My lookup field is ABCDEFG-01-12345.jpg
      I want to automaticaly extract only 12345 to be placed into another field? I tried the Calculation Right ( Sorce field; 5 ) but that returns me 5.jpg
      any adivise is most welcome.
      Thank you
    • By Ponderosa
      So there's probably something simple, but do you guys know of a way to automate dates from one record to the next? Each record date needs to be seven days after the previous one, and I'd like for my client not to have to enter it manually. I tried doing
      Let ( Today = ( ReleaseDate ) ;

      Date ( Month ( Today ) ; Day ( Today ) + 7 ; Year ( Today ) )

      and some others, but none appear to automatically fill in. Help!

Important Information

By using this site, you agree to our Terms of Use.