Hi,

I am trying to create service call database as following:

Table1:Service_Call

Field_1:Service_Call_ID

Field_2:Service_Call_Assigned_Enginners

Field_3:Service_Call_Status

Table2:Assign_Task

Field_1:Service_Call_ID

Field_2:Engineer_Name

Field_3:Task_Date

Table3:Service_Visit

Field_1:Service_Call_ID

Field_2:Service_Visit_Status

Field_3:Service_Visit_Date

The Problem is:(in screenshot2)

How can I define the caculation to make Service_Call_Assigned_Enginners="Ben+Ken+Jason"? And Service_Call_Status="In_Progress"? (if there's a "Finished" status in "Children", the status of Service_Call will be "Finished", otherwise it's set to the latest status of the "Child".)

Screenshot 1/2 and sample file are attached.

Service_Call.zip

Edited June 19, 200619 yr by Guest

If your Engineer names can be in alphabetical order then you can use a value list.

For the status field you pretty much spelled it out. You need to define a field in Service_Call that = "Finished" and then link this to a new Table Occurrence of ServiceVisit where:

ServiceCall::ID_ServiceCall = ServiceVisit::id_ServiceCall

AND

ServiceCall::StatusFinished = ServiceVisit::Status

Example attached

Service_Call_Revised.zip

Edited June 19, 200619 yr by Guest

Thanks!

Your solution just open my mind.

Thanks again, I love this forum.