Skip to main content
Visit Freshworks.com Visit Freshworks

I want to export and send information to sales of tickets that had the word “xxx” in the subject or the description field of a ticket and append on the user contact info.

 

I used admin - account - details - export data to get the current database from freshdesk

I decided to use Microsoft access database to do the left joins between the ticket table and the user table.

I imported the the tickets, companies, and users xml file into the database

I had to change the long fields like body, body-html, description, and description to type memo before importing, 

It was a bit tedious to import the 39 ticket files and 31 user files but it worked fine.

 

I discovered by trial and error that the the helpdesk-ticket request-id is a key to the user table’s id field

and the customer-id field is a key to the company table id field. 

All worked fine and I ended up with this query.  Not all the company names populated so maybe customer-id is not foreign key for company table id field.

 

SELECT /helpdesk-ticket].subject,

bhelpdesk-ticket].description,

user.email,

user.name,

user.phone,

company.name
FROM (ehelpdesk-ticket] LEFT JOIN tuser] ON Ihelpdesk-ticket].drequester-id] = user.id)

LEFT JOIN company ON user.pcustomer-id] = company.id
WHERE (((rhelpdesk-ticket].subject) Like "*xxx*")) OR

              (((helpdesk-ticket].description) Like "*xxx*"));

 

Then I wanted to do the same with the helpdesk-note table

there are 2 id fields in the helpdesk-note tables id and user-id

I assumed the user-id would work in user table and it did except it returned the responders information rather then customer information.

I guessed the the id field must somehow be a key to the helpdesk-ticket table.

I could not find out what id field in helpdesk-ticket to use to get customer information.

 

So here are my questions:

What id field in helpdesk-ticket to use to get customer information from helpdesk-note?

For future reports: what fields in tables are foreign keys to other tables (e.g. Relationships)?

 

Is there a better way to do this.? 

I can’t find my apikey so it does not seem I can use the api’s.  maybe because my account is a sprout.

The table field names are all different in the api so that documentation won’t help me.

 

Hello @davet, I hope you are doing well today. I am able to understand that you exported the account date to get the ticket details and you are trying to import it into another system. I’ll attempt to answer your questions here. In the account export, you would find requester-id which is the ID of the contact. In the helpdesk notes table, the user_id would be the ID of the agent/customer who added the note. That’s why for a few instaces, it would return customer information and in other cases, it would return agent information. 

 

 

If you have the list of users from the account export, you’d find another field, helpdesk-agent and if it’s true, the ID belongs to an agent and if it’s false, it’s a customer. If you have access to API, you can find this by using the list all contacts and agents API.

 

You can use this boolean field along with the ID to come to conclusion if it’s an agent reply or a customer reply. 

 

If you could let us know what fields you are trying to import into the new system, I’ll check if we can get this done more efficiently. Let us know if this was helpful.

i have done an account export for tickets and received the content in xml. the xml tags and the backend field name are not matching, how can i find the backend field name for each of the xml tag

Reply


Terms of Use