Condition to change field to specific image

[Capt JB]

Hi,

 

I am a beginner here and I really fill it.!!

I have a table of events with a related table of requirements. I can have requirements from 0 up to any number as required

In the table of events I have a calculated field (result is a container) which has there options "approved" "pending" "failed". The 3 icons comes from preference table and are global fields.

The failed situation is based on the event creation date (entry field) + 30 days condition.

the pending situation is based on the related requirement existing - if no related requirement exist the situation is Approved.

The approved situation is based on the requirement completion date (entry field) from the requirement table.

 

My problem is having several requirements (one or more but not all) completed the field changes to approved.

 

How can I make the calculation to be sure that if ALL (only) requirements are completed the status field is set to approved. All the records in the event table will have any number of related requirements from 0 to x.

I turned that around several times. I started to investigate with a 0/1 calculated field to sum then compare to total of related requirements ... 

Any help will be very much appreciated.

 

Regards

[comment]

How can I make the calculation to be sure that if ALL (only) requirements are completed the status field is set to approved. All the records in the event table will have any number of related requirements from 0 to x.

 

Assuming you have the following relationship in place:

 

Events::EventID = Requirements::EventID

 

you can tell that all event's requirements have been completed when =

Count ( Requirements::EventID ) = Count ( Requirements::CompletionDate )

Note: if you are using this in a calculation field, set the field to evaluate even if all referenced fields are empty (in order to get a result when an event has no requirements).

[Capt JB]

I do have the ID relationship.

 

I ´ll bet this will work. Thanks as I was near by but missed the point.

 

Thanks