220
I’ve been asked to produce a report (excel) on inventory items and one of the request was to include a yes/no field to indicate if the item has an attachment or not. Do you know how I can do this – how can I could query it out?
We’re running GP 2013.
Thanks,
Mike
*This post is locked for comments
SELECT A.ITEMNMBR ,
A.ITEMDESC ,
CASE ISNULL(B.ItemNumber, '')
WHEN '' THEN 'No'
ELSE 'Yes'
END Attachment,
ISNULL(B.CreatedDate,'') AS CreatedDate,
ISNULL(B.CreatedUser,'') AS CreatedUser
FROM dbo.IV00101 AS A
LEFT OUTER JOIN ( SELECT RIGHT(RTRIM(BusObjKey),
LEN(BusObjKey)
- ( CHARINDEX('Item Maintenance',
RTRIM(BusObjKey), 1)
+ LEN('Item Maintenance') )) AS ItemNumber ,
CREATDDT AS CreatedDate ,
CRUSRID AS CreatedUser
FROM CO00102
WHERE BusObjKey LIKE '%Item Maintenance%'
) AS B ON A.ITEMNMBR = B.ItemNumber
ORDER BY Attachment DESC
I found the table, I had a zero where an 'O' should have been - but, I get this error now:
Msg 207, Level 16, State 1, Line 18
Invalid column name 'AttachmentOrigin'.
Where/What is the C000102 table? I'd tried finding in in my company database, and the Dynamics database but it doesn't seem to exist?
Mike
This is an interesting case, I have just worked it out on my test machine. Here is the SQL script for that,
SELECT A.ITEMNMBR ,
A.ITEMDESC ,
CASE ISNULL(B.ItemNumber, '')
WHEN '' THEN 'No'
ELSE 'Yes'
END Attachment,
ISNULL(B.CreatedDate,'') AS CreatedDate,
ISNULL(B.CreatedUser,'') AS CreatedUser,
ISNULL(B.DELETE1,'') AS Deleted
FROM dbo.IV00101 AS A
LEFT OUTER JOIN ( SELECT RIGHT(RTRIM(BusObjKey),
LEN(BusObjKey)
- ( CHARINDEX('Item Maintenance',
RTRIM(BusObjKey), 1)
+ LEN('Item Maintenance') )) AS ItemNumber ,
AllowAttachmentFlow ,
AllowAttachmentEmail ,
AttachmentOrigin ,
DELETE1 ,
CREATDDT AS CreatedDate ,
CRUSRID AS CreatedUser
FROM CO00102
WHERE BusObjKey LIKE '%Item Maintenance%'
) AS B ON A.ITEMNMBR = B.ItemNumber
ORDER BY Attachment DESC
The script above will retrieve all your items, along with the attachment index, with a value of Yes or No.
Your feedback is highly appreciated,
#1
André Arnaud de Cal...
292,516
Super User 2025 Season 1
#2
Martin Dráb
231,432
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
