9 Replies Latest reply on Apr 14, 2011 9:53 AM by philmodjunk

    Need Script for finding "Max Date"???



      Need Script for finding "Max Date"???


      Ok, I'm still quite new to scripting and programing Filemaker, but I'm hoping you guys can give me some help. I have a database that has all my part numbers in it. Within it is a portal that pulls all the info on when the part was made, who it was made for, job number, po number, etc. What I'm looking to do is quickly find all parts that haven't been made in say 7 years, and create a list. Can someone help me find the easiest way to do this? If I look for specific dates, it still comes up with the newer ones, because that date range falls in there, but it isn't "maxing" it. Like the part has been used again since then, but it still displays. Thank you!!! -Matt

        • 1. Re: Need Script for finding "Max Date"???

          Make sure your date fields are of type date.

          Then if you want all records dated earlier than 1/1/2004, use < 1/1/2004 as your search criteria.

          • 2. Re: Need Script for finding "Max Date"???

            They are date fields....I tried doing this and it still pulls up any result that has those dates, which includes parts with newer dates as well. I'm not sure why it's doing this. I didn't create this database, so I'm trying to learn what the other guy did, and learn by doing.

            • 3. Re: Need Script for finding "Max Date"???

              Can you describe in detail how you are trying to do this?

              Check the layout design where you are performing the find.

              Are you entering such criteria in a portal and after performing your find, you see other rows of data with other dates in this portal?

              • 4. Re: Need Script for finding "Max Date"???

                Basically I'm using "Perform Find" and entering the find into the Date field. The date field is pulled from another table, and I'm having it input <5/1/2004 as the find. It's coming up with results, but it shows all the results for each record, IE every time it has been made instead of eliminating the ones made after 5/1/2004. I could take a screen shot of the result if that helps, I just have to blank out some info. Like I said, I'm REALLY new at this and when I try to make changes, it seems I mess up more than I fix!!

                Thanks for your quick replies!

                • 5. Re: Need Script for finding "Max Date"???

                  "The date field is pulled from another table"

                  That's the key detail. When you enter criteria in a field from a related table, you have to keep in mind that you are finding records in the layout's table, not the related table.

                  If you enter < 5/1/2004 in the date field of your related table, you are telling FileMaker "find every record in the layout's table that has at least one related table with a date less than this expression. Once the find is performed, you are back in browse mode and each record in your found set then displays the related records like it always does and thus can and will still display other records that are also related to it even though they have different dates.

                  For the results you want, I suggest performing the find on a layout that refers directly to these records instead of via a relationship.

                  Hmmm, or it might make more since if you do this:

                  1. Enter find mode
                  2. Enter > 5/2/2004 in the related record's field
                  3. Click the omit button at the top of the screen
                  4. Perform the find.


                  This finds all records that do not have a related record dated later than 5/2/2004.

                  • 6. Re: Need Script for finding "Max Date"???

                    Man....I cannot get this to work. I even tried what you just suggested...This is driving me crazy!

                    • 7. Re: Need Script for finding "Max Date"???

                      You'll need to figure out the design of your layout and the underlying relationships. Something I can't really do from here.

                      • 8. Re: Need Script for finding "Max Date"???

                        I appreciate you trying to help!!

                        • 9. Re: Need Script for finding "Max Date"???

                          Are you able to identify the table occurrence in Manage | Database | Relationships that is specified for your layout? (You'll find the name of this in Layout Setup | Show Records From.)

                          Can you describe how the two tables are related?

                          Were the fields from the related table listed in a portal where you preformed your find?

                          (Table occurences are what we call the "boxes" found in Manage | Database | relationships )