Item Search, Criteria:
CASE WHEN (NVL({locationqua...
# administrationm
mightyd3
07/19/2021, 4:58 PMItem Search, Criteria:
CASE WHEN (NVL({locationquantityavailable}, 0) <= 0 AND {inventorylocation} = 'test1' ) AND (NVL({locationquantityavailable}, 0) <= 0 AND {inventorylocation} = 'test2') THEN 1 ELSE 0 END
if one of these locations is empty on the inventory table, this criteria will filter out the product. as if NVL is not working. Can NVL not be used in criteria like this?
b
battk
07/19/2021, 5:07 PMCASE WHEN 0 <= 0 AND {inventorylocation} = 'test1' ) AND 0 <= 0 AND {inventorylocation} = 'test2') THEN 1 ELSE 0 ENDbattk
07/19/2021, 5:08 PMsimplifies to
CASE {inventorylocation} = 'test1' AND {inventorylocation} = 'test2' THEN 1 ELSE 0 ENDm
mightyd3
07/19/2021, 5:10 PMHey @battk, I am not sure I am catching your drift. Are you saying my formula is no good?
b
battk
07/19/2021, 5:12 PMyou need to be able to tell how useful
CASE {inventorylocation} = 'test1' AND {inventorylocation} = 'test2' THEN 1 ELSE 0 ENDm
mightyd3
07/19/2021, 5:13 PMThe formula is simply for filtering out items which may be in-stock somewhere. I want, in my results, items which are not in-stock across a few specific locations. Hence trying to NVL(inventory).
b
battk
07/19/2021, 5:13 PMthats not your problem
m
mightyd3
07/19/2021, 5:13 PMBecause the location may have a value, or not.
b
battk
07/19/2021, 5:13 PMinventorylocationm
mightyd3
07/19/2021, 5:17 PMIf both the locations contain a value, the formula works fine. So, I think in this scenario, it can?
b
battk
07/19/2021, 5:18 PMmake your formula not involve
locationquantityavailablem
mightyd3
07/19/2021, 5:20 PMWell, in that scenario, I would just put criteria of inventory location = those locations.
mightyd3
07/19/2021, 5:20 PM(not in a formula)
b
battk
07/19/2021, 5:21 PMdo the formula anyways
m
mightyd3
07/19/2021, 5:24 PMI guess it wouldn't make sense.
b
battk
07/19/2021, 5:27 PMagain, my statment remains:`inventorylocation` cant be 2 different values at once
battk
07/19/2021, 5:27 PMif you think it can, my recommendation is making a formula to confirm that information for yourself
m
mightyd3
07/19/2021, 5:38 PMSo, I broke it into two separate criterias: CASE WHEN (NVL({locationquantityavailable}, 0) <= 0 AND {inventorylocation} = 'test1' ) THEN 1 ELSE 0 END (for both locations)
the search works as expected. However, if one location has null inventory - the item will not appear in the results.
b
battk
07/19/2021, 5:47 PMnot sure how you implemented your search criteria
battk
07/19/2021, 5:51 PMyour description of
for both locationsis not really enough to tell what you did with your search criteria
m
mightyd3
07/19/2021, 6:04 PMnew criteria line of that formula for 1 location.
mightyd3
07/19/2021, 6:04 PMa 2nd criteria line with the same formula, but a different location
b
battk
07/19/2021, 6:17 PMare you using expressions?
m
mightyd3
07/19/2021, 7:35 PMThanks for the help battk. I tried this like 50 different ways. End result seems like you are correct. NetSuite won't allow what I am trying to do. Adjust the search and will just filter the rest via script.
4 Views