Is it possible to have a list of items with no tra...
# generall
Luis
06/21/2023, 5:47 AMIs it possible to have a list of items with no transactions yet in a specific location? And another that have no transactions in a specific location for a specific period?
watching following 1
d
David B
06/21/2023, 9:11 PMshared to #C2A1ZEMF0, interested to know the answer.
I think it could be done with SuiteQL, but not sure if/how it could be done via UI (search/SA workbook)
l
Luis
06/21/2023, 10:58 PMThank you!
l
leo_ns
06/21/2023, 11:16 PMi think years back i was given a workaround for a similar requirement. that is create a hidden custom item field, "hasTransaction". when sold, flag to true.
🤔 1
d
David B
06/21/2023, 11:21 PMactually, I think you'd be able to do this with a summary search/summary criteria. However the caveat is that it would only return items that had at least one transaction (no necessarily in the target location)
in results, summarise by grouping item. in summary criteria, create a numeric formula with something similar to this (summary MAX, equal to 0):
CASE WHEN ${transaction.location} = 'targetLocationName' THEN 1 ELSE 0 ENDDavid B
06/21/2023, 11:23 PMpretty hefty caveat though IMO..
I like leo's idea, but I think you'd run into a similar limitation when you tried to report on 'no transactions in a location for a specific period'
l
leo_ns
06/21/2023, 11:27 PMyep. more complex requirements. location, time periods...
leo_ns
06/21/2023, 11:29 PMhow about making the location and time period criteria shown in the filter. then set sum(qty) = 0
leo_ns
06/21/2023, 11:30 PMlocation can be multi-select
d
David B
06/21/2023, 11:32 PMI don't follow, sorry 😕
David B
06/21/2023, 11:44 PMFWIW here's a SQL query that works:
Copy code
SELECT
itemId
FROM
item
WHERE NOT EXISTS
(SELECT NULL FROM transactionLine WHERE transactionLine.item = item.id)l
Luis
06/22/2023, 3:42 PMOhhh great! Thanks. Are you able to lead me to the right direction on how I can exclude items that do not have Quantity on hand the day before the period I want to analyze?
In saved search, I have to query all the inventory affecting transactions. Is that also the case for that SuiteQL tool?
5 Views