5 Replies Latest reply on Jun 23, 2016 4:18 AM by Mike_Mitchell

    Need date of first occurance

    stevem

      I keep track of music chart data.  I have input data since 1/1/2016.  I have now created a summery report that sorts the data by song title.  In the sub-sumery part of the layout, I have the song title, singer, and the peak chart position of the song (using a summery field).  For this example, I have the chart history of "Country Nation" by Brad Paisley.  I can clearly see in the sub-summery line the song reached #18.  I want to put in the sub summery line the date that the song first reached #18.  I can go down the chart history and see it reached #18 on 2/13/2016, but I want that date to appear in the highlighted sub-summery line beside the peak position.  How do I do that?

        • 1. Re: Need date of first occurance
          Mike_Mitchell

          Hello, Steve.

           

          You can accomplish this using another summary field of type "Minimum":

           

          Screen Shot 2016-06-17 at 5.08.31 AM.png

           

          HTH

           

          Mike

          • 2. Re: Need date of first occurance
            stevem

            Mike

            Thanks for your help.  I created a summery field with the type Minimal on the Date field.  As you can see, it returned the date that the song was first on the chart (1/2/2016).  I want the date the song reached its peak position at #18 (2/13/2016).  Any ideas?

            • 3. Re: Need date of first occurance
              Mike_Mitchell

              Ah. Okay, that's a little different. You actually have two predicates: Highest chart position in this group, and earliest date that happened. Couple of different ways:

               

              1) Use ExecuteSQL to write query that fetches the value and assign it to a global field. The query would be a skosh complex, but not too bad. It might look something like this (totally untested, off the top of my head):

               

              SELECT "\Date\" FROM "\Chart History\" WHERE \"Chart Position\" IN (SELECT MIN ( \"Chart Position\" ) FROM \"Chart History\" WHERE \"Artist\" = 'Brad Paisley')

               

              2) Create a self-joining relationship from the chart history table to another occurrence of the same table. Base the relationship on the artist. Sort the relationship by chart position (ascending), followed by date (ascending). Then, Chart History Self::Date will be the date of the first occurrence of the lowest-numbered position. Set a field on each record equal to that value whenever you run the report.

               

              The first method is more flexible, but likely to perform poorly (unless you store the value in another table via a script). The second method monkeys with every record, so it's not going to be multi-user safe. But it should work.

               

              HTH

               

              Mike

              • 4. Re: Need date of first occurance
                stevem

                Hi Mike

                 

                For the past 3 days, I've watched videos on Execute SQL and self joins.  None of them make sense.  Could you explain these two concepts (or at least the easiest one) in greater detail in relation to my data project.  Thanks so much for your time.

                 

                Steve

                • 5. Re: Need date of first occurance
                  Mike_Mitchell

                  Sure. A self-join is a relationship between two table occurrences that both point back to the same table. I've attached a file that demonstrates the concept.

                   

                  ExecuteSQL (the function, as opposed to the script step Execute SQL - note the space) is a way of using the Structured Query Language (SQL) to fetch data from your FileMaker database without utilizing the Relationships Graph. It has a lot of advantages with regard to that - breaking the need to be on a layout that references the data - but requires that you understand SQL, at least to some degree. (It also can be a negative impact on performance, depending on the situation.) The query I gave you above would pull the same data as the first row in the portal in my attached example. There are many references for using SQL online, including this excellent one from Beverly Voth:

                   

                  https://filemakerhacks.com/2012/10/19/the-missing-fm-12-executesql-reference/

                   

                  HTH

                   

                  Mike