12 Replies Latest reply on Nov 24, 2008 10:13 AM by TSGal

    help on finding date ranged on different fields

    rayce

      Title

      help on finding date ranged on different fields

      Post

      Good Day Sirs,

       

       

      I'm a newbie on filemaker. I am using Filemaker8.

       

      I have a problem on creating one of the reports i need.

       

      I have a layout where you have fields that are repeating on where you can enter multiple data due to its design that is vertically repeating, and same setup also on their dates...

      What i need is to generate a report where you can show all data under a specific date range and from different records and the repeating fields in it...

       

      Pls... I really need your help on this...

      I would appreciate any help...

       

      Thank you!

       

       

       

        • 1. Re: help on finding date ranged on different fields
          TSGal

          rayce:

           

          Thank you for your post.

           

          Go to the layout you want to print, pull down the View menu and select "Find Mode".

           

          In your repeating date field, enter the beginning date of the date range followed by two periods, and then the ending date of the date range.  For example, if you want to find all records between 1/1/2008 and 5/5/2008, you would enter:

           

          1/1/2008..5/5/2008

           

          Press return, and this will find all records that have a date within that range.

           

          If you need clarification, let me know.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: help on finding date ranged on different fields
            rayce
              

             

            thank you for the response...

             

            but i would like to know is how i can search all the records within a certain date range that have those fields that are repeating and generate a report from the results that will be found... 

             

            for example:

             

            record 1

              

            date(repeating field)          number(repeating field)

            09/12/2008                      12345

            10/12/2008                      23456

            11/12/2008                      34567

             

              

            record 2

             

            date(repeating field)          number(repeating field)

            08/11/2008                      45678

            09/11/2008                      56789

            10/11/2008                      67890 

             

             

             

            How can i retrieve all the date and number under 09/1/2008 to 10/31/2008 from different records?

             

             

            thank you... hope you can help me with this... i really need to get this done...

            • 3. Re: help on finding date ranged on different fields
              TSGal

              rayce:

               

              Thank you for your post.

               

              Using your example, if you only had two records, both records would be found, because you have dates within the date range.

               

              However, I think you're asking to only show those values in the (repeating) field with just those instances.  If so, then you would need to create a new table with the Date and Number fields (non-repeating) and import the records from the main table.  When importing, there will be an option to either keep the values in the same record, or split the repeating values into separate records.  Take the latter option.  With your example, you will end up with six records.  Then, you can perform the date range search to show the four records.

               

              TSGal

              FileMaker, Inc. 

              • 4. Re: help on finding date ranged on different fields
                rayce
                  

                 

                 

                Thank you again for your response.

                 

                 

                I have almost 2000 records with similar layouts and fileds, on where I need the date ranged report.

                How would I do what you said? Is there a script for that?

                I can send you the actual file on where I need the report. You can give me an email on where I can send you the file so you can see for yourself the actual layout.

                 

                Thank you and hoping for your response.

                • 5. Re: help on finding date ranged on different fields
                  TSGal

                  rayce:

                   

                  This is how you would split the repeating data into separate records.

                   

                  1. Pull down the File menu and select "Manage -> Database"

                   

                  2. Select the "Tables" tab and select the table you want to report.

                   

                  3. Click on the "Copy" button and then click on "Paste".  This will create a new table with the same name but the extension "2" added.

                   

                  4. Click "OK" to return to your database file.  Pull down the File menu and select "Browse Mode" (if you are not already in Browse mode)

                   

                  5. From the left side, select the last layout (which should be your new file).

                   

                  6. Pull down the file menu and select "Import Records -> File".

                   

                  7. Select the current file and click "Open".

                   

                  8. In the next dialog box, at the top of screen under "Source", select the original table.

                   

                  9. In the next dialog box, change the option under "Import values from repeating fields by;" to "Splitting them into separate records".  Then click "Import".

                   

                  -------

                   

                  You now have all repeating values in separate records, and all other information is there.

                   

                  For future reports, you can delete all records from the print layout and import again from the main layout.

                   

                  Let me know if you have any difficulty.

                   

                  TSGal

                  FileMaker, Inc. 

                    

                  • 6. Re: help on finding date ranged on different fields
                    rayce
                      

                     

                    Thanks again for your response.

                     

                     

                    I will try your option. I would just like to know, if your suggestion will work, (based from the example I've posted before) how can I get the corresponding number of the date from the same row of a different field?

                     

                     

                    Thank you.

                    • 7. Re: help on finding date ranged on different fields
                      TSGal

                      rayce:

                       

                      When you split the repeating values into separate records, all data for each repeating value is put into its own record.

                       

                      Here is an example....

                       

                      Suppose you have one record with the following fields:

                       

                      Name (Text)

                      Date (Date - repeating 10 times)

                      Description (Text - repeating 10 times)

                       

                      Now, let's assume we have one record with the following values:

                       

                      Name = TSGal

                       

                      Repeating 6 values for Date - Description

                      1/1/2008 - First repeat

                      2/2/2008 - Second repeat

                      3/3/2008 - Third repeat

                      4/4/2008 - Fourth repeat

                      5/5/2008 - Fifth repeat

                      6/6/2008 - Sixth repeat

                       

                      When this is imported splitting the repeating values into separate records, we end up with SIX records with the following information:

                       

                      Name - Date - Description

                      TSGal - 1/1/2008 - First repeat

                      TSGal - 2/2/2008 - Second repeat

                      TSGal - 3/3/2008 - Third repeat

                      TSGal - 4/4/2008 - Fourth repeat

                      TSGal - 5/5/2008 - Fifth repeat

                      TSGal - 6/6/2008 - Sixth repeat

                       

                       

                      Therefore, you do get the corresponding date with the same row of Description.  Does this make sense?

                       

                      TSGal

                      FileMaker, Inc. 

                       

                      • 8. Re: help on finding date ranged on different fields
                        rayce
                          

                         

                         

                        Thanks!

                         

                         

                        I've done what you said... And I think I got what I need!

                        I produced the needed report and saved it into excel and it looks just right!

                         

                        thanks for the help! I will update on the progress on my project!

                         

                        Thanks a lot!

                        • 9. Re: help on finding date ranged on different fields
                          rayce
                            

                           

                          I'm back once again with another problem.

                           

                          I did what you said and it worked just fine.

                           

                          But there's another problem that was found. In my original file there is a computation on one of the fields that is repeating. It's a calculation of numbers.

                          So when I separated repeating fields what happened was the computation was also distributed. So the output of the computation is now different. How can I get the summation of the field from the original file to retain on all the separated records?

                           

                          Thank you and Hope you can help me on this as well!

                          • 10. Re: help on finding date ranged on different fields
                            TSGal

                            rayce:

                             

                            Yes, if you have any kind of summarization of a repeating field (Sum, Count, etc.), that value will be lost when you split the repeating values into separate records.

                             

                            Before exporting, you may want to run a script that takes the computed values and puts them into another Number field.  That way, the Number field will be available for export and will not be calculated.  Therefore, create a new Number field (for this example, TotalNumber) and place it on a layout with the calculation of your repeating field.

                             

                            Next, add to the beginning of your script (if you have one for your export)

                             

                            Replace Field Contents [No dialog;TotalNumber; <repeat calculation> ]

                             

                            That is, you want to replace the contents of TotalNumber with the calculation field that summarizes your repeating field.

                             

                            Let me know if this is what you want.

                             

                            TSGal

                            FileMaker, Inc. 

                             

                            • 11. Re: help on finding date ranged on different fields
                              rayce
                                

                               

                               

                              Just want to up this thread. I still have a problem on my filemaker. The issue is still the one from my previous entry.

                               

                               

                              Please guys, I really need to get this done. Any response would be of great help. Thank you.

                              • 12. Re: help on finding date ranged on different fields
                                TSGal

                                rayce:

                                 

                                Okay.  Let's break it down even further.

                                 

                                After you run the Replace Field Contents script, are you getting the right values into the TotalNumber field?  If not, let me know what you are getting.

                                 

                                When splitting the values, is the TotalNumber field not coming across with the same value from the previous step?

                                 

                                If that is working, then what are you expecting to see?  Any example would be appreciated.

                                 

                                TSGal

                                FileMaker, Inc.