Hi All,
I am trying to create a validation on an option where I use a formula to lookup to the Accounting Period table in NAV to retrieve the start of the financial period and then have a validation incase the user changes this to something that does not exist on the period table.
In other words, a financial period can start in April instead of January so I use a lookup =NL("Lookup";"Accounting Period";"Starting Date";"New Fiscal Year";"True") to look up the available fin periods which start in April.
I then want to ensure that this date is a valid start date in the acc. period table should the user change it for any reason but I don't know what to use to do the validation.
I have managed to do a validation that does a vlookup off an excel range for another option but this is required off the table itself now so I am a bit lost.
Many thanks
Tom
Date
Votes
5 comments
-
Jet Reports Historic Posts Hi Tom,
The Jet Report Options feature does allow for data validation. You add a column to the right of the Lookup column with the keyword Valid in row 1. Then in that column you have a formula that returns TRUE or FALSE depending on whether the user's option value is valid or not. You can use a Jet formula to do this so I think it should solve your problem. Let's say your Value column is column C and this option is on row 2. Then you could write the following formula in your Valid column:=IF(NL(;"Accounting Period";;"New Fiscal Year";"True";"Starting Date";C2)="";FALSE;TRUE)
Then when you run the report, if you try and put a date value that is not valid, you should get an error message from Jet that prevents the report from running. Does this help?
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
Your code works up until I run the report - giving me an error?
Please see attached spreadsheet and look at Options worksheet D8, E8, F8.
Perhaps you can advise me where I have gone wrong?
Thanks
Tom -
Jet Reports Historic Posts Tom,
Your workbook actually works fine for me once I changed the ; to , in the NP(Eval) formula (since I am in the US). It strikes me that your NP(Eval) validation formula is set to return "FALSE" which is an English word, but if you are using Excel in a different country, maybe TRUE/FALSE are not the words Excel expects? Do you need to change that to return your local word for false? If that doesn't work, can you tell me what error you're actually getting?
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
I am only using the NP(Eval) in F7 which works so I apologise but not sure which Eval you are refencing?
The problem is in F8 where the TRUE/FALSE evaluation evaluates to True of false but only before running the report (checked - TRUE/FALSE is valid in our version of excel and ";" and "," work dependant on my regional settings) but not at time of running the report.
If I run the report and put in the Financial Period Start, ie. 01/04/12 , it runs but it should be confirming that it is a valid period start date from the Accounting period table which in the case of cronus should be 01/01/YYYY (any valid year) so should have MSG user that the date is not valid as per G8.
If you run the report and put garbage in the option ie. jhvjhfvj, the validation should give the same MSG but instead gives:
"Invalid Report Option validation formula for the Report Option 'Financial Period Start'. Report Option validation formulas must evaluate to true or false."
Your help is greatly appreciated
Tom -
Jet Reports Historic Posts Hi Tom,
The entry in the Value column is a text entry. So, if you add a check if it is a date to your formula, you should be fine. I modified the function a bit and added a DATEVALUE function to check the date for me.
=NL("First";"50 Accounting Period";;"3 New Fiscal Year";"1";"1 Starting Date";IFERROR(DATEVALUE(C3);0))<>""
Does it work for you?
Cheers,
Hans
Please sign in to leave a comment.