https://netsuiteprofessionals.com logo
Join Slack
Powered by
Hey experts, I’m looking for a way to find all mis...
# suiteql
n
Hey experts, I’m looking for a way to find all missing “item location configuration” records against items. (It’s when the advanced item location configuration feature is enabled). I believe we need to work with joins between item, location and itemlocationconfiguration tables, but I can’t get my head around it.
c
How do you define "missing?"
😇 1
n
So with the feature enabled, we can create an independent record that is handling the data per item per location. But the record is not created automatically. So if for a specific item, for a specific location that should handle inventory (location record, make inventory available), the item location configuration record does not exist, the script will create.
c
Will you be creating a query to determine whether a specific item / location combination exists, or do you need a query that shows all item / location combos that don't exist?
n
The ones that don’t exist in order to create them
I was thinking of using where not exists condition but I’m not sure how to look for item and location within the ILC record… I guess there is a full join at first for items and location where make inventory available is true, then something like looking for every combinations vs the ILC records and report only the missing ones
c
Try something like this
Copy code
SELECT
  item.id AS item_id,
  location.id AS location_id
FROM item
CROSS JOIN location

WHERE item.id||'_'||location.id NOT IN (
  SELECT item||'_'||location
  FROM itemlocationconfiguration
)
You'll need to tweak it to filter based on your location-specific requirements of course
maybe
Copy code
SELECT
  item.id AS item_id,
  location.id AS location_id
FROM item
CROSS JOIN location

WHERE
  item.id||'_'||location.id NOT IN (
    SELECT item||'_'||location
    FROM itemlocationconfiguration
  ) AND
  location.makeinventoryavailable = 'T'
n
hooooo that's cross join !!! 🤦🏻‍♂️ Thanks a lot 🙏🏻
c
You're welcome! There's probably a smarter way to detect when the item+location combo isn't there, but this string concatenation seems to work! Please rigorously validate 🤓
n
Ha ha yes, I'm using your idea to try to use ItemLocationConfiguration.item = Item.id AND ItemLocationConfiguration.location = Location.id 🙂
✨ 1
Copy code
SELECT item.itemid AS item_id,
       location.id AS location_id
FROM   item
       CROSS JOIN location
       LEFT JOIN itemlocationconfiguration
              ON itemlocationconfiguration.item = item.id
                 AND itemlocationconfiguration.location = location.id
WHERE  location.makeinventoryavailable = 'T'
       AND itemlocationconfiguration.item IS NULL;
That was the easy part... Because with seeing the results, I see now that I forgot to exclude the locations based on the subsidiaries selected on the item record 🤯🤦🏻‍♂️
Thanks to @tdietrich (https://timdietrich.me/blog/netsuite-suiteql-suitescript-multiple-subsidiaries-locations/) Here is the result:
Copy code
SELECT
   inventoryitemlocations.item AS item_id,
   inventoryitemlocations.location AS location_id 
FROM
   inventoryitemlocations 
   LEFT JOIN
      itemlocationconfiguration 
      ON itemlocationconfiguration.item = inventoryitemlocations.item 
      AND itemlocationconfiguration.location = inventoryitemlocations.location 
WHERE
   inventoryitemlocations.item = 5805 
   AND itemlocationconfiguration.item IS NULL;
Since inventoryitemlocations does not have the names of item or location, here is the final result:
Copy code
SELECT
   inventoryitemlocations.item AS item_id,
   inventoryitemlocations.location AS location_id,
   item.itemid AS item_name,
   location.name AS location_name 
FROM
   inventoryitemlocations 
   INNER JOIN
      location 
      ON (location.id = inventoryitemlocations.location) 
   INNER JOIN
      item 
      ON (item.id = inventoryitemlocations.item) 
WHERE
   NOT EXISTS 
   (
      SELECT
         1 
      FROM
         itemlocationconfiguration 
      WHERE
         itemlocationconfiguration.item = inventoryitemlocations.item 
         AND itemlocationconfiguration.location = inventoryitemlocations.location 
   )
;
It won't work eventually 😞 The inventoryitemlocation table is actually only populating if there is / was inventory against the item.... So when an item is brand new, there is no activity yet, hence no inventoryitemlocation lines 😞 I need to go back to the original idea to check the subsidiaries on the item record, extract all inventory ocations from these subsidiaries and then do the match. I'll be back 😉
c
Good luck out there! 💪
🙏🏻 1
s
Ooh, please share if you find something that doesn't just time out. I have a query that finds items without any itemlocationconfiguration lines, or items with less than 11 (which is our number of active locations, meaning at least one is missing). We then just import to create new ones and let it fail on ones that already exist. I believe I tried to have it compare to the existing but gave up after it was running for 20 minutes and never completing. It is pretty rare that my script to create them for each item upon item creation doesn't work, so random imports are OK for now.
n
Could you please share what you have? Maybe it will help 🙂
c
@Nairolf Does your project allow you to separate queries and process results in Suitescript?
✔️ 1
s
So caveat I haven't seen the results in a long time as someone else monitors it and does the import now, but it should just dump you a list of items, locations where the itemlocationconfiguration doesn't exist, and a item-location combo field to use for the label on the record to create.
Copy code
WITH cteItems AS (                              
  SELECT item.id, itemid
  FROM item
  LEFT OUTER JOIN (
    SELECT item, COUNT(*) as ilc_count
    FROM itemLocationConfiguration
    GROUP BY item
  	) ilc ON item.id = ilc.item
	WHERE itemtype = 'InvtPart' AND (ilc.ilc_count < 11 OR ilc.ilc_count IS NULL)
)
SELECT locid, itemid, itemsku, locationname, CONCAT(CONCAT(itemsku, ' - '), locationname)
FROM 
 (SELECT loc.id as locid, loc.name as locationname
  FROM location loc
	WHERE loc.isinactive = 'F'
  ) A
CROSS JOIN 
 (SELECT id as itemid, itemid as itemsku 
  FROM cteItems                 
  ) B
thanks 1
n
@Clay Roper yes, and I believe I won’t have a choice there since SuiteQL does not support “Recursive”.
So I’ll need to find the subs children with SuiteScript and then do the SQL
5 Views
Open in Slack
PreviousNext
Powered by