1 2 Previous Next 15 Replies Latest reply on Dec 27, 2011 12:43 PM by FileMakerNovice

    Populating fields in related table

    FileMakerNovice

      Title

      Populating fields in related table

      Post

      Hello,

      Thanks in advance for the help.  I am trying to make a database for interviewing new candidates.  Attached is a screenshot of my database relations.  What I'm trying to do is to go to a Pre-Work layout and it prepopulate the candidate name, prework email and manager email from the Candidate table.  Please let me know if I need to explain a little better.

      Screen_Shot_2011-12-27_at_10.47.31_AM.png

        • 1. Re: Populating fields in related table
          philmodjunk

          You don't actually need these fields to be defined in Prework if they are already defined in Candidate. The only reason you would do so is if it is important to your process to capture a 'snapshot' of this data to record what was current at the time a Prework record was created. If you do not need such a 'snapshot', you can just add the fields from Candidates to your PreWork layout. WHen you enter or (hopefully) select the Candidate ID number, these fields will then automatically display the data from the matching Candidate record.

          Note: you have a set of fields in Prework and another set in Interviewer that appear to be numbered copies of each other. That structure suggests that you may be better off to add related tables where one related record takes the place of one such field in the group and you'd create a group of such records in the related table instead of a set of individual numbered fields as currently set up.

          • 2. Re: Populating fields in related table
            FileMakerNovice

            Thanks for the quick response.  I have it now set up with your suggestions.  The Interviewers table is different, unrelated answers to questions, please ignore this for now.  I have gotten it to where I can input the Candidate ID and it populates the data.  My goal is to have a pull-down menu or something that populates when you type.  Attached is a screenshot of what I mean.  I want to push "new grade" and it go to the name field, the name field would then pull down to show the list of candidates.  Once this is selected, I'd like the prework email and manager email to populate.  Thanks again, this forum community is great.

            • 3. Re: Populating fields in related table
              philmodjunk

              Your relationship is based in candidate ID. Thus you need to put PreWork::candidateID on your prework layout and format it with your value list. This can be a value list that lists Candidate ID's in field 1 and Candidate Names in field 2 so that you can select a name in the drop down list, but have it enter the candidate ID to establish the relationship.

              There are several variations of this you can work with: You can specify that the first field (the one with ID numbers) be hidden in manage | value Lists for this value list. If you do, you should enforce "unique values" for your candidate name field or a second candidate with the same name will not appear in your list. If you use these options with a drop down list, you will see only the candidate names when you drop down the list, but the ID number will appear in the field when you exit it. You can adapt to this issue by adding the candidate::Name field to your layout. You can place it next to the drop down list or, if you use behavior settings to prohibit entry into the name field while in browse mode, you can even place it on top of the drop down list field.

              Another option is to use a pop up menu instead of a drop down list. With the "hide 1st field" option on a pop up menu, the name will appear in the pop up menu formatted field when you exit it.

              These are just the simplest ways to select a candidate record for the current PreWork record. I suggest selecting one of them in order to confirm that everything works as it should. Once that works for you, you can consider more advanced options for your value list if you see the need. You can reduce the number of candidates that appear in your list to only those being considered for the current job position and/or you can use a scripted technique that lets you enter a portion of the candidate's name to get a list of only those candidates whose name starts with or contains those letters.

              • 4. Re: Populating fields in related table
                FileMakerNovice

                Thanks so much for this.  I did not know about the showing/hiding second value.  I am going to continue to work on this but I think you got exactly what I was meaning.  Thanks again.

                • 5. Re: Populating fields in related table
                  FileMakerNovice

                  Gr... Okay, maybe not.  Is there anything special I need to do further... So a little more detail.  Each candidate will have multiple grades on prework.  2-3 people will pre-grade each candidate.  That is what the prework database is.  I cleared the pre-work database and now the pull down menu does not work.  My value list for the drop down is first field Candidate ID, second field name.  Include all values is selected, I haven't hidden the first field yet.  Sort values is selected as second field and resort is selected as english.  Thanks again, sorry for my newbism.

                  • 6. Re: Populating fields in related table
                    philmodjunk

                    And what field is formatted with this value list? It should be the Candidate ID field defined in Prework.

                    And you will need to create a new records before you select a candidate in the drop down list.

                    • 7. Re: Populating fields in related table
                      FileMakerNovice

                      Nice catch.  I changed it from the Candidate Name to the ID.  That fixed a problem with the id overriding the name.  My other problem still exists though.  I have 2 records with information in them.  I added these by manually inputing them into the Table View.  Those two will properly bring up the pull down menu.  When I create a new record, the pull down menu does not bring anything up.

                      • 8. Re: Populating fields in related table
                        philmodjunk

                        That should not happen. How have you set up the value list? does it list data from Prework or Candidate?

                        As currently defined, this value list should list all ID's and names from candidate.

                        • 9. Re: Populating fields in related table
                          FileMakerNovice

                          I'm pretty sure I did it correctly.  Here is my value list:

                          • 10. Re: Populating fields in related table
                            FileMakerNovice

                            Note:  Inspector shows edit box but I was not selected on the field at the time.

                            • 11. Re: Populating fields in related table
                              philmodjunk

                              Hmmm, why do you have the "resort" option selected? Don't see why that's a factor but neither should you need to select it in most cases.

                              • 12. Re: Populating fields in related table
                                FileMakerNovice

                                I do not know why that was checked.  I unchecked it and issue persists, unfortunately.  When I click on new record, and then click on the name, nothing happens.  When I go into Table view, create a new record, manually enter the candidate id, then go back to layout view, then select on the name field, the pull down comes up. 

                                • 13. Re: Populating fields in related table
                                  philmodjunk

                                  It all looks correct and the behavior of such a drop down should not in any way be affected by the current record or any values entered or not entered in any of its fields--which leaves me scratching my head as to how what you describe could be possible.

                                  I've tried uploading a demo file for you to examine to see if you can spot any differences, but the share site's software is currently not allowing me to upload...

                                  • 14. Re: Populating fields in related table
                                    FileMakerNovice

                                    Thanks.  That would be great if you can upload that when it's up.  Is there anything else that should change in the relationship of the Tables?  I still have a one to many relationship on Candidate ID from the Candidate Table to the PreWork Table.

                                    1 2 Previous Next