1 2 3 Previous Next 31 Replies Latest reply on Apr 8, 2014 4:35 PM by JamesAllen_1

    NEWBIE - Basic help required!!



      NEWBIE - Basic help required!!


           Hi All,

           apologies for this probably being a very basic question, I'm just finding my way around FMP, but in reality have a background in Excel, and the knowledge just doesn't transfer that well!

           I am looking to set up a DB that has what i think will be 2 basic elements, A list of people, and a list of achievements. 

           For the achievements I'd like to be able to have a form view where I can add these achievements to 1 at a time (think I've achieved this)

           I'd then like a separate form view where I can add people to it, and based on criteria (such as job role) it displays in a portal the relevant achievements. (think I've managed to do this)

           From within this portal, I'd like to be able to click a checkbox alongside the achievement, and have it add the 'points value' for that achievement to the employee.

           This could then be ranked etc in another portal (preferably in the same view so that I could jump to the other people from within that view.

           I have managed to make it 'look' like I want, but I have 2 things I can't seem to get working, and although I'm sure it's simple, I simply can't make it do it... 

           1) I can't seem to make the running score 'stick' to the employee in any way, it seems to exist for each employee of that type... for example, if I move between different 'Managers' the points value stays the same and sticks, if I move to a different job role say 'Employee' it shifts, but stays the same for each of these/

           2) The Portal that I have trying to display a 'League Table' seems to work OK using a self-relating portal (apologies if thats not the right term), and will filter based on role, and sort, but it won't let me jump the main window to that employee (I set up a clear button over the top of the portal row with a 'go to related record' script. Also it isn't reading the correct score for any employee... in fact only the top portal row has any data at all, although this could be because of fault #1

           Any help you can provide, would be gratefully appreciated.... I know FMP is a powerful tool, but I'm just stabbing around in the dark a bit at the moment!




        • 1. Re: NEWBIE - Basic help required!!

               You have a many to many relationship. Any given achievement can be awarded to any number of employees and any given employee can earn a number of different achivements. This requires a third table to serve as the "join" linking a specific employee to a specific achievement.

               Start with these relationships:


               Employees::__pkEmployeeID = Employee_Achievement::_fkEmployeeID
               Achievements::__pkAchievementID = Employee_Achievement::_fkAchievementID

               You can place a portal to Employee_Achievement on the Employees layout to list and select  Achievements records for each given Employees record. Fields from Achievements can be included in the Portal to show additional info about each selected Achievements record and the _fkAchievementID field can be set up with a value list for selecting Achievements records by their ID field.

          • 2. Re: NEWBIE - Basic help required!!

                 For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

            • 3. Re: NEWBIE - Basic help required!!

                   Thanks so much for your help... looking at your notation, I can see how these identifier fields are what I've been missing.

                   Would I just need to have the 'Employee Achievements' Table as just a bridge between the 2 form views I actually want, or would I need to have any actual fields in there? Such as 'Total Score' or similar?


                   Thanks again,




              • 4. Re: NEWBIE - Basic help required!!

                     It depends on your data and what you want to do with it. It's often useful to set up summary reports on layouts based on the join table as this allows you to include fields from all three tables to create your report. A summary field that totals the points values could be useful, but a calculation field using the sum function and defined in the employee table could compute the same total.

                     And you may or may not need a points field in the join table. If you might at some time in the future need to change the point value of an achievement without changing the points earned in the past, a field in the join table that looks up or auto-enters the value of the specified achievement would be useful.

                • 5. Re: NEWBIE - Basic help required!!

                       Thanks again!

                       Is there a reason why my self-relating table (showing the league table) is not allowing me to change the record on show in the main form view? Should this be pointed at another table occurrence? ( at the moment its pointed at a copy of People.


                  • 6. Re: NEWBIE - Basic help required!!

                         What self relating table?

                         What League Table?

                         I only know what you include in this thread and this is the first you have mentioned about them. I'd need a more detailed description of what you have set up before I could suggest an answer.

                    • 7. Re: NEWBIE - Basic help required!!

                           Sorry, I'm not being clear...

                           This was what I was referring to in the 2nd fault I have found.

                           In essence, from within the People view, I'd like to be able to have a Portal that displayed the Name, Role and Total Points for each person (sorted by Points Total) and be able to click on a person in that portal, and have it jump the main window to that person to see their details (i.e. what achievements etc they had accomplished)

                           Does this make sense?

                           thanks again,


                      • 8. Re: NEWBIE - Basic help required!!

                             If you link your Employees table to a second occurrence of Employees using the X operator, you can set up a portal to that second occurrence and it will list all employees. The total points part will depend on how you set up your fields to compute that total. To sort on it, you'll need a calculation field in employees that computes that value.

                             A button in this portal row (or you can select all the fields and use button setup to turn the entire row into a button) can be used to perform a Go to Related Records to pull up employee record on the same layout or any other layout based on the employee table.

                        • 9. Re: NEWBIE - Basic help required!!


                               thanks so much... I'm going to plough through it all tomorrow and try and put your great advice into practice.

                               I really appreciate all your help.


                          • 10. Re: NEWBIE - Basic help required!!

                                 Hi Phil,

                                 I'm still struggling to make it work. I've gone and recreated a new DB just to try and get the functionality straight using your method, but still must be missing something. Here is a list of the relationship setup I have to deal with the portal that is supposed to be displaying the achievements that are applicable to that employee (currently it remains blank)

                                 What am I missing?

                            • 11. Re: NEWBIE - Basic help required!!

                                   Ok, so inline screenshots aren't a thing on this Forum! ;)

                                   here's the link to the file.

                              • 12. Re: NEWBIE - Basic help required!!

                                     Looking at it, 

                                     I think the bit I'm missing is how the Portal knows which records from Achievements to display. (I'd like it to decide by the Job Role assigned to that person) But I have nothing telling it to do that?


                                • 13. Re: NEWBIE - Basic help required!!

                                       I find that the "upload an image" controls below "Post a New Answer" work very well for uploading an image as long as I keep to the requirements posted next to those controls.

                                       To link a record in People to a record in Achievements requires that you create a record in Employee_Achievement--the join table that has the ID from People and the ID from Achievements. Normally, you can enable "allow creation of records via this relationship" for the People to Employee_Achievement relationship and then format the _fkAchievementsID field as a drop down list of Achievements using __pkAchievementsID as the source of values for the first field in that drop down list's value list.

                                       This allows you to select one achievement at a time for the current employee.

                                       I don't know how you want "role" to affect this process, but it can be done. The list of achievements in the value list, for example, could be a conditional value list where only the achievements for a selected role appear in the value list.

                                  • 14. Re: NEWBIE - Basic help required!!

                                         Hi Phil,

                                         I'm sorry to be so needy of your attention, but I just can't seem to make this do what I want, and must have missed a beat somewhere along the way.

                                         I'm assuming that when I add a record in either Employees or Achievements it going to create a new record in Employee Achievement? This isn't happening at all (I have enabled creating a record via this relationship) The other bit that I think could be hindering it, is that I'm not able to find a way to tell the document that the employee has achieved a particular achievement, mostly because i'm unable to pull the achievements through the join table to the employee view? If I could get them to be viewable, I can see that then by clicking a checkbox (or whatever) it could perhaps trigger to create that record in the join table and therefore remember it throughout for each employee.

                                         Is there any screenshots I can send you that might help you, as I realise I'm probably showing a good deal of naivety in my terminology which probably isn't helping matters!

                                         thanks again for your help and patience


                                    1 2 3 Previous Next