3 Replies Latest reply on Nov 15, 2013 3:34 PM by philmodjunk

    Calculated Value Between 2 Dates

    KeithWallace

      Title

      Calculated Value Between 2 Dates

      Post

           Hi, all I've a bit of a noob question again which I could need some help on please.  

           We are a manufacturer that has moulds and what I would like to get is a total amount of "shots" the mould has done between 2 date ranges.  We have a daily input into the database of what the mould has done so that's where I get the number from.

           Does anyone have any idea on this please :D

        • 1. Re: Calculated Value Between 2 Dates
          philmodjunk

               if each shot is logged as a separate record, a relationship can match to records on a date range. A find on a date range and a filtered portal can filter for a date range. A filtered portal with a date range filter can use a summary field to display a count of the records passing the filter.

               Here are two relationships that match on date ranges. You can add additional match fields such as a mold ID to further narrow the set of related records:

               Table1::StartDate < Table2::Date AND
               Table1::EndDate > Table2::Date

               Table1::DateList = Table2::Date

               In the second case, DateList is either a text field or a calculation field that returns text. Either way the values in the field are all the dates from startDate to EndDate separated by returns. There are custom functions and scripts that can take two dates and generate such a list.

               With either relationship, Count ( Table2::Date ) can be defined in table 1 to count the number of related records. You can also place a summary field from Table2 on a table 1 layout to get the same count.

          • 2. Re: Calculated Value Between 2 Dates
            KeithWallace

                 Thanks Phil, at the moment your explanation is above my pay grade ;)

                 With functions, I would like (if possible) to do this on 1 table only.  

                 With the above, will that only report individual records or a combined record of shots? 

                  

            • 3. Re: Calculated Value Between 2 Dates
              philmodjunk
                   

                        I would like (if possible) to do this on 1 table only.

                   "table1" and "table2" can be two table occurrences of the same table. Thus, this can be done for just one table.

                   

                        With the above, will that only report individual records or a combined record of shots?

                   I don't see a significant difference between the two. The relationship examples match to a group of records that have a date that fall in the date range specified by StartDate and EndDate. Since they match to a group of records, summary fields and aggregate function calcualtions such as Sum() and Count() can return values computed from the set of matching records.

                   The actual relationship that you need, will likely match also by Mould ID so that you match to all records in a given range that represent shots using the same mould.

                   Table1::mouldID = Table2::mouldID AND
                   Table1::StartDate < Table2::Date AND
                   Table1::EndDate > Table2::Date

                   And without using such relationships, you can also use a summary report to produce the same totals--either for one mould, multiple moulds or all moulds. With a summary report, you perform a find for all records in a given date range and (optional) mould or mould numbers. A summary field then can show the number of records in your report.

                   If your report needs to list these counts for multiple moulds in the same report, you can add a sub summary layout part "when sorted by MouldID" and put your summary field in this layout part to show the count for that date range for that mould. With this approach, you have to sort your records by the "sorted by" field in the sub summary layout part or that layout part disappears.

                   If you think this approach will work for you, see this tutorial on Summary Reports: Creating Filemaker Pro summary reports--Tutorial