Knowledgebase
Export the client list
Posted by Mikhail Sergievskiy (AIT) on 15 January 2015 10:11 PM

Question: How can I export the full client list from Projetex?

 

Answer: We have prepared a query for you, which will extract client information from the Projetex database. 

Please copy the code to the clipboard, then open the Projetex Server Administrator, proceed to the "Corporate Settings" - "Queries" tab, click "New" and paste the code into the "Content" field. 

Please name the query "Clients - List" (or similar) and click OK.

Then highlight the query, click "Export Data", choose the file format and save the file.

Here is the code:

 

/*This report shows client information with prices.*/
SELECT cl.client_name,cl.client_street1,
cl.client_street2,
cl.client_city,
cl.client_state,
countries.coun_name,
cl.client_zip,
cl.client_www,curr.curr_name,
cl.client_vatnum,
cl.client_email1,
cl.client_email2,
cl.client_phone1,
cl.client_phone2,
cl.client_phone3,
cl.client_phone4,
cl.client_fax,srv.serv_name,
units.unit_name,
cp.price
FROM curr, countries, clients cl
left join cprices cp on cl.client_id = cp.client_id
left join units on units.unit_id = cp.unit_id
left join services srv on srv.serv_id = cp.serv_id
WHERE
curr.curr_id = cl.curr_id
AND countries.coun_id = cl.coun_id
ORDER BY 1

 

And as a bonus, this code which exports not only the client names, but all contacts created within the client records too:

 

/*This report shows client information with contacts and prices.*/
SELECT cl.client_name,cl.client_street1,
cl.client_street2,
cl.client_city,
cl.client_state,
countries.coun_name,
cl.client_zip,
cl.client_www,cc.ccon_title,
cc.ccon_name,
cc.ccon_email1,
cc.ccon_email2,
cc.ccon_phone1,
cc.ccon_phone2,
cc.ccon_phone3,
cc.ccon_phone4,
cc.ccon_fax,curr.curr_name,
cl.client_vatnum,
cl.client_email1,
cl.client_email2,
cl.client_phone1,
cl.client_phone2,
cl.client_phone3,
cl.client_phone4,
cl.client_fax,srv.serv_name,
units.unit_name,
cp.price
FROM curr, countries, ccontacts cc, clients cl
left join cprices cp on cl.client_id = cp.client_id
left join units on units.unit_id = cp.unit_id
left join services srv on srv.serv_id = cp.serv_id
WHERE
cc.client_id = cl.client_id
AND curr.curr_id = cl.curr_id
AND countries.coun_id = cl.coun_id
ORDER BY 1

 

(1 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please enter the text you see in the image into the textbox below (we use this to prevent automated submissions).