Creating a Custom K1000 Report
I'm looking to create a report that will show the asset name, system description, Gl number and location. this is pretty easy but I'm looking for a way to also show from history "who" changed a gl or location and "when" if possible.
Community Chosen Answer
Try this out - you may need to use the report wizard to create a dummy report and go into the SQL version of the dummy report to get the RD columns and LEFT JOIN statements to work for your setup, but otherwise it should give you want you need.
Hope that helps!
SELECT A.NAME, AH.DESCRIPTION, A.MODIFIED, U.FULL_NAME,
RD3.FIELD_9 AS PO_NUMBER, RD3.FIELD_6 AS UNIT_COST
LEFT JOIN ASSET A on (ASSET_DATA_5.ID = A.ASSET_DATA_ID AND A.ASSET_TYPE_ID=5)
LEFT JOIN ASSET_ASSOCIATION JR3 on (JR3.ASSOCIATED_ASSET_ID = A.ID AND JR3.ASSET_FIELD_ID=3)
LEFT JOIN ASSET R3 on (R3.ID = JR3.ASSET_ID)
LEFT JOIN ASSET_DATA_7 RD3 on (RD3.ID=R3.ASSET_DATA_ID)
JOIN ASSET_HISTORY AH on (AH.ASSET_ID = A.ID)
JOIN USER U on (U.ID = AH.USER_ID)
WHERE A.ID = AH.ASSET_ID