Skip to main content
Answer

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

  • July 2, 2023
  • 2 replies
  • 109 views
jhouser
Captain II
Forum|alt.badge.img+6

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

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.

1 Attachments
Contract for hire: https://www.linkedin.com/in/jesse-houser-052817b6/

2 replies

aaghaei
Captain II
Forum|alt.badge.img+10
  • aaghaei
  • Captain II
  • Answer
  • July 2, 2023

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.

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
Chris Hackett
jhouser
Captain II
Forum|alt.badge.img+6
  • jhouser
  • Author
  • Captain II
  • July 10, 2023

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

1 Attachments
Contract for hire: https://www.linkedin.com/in/jesse-houser-052817b6/
Chris Hackett
Terms & ConditionsCookie settingsAccessibility statement
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