14 Replies Latest reply on Feb 27, 2014 8:55 AM by philmodjunk

    Counting a date with various activities

    axl

      Title

      Counting a date with various activities

      Post

           I have a database that tracks aircraft usage. The aircraft can be used by different pilots on the same day and I need some way of being able to track the number of days the aircraft is used.

           Example:

           In the example the number of days (the number I am trying to capture) the aircraft was flown would be 2 (7 sorties over those 2 days).

           I have two tables associated with this example - one captures the event (date, aircraft used, pilot at the time of the flight) and the other captures the details of the flight (departure location, beginning engine meter reading, arrival location and ending meter reading and total flight time).

           Any suggestions?

           In a report using list view I want to be able to count the number of days the aircraft was flown, I am not having any issues summarizing the aircraft hours or the number of flights over those days.

            

            

        • 1. Re: Counting a date with various activities
          axl

               I am having trouble uploading my example image

          • 2. Re: Counting a date with various activities
            philmodjunk

                 So you want the number of unique dates in the date field for a specified aircraft over a specified time interval. Correct?

                 There are several possible options. If you have FileMaker 12 or newer, ExecuteSQL() can compute those totals by using the DISTINCT keyword: A new way to count unique values in FileMaker 12

                 If you are using an older version of FileMaker or don't care to try your hand at an SQL query, sum the reciprocal might be used: How to count the number of unique occurences in field. (Warning, this one's a bit bizarre, but it works.)

            • 3. Re: Counting a date with various activities
              axl

                   Thanks for you help

                   I may be missing something with those links and I just noticed my example didn't post and will type it out:

                   I have a date that can be used for an aircraft several times. each date can have one to several flights.

                   Event on 1/1/01 (Event id 1) within the event; Flight 1, Flight 2 (each one of the flights would have their own id on their respective table).

                   Event on 1/1/01 (Event id 2) within the event; Flight 1.

                   Event on 1/2/01 (Event id 3) within the event; Flight 1, Flight 2.

                   Event on 1/3/01 (Event id 4) Within the event; Flight 1.

                   Event on 1/3/01 (event id 5) withing the event; Flight 1, Flight 2, Flight 3.

                    

                   In the above the aircraft was flown on 3 separate days; and had 9 flights.

                   I can get the 9 flights but can't figure out how to get the 3 separate days.

                    

              • 4. Re: Counting a date with various activities
                philmodjunk

                     I don't understand your example data.

                     Is each row of data a different record?

                     So in one record, you list different flights for the same event? How?

                     In the last row of your data, does Flight 1, Flight 2 and Flight 3 represent flights by the same aircraft or 3 different aircraft?

                • 5. Re: Counting a date with various activities
                  axl

                       Were close. This may explain it better.

                       Event 1 - Pilot A goes flying on 1/2/2013 in Plane Z. Pilot A flys Plane Z from Las Vegas to Los Angeles and then from Los Angeles back to Las Vegas all on the same day, 1/2/2013. This repersents 1 event and 2 flights on 1/2/2013.

                       Event 2 - Then on 1/3/2013 Pilot A flys Plane Z from Las Vegas to Reno and on the same day a different pilot takes command, Pilot B and flys Plane Z from Reno to Las Vegas. This represents 2 events and 1 flight for each pilot (the Pilot in command changed creating a new event).

                       In Event 1 there is only one pilot and 2 flights within the one event.

                       In Event 2 there is actually two events that would occur and the pilot would log whichever leg the flew: Pilot A would log a flight on 1/3/2013 going from Las Vegas to Reno. Pilot B would log their own event (creating a 3rd event) on 1/3/2013 going from Reno to Las Vegas.

                       So what I am trying to get is the actual number of days Plane Z was flown. In the senerio above Plane Z was flown 2 days total (1/2/2013 & 1/3/2013). I can extract the 4 takeoffs and the 4 landings no problem.

                       The problem I am having is extracting the 2 days from the 3 events.

                        

                       Thanks again for looking at this.

                  • 6. Re: Counting a date with various activities
                    philmodjunk

                         But what I am asking is how this data is recorded in tables of your database. I can guess, but prefer not to. From what I see here you need a table of events, a table of Flights, a table of aircraft and a table of pilots.

                         And this one puzzled me:

                         Event 2 - Then on 1/3/2013 Pilot A flys Plane Z from Las Vegas to Reno and on the same day a different pilot takes command, Pilot B and flys Plane Z from Reno to Las Vegas. This represents 2 events and 1 flight for each pilot (the Pilot in command changed creating a new event).

                         Then wouldn't that be two events not 1? An event 2 and an event 3?

                         Event 2 -on 1/3/2013 Pilot A flys Plane Z from Las Vegas to Reno

                         Event 3-on 1/3/2013 Pilot B flys Plane Z from Reno to Las Vegas.

                    • 7. Re: Counting a date with various activities
                      axl

                           Correct - 4 tables.

                           Event; when the current pilot surrenders the plane to another pilot they have effectivly ended their event.

                           When the new pilot takes over it creates an event for the new pilot.

                           I know it is confuzing but it identfies who the pilot is during the flight. We have a lot of that - pilots fly to a location and another pilot jumps in the plane and flies to a diffrent location and then may be flies back to where they got the plane and then the original pilot will fly the plane back to the orignial location at the begininng of the day.

                           The event table stores the date, the pilot, time the event occured.

                           The flight table stores where the aircraft took off from, the begining engine meter reading, where the plane landed and the ending meter reading.

                           The pilot table stores the pilot name and various information.

                           The Aircraft table pulls the meter readings from the flight table, stores the tail number.

                            

                      • 8. Re: Counting a date with various activities
                        philmodjunk

                             What's confusing is that your own example appears to contradict the "rules" that define what makes up an event as your data is labeled as a single event but then you indicate that it's really two events. So either your data example should list that info as two events--which is not what you posted or there is some additional factor not yet explained here.

                             Can't really move forward from here without that clarification.

                             My best guess as to your tables or relationships is this:

                             Pilots----<Events----<Flights>-----Aircraft

                             Pilots::__pkPilotID = Events::_fkPilotID
                             Events::__pkEventID = Flights::_fkEventID
                             AirCraft::__pkAircraftID = Flights::_fkAircraftID

                             But that assumes that your Event 2 should actually be logged as two records in Events--one for each pilot, which is not consistent with your original example data.

                             For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                        • 9. Re: Counting a date with various activities
                          philmodjunk

                               I have a solution for that, but your post still leaves the issue that I have raised unresolved and that must be dealt with before I can describe the needed changes to get the counts that you want for each aircraft.

                               To repeat the question:

                               You posted this example event:

                          Event 2 - Then on 1/3/2013 Pilot A flys Plane Z from Las Vegas to Reno and on the same day a different pilot takes command, Pilot B and flys Plane Z from Reno to Las Vegas. This represents 2 events and 1 flight for each pilot (the Pilot in command changed creating a new event).

                               The text in red contradicts the text in blue. What makes sense to me is to log this as two records in Events

                               Event 2 - 1/3/2013 Pilot A flys Plane Z from Las Vegas to Reno

                               Event 3 - 1/3/2013 Pilot B flys Plane Z from Reno to Las Vegas

                               Either that or there is some additional criteria that you need to explain to show why this would all be recorded in your table as a single event.

                               It's the one pilot to one event rule that I have to clarify here. I linked the Pilots table to Events in my previous post because that is consistent with that rule. With one pilot to 1 event, you can easily reference the info on the pilot for that event via that relationship. But if there are exceptions to that rule that result in more than one pilot for a given event, the data model must be changed.

                               Once the data model is clarified, I can describe one or more options on how to get the totals days per aircraft counts.

                          • 10. Re: Counting a date with various activities
                            axl

                                 I am sorry - I have tried numerous times to send a screen shot of what I am trying to do but haven't figured it out how to get the screen shot to come across.

                                 Essentially, if I sort by using the date as the break I can get what I am looking for. If I could just count the number of breaks.

                                 If ran a report using a date range and the plane flew on three different days within that range (no matter who flew the plane or how many times it was flown on that date) the total number of days the plane was used in the date range is 3. This is really what I am looking for.

                                 I am rereading what I had previously posted and I didn't explain myself very well, but this might. Thanks for staying with me on this.

                            • 11. Re: Counting a date with various activities
                              philmodjunk

                                   But this STILL doesn't answer what for me is a very simple question. I can't see any way to state it more plainly. I don't see that it needs any picture in order to answer it.

                                   To upload a picture, make sure it is one of the file types supported by this forum. The types are listed beneath the Browse button below Post a New Answer.

                                   You click Browse, select the file on your system that you want to upload to your post, then click the Open button in the Open File dialog and that's it. When you click Post Answer, you should see the image file appear as a picture in your post, but note that it sometimes takes more than 10 minutes before others see that image file when they open the thread. The text of your message can thus appear and then the image appears a bit later.

                              • 12. Re: Counting a date with various activities
                                axl

                                     The screen shot shows my reporting layout and shows the aircraft was used on 3 separate days (the number that I am trying to get). I am new to FM and am using FM 11.

                                • 13. Re: Counting a date with various activities
                                  axl

                                       I am using the second TOG in this image for the report.

                                  • 14. Re: Counting a date with various activities
                                    philmodjunk

                                         Sorry again. It's good to see this info--especially the relationship map. But it leaves the question/issue unanswered. You described a contradiction. I've documented that contradiction as clearly as I know how to do. Clear that up and we can move forward.