Hi, 

i'm creating a group report for Tickets so that I can easily pull out the report. However, I cannot locate the Ticket source field. It might e named differently.  Can you help me locate it? Thanks

The field is Ticket:VIACode.

You have to link to Picklist table to get the text.

Right click the ViaCode and select Global Join to Picklist. Then it will appear underneath (as a linked table) - you then drag the Text field to the group (not ViaCode).

Or, you can just drop the ViaCode directly into the group, double click it and in FormatType select PicklistItem.

Thanks that's a great help. One more thing, do you know where can I pull out. The serial No. field from the ticket just like the source I don't know where to get it and include it as a fiedl in the group?

SerialNumber is not a field in Ticket. Rather, it's a link to the table accountproduct :

(TICKET:ACCOUNTPRODUCTID>ACCOUNTPRODUCTID.ACCOUNTPRODUCT!SERIALNUMBER)

So, to do this in Query Builder:

1) Right click the Ticket AccountProductID

2) Select Create Local Join, select the child table AccountProduct

3) On the join field select AccountProductID from the drop down

4) Select Left Join

You'll now see a new table appear in the list on the left. Select SerialNumber from AccountProduct into your layout.

Hi Mike,

I was able to follow your instruction but when I tried dragging the Serial No. into the group layout no data appeared. Is there anything that I have to do in the format type?

Thanks

No, formattype isn't relevant. The data will, of course, only show when there is a matching row within Ticket (Serial Number is filled in) and AccountProduct (where a link exists). You can change the join to Inner to show only those that have this filled in to test.

This is the equivalent SQL Syntax - to show only those tickets with linked serial numbers;

SELECT A1.TICKETID, A2.ACCOUNT A2_ACCOUNT, A1.VIACODE, A3.SERIALNUMBER A3_SERIALNUMBER 
FROM TICKET A1
INNER JOIN ACCOUNTPRODUCT A3 ON (A1.ACCOUNTPRODUCTID=A3.ACCOUNTPRODUCTID)
LEFT OUTER JOIN ACCOUNT A2 ON (A1.ACCOUNTID=A2.ACCOUNTID) 
ORDER BY A1.TICKETID ASC 

Thanks. I'll have this reviewed. Also, when you mean account product are you referring to Account asset because that it where I usually type the serial no. for it to show in the ticket detailed view.

Thanks

Yes, whilst it's true that's what you do in the front end (asset is the new name of product) the actual data is stored in the table ACCOUNTPRODUCT (as there is no table called Asset of course).

I think that is now clear. Thanks for the help.