GI to show only lines with an empty value in joined field of child table?

Userlevel 5
Badge +1

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!





Best answer by aaghaei 2 July 2023, 23:42

View original

2 replies

Userlevel 7
Badge +9

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.

Userlevel 5
Badge +1

@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. 


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved