Using data from one table numerous times in another table

  • 0
  • 2
  • Question
  • Updated 12 months ago
  • Answered
I'm struggling with table relationships.  Here's my scenario;

I have monthly goals for every employee.  At the end of the month, I'd like employees to go in and tell me whether the goal is Green, Yellow, or Red. I need to be able to pull a report/view that shows how many of each "status" per month, as well as per employee over a given time period. 

What is the best way to set this relationship up? 
Photo of Patrick MacCoubrey

Patrick MacCoubrey

  • 100 Points 100 badge 2x thumb

Posted 12 months ago

  • 0
  • 2
Can the goals and goal types change per employee and/or per month?
Photo of Patrick MacCoubrey

Patrick MacCoubrey

  • 100 Points 100 badge 2x thumb
The goals are the same usually month to month, but it is possible it changes from time to time, but they would always be either green, yellow, or red.  Does that answer the question?
Kind of... But I could have been more clear so that's probably my fault. Let me ask a couple situational questions and I'll give you a few examples of how you could structure your app to solve for each scenario...

Lets say you have a landscaping business and only have two salespeople; John and Steve.

1. When you say sales people can have different "Goals", this could mean
a.) John could have a "Revenue" type Goal, and Steve could have a "number of deals won" type goal, 
b.) John and Steve both have a sales "Revenue" goal type, but their respective "Goal numbers" could be different.

2. in either scenario, can John and Steve have more than one goal per month, and if so, can the number of goals per employee each month vary, ex. Steve has a sales revenue and number of deals closed in May, and in June, just a Sales Revenue goal...

Apologies for being so granular, but it's here's an example of how this could work if each employee had one goal per month that was of the same goal type for all employees, (like "Revenue") and the measure of that goal could change month over month...

In that scenario, you'd need at least 2 tables, Employees, and let's call the second one "Goal Reports"

 - You'd first create the field on the Employee's form for "Monthly Revenue Goal"
 - Next you'd create a Text Field field on the "Goal Responses" form and Call it Current Months Goal.... (We'll be using this later) you'll also want a text multiple choice field with Red, Yellow and Green as your choices.
 - Next you need to build a relationship between the two tables where One employee can have Many "Goal Responses" (make sure you send the Employee Name, and the "Monthly Revenue Goal" field down as lookup fields....
 - The last step is to go back to the "Current Months Goal" field on the "Goal Responses" form, and edit the field properties. You'll see a section towards the bottom called "Snapshot" where you can tell the field to get it's value from another field, and select the "Monthly Revenue Goal" lookup field as your source. What this does is allow you to change the goals on the employees table without affecting the previously created "Goal Response" records.

Now the reason I asked the questions earlier is things get a bit more complicated if there's a "Mixed Bag" of goals possible per employee, and over time, both in number and how you measure them...

Let me know how this compares to how your process actually works and we'll go from there.

Always happy to help.
Photo of Patrick MacCoubrey

Patrick MacCoubrey

  • 100 Points 100 badge 2x thumb
Thanks for the detailed response!  
Should have gone down this road from the beginning;

I have one table for employees, I created relationships for a number of other tables, that allows employees to to enter time off, expenses, recognize other employees for internal awards, etc.  I'd like to have a relationship setup with an additional table called KPI's, that would allow an employee, under the employee tab, click "add KPI", which would (as the very least), allow them type in the name of their KPI (for example, x-number of demos), indicate the final "result" (a simple text field would suffice, to accommodate different types of goals), and then a drop down to select whether it was green, yellow, or red, as far as a threshold.  

I would also need to then be able to pull a report to show what everyone's goals are, their result, and whether its Green, Yellow, or Red.

I think you're proposed solution could get me there, but I'm a self-taught, novice app-builder so it may take some playing around.

Thanks again!