Skip to main content
Solved

GI to Exclude SO from list where item meets criteria

  • 13 July 2022
  • 5 replies
  • 111 views

Hello,
I have a GI where I need to include certain Sales Orders AND exclude orders from that subset where they have 1 particular NONSTOCK inventory item "COLOFEE"

Hoping someone can help with the funky GI conditions functions:

image

 

image

 

I can do this in sql using the Having clause:

Select c.OrderNbr
from soorder C, Soline A, Inventoryitem B
where c.OrderNbr=a.OrderNbr
and A.inventoryid=B.inventoryid
and B.inventorycd = 'colofee'
group by C.OrderNbr
having sum(case when b.inventoryCd = 'colofee' then 1 else 0 end) = 0

help is appreciated!

k2

5 replies

K2a
Rank
Userlevel 4
Badge +1

@DanielByrum that definitely seems to work.  I’ll check to see if we’re up for doing that in our PROD enviro.

 

K2a
Rank
Userlevel 4
Badge +1

@DanielByrum that’s an interesting idea...I will look at that.

D
Rank
Userlevel 2
Badge

Have you tried adding a UDF that the import scenario can ‘mark’ the order as updated? Then filter the GI on that UDF. We use something similar to mark orders as printed/purchased.

K2a
Rank
Userlevel 4
Badge +1

Naveen,
A bit more background: 

Colorado just implemented a 0.27 retail sales tax on anything delivered by vehicle in the state.   So when our Shopify orders originate with a ship-to in CO they capture this 0.27 fee.   However the commerce connector does NOT bring this over into ACU.  Since we are using Avalara (and so is Shopify) we’ve been instructed to create a $0 non-stock item and add it to the SO and it will create the tax line.  


I’m trying to automate adding the non-stock line to the SO.  I have an import scenario based on this GI (XML Attached) that updates the SO perfectly.  The challenge is this GI still recognizes the SO as needing the the non-stock item after it has been added because of my criteria (state = CO, status = Open). 

I need to evaluate ALL the rows in a Sales Order and only surface the ones that meet my criteria and DO NOT have the Non-Stock item (COLOFEE) attached! (i.e. the “having” clause in sql)

I heard that grouping in GI’s doesn’t work very well so you will see an aggregate function where I am trying to add up a field to get a filtering criteria...=iif([InventoryItem.StkItem]=1,0,1)

Any ideas are appreciated!

we are on 2021R1.

1 Attachment
Naveen Boga
Rank
Userlevel 7
Badge +19

Hi @K2a  Please share the XML format of the GI here?

Naveen B

Reply


Terms & ConditionsCookie settings