Anyone know why I can't see the AggregateItemLocat...
# suitescriptc
Craig
06/26/2024, 2:06 PMAnyone know why I can't see the AggregateItemLocation table here?
e
erictgrubaugh
06/26/2024, 2:15 PMFWIW I can't see that table in my Records Catalog
c
Craig
06/26/2024, 2:15 PMIt does exist though, I can query it.
m
Mike Robbins
06/26/2024, 2:32 PMI've noticed that too with some tables. They show in the main search, but if you navigate to a related table, then click the target table in the join list, you can see the table details.
j
jen
06/26/2024, 3:04 PMthere are tables you can get to that aren’t in the menu. I often will just directly edit the URL to get where I want to.
c
Craig
06/26/2024, 3:13 PMIs there a syntax guide for suiteql. Things I would expect to work do not work
Craig
06/26/2024, 3:14 PMe.g.
WHERE Item IN (16862, 16863, 16864, 16865)
AND Location = 'name of location'
Craig
06/26/2024, 3:14 PM Copy code
Search error occurred: Invalid or unsupported searchCraig
06/26/2024, 3:14 PMworks fine if I remove the AND
c
Clay Roper
06/26/2024, 3:18 PM@Craig When you remove just the AND and leave the rest of the clause there?
c
Craig
06/26/2024, 3:19 PMWorks fine in that scenario
Craig
06/26/2024, 3:19 PMWHERE Item IN (16862, 16863, 16864, 16865)
Craig
06/26/2024, 3:19 PMSELECT
BUILTIN.DF(AIL.Item) AS Item,
BUILTIN.DF(AIL.Location) AS Location,
AIL.QuantityOnHand,
TO_CHAR(AIL.LastQuantityAvailableChange, 'YYYY-MM-DD HH12MISS PM') AS LastQtyAvailChangeTS,
AIL.OnHandValueMLI AS Value,
AIL.AverageCostMLI AS AverageCost,
AIL.LastPurchasePriceMli AS LastPurchasePrice,
AIL.PreferredStockLevel AS PreferredStockLevel,
AIL.LeadTime AS PurchaseLeadTime,
AIL.SafetyStockLevel,
AIL.LeadTimeOffset,
AIL.QuantityOnOrder,
AIL.QuantityCommitted,
AIL.QuantityAvailable,
AIL.QuantityBackOrdered,
AIL.QuantityInTransit,
AIL.QtyInTransitExternal AS QuantityInTransitExternal,
AIL.LastInvtCountDate AS LastCountDate,
AIL.NextInvtCountDate AS NextCountDate,
AIL.InvtCountInterval AS CountInterval,
AIL.InvtClassification AS Classification,
ITM.itemid
FROM
AggregateItemLocation AIL
JOIN
item ITM ON AIL.Item = ITM.id
WHERE
ITM.itemid IN ('SECRET NAME!')
ORDER BY
AIL.Item,
AIL.Location
c
Clay Roper
06/26/2024, 3:20 PM@Craig That makes more sense 😄 you can't use aliases in your WHERE clause, so you'd need to use
BUILTIN.DF(AIL.Location)Locationc
Craig
06/26/2024, 3:21 PMAND BUILTIN.DF(AIL.Location) = 'NAME'
Craig
06/26/2024, 3:21 PMworks - thanks!
🙌 1
96 Views