Want a report of the assets on a purchase order along with the owner and location? Give this a try.
-- In owner's resource association, parentresourceguid is asset guid and childresourceguid is user guid select distinct j.name as 'PO Number', k.name as 'Asset', va.[Serial Number], va.[Asset Type], [Unit Cost] as 'Unit Cost', [Quantity] as 'Quantity', O.ParentResourceGuid as _ResourceGuid, IAL.Name as 'Asset Location', IU.Name as 'Owner', IUL.Name as 'Owner''s Location', va.Status, va.Manufacturer, va.Model, va.[System Number], va.Barcode, va.[Last Barcode Scan], va.[Asset Tag] -- va.* from inv_parent_lines rec join item i on rec.[_resourceguid] = i.[guid] --receiving slip join Inv_Line_Items li on rec.[parent line item id]=li.[line item id] --PO join item j on li.[_resourceguid]=j.[guid] join vReceivingslipassoresource rs on i.guid=rs.parentresourceguid join item k on k.guid=rs.childresourceguid --asset join vasset va on rs.childresourceguid=va._Resourceguid join vItem vi on vi.guid=va._Resourceguid join ResourceAssociation O on k.guid=o.ParentResourceGuid --join on the resource type inner join vResourceHierarchy vrh on --filter the resourceassociation table on the Owner resourceassociation (O.ResourceAssociationTypeGuid = 'ed35a8d1-bf60-4771-9dde-092c146c485a') and (vrh.Guid = O.ParentResourceGuid) --join User location left join ResourceAssociation U on (U.ResourceAssociationTypeGuid = '2030c6cd-c049-4c81-957d-34e4dfb23bcf') and (O.ChildResourceGuid = U.ParentResourceGuid) --join Asset location left join ResourceAssociation A on (A.ResourceAssociationTypeGuid = '05de450f-39ea-4aae-8c5f-77817889c27c') and (O.ParentResourceGuid = A.ParentResourceGuid) join Item IA on O.ParentResourceGuid = IA.Guid left join Item IAL on A.ChildResourceGuid = IAL.Guid join Item IU on O.ChildResourceGuid = IU.Guid left join Item IUL on U.ChildResourceGuid = IUL.Guid where j.name = '%PONUM%' order by j.name, k.name
You need to create a parameter called PONUM and prompt the user at run time to enter a purchase order number. I made mine a Fixed List that was a query of PO's (basically using the first four tables used in the FROM...JOINs from the query above).
Click to view.
Works on my system with demo data but has a lot of tables that have to have data in them such as the receiving slip. Hope it helps you get what you need.