Report on all calls and JOIN on h_sys_contacts

[chriscorcoran]

Hi 

 

I'm trying to do a quick report on all calls in the system but its only pulling back 6154 even though I have increased the limit to 25000. Any ideas? The report is fairly straight forward see filter below.

 

Thanks

Chris

 

 

[chriscorcoran]

Some more info. The report works fine and pull back over 15000 if I remove the join to the h_sys_contact table. The issue is I need the column company name which is found in his table, so if I add it back it ignores all internal calls that are logged, I'm guessing because internals don't have a company ID?

[Hornbill Staff DR]

Hi Chris,

 thanks for your post.

I believe the issue here is the type of JOIN you are using. A standard JOIN will only return records when there is a corresponding record in both tables. You should change your report to use a LEFT JOIN. A LEFT JOIN will return all records in the left table (h_itsm_requests) and the matching records in the right table (h_sys_contact).

The reason why some request records will not show a company name is indeed because the customer of these requests will be Basic users whos details are held in h_sys_accounts.

I hope that helps,
Dan