Skip to main content
Solved

GI to Exclude SO from list where item meets criteria


K2a
Jr Varsity III
Forum|alt.badge.img+1
  • Jr Varsity III
  • 92 replies

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

Best answer by DanielByrum

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.

View original
Did this topic help you find an answer to your question?

5 replies

Naveen Boga
Captain II
Forum|alt.badge.img+19
  • Captain II
  • 3381 replies
  • July 13, 2022

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


K2a
Jr Varsity III
Forum|alt.badge.img+1
  • Author
  • Jr Varsity III
  • 92 replies
  • July 13, 2022

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.


Forum|alt.badge.img
  • Jr Varsity III
  • 15 replies
  • Answer
  • July 13, 2022

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
Jr Varsity III
Forum|alt.badge.img+1
  • Author
  • Jr Varsity III
  • 92 replies
  • July 13, 2022

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


K2a
Jr Varsity III
Forum|alt.badge.img+1
  • Author
  • Jr Varsity III
  • 92 replies
  • July 13, 2022

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

 


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings