1 2 Previous Next 18 Replies Latest reply on Apr 15, 2011 8:21 AM by EmilyShep

    Show all records for each person for a specific date?

    EmilyShep

      Title

      Show all records for each person for a specific date?

      Post

      I have a bunch of records for a group of people but I want to have a table in table view that will show all records for the guy for a specific date.

      I don't know how to get multiple records to show for one guy and not sure how to go about sorting it to the specific date that can be user defined.

      Please help!!

        • 1. Re: Show all records for each person for a specific date?
          Frinholp

          Hi Emily

          1 Switch to table view

          2 Click on Find

          3 Enter the guys name in your name column.

          4 Enter the date you wish to search for under the date column

          5 Click Perform Find

          This is the most basic way to extract the data.

          If you have anymore requirements on the way you would like to extract this data let me know.

          Lee

          • 2. Re: Show all records for each person for a specific date?
            EmilyShep

            Thanks I have more of an idea now how I would like to do it however I want it to be a script, and I want it to show ALL the guys in the table view.

            For example:

            Name: Bob D

            Codes

            1020              1030             1145        4515

            Hours

            4                     16                9               6

            Name: Rick S

            Codes

            1045              1635             1340        4010

            Hours

            5                     3                18               9

            etc...

            • 3. Re: Show all records for each person for a specific date?
              Frinholp

              So you want something similar to a dialog box that asks you to enter the search date? User enters the date and the script finds all records for that date for all the guys?

              Let me know if I understand you correctly.

              What about the girls?

              Lee

              • 4. Re: Show all records for each person for a specific date?
                EmilyShep

                Haha could be girls too! I just say guys because I work on a construction site and its all guys.

                Yes that is exactly what I want. But I don't want it to show:

                Bob D     Code 2130  Hours 4

                Bob D     Code: 2210  Hours 8

                Bob D     Code: 1415   Hour 9

                I want it to only have his name once but display the codes and hours combination (could be up to 20 different codes).

                Thanks.

                • 5. Re: Show all records for each person for a specific date?
                  philmodjunk

                  How are the records structured in your table?

                  Do you have one field for code and one field for hours or do you have separate hours fields for each code?

                  If you have one field for the code and one field for the hours so that each record reports one person's hours under one code, this format report is much simpler to set up. (What you described can be done, but takes a lot more design work to set up.)

                  Bob D
                      Code      Hours
                      2130       4
                      2210       8
                      1415       9

                  John S
                      Code      Hours
                  //and so forth...

                  • 6. Re: Show all records for each person for a specific date?
                    EmilyShep

                    Yes I have one field for hours and one field for codes. I used a portal to input the hours and a drop down list for the codes.

                    What you show in your example is exactly what I would like to do.

                    • 7. Re: Show all records for each person for a specific date?
                      philmodjunk

                      What I last posted is an example of a summary report. Next question, Does "Bob" have one record for each code or could he have multiple records for the same code and you only want the total for each code?

                      We'll start with the basics:

                      Create a new layout for this table, enter layout mode and add a sub summary layout part to it. (See part setup in the Layouts menu.)

                      Specify the name field as your "when sorted by" field for this sub summary part.

                      Put The Name field and the field labels inside this sub summary part. If you have just one record for every code, you can then just put the code and hours fields in side the body and you are basically done. Perform a find specifying just the date in your date field, then sort the records by the name field to group them and make the sub summary part appear as the appropriate sub header for each group of records.

                      If you have multiple records for the same person and code, we need one more field defined in your table. Define a summary field, sTotalHours as the total of your hours field. Now enter layout mode and double click the body layout part's label to bring up part set up. Change it into a sub summary part sorted by your Code field. Place the Code field and STotalHours inside this second sub summary part. Now perform your date find as I described earlier, but now sort the records by both name and code to get both sub summary parts to appear.

                      If you want to explore the possibilities of summary reports further, you might check out this tutorial on the subject:  Creating Filemaker Pro summary reports--Tutorial

                      • 8. Re: Show all records for each person for a specific date?
                        Frinholp

                        Phil I think the codes represent job type or pay rate rather than a percode.

                        I'm not too sure how relationships have been set up but if there was a Person table and a Hours table (Hours table holds number of hours and code) would it not be simplest just to display the hours in a portal on the Person layout?

                        Is the layout to be printed and that's why you want it set out like that?

                        Lee

                        Edit: Ignore this. Phil posted as I was typing

                        • 9. Re: Show all records for each person for a specific date?
                          EmilyShep

                          Ok so I set up the summary report and it looks like how I want it but I can't get the find to work in my script. When I do it it changes completely how the report looks.

                          Is there any way I can select the date using a drop down calender for the records I want to see instead of typing it in manually? or maybe even choose from a value list of the dates that exist?

                          • 10. Re: Show all records for each person for a specific date?
                            philmodjunk

                            Make sure that you sort the records after performing the find to get them back into the sort order needed for your report.

                            You can format date fields with a drop down calendar. Select the field while in layout mode and use the Control Style drop down on the Inspector's Data tab.

                            You can also specify a drop down list from a value lists that draws its values from a field in a table in your database. See Manage Database and try the Specify field option.

                            • 11. Re: Show all records for each person for a specific date?
                              EmilyShep

                              Thanks, sorting the records after the find fixed my problem.

                              I know how to make a drop down calender/value list for a field but my question is, when I perform the find, I want the user to enter a specific date to pull up those records. I would really like to be able to provide a calender or value list in my "Show Custom Dialog" input option... is that possible? Or does the user have to manually type in what they want?

                              • 12. Re: Show all records for each person for a specific date?
                                philmodjunk

                                Show Custom Dialog is very limited. You can't apply formatting to the input fields in it nor can you perform any error checking until after they dismiss the dialog.

                                You can simply switch to a different layout or you can use New Window to open a new window with a layout you specify for the new window. Since these approaches use standard FileMaker layouts you have full up field formatting and validation checks you can use. (I rarely use Show Custom Dialog for anything but plain text data entry anymore due to these limitations.)

                                Here's a file I share out on Known Bugs: http://www.4shared.com/file/8orL8apk/FMP_Bugs.html

                                It uses New Window in several places to collect data from the user. (Try clicking New on the main layout.)

                                If you try to replicate this approach, be careful as it uses an infinite loop with User Abort disabled to make the pop up windows "modal". If the buttons that close this window do not use Halt Script as a button option or a step at the end of the script, you can't terminate this infinite loop without force quitting FileMaker.

                                • 13. Re: Show all records for each person for a specific date?
                                  EmilyShep

                                  Ok so it turns out my find actually isnt working, its just pulling up the most recent date.... instead of the date I type into the find command... If I were to go the route of "new window" how would I get the find to work?

                                  • 14. Re: Show all records for each person for a specific date?
                                    philmodjunk

                                    Whether using Show Custom Dialog, a find layout, or new window. The following script example should work, if the data the user enters for the date is entered into a field of type date with global storage specified in field options.

                                    Enter Find Mode [] //clear the pause check box
                                    Set Field [YourTable::YourdateField ; YourTable::YourGlobalDateField ]
                                    Set Error Capture [on]
                                    Perform Find []
                                    Sort [Restore ; No dialog ]
                                    If [ Get ( FoundCount ) = 0 ]
                                       Show Custom Dialog ["No records with a date of:  " & YourTable::YourGlobalDateField & " were found."]
                                    ENd IF

                                    YourTable::YourGlobalDateField is the field you use as the input field in Show Custom Dialog or it's the field you format with a calendar pop up in the search layout and/or window.

                                    1 2 Previous Next