Skip to main content

Hi everyone, 

I am working on a GI for field service appointments. I need to show Detail lines for completed appointments that do not have a corresponding Log line. The related fields are Detail: LineRef and Log: DetLineRef. The result seems correct, but, I can’t get it to filter to show only the lines with an empty DetLineRef. When I try to filter or sort that field it acts as though I am filtering LineRef from the Detail table. I’ve tried conditions, filters, and different joins, but can’t seem to figure it out. A screenshots below to illustrate, and I’ve also attached the XML.

I need to see only rows where Detail Ref. Nbr. is empty. Thanks in advance for any help!

 

 

 

The FSAppointment has a pretty complex structure and although FSAppointmentDet, FSAppointmentEmployee and FSAppointmentLog have n-1 relation with FSAppointment, the children themselves have n-n relation. 

First, the proper keys are SrvOrdType and RefNbr which links the FSAppointment to the children. 

Second, you are missing the join between FSAppointmentDet.StaffID, FSAppointmentEmployee.EmployeeID and FSAppointmentLog.BAccountID

These are the major issues I see in your GI but still, you might need to fine-tune it further to get it to work.


@aaghaei Thanks for your response! As you suggested, instead of joining Detail to Log, I joined both to Appointment using SrvOrdType and RefNbr. This got me closer and I was able to figure out the rest. 

 

Also, turns out they wanted appointments with no Log lines at all. This made it a bit easier. Attached is an XML of what I have at this point, if anyone is interested. 


Reply