3 Replies Latest reply on Mar 25, 2014 4:03 PM by philmodjunk

    Creating relationships based upon current tax yea

    SimonSherwood_1

      Title

      Creating relationships based upon current tax yea

      Post

           Hi Guys

           I wonder if you can help, I think I have the right idea but I'm having trouble to get scripts to execute properly.

           I have a table called REPORTS with all fields set to global storage, I use layouts based upon this table to display summery fields from a related table called TRANSACTIONS.

           TRANSACTIONS has records with date,payment,price fields ect and Summery Total and count fields.  I have no problem displaying these total fields in a REPORTS table layout based upon various date relationships to get the information I want.

           However I am having difficulty creating a script so that I can display Summary fields from the TRANSACTIONS table in a REPORTS layout based upon the current tax year which runs from 6th April previous year to the 5th April following year in my country.

           Here's what I have done so far.

           In REPORTS table I've added 2 new date fields REPORTS::TaxYearStart & REPORTS::TaxYearEnd

           I've created 2 relationships with the TRANSACTIONS table under the same table occurrence

           REPORTS::TaxYearStart < TRANSACTIONS::Date  

           REPORTS::TaxYearEnd > TRANSACTION::Date

           I've then wrote a script to execute upon the REPORTS layout enter, this script will populate the REPORTS::TaxYearStart & REPORTS::TaxYearEnd with the correct dates for the current tax year.

           This is the script

           if[IF(Get(CurrentDate)<5/4/Year (Get(CurrentDate)):1:0]

             Set Field[Reports::TaxYearStart; "6/4/" & Year (Get(CurrentDate))-1]

             Set Field [Reports::TaxYearEnd; "5/4/" & Year (Get(CurrentDate))]

           Else

             Set Field [Reports::TaxYearStart;"6/4/" & Year (Get(CurrentDate))]

             Set Field [Reports::TaxYearEnd;"5/4/" & Year (Get(CurrentDate))+1]

           Commit Records/Requests [Skip data entry validation;No dialog;Force Commit]

            

           It seems at the moment that the REPORTS taxyearstart and taxyearend date fields don't even populate, they are set to global storage.

           Does any one have any ideas?

           Simon.