GI Scenario - can I build it

  • 15 February 2024
  • 1 reply

Userlevel 5

I have two tables created but unsure how I can join these since I have different conditions & aggregate functions.


Our goal is to see the total # of Service hours for an Installs completed last week. Service hours are defined as estimated duration of inventory IDs on SORs that start with “SER”. Ideally table would look as follows:

Service Order Nbr.     Install Appointment #    Install Date   Total Service


Here is table 1:

  • The conditions here are FSSODET.InventoryID = Install
  • Grouped by FSServiceOrder.RefNbr
  • The Install Date is the FSAppointmentDet.TranDate and aggregate function set as Max (to give us the latest Install Date.


Here is table 2:

  • The condition here is FSSODET.InventoryID starts with “SER”.
  • Grouped by FSServiceOrder.RefNbr
  • The total service is the FSSODet.EstimatedDuration and aggregate function is set to SUM.




I’ve tried to combine these two tables but get inconsistencies - I’m unable to sum the FSSODet.EstimatedDuration while omitting the Install item. Not sure whether my approach is working - would love some help 


Best answer by dcomerford 15 February 2024, 22:01

View original

1 reply

Userlevel 7
Badge +12

With different group bys and aggregates you might be better consider a SQL view to do all the maths. Then use this to build a DAC fot your generic inquiry.


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