3 Replies Latest reply on May 17, 2011 2:15 PM by philmodjunk

    Effective Dating Tables

    aammondd

      Title

      Effective Dating Tables

      Post

      Im fairly experienced with FMP and looking for some advice on how to setup effective dating in tables and manage them in protals.

      The general sql that adds effective dating to most views

       

      slect fieldnamesfrom said table

      where criteria

      and EFFDT = (Select (Max(EFFDT) from same table alias1

                         where EFFDT <= Sysdate )

      and EFFSEQ = (Select Max(EFFSEQ)  from same table alias2

                         where alias2.EFFDT = EFFDT)

       

      Im trying to figure out how to represent these as relationships and for some reason im drawing a blank.

        • 1. Re: Effective Dating Tables
          philmodjunk

          I'm not sure I'm decoding the SQL correctly.

          Can you describe in general English what you are trying to accomplish with this?

          (Some relationships that are possible with SQL require scripting in order to accomplish equivalent results in FileMaker.)

          • 2. Re: Effective Dating Tables
            aammondd

            Basically its going to show me only the  max date and sequence (multiple records with the same date) record  that is less than the current date.

            I know that I could show all records less than the system date and sort them but I dont want to do that I only want to see the currently effective row/record (I plan to use a custom security features to bring selected users to a layout where they may be able to view and/or edit history)

            Part of my blank may be not having FMP handy.

            FMP 11 Adv

             

             

              

            • 3. Re: Effective Dating Tables
              philmodjunk

              Yes, but if you use that sort, there are several ways to keep all but that most recent record from being visible and accessible in a portal.

              You can use a one row portal. You can also do this with a filtered portal.

              Relationship for a one row portal:

              Table::cToday > TableSelfJoin::effDate

              (and you can use additional pairs of fields if you need to be more specific.)

              Then you can either sort the portal by effective date and sequence in descending order or you can sort the relationship by effective date and sequence. With a one row portal, you get identical results either way, but the sorted relationship may be the better option if you then need to perform calculations that refer to or alter this related record.

              For that matter, with the sorted relationship, you don't even need the portal with a layout based on Table, you can just add the fields you need from TableSelfJoin directly to the layout.