Validating by calculation on related field

[stockingup]

Hi everyone. I have been messing around with this for hours and it just won't work!! I'm starting to think I'm missing something incredibly obvious.

To simplify things, I have a field called 'status' in a table called 'serviceEvents'. I then have another field called 'routeDate' in a table called 'Routes'.

Essentially, UNLESS the job in 'serviceEvents' is currently on a Route, and hence in the 'Routes' table, I want it to only allow the status field to have the value of "incomplete". Otherwise, I want it to allow "Incomplete" or "Complete" (thinking about the problem logically, it wouldn't make sense to mark a job as complete that has never been on a route - and hence can't BE complete!!).

I never seem to manage to do this. I can display the related field, and it works. I have run tests eg: if routes > 0 show a certain dialog box on a test script and that worked aswell.. However, no matter how I reference the field in a validate via calculation field, it just doesn't work.

Any suggestions??

Best Regards,

Jason V.

[Fenton]

Validation calculations can be done different ways, and can be tricky. In this case I'd do a "negative" test for the "not" conditions, explicitly producing a 0 (fail), but anything else is OK.

Case ( IsEmpty (Routes::routeDate) and Status = "Complete"; 0; 1 )

// or, for you minimalists

not ( IsEmpty (Routes::routeDate) and Status = "Complete" )

Edited December 16, 2008 by Guest
minimalists

[stockingup]

That worked like a charm. Thanks for the help... Would have spent countless more hours trying this otherwise

Best Regards,

Jason V.