Ok, this calculation worked fine with FM3, but we recently upgraded to FM7 and now it doesn't work right. This calculation is used to validate the infomation entered into the "Work Order Number" field (a text field).

Calculation: If(New Projects::Project Complete = "Project Complete", "", "complete")

Background: Three relational databases - Timesheets, New Projects, Work Orders.

Timesheets is for employees to enter in the work they've done on projects. Timesheets is linked to New Projects through "Work Order Number" field. New Projects lists information on client projects, such as status, project start and finish dates, and a "Project Complete" field (set up on the main layout as a radio button list with a value list of Project Complete; you click the button when the project is finished). New Projects is linked to Work Orders through the "Work Order Number" field. (Work Order db should not be a factor, but want to give the complete picture here). Work Orders lists all the tasks to complete for each project in the New Projects db. There is a portal on the main layout of the New Projects db that shows the tasks for the project being viewed.

Problem:

In FM3 when an employee would enter the work order number in their timesheet entry, the calculation would cause the validation message to appear if the employee was trying to use a number for a project that was already marked completed in the New Projects db (once a client project is complete and billed, no more time should be applied).

Now, in FM7, the validation message always appears, even if the project isn't complete. It always tests as false. I am not very good with calculations and I've tried everything I could think of (including IsEmpty) and I'm not having any luck.

I'm betting this is a real easy one, but I just don't know enough to get it right. Please help. Thanks.

Have you cleaned up your File References? Did you try

Case( New Projects::Project Complete = "project complete"; "complete" ) ?

I know it's the reverse of your test, but somehow it isn't making sense to me to mark a field 'complete' if the project isn't or to leave it empty if it is.

No luck with that calculation either.

The calculation is used to validate the Work Order Number field (Validate by Calculation field option). It is set as a validation option for Work Order Number in the Timesheet db. When a project (identified by Work Order Number) is complete in the New Projects db, the Project Complete field is filled with "Project Complete". If someone enters the Work Order Number in the Timesheets field the validation calculation is supposed to look at the Project Complete field. If it's marked "Project Complete" then the validation fails and the message appears (you can not use this work order number because the project is complete).

It worked in FM3. The message would appear if the project was complete. In FM7 it always displays the message, even when the project is not complete and the Project Complete field is blank/empty.

Oh, it's for a validation calculation. In that case, I don't see how the original ever worked. You need a true/false result for a validation calculation. Something like New Projects::Project Complete <> "project complete" should work. If it's true, then the calc returns a 1. If it's false, then it returns a zero, and the custom message is returned.

Voila! It worked! I don't know about the original either...I didn't set them up, I'm just trying to convert them.

Thank you sooooo much!