Solved

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

  • 2 July 2023
  • 2 replies
  • 57 views
jhouser
Rank
Userlevel 4
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!

 

 

 

icon

Best answer by aaghaei 2 July 2023, 23:42

View original
1 Attachment

2 replies

aaghaei
Rank
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.

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*.
jhouser
Rank
Userlevel 4
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. 

1 Attachment

Reply


Terms & ConditionsCookie settings
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