7 Replies Latest reply on Oct 2, 2014 8:11 AM by philmodjunk

    Report not showing correct find date from portal



      Report not showing correct find date from portal


      Hi All, 

      My issue is as follows. I have 2 Databases that are linked by a relationship Contact Management and Work log.

      Contact management shows all customer info such as name, address, phone, etc... and work log shows the history of appointments for each customer.

      My issue is I'm trying to create a report that i can use for appointments. In a given day i can have 10 appointments a day. So in my portal (work log) i log the appointment into a field called ApptDate. And on the day of my appointment I would like to do a find on the ApptDate field and generate a report with all the appointments for that date. 

      I can generate the report with no problem but my issue is that when I look up past dates my report does not necessarily  display the date that I am looking for if there is an newer date in the customers history the report displays the newer date.

      So for instance I did a find on the date 11/30/2013 (see below) but i got 1 record that displays the customer info from 8/2/2014 cause its the newest date

      <!--StartFragment-->                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               <!--EndFragment-->      
      Patricia Bobe Staten IslandNY10312-510911/30/2013Clean Gutters
      Francis Demayo Staten IslandNY10312-510911/30/2013Clean Gutters
      Louise Meola Staten IslandNY10307-160108/02/2014Replace Back gutters, Screen, and patch chimney
           11/30/2013Clean Gutters
      Dan Ramona Staten IslandNY10312-622811/30/2013Clean Gutters


      Is there a way around this?


        • 1. Re: Report not showing correct find date from portal

          Use a layout based on WorkLog, not contact management for performing your find and generating your report.

          • 2. Re: Report not showing correct find date from portal

            Im not following you. Work log is a portal that runs in contact management. 

            Please explain what you mean. 


            Thank you, 

            • 3. Re: Report not showing correct find date from portal

              I mean that the portal is based on a table occurrence in Manage | Database | Relationships. It is specified in portal Setup | Show related records from and corresponds with the identically named "box" found in the relationships graph. You have or can create a layout based on the same table occurrence by selecting this option in Portal Records | Show Records From.

              By performing the find on this layout, you find the records in your portal table rather than the parent table and only records matching your find criteria will be in the found set. You can make this a list view type report and include fields from the related parent record in layout parts other than the body layout part. (exactly where depends on the type of report)

              When you use a layout based on the parent table and specify criteria such as your date in the related table (portal), you find all parent records that have at least one related record matching your criteria. But one the find is performed and the layout returns to browse mode, all related records appear in the portal, not just the ones matching your specified find criteria.

              In the Invoices starter solutions found in FileMaker 11, 12, and 13, this is the method used to print an invoice--the "print layout" is based on the Invoice Data or Line Items portal instead of printing from the Invoice based layout using a portal.

              • 4. Re: Report not showing correct find date from portal

                I understand your explanation and appreciate it. But do you have a solution to make this work in list view?

                My whole resigning to generate this list view is so i can run a report by date and have each individuals cost with the individuals personal info. 



                Thank you 


                • 5. Re: Report not showing correct find date from portal

                  But do you have a solution to make this work in list view?

                  What I am describing is done in List View. Can you describe what you want in more detail?

                  • 6. Re: Report not showing correct find date from portal

                    I think my problem is that I'm not expelling myself correctly. 

                    See when i execute the find I am able to find the records but if i execute the find on an older date and the customer has newer dates in there history the newer date shows up in the find. See my example above. Search was done on date of 11/30/2013 but one of the searches finds shows a date of 8/2/2014. This person appears in the find  because in there history they have a date of 11/30/2013 as well. 

                    The second part of my problem is these customers have prices for the date of service. When i try to add up the total cost for the days work in the report the $ amout is always incorrect. Im hoping when I fix the issue with the dates that the $ amout figure will be correct as well. 

                    Hope this clarifies my issue. 


                    Again i really appreciate your time in trying to help me. 


                    Thank you, 

                    • 7. Re: Report not showing correct find date from portal

                      And this is why I have made a suggestion that you search on a different layout.

                      If you have this setup:

                      LayoutTable::Primarykey = PortalTable::ForeignKey

                      If you perform a find on a Layout based on LayoutTable and specify criteria in a field in PortalTable, you are telling FileMaker to find all records in LayoutTable that have at least one related record in PortalTable matching the specified criteria (such as your date).

                      But once the find has been performed and the records in LayoutTable have been pulled up in a found set, you are returned to browse mode and the standard settings you have specified for match fields and portal filter then control what records appear in the portal. You'll see not only the portal record that matched your criteria but also any other portal records that are also linked to the LayoutTable record and which pass any specified portal filters.

                      So you have two choices if you don't want to see any records from the Portal table that don't match your criteria:

                      a) search on a layout based on the Portal Table as I have recommended previously

                      b) use a portal filter expression or relationship where you specify criteria in fields or variables as part of your find script that omit the records from the portal that don't match your find criteria.