7 Replies Latest reply on Feb 17, 2016 5:33 AM by AllegroDataSolutions

    Calculated Key Based on Unusual Date Range

    AllegroDataSolutions

      I need to create a calculated key field that will link to records in a child table.

       

      As the client designed the solution, the child table records have a field for the date of the sale (also a Month field and a Year field which are calculated from the date of the sale field). There is a key field that links to the vendor's record. The problem is that the relationship links to EVERY vendor's record. The client wants to link only to those records in the current contract period.

       

      Every vendor record has fields for the start and end dates of the current contract. All of them span several years and cover twelve consecutive months, but not every contract year begins in January and ends in December. For example:

      • Vendor #1's current contract year may be from January 1, 2016 through December 31, 2016.
      • Vendor #2's current contract year may extend from June 1, 2015 through May 30, 2016.
      • Vendor #2's upcoming contract year may extend form June 1, 2016 through May 30, 2017.

       

      If all the client wanted to do was display the record in a portal, I'd simply use a filtered portal. But complicated formulas to determine things like dealer incentives depend on only sales from the current contract year. So, I need a relationship that will apply just to the child records that fall within that range. If every contract year corresponded with the calendar year (like Vendor #1 in the example above) it would be easy. I would link on the vendor ids and the current year. But, for Vendor #2, I would need to include records for the last six months of the previous year. And, over time, there could be vendors with different contract start and end dates. Though, the client tells me, all contracts so far have spanned multiple years. I'm looking for one method that covers everything.

       

      I've been thinking along the lines of an unstored calculated field in the vendor table that determines all the month and year combinations from the contract start and end dates and expresses them as a carriage return delimited list.  Something like this ...

       

      Jun 2015

      Jul 2015

      Aug 2015

      Sep 2015

      Oct 2015

      Nov 2015

      Dec 2015

      Jan 2016

      Feb 2016

       

      ... and so on, which would be linked to the month and date of the sales table records. But I wonder if I'm making this too complicated. I would be grateful for any other ideas.

       

      Thanks.

        • 1. Re: Calculated Key Based on Unusual Date Range
          siplus

          On a vendor's record you need to see a portal of all his contracts. When you click on a contract, you set 2 globals to start and end of contract. Together with VendorID you can point to related child records via a rel based on 3 conditions: PKVendorID = FKVendorID, gSelectedContractDateBegin ≤ SaleDate,   gSelectedContractDateEnd ≥ SaleDate. You can sum across the relationship, view that contract's sales in a portal base upon this rel etc.

          • 2. Re: Calculated Key Based on Unusual Date Range
            DavidJondreau

            I think you're making it too complicated. Could you clarify the tables? It sounds like the parent table is Vendors and the child table is Sales; the contract start and end dates are in Vendors table; and you want to view Sales from Vendors for the current contract year. Is that accurate?

             

            I think you're overlooking the comparison operators in relationships.

             

            Your relationship can be

            Vendors::VendorID = Sales::VendorID

            Vendors::ContractStart ≤ Sales::SaleDate

            Vendors::ContractEnd ≥ Sales::SaleDate

            • 3. Re: Calculated Key Based on Unusual Date Range
              siplus

              Adding a Contract table as I suggested will allow looking at past performance. Keep track of past contracts, too.

               

              It will also allow to enter future contract data today.

               

              Sooner or later client will want it, so I'd better anticipate

              • 4. Re: Calculated Key Based on Unusual Date Range
                AllegroDataSolutions

                Thanks for the prompt feedback.

                 

                To clarify:

                 

                There are less than a dozen vendors in the vendors table that have contracts. There are only three fields which currently deal with contracts: the start date, end date, and a container field in which a copy of the active contract is stored while it is in force. The contract start and end dates are only going to be used to link the sales record for the purpose of calculating progress toward meeting the vendor incentive goals. (When a goal is met, the client gets a rebate.) The client doesn't need or want additional portals or layouts and won't use them if I build them. I just want to correctly model the relationships where 1 year = 12 consecutive months that do not begin with January. If need be I can build four or five relationships specifically for the vendors whose contracts require this and link the rest with


                Vendors::VendorID = Sales::VendorID and
                Vendors:CurrentYearCalculation = Sales:SaleYear

                 

                I like the idea of exploring the comparison operators other than equals and not equals. But I don't think the example given above is quite there.

                 

                Vendors::VendorID = Sales::VendorID

                Vendors::ContractStart ≤ Sales::SaleDate

                Vendors::ContractEnd ≥ Sales::SaleDate

                 

                only makes sense to me if the contract lasts for a single year. The problem is that all existing contracts are for three or more years, but the vendor incentives apply only to the current year -- and the "current year" of a contract that doesn't begin in January will span part of one year and part of the next.

                • 5. Re: Calculated Key Based on Unusual Date Range
                  DavidJondreau

                  OK, it wouldn't be a bad idea to create an additional table with "Contract Years". But if not, what you need is two additional fields that take the ContractStart and ContractEnd and convert them to CurrentYearContractStart and CurrentYearContractEnd (or something named more simply).

                   

                  That conversion would be:

                  CurrentYearContractStart =

                  Let ( [

                  today = Get ( CurrentDate ) ;

                  start = Vendor::CurrentYearContractStart ;

                  m.start = Month ( start ) ;

                  d.start = day ( start ) ;

                  y.start = year ( start ) ;

                  y.today = year ( today ) ;

                  //this gives a 1, 2, or 3, etc depending on the year

                  year.number =  y.today - y.start + ( date ( m.start ; d.start ; y.today ) < today )  ;

                  contract.year.start = Date ( m.start ; d.start ; y.start + year.number - 1 )

                  ];

                  contract.year.start

                  )

                   

                  CurrentYearContractEnd =

                  Let(

                  contract.year.start = Vendors::CurrentYearContractStart ;

                  Date  ( month ( contract.year.start ) ; day ( contract.year.start ) - 1 ; year ( contract.year.start ) + 1  )

                  )

                  • 6. Re: Calculated Key Based on Unusual Date Range
                    siplus

                    no problem,

                     

                    SetField [ gSelectedContractDateBegin;


                    Let(


                    minusone = month(CurrentDate) < month(ContractStart);


                    date(


                    month(ContractStart);

                    day(ContractStart);

                    Year(CurrentDate) - minusone


                    )

                    )

                    ]


                    and analogously


                    gSelectedContractDateEnd


                    all the rest David (and I) said holds.


                    • 7. Re: Calculated Key Based on Unusual Date Range
                      AllegroDataSolutions

                      Thanks. I'm all ready working on something along the lines of the last two examples -- basically, using the Date() function to specify the day and month of the original start date and end dates but determining the date based on the current date and the length of the contract.