2 Replies Latest reply on Jun 11, 2012 6:28 AM by PeterEvans

    Dynamic Relationship

    PeterEvans

      Hello All

       

      I'm trying to make a relationship to show sales of a rolling 12 months. I want to sum the Quantity field from the Invoice table, and the TotalPrice from the Invoice table in the Stock Table against the stock code record.

       

      My original idea was to have a relationship based on:-

       

      Stock::CurrentDate&StockCode = IF(Invoice::Date > (Get(CurrentDate)-365); CurrentDate&StockCode; "" )

       

      Needless to say, this isn't working.

       

      Can anyone help?

       

      Thanks a lot

       

      PETE

        • 1. Re: Dynamic Relationship
          erolst

          If I understand your description correctly, you need a relationship from Stock to Invoices. If so, then in your StockCode table, define two globally stored calculation fields, result type date, gcTodayThisYear and gcTodayLastYear.

           

          Define gcTodayThisYear as Get ( CurrentDate ). For gcTodayLastYear, use Date ( Month ( Get ( CurrentDate ) ) - 12 ; Day ( Get ( CurrentDate ) ) ; Year ( Get ( CurrentDate ) ) ), (Note that on the 29th February, this yields the 1st March of the previous year).

           

          Base the relationship from Stock to Invoices on:

           

          Stock::gcTodayThisYear >= Invoices::invoiceDate

           

          Stock::gcTodayLastYear <= Invoices::invoiceDate

           

          Stock::stockCode = Invoices::stockCode

          • 2. Re: Dynamic Relationship
            PeterEvans

            Thanks a lot

             

            I just had brain ache and couldn't think.