AnsweredAssumed Answered

Creating relationships based upon current tax yea

Question asked by SimonSherwood_1 on Mar 25, 2014
Latest reply on Mar 25, 2014 by philmodjunk


Creating relationships based upon current tax yea


     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))]


       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?