1 2 Previous Next 28 Replies Latest reply on Nov 8, 2011 5:35 PM by imedic

    Trouble with finding records based on dates

    imedic

      Title

      Trouble with finding records based on dates

      Post

      So basically I have a database that has 3 tables. Personnel, Events, and Registrations. It is a training database. I am trying to pull a report that will give me classes the a person has attended for a specific date range. Everything I have tried has failed. I would be happy to answer any questions to help get this solved and working.

      I have the report running and setup like I want but I want to constrain the classes listed to certain dates that the user can enter. 

      I tried google and spent several hours trying different things.

      Any help would be greatly appreciated.

      Untitled.jpg

        • 1. Re: Trouble with finding records based on dates
          philmodjunk

          First an obvious question, but gotta be sure: Is Event Date a field of type date? (amazing how many newbies use a text field...)

          Must assume that you have this set of relationships:

          Personnel----<Registrations>--------Events

          If you base your report layout on the Registrations table, then you can enter a date range by entering first and last dates into a pair of global fields, then a script can find all Registrations records related to Events records with event dates falling in that range.

          IF [globals::Date2 > Globals::Date2 //confirms a date range was entered ]
            Go To layout [ Registrations ]
            Enter Find Mode [] //clear the pause check box
            Set Field [Events::EventDate ; Globals::Date1 & "..." & Globals::Date2]
            Set Error capture [on]
            Perform Find[]
            Sort [restore ; no dialog ]
          End IF

          Global storage must be specified for Date1 and Date2 in order for this to work the way I have written this script. I've put them in their own globals table, but you can actually define them in any table in the database and they'll still work.

          • 2. Re: Trouble with finding records based on dates
            imedic

            Yes it is set up as a date field and I have set the fielgs as global. I think that something with my relationshop is messed up because it is not working. Im scared to mess with the relationships becuase everything is working except for this. I have attached the relationships below.

            I am a newbie, but I am learning (I think)

            Thanks for your help in advance.

            • 3. Re: Trouble with finding records based on dates
              philmodjunk

              What happens when you run the script I suggested?

              As with almost all Filemaker scripts, the layout from which you start and the layouts specified in the script and report layout are crucial. Specify the wrong one and it won't work.

              • 4. Re: Trouble with finding records based on dates
                imedic

                I get the report below. It does not exclude the 2009 date when I enter dates from 2010-2011 and it pulls the report for everyonoe instead of just 1 person. My script is below.

                If [Registration::End Date 1 >= Registration::Start Date 1]

                   Go to Related Record [Show only Related Records; From Table: "Registration", Using Layout: "Recertification Report Search" (Registration)]

                   Enter Find Mode []

                   Set Field [Classes:Event Date; Registration::Start Date 1 & "..." & Registration::End Date 1]

                   Set Error Capture [On]

                   Perform Find []

                   Sort Records [Restore; No Dialog]

                End If

                 

                • 5. Re: Trouble with finding records based on dates
                  philmodjunk

                  The set field step references the wrong table occurrence. It should read:

                  Set Field [Classes Event ID::Event Date ; ...

                  The find only finds on date it does not specify a specific person. The go to related records step you have specified does not do any thing but change layouts as any find performed pulls up a completely new set of found records.

                  To make use of that GTRR step to limit records to one person, change the Perform Find step at the end to constrain found set.

                  That should enable your script to work for all cases where the current Personnel Record is linked to at least one class. It will fail if they are not signed up for any classes. It would be safer to insert an If block just after the GTRR step:

                  Go To Related Records [ ...
                  IF [ Get ( LastError ) = 0 // there really are related records]

                     #Put the rest of your script here

                  End IF

                  • 6. Re: Trouble with finding records based on dates
                    imedic

                    Ok I modified my script as you said, but get a related field error. I have attached my layout list for you.

                    I really appreciate your patience and all of your help. This is my last thing to be done and it is kicking my butt.

                    I have this script that works to pull records for 1 person but it doesn't do anything with dates. It just gives all the dates for that person. But it does work to only pull records for 1 person.

                    If [IsEmpty (Registrations Contact ID:Event Name)]

                         Show Custom Dialog ["Oops!"; "This person has not registered for any classes."]

                         Exit Script []

                    End If

                    Go to Related Record [Show only related records; From Table "Registrations Contact ID"; Using Layout "Recertification Report 2" (Registration); New Window]

                    Adjust Window [Resize to Fit]

                    Sort Records (Restore; No Dialog]

                    Enter Preview Mode [Pause]

                    Close Window [Current Window]

                    • 7. Re: Trouble with finding records based on dates
                      philmodjunk

                      Tell me more about the related record error.

                      What layout are you one when you perform this script?

                      What exactly does this error say and when does it appear?

                      If you have a copy of FileMaker Advanced, you can enable the script debugger and then run this script so you can step through it and see exactly where the error appears. If you do not have FIleMaker Advanced, you can temporarily add some Show Custom dialog steps to your script to see which ones appear before the error message appears.

                      • 8. Re: Trouble with finding records based on dates
                        imedic

                        PhilModJunk,

                        Thank you soooo much for your help. It turns out that after I made the changes and reading you last post, I went back to verify my layouts. I change a layout to be base off another table and now it is working perfectly. It sorts by person as well as eliminates the dates not within the date range entered. 

                        Thank you so much again for all of your help and patience!!!

                        • 9. Re: Trouble with finding records based on dates
                          imedic

                          Ok, So one last thing. Everything is workin fine, but when I bring the report up in Preview mode, it defaults to the 1st record and not the found record.

                          • 10. Re: Trouble with finding records based on dates
                            philmodjunk

                            Preview will show the first record in the found set, not the current record. It assumes that you will be printing with the "records being browsed" option. THus the record you see on the page may differ from the record you see in preview. If you click on the book control, however, you should see that the preview can change from page to page to show you the entire set of pages that would be printed should you choose print.

                            If you are only seeing one record per page, make sure that View as List is specified before you enter preview mode.

                            • 11. Re: Trouble with finding records based on dates
                              imedic

                              Ok. So i can't for the life of me figure out why this isn't working. The first script I have "Recert Report All Dates" Returns results for the user by clicking on Recrtification Report. It works perfectly.

                              When I try to throw the date selection in it doesn't return to the person, only the dates. I am really lost right now. I need to walk away for a little bit I think.

                              I am putting up the scripts I have for both. I appreciate all your help!

                              • 12. Re: Trouble with finding records based on dates
                                imedic

                                How can I make this not only display certain dates, but also to 1 person?

                                • 13. Re: Trouble with finding records based on dates
                                  philmodjunk

                                  What is the relationship used to make the Go To related records step happen?

                                  I've assumed that relationship matched the current record for a specific person to all of their registration records. If that is the case, then your script should only pull up records for that person and the constrain found set step should reduce that set of registrations records to only those falling in the specified date range.

                                  • 14. Re: Trouble with finding records based on dates
                                    imedic

                                    Can I just send you the Database so that you can look at it and we can go from there? I don't think that I am explaining very well.

                                    1 2 Previous Next