Jump to content
Sign in to follow this  

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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Similar Content

    • By Rich
      Greets, all:
      I'm using this calculation--in a parent table in a field temporarily named 'Temp'--to grab course numbers from the course_number field in a related/child table:
      UniqueValues ( List ( ASSIGNMENT_COURSE::Course_Number__lxt ) )
      (I'm using this calc because the child table contains multiple records with the same course number; it's not a key field.)
      My intent is to use six buttons--in the parent table--where each button would grab an assigned row's value using the calculation above. For example, say that the above calc has this result:
      I want Button 1 to grab AST-100; Button 2 to grab EDU-100; Button 3 to grab EDU-300, etc.
      Mind you, I can't make a value list out of Temp because it can't be indexed.
      TIA for your help! 
    • By Asu
      I know. Don't use them. But There is a simple task and a repeating field would be a quick solution.
      Field 1: a number
      Field 2: a calculated repeating field, each repetition adds the repetition number to the value of Field 1.
      Can it be done?
    • By stan111
      Gents, need your help on the following issue:
      I need to track my expanses. There are 4 tables: Products, PriceChanges, Transactions.
      The problem: I need to make a script which will make calculations in transactions tab, accounting for the latest price change.
      01 jan17 I bought one bottle of wine for $1 
      03 jan 17 I bought the same bottle of wine for $1,5. So the latest price is $1.5
      In Products table there is one record which is wine. 
      In PriceChanges table there are two records: $1 and $1.5
      I need that Transactions table automatically take corresponding wine prices while adding new or past records.
    • 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
  • Create New...

Important Information

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