Solved

Create a Generic Inquiry that Shows Only Orders with Specific Items

  • 5 September 2023
  • 5 replies
  • 197 views
B
Rank
Userlevel 4
Badge

Hello, 

I’m trying to create a GI that only shows orders that have specific items included. 

The items are all inventory items that start with 6. 

Here are some logical examples: 

  1. If an order has several items
    1. 60001
    2. 60002
      1. This order should be included in the GI
  2. If an order has several items
    1. 60001
    2. 10001
      1. This order should NOT be included in the GI
  3. If an order has only one item
    1. 60001
      1. This order should be included in the GI. 

Does anyone have an idea on where I should start? 

Thanks!

 

icon

Best answer by dcomerford 5 September 2023, 21:39

View original

5 replies

dcomerford
Rank
Userlevel 7
Badge +12

Here you go easier than screenshotting it as i was working on another GI. SO Line will contain the line detials and join the Inventory Table. You can add the SOORder table if needed etc. The Condition is using a Start with of A as my data set my items mostly begin with A

1 Attachment
Dermot Comerford
B
Rank
Userlevel 4
Badge

Hi @dcomerford 

Thanks for sharing this!

It’s really close, but it’s still populating orders that have the item I’m looking for PLUS additional items. 

For example, here is an order CP043339 that has these 3 line items which are all gift boxes. (Gift boxes is what I’m trying to filter on). 

However, if I look into the order details, it includes other items as well. 

 

Since this order included items other than ID starts with 6 (gift box), I don’t want that order to show up on the GI. 

Right now, it looks like it’s only showing line items that do start with 6, but how do I ONLY show orders where there is nothing but an item that starts with 6? 

Thanks!

palbores
Rank
Userlevel 3
Badge

I think this is not possible in GI of Acumatica. It is possible if you will customize an Inquiry screen and add the logic in the view delegate to filter the order who have several items that don’t have a starting name of ‘6’ to not include it.

~ Phil
dcomerford
Rank
Userlevel 7
Badge +12

@bclark Sorry i missed the piece where if the order has serveral items and some of them began with 6 and you did not want it to display.

Try this new GI we so we are using an IIF statement in the results to set a value of 1  if it begins with a ‘A’ you can change this to be 6 and 0 if not we are then using the Aggregate option to sum this up so we get a total value for each order. A 1 means an order has all lines begining with A

You can then use a Saved Default filter to only show you the 1

 

1 Attachment
Dermot Comerford
B
Rank
Userlevel 4
Badge

@dcomerford 

Thank you for looking into this more!

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