1 2 Previous Next 17 Replies Latest reply on Jan 6, 2009 1:04 PM by TSGal

    Records found not appearing in report

    BG

      Title

      Records found not appearing in report

      Post

      I have a database with related tables and on the reports I run the information from the first record is all that appears on the report regardless of my search criteria.  The right records are being found but the wrong data is showing up on the report.  For example I have employee training database.  The employee has a main table with different training modules related to the main record.  I search for a specific date the right employees name comes up but the wrong dates appear on the report.  It always pulls the first record in the series of records.  Someone please help!!!

        • 1. Re: Records found not appearing in report
          TSGal

          BG:

           

          Thank you for your post.

           

          I'm having a little difficulty understanding exactly what is happening.  Is the first record from the portal showing up for all records?  Is it the same record?  Is the portal touching the header?  If you could give me some sample data to help describe the problem, this would help me narrow down the possibilities.

           

          TSGal

          FileMaker, Inc.

          • 2. Re: Records found not appearing in report
            Stephen Huston
              

            It sounds as though you are doing both your search and your report from layout(s) based on the employee table.

             

            To get the exact report for just the module records you want, you need to use a layout based on the module table (with employee as related fields) to find only the exact modules to report and the view/print the report for just those module records.

             

            You can set the base table for a layout when creating it, or from the Layout Menu in layout view.

             

            Stephen Huston

            • 3. Re: Records found not appearing in report
              BG
                

              Thank you for taking the time to respond to my problem.  I have been out for a while so hopefully you can still try and help. 

               

              Here is the deal.  I developed a database to assist in controlling our training program.  The database has a main employee table and then I link a bunch of tables (each table representing a distinct training module) to the main table.  So each employee is linked to the training modules. 

               

              The report that is giving me a problem is the Training Program Report.  This report lists all the employee names vertically and horizontally all the training modules are listed.  So each column (except the first column) is a module, each row will have a employee name and then the applicable dates for the particular module (not all employees have to take each training module).  So for example a employee, John, will have training in one module one training was completed on 1/1/2008 and the next training is scheduled for 1/1/2010.  Now I want to perform a search that looks for scheduled training greater then 12/1/2008.  What happens in the report is that the search finds the right employee name, John, that matches the search criteria but the date that populates is 1/1/2008 not 1/1/2010 (which is what the search criteria specified).  Hope this is a little more clear.

              • 4. Re: Records found not appearing in report
                TSGal

                BG:

                 

                Thank you for the clarification.

                 

                It sounds like you have your Employee table linked properly to all of the training tables properly.  However, searching for a date in a portal is not going to limit the number of records found in the portal.  It will limit the number of records in the Employee table that meets the criteria in the related table.  Therefore, if you want to display the latest date in the portal, then you need to set up the portal to be sorted by Date in a descending order.

                 

                To do this, go into "Manage -> Database" from the File menu, and click the Relationships tab.  Between each link is an icon.  Double-click on that icon and that will bring up the "Edit Relationship" dialog box.  On the side of the training table, click Sort records and specify the sort to be the Date field in descending order.  Click OK and exit back to Browse mode.  Now, when you perform your search, the most recent date will be listed first in the portal.  If you are only showing one field, then you will have the most recent date.

                 

                Does this help?

                 

                TSGal

                FileMaker, Inc. 

                • 5. Re: Records found not appearing in report
                  BG
                    

                  Thank you so much it worked!!!!

                   

                  One last thing.  I have some tables that have multiple dates linked to that table.  For example each table is a department.  Some departments have multiple training modules.  So on the training plan I have possibly three dates coming from one table.  I have not tried anything yet but I am wondering how that will impact this.  Any input???

                   

                  Thanks again I have been struggling with this for sooo long!!!!

                  • 6. Re: Records found not appearing in report
                    BG
                      

                    One more last thing.  Is there a way I can prevent data from showing that does not match the criteria?  For example, The right record is pulled but not all the data for the employee matches the search criteria.  So the right record is pulled but it pulls all the dates and populates each field and they don't necessarily meet the criteria.  I search for dates greater then 1/1/2009.  It selects the right employee shows the correct date in the appropriate field (now that you helped me) but each other field that has a date populates so other dates that do not match the criteria are still appearing.  Hopefulle that makes sense.

                    • 7. Re: Records found not appearing in report
                      TSGal

                      BG:

                       

                      Glad you got it to work.

                       

                      If your key field(s) bring up more than one record, and if you are sorting by the date in descending order, then the last date to occur will display first.

                       

                      If you want to prevent data from not showing, then you need to modify your key fields so that it also includes dates after a certain time.  For example, Date > 1/1/2009.

                       

                      TSGal

                      FileMaker, Inc.

                      • 8. Re: Records found not appearing in report
                        BG
                          

                        I am not the sharpest knife in the drawer, so I need a little more detail like in your last answer. 

                         

                        Let me try and redescribe my problem as well.  For example I have john that receives three types of training.  Module 1 occurred 1/1/2008, Module 2 is scheduled for 1/1/2009 (received module 2 training in 07), Module 3 occurred 1/1/2008.  All of these modules appear horizonitally on the report.  I do my search (which is search each field for date greater then 1/1/2009) and the report finds John (the only record in the database that matches the criteria) and but instead of only populating module 2 date field with 1/1/2009 (and leaving module 1 and 3 blank), module 1 and 3 are displayed with dates of 1/1/2008.

                        • 9. Re: Records found not appearing in report
                          TSGal

                          BG:

                           

                          Your knife is sharp enough.  :-)

                           

                          Seriously....  Create a new field "gDate" in your Employee table.  Set it to type "Date".  Click on Options, select the "Storage" tab and check the option for "Global storage (one value for all records)".  Click "OK", and then click the Relationships tab.

                           

                          In the relationships graph, double-click on the icon linking the Employee table with the Module1 table.  This is where we will be editing the relationship.

                           

                          At the top, select "gDate" from the Employee table, Click "Date" from the Module table, and between the two tables, the icon is defaulted to "=".  Change this to < (if Module is on the right side.  Otherwise, use > ).

                           

                          Click the "Add" button, and you will now see something in the middle section like:

                           

                          EmployeeID = EmployeeID

                          AND gDate < Date

                           

                          Click OK, and you should be in good shape.

                           

                          Now, return to Browse mode.  In your Employee table, you may see the field "gDate".  If not, add it to the layout (temporarily).  Enter the date 1/1/1900.

                           

                          All of your related data will be visible.  If you only want to see those dates after 12/1/2008, then change gDate to 12/1/2008.  Only those related records where the Date is greater than 12/1/2008 will display (for Module 1).

                           

                          You will need to set up same relationship for all other Modules.

                           

                          This should get you pointed in the right direction.

                           

                          Let me know if you have any questions or need more clarification.

                           

                          TSGal

                          FileMaker, Inc. 

                          • 10. Re: Records found not appearing in report
                            BG
                               Your are awesome!!!!  Thanks!
                            • 11. Re: Records found not appearing in report
                              BG
                                

                              Hello TSGal, 

                               

                              I ran into a problem and wonderful if you could still help.

                               

                              Since I have put in this Gdate relationship I am no longer able to add records.  The relationship tables autmatically takes away the option of adding records via this relationship.  Is there anyway around this?  Thanks!

                              • 12. Re: Records found not appearing in report
                                BG
                                   One more thing.  Is it possible to do this work around in a script step?  That way it is a temporary thing only when the script step is executed then it goes away when the script step is complete?  Thanks!!!  Happy Holidays!!
                                • 13. Re: Records found not appearing in report
                                  TSGal

                                  BG:

                                   

                                  Happy New Year!

                                   

                                  Pull down the File menu and select "Manage -> Database".  Click on the Relationships tab, and you will see a graphical representation of the tables in your database.  There is a line connecting your two tables together that forms the relationship.  If you double-click the icon on the line between the two related tables, a new dialog box "Edit Relationship" displays.  Each side has its own options.  At the bottom of the respective table, put a check mark into "Allow creation of records in this table via this relationship".  This will then allow you to add records to the relationship.

                                   

                                  I'm not sure what you want to do in the script, but you can add a record to the related table and use the "Set Field" script step to put data into the appropriate fields.

                                   

                                  If you have difficulty with this step, let me know exactly what you are trying to do (with an example), and I'll make sure to point you in the right direction.

                                   

                                  TSGal

                                  FileMaker, Inc. 

                                  • 14. Re: Records found not appearing in report
                                    BG
                                      

                                    Thank you for the response I really appreciate you helping me out.

                                     

                                    The problem I ran into is I did what you said but when I setup the relationship as you described gdate < date the "Allow creation of records in this table via this relationship" gets whited out and I can no longer select it.  So your logic worked.  The report shows me the correct data but now I can no longer add records.  This is a training database so we will continue to add dates to each module because the training is recurring they take it every two years and we need to show the history.  I have tried gdate = date this still allows me to select creation but as soon as I put "<" the selecting creation is no longer an option.  

                                     

                                    A little background on my database, I have a employee main table that is related to each training model.  Some modules will have a series of trainings within the module(another layer of complexity) .  So I need to pull the dates out onto the master training plan.  So from some tables I am pulling more then one date and putting it onto the master plan.  Hope this makes sense. 

                                     

                                    Again thanks for your time and help...

                                    1 2 Previous Next