12 Replies Latest reply on Oct 9, 2012 8:13 AM by pillar133

    Multiple Record Question

      Hi,

       

      Very new to FileMaker and as many others here have a deadline for a project. The project I'm working on is for a payroll system in which shift foreman will use an ipad to enter in daily exceptions for workers, for example: If a worker is usually supposed to be digging a hole and for the particular day they are painting a wall, the foremen needs to record that information on the app and sync it up at the end of the shift. They have a pre-existing Oracle database that contains information about each employee: A "crew" number, their payroll check number, birthdate, and a couple other things. I will have this information populate the form based on the crew ID or Employee name they enter. My question is, is there a way to have multiple records on the same form. for example, if I have an area to enter the Crew ID and they enter the number 005: this crew will have 4 people on it, Jeff, Mike, Tom, and Roger. Is there a way to set the forms up so all four of their records populate with the information from the Oracle database and then they finish by entering the exceptions to their work day? This would be in contrast to entering the Crew ID and only being able to view and enter the data for one individual at a time. Any help is appreciated.

        • 1. Re: Multiple Record Question

          Bump.

          • 2. Re: Multiple Record Question
            p_pohlhammer

            This sounds like an opportunity for the portal tool. You'll need separate tabel for crew information and employee info. The employee table should have a field for the crew ID. (In FileMaker parlance this might look like k_f_CrewID.) In relationships create a relationship between these two tables using the CrewID fields where CrewID = k_f_CrewID. In the edit layout view select or setup a form for the crew. Once you have setup the fields from the crew table click and drag the portal tool into the layout area. Now you can define exactly which fields from the employee table you want to appear in the layout. Once you have saved the layout and exited the edit mode you will now see all the individual employees for each crew. When you scroll to crew 005 you will see Jeff, Mike, Tom and Roger assuming that you've included 005 in their k_f_CrewID fields. Hope this helps.

            • 3. Re: Multiple Record Question

              Going to give this is a shot and see how the results go. Thanks!

              • 4. Re: Multiple Record Question

                Okay, haven't got that to quite work yet. I think I'm missing something to get those fields to populate.

                • 5. Re: Multiple Record Question
                  p_pohlhammer

                  Have you created the relationship between the two tables? Do the employee records in the employee table include the CrewID numbers? These are two of the key steps that may not be complete yet.

                  • 6. Re: Multiple Record Question

                    I have not created the relationship yet. I'm having a hard time understanding how to do that. The excel file that I have imported to create the first table (named 'Cybershift') includes both the CrewID Numbers and Employee number. I have created another table named 'EmployeeCrew'. I'ver tried a few things to get that to work, but im not sure how to relate the two properly, most of this is very new to me. I guess what Im confused about is how do I correctly populate it with records. Do I do another import of the exact same excel spreadsheet? or do I hand enter the records?

                    • 7. Re: Multiple Record Question
                      p_pohlhammer

                      It is possible to import your Excel records into separate tables. You'll have to do the import twice, once for each table, but it's pretty quick. First make sure you've got the FileMaker tables created, even if you leave them blank to begin with. Open the first table say the crew table, go to File / Import Records / File and select you Excel file. At this point you should have the "Import Field Mapping" window open. In this window you can select the specific crew fields from Excel to bring into the Crew table. The Excel field is on the left, FileMaker on the right. Import only those fields with crew info. You'll repeat this process for the employee data. Select "Add New Records" and check the box at the bottom that indicates that the first row in Excel is field names.

                       

                      Once you've done this for each table and imported the appropriate data for each go to File / Manage / Database. In the "Manage Database" window that opens click the "Relationships" tab at the top. You should see all the table you've created so far including the Crew and Employee tables. In the crew table click and drag the CrewID field to the k_f_CrewID field in the employee table or whichever field you've setup for the CrewID in the employee table. By default this creates a relationship that looks for equal values in these two fields. Your portal fields in the crew layout should now populate with the crew members for each crew.

                       

                      There is a tutorial that explains this process pretty well. In FileMaker Pro 11 go Help / Product Documentation / Tutorial. (It's probably the same in other version as well.) This is a PDF document that you can save and/or print. I printed a copy and found it very helpful for learning FileMaker Pro.

                      1 of 1 people found this helpful
                      • 8. Re: Multiple Record Question

                        Ive pretty much started from scratch. I created a table called employee and another called crew. I imported only the crewID to crew table and then CheckNumber, EmployeeName, ClassifiedJob, JobDesc, CostCenterID, ContractInfo and e_c_Crew fields to Employee table. i created a relationship between crewID and e_c_crew fields in the two tables. I am still not getting the portal to populate with information. I am getting more and more lost.

                        • 9. Re: Multiple Record Question
                          p_pohlhammer

                          What values are in the e_c_Crew fields? I gather that each record in the Excel file includes each of these fields [crewID, CheckNumber, EmployeeName, ClassifiedJob, JobDesc, CostCenterID, ContractInfo and e_c_Crew]. If this is correct, and the e_c_crew values are not equal to the crewID the portal will not populate. The employee table must include a field where the crew number can be typed. If the crewID is 1 each employee on crew 1 should have a crewID field with the value 1. If e_c_Crew is the field youwant to use for the relationship then it should show 1 for each employee on crew 1, 2 for those on crew 2, etc.

                           

                          Can you send upload a sample of a crew and employee record, and an image of your relationship graph?

                          1 of 1 people found this helpful
                          • 10. Re: Multiple Record Question

                            Starting to worry that what I need to is too much for filemaker. Here's an upload of what my excel records that Im importing look like and the relationship. Ive changed the names of the fields in file maker. Payroll is CheckNumber, Department (Crew) is CrewID.

                            Screen Shot 2012-10-08 at 3.13.09 PM.pngExcelSS.png

                            • 11. Re: Multiple Record Question
                              p_pohlhammer

                              Based on the info you provided I took a minute and created a sample of what I think you're looking for. I can't attach the file here, but if you'd care to post your email I'd be happy to attach it and send it along. I've only included 6 of the sample names and I can't see any last names or payroll information. However, You can import the rest of the records once you see how its set up. When you do examine the check boxes in the dialogue window and you'll see selections to update current records and add new ones. Hope this helps.

                              • 12. Re: Multiple Record Question

                                Absolutely, DrewRichardson2@gmail.com. I really appreciate the help. I've been toying around and have been able to get halfway desirable results, but not entirely what Im looking for.