7 Replies Latest reply on Jan 25, 2017 10:39 AM by philmodjunk

    Is this Relation possible?

    ChristofferRexen_1

      Hello everyone,

       

      So working on a DB, in which a client has asked me to - maybe / maybe not - set up, at specific relationship which I am still fiddling with.

       

      Two tables, "Customer" and "Policy"

       

      The client wants, from "Customers" with a portal, to see every active policy in the "Policy" tabel with a portal, based on "InPowerDate" (Date field), but from the same relationship also omits every policy, where "EndDate" (Date field) and "TerminationDate" (Date Field) is ≤ to Today ( Get ( CurrentHostTimeStamp ).

      The client will in many cases put a future date into "TerminationDate" and I might have screwed it up a bit, by using two different fields to accomplish the relation, I might only need "InPowerDate" and "EndDate"

       

      The problem is, when the client is putting a future date into the "EndDate" field, in which the criteria for the relationship is no longer met and thereby omits the actual policy.

       

      So, to sum up -- Show Active Policies, even when the client have entered a future "EndDate"

       

      Please let me hear you're thoughts, dying to get his one solved!


      RegardsCR

        • 1. Re: Is this Relation possible?
          ninja

          Couple of things,

           

          Sounds like a filtered relationship, showing policy records with Start Date <= today

          AND

          Termination date >= today

           

          In your post I think you have the <= sign the wrong way around.

          Show things that are not terminated yet...

           

          I would filter this in the relationship, not the portal, but that's me.

          Having Start date (as date) and termination date (as date, not timestamp) both being filter criteria is not an issue.

           

          FWIW, having a future end date = the policy has not terminated yet = it is active (assuming it has started).

          1 of 1 people found this helpful
          • 2. Re: Is this Relation possible?
            ChristofferRexen_1

            Sounds like a filtered relationship, showing policy records with Start Date <= today

            AND

            Termination date >= today

            In your post I think you have the <= sign the wrong way around.

            Show things that are not terminated yet...

            Yes it is a bit filtered relationship.

            It is correct, the client wants to see/show things (policies) that are not terminated yet - even those with a future date.

             

            I would filter this in the relationship, not the portal, but that's me.

            I am curious, how could/would do such a thing?

            I don't see how I can sort/filter this?

             

            FWIW, having a future end date = the policy has not terminated yet = it is active (assuming it has started).

            Yes and no...

            We are talking about policies, which is always "born" with an EndDate / TerminationDate, since the policy is - as for the most parts - only run for a year, in which "EndDate" / "TerminationDate" is born with a date.

            Say StartDate 01.01.2017 - EndDate/TerminationDate 31.12.2017

             

            Although the client could always alter the EndDate/TerminationDate if needed.

            • 3. Re: Is this Relation possible?
              ninja

              Open your relationship window (double click on the [=] box in the relationship graph)

              Set your {Today > Start date} relationship

              Choose your {Today < End Date} and click "Add"

              simple as that.

              Records are only related when they meet both criteria.

               

              The relationship won't care much where the end date came from or how it was set...it just cares about what day today is, and what value the EndDate field has.

              Policies with a termination date in the future are (by definition) not terminated yet.

               

              For sorting, I would handle that in the portal...simply set up the portal to sort by your desired criteria.

              1 of 1 people found this helpful
              • 4. Re: Is this Relation possible?
                philmodjunk

                Note, however, that Eric's suggested solution requires that there be a date in both date fields. If the termination date field is sometimes left blank, it won't appear i a portal based on this relationship. If that's a possibility, you may need to use a portal based on a different relationship that uses a portal filter expression to control which records appear in the portal.

                1 of 1 people found this helpful
                • 5. Re: Is this Relation possible?
                  ninja

                  Yes, I did make that assumption.  Thanks for calling it.

                  • 6. Re: Is this Relation possible?
                    ChristofferRexen_1

                    philmodjunk wrote:

                     

                    Note, however, that Eric's suggested solution requires that there be a date in both date fields. If the termination date field is sometimes left blank, it won't appear i a portal based on this relationship. If that's a possibility, you may need to use a portal based on a different relationship that uses a portal filter expression to control which records appear in the portal.

                    Hi Phil!

                    You are indeed correct.

                     

                    StartDate and EndDate is always forfilled, say 01.01.2017 - 31.12.2017 (Danish standards) in which the criteria for the relationship is forfilled, but since the client ONLY wants to see active policies, the "StartDate" / "EndDate" criteria won't really work since the policies will "disappear" when moving across the EndDate.

                     

                    I therefore created the field TerminationDate to add/change the relationship criteria, so the policies only "disappear" if the policies are:

                     

                    1. Today ≥ InPower

                    2. Today ≥ EndDate OR TerminationDate (Which is best?)

                     

                    But as Phil writes, above requires a date, and since TerminationDate does not always have a date, this criteria won't work, furthermore using ≤ will only show policies with an OldDate and using ≥ will only show policies with a NewDate.

                     

                    Any suggested method to accommodate the clients wishes are eagerly met.

                     

                     

                    Regards

                    CR

                    • 7. Re: Is this Relation possible?
                      philmodjunk
                      furthermore using ≤ will only show policies with an OldDate and using ≥ will only show policies with a NewDate.

                      Not when used as part of the same relationship.

                       

                      But it's never made sense to me why you have both an "end date" and a "termination date". Both would seem to be the same thing to me.

                       

                      But you can set up a relationship using the first half of Eric's suggested relationship:

                       

                      Set your {Today > Start date} relationship

                      Then set a portal filter expression up that refers to the the Termination Date if it has a date and which ignores this if it does not:

                       

                      IsEmpty ( PortalTable::Termination Date ) or ( Get ( CurrentDate ) <= PortalTable::TerminationDate )

                       

                      I reversed the inequality here as that makes more sense to me. you want this allow records to appear if they do not have a termination date or if the termination date comes later than today's date.