Creating relationships based upon current tax yea
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
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?