Solved

Is there a way to show line detail data (no grouping) yet pull the min (first) shipment on an orders data only?

  • 14 September 2022
  • 13 replies
  • 273 views
hayleehicks
Rank
Userlevel 4
Badge

I’ve tinkered with the groupings and aggregates for a bit and haven’t come up with a way to do this and wanted to check here first before going the SQL view route. I have an inquiry that is showing shipment line detail per order. I only want to see the data for the sales order’s FIRST shipment though. How can I accomplish this without grouping by order nbr and doing a min on the shipment nbr? I still want to see the line detail and the grouping then takes this option away.

Thanks!

icon

Best answer by lauraj46 15 September 2022, 00:40

View original
Haylee Hicks // i-Tech Support // ERP Client Success Manager

13 replies

lauraj46
Rank
Userlevel 7
Badge +7

Hi @hayleehicks18 ,

If I’m understanding correctly, I think you could accomplish this using a self-join. 

The trick I’ve used before is to include the DAC twice using a LEFT join. 

  Be sure to join the two tables on the fields that you would normally group on.  Add another row to the join that compares the field you are using to determine the “first” shipment.

 

The record you’re looking for is the one that doesn’t have any other lower shipment numbers (or whatever criteria you are using to find the “first” record).  This goes in the conditions - if there aren’t any matching records then the values of the fields for the SOOrderShipment table will be empty.

 

It’s a bit hard to explain in words, but I’ve attached a sample GI.  Just be careful to get the joins right or you may cause some performance issues in your instance with a Cross Join :)

You could add additional joins to pull in the shipment detail, just be sure to join on the FirstSOOrderShipment and there shouldn’t be any need to group.

Hope this helps!

Laura

1 Attachment
Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
hayleehicks
Rank
Userlevel 4
Badge

Hi @lauraj46! This is great! Thanks for the quick response. How would you handle when more than 2 shipments? I just tested, and it’s giving me the first and second shipment and excluding the third, so it’s close. When I join to ship line, it also looks like it’s doubling the amount of lines for shipment 1 and not shipment 2.

Haylee Hicks // i-Tech Support // ERP Client Success Manager
lauraj46
Rank
Userlevel 7
Badge +7

Hi @hayleehicks18 ,

It sounds like something maybe reversed.  Can you attach your GI?

Laura

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
hayleehicks
Rank
Userlevel 4
Badge

Of course! See attached. All I did was import your XML in and link to the SOShipLine table. I also have a condition to view the data for a specific order nbr I’m testing with, so you may need to modify on your end to an order nbr from your instance.

1 Attachment
Haylee Hicks // i-Tech Support // ERP Client Success Manager
lauraj46
Rank
Userlevel 7
Badge +7

Hi @hayleehicks18 ,

I think the problem is with OR instead of AND on the conditions tab.  Please change this to AND and try again. I tested on a sales order with three shipments and it seemed to work as expected.  

Laura

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
hayleehicks
Rank
Userlevel 4
Badge

Hi @lauraj46! I initially had AND, but it gives me zero results. I’m not sure what would be different about my three shipments on the order.

Haylee Hicks // i-Tech Support // ERP Client Success Manager
lauraj46
Rank
Userlevel 7
Badge +7

Definitely needs to be AND, but it looks like I had the condition reversed on the Left Join.  It should be “Greater Than” not “Less Than”.  

Let me know if this works better :)

1 Attachment
Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
BenjaminCrisman
Rank
Userlevel 7
Badge +4

@lauraj46 That GI still didn’t work for me, I got many duplicates in my results using Less Than and zero results using Greater Than.

I know I’ve seen (and created) GIs which can do this, but I don’t get as much time to work on GIs as I used to, so I’m a tad rusty I guess😭

I was able to get the correct result if I added a grouping on shipmentnbr or a condition that SOShipLine.LineNbr = 1

lauraj46
Rank
Userlevel 7
Badge +7

HI @BenjaminCrisman ,

Using the SOShipLine.LineNbr is a good idea in this situation!

I know there is a way to configure the GI to do this as I have used this paradigm for similar scenarios, but apparently I can’t quite get my head around it today either. I’ll try to straighten this out and post here for reference once I work it out 😂.

Laura

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
BenjaminCrisman
Rank
Userlevel 7
Badge +4

@hayleehicks18 @lauraj46 Ok, I randomly remembered some steps I had taken before and I think I got it.

Using Greater Than the CreatedDateTime instead of ShipmentNbr works better for this type of situation, I also switched the starting table to SOShipment, and then used the second SOOrderShipment table to link to SOShipLine.

Let me know if this looks better 

1 Attachment
lauraj46
Rank
Userlevel 7
Badge +7

@BenjaminCrisman @hayleehicks18 ,

Here’s one more go at it…

I started with what Benjamin did and changed the filter to the FirstSOOrderShipment table.

And I changed this join to start with FirstSOOrderShipment since that is the record we want in the results.

 

1 Attachment
Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
hayleehicks
Rank
Userlevel 4
Badge

Woohoo!! That latest one worked. I just checked against my client’s data. Thank you guys so much for your help!!

Haylee Hicks // i-Tech Support // ERP Client Success Manager
lauraj46
Rank
Userlevel 7
Badge +7

Awesome, @hayleehicks18 .  Thanks for the update 😊!

Laura

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.

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