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

    Dynamic Relationship


      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



        • 1. Re: Dynamic Relationship

          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

            Thanks a lot


            I just had brain ache and couldn't think.