Amphis Software Forum

show all products/items

amphis — Mon, 20 Feb 2012 22:56:40 GMT

this SQL can be used to create a custom report that shows all the products/items available

SELECT c.description as category, p.id,p.description, p.costprice,p.unitprice
from product p, category c
where p.categoryid = c.id
order by c.description,p.id

see http://www.amphis-software.com/crm-custom-reports.htm for how to use the SQL to make a custom report

Forum: Custom Reports

report showing customer details, all contacts and all groups customer belongs to

amphis — Thur, 16 Feb 2012 21:03:08 GMT

this report shows

customer details, all contacts and all groups customer belongs to

SELECT cu.displayid as customerid,'Main Customer' as info,s.lookupvalue as status,'' as , t.lookupvalue as title, cu.firstname,cu.lastname,cu.companyname, cu.addressline1,cu.addressline2,cu.addresstown,

cu.addresscity,cu.addresscountystate,cu.addresspostzipcode,lc.lookupvalue as country,

cu.emailwork, cu.emailhome,cu.worktel as , hometel as ,mobiletelwork,mobiletelpersonal, websitework as website

from customer cu, lookup_country lc, lookup_contacttitle t,lookup_customerstatus s

where cu.addresscountryid = lc.lookupkey

and cu.titleid = t.lookupkey

and cu.customerstatusid = s.lookupkey

union

SELECT cu.displayid as customerid,'group->','',g.groupname as , '', '','','', '','','',

'','','','',

'', '','', '','','', ''

from customer cu, groupcustomer gc, g

where cu.customerid=gc.customerid

and gc.groupid=g.id

union

SELECT cust.displayid as customerid, 'contact->','','' as , c.title, c.firstname,c.lastname,'','','','',

'','','','',

c.emailwork,c.emailhome,c.worktel as , c.hometel as ,c.mobiletelwork,c.mobiletelpersonal,''

from customer cust, contact c

where cust.customerid=cint(c.customerid)

order by 1,2 desc,8,7

for details of how to use custom reports see http://www.amphis-software.com/crm-custom-reports.htm

Forum: Custom Reports

overdue invoices

amphis — Thur, 16 Feb 2012 20:58:34 GMT

this SQL can be used in a custom report to show overdue invoices. at the click of a button you can access the customer record or view the invoice. also you can export the details to a spreadsheet or print the report

select displayid as CustomerId, CompanyName, LastName as ,FirstName as , documentnumber as , displaydate as , descr as Description,Total,Status, FileName from customer c inner join history h on c.customerid=h.customerid where what='invoice' AND status='overdue' ORDER BY displaydate

Forum: Custom Reports

example envelope template

amphis — Sun, 12 Feb 2012 21:39:28 GMT

it turns out to be quite difficult to create a Word envelope without also having a blank page inserted after it so here is an example envelope Word document you can use with Amphis Customer software to Print Envelopes without having the blank page after it.

http://www.amphis-software.com/templates/envelope/template.doc

Forum: Document templates

Include Appointment Data in template fields

srh — Sun, 12 Feb 2012 16:35:39 GMT

Hi - im a recent user of Amphis as a trial, and am very impressed with it so far! In particular i love being able to edit templates in word, instead of complicated CSS editors! I would be keen to look at including appointment data in some way as a template field - that way confirmation letters for appointments could be made? Am i missing it somewhere, or could it be included in a future version? Grateful for any support. Thanks!

Forum: Document templates

how to print envelopes

amphis — Sat, 11 Feb 2012 09:28:46 GMT

we're asked a lot how to print envelopes. here's some suggestions using existing functionality.

1) in Amphis Customer go to Tools->Maintain Templates. click Create New Document Type. type envelope and click OK. select envelope from the drop-down list of document types. select template.doc and click Edit Template.

Now you are in Microsoft Word, click on the Mailings tab and click Envelopes. in the Delivery Address field type this

<!name!>
<!companyname!>
<!address!>

enter your return address if required

click Add to Document

delete the 2nd page as we don't need that. (we haven't worked out the best way to do that yet).

you can edit the envelope using standard Word facilities to add a letterhead or artwork etc.

now to print an envelope, just go to the Customer History tab for a customer, select envelope from the docuiment types on the right hand side and click Create Document.

You can also Mail Merge to generate multiple envelopes at a time. Just go to the Mail Merge menu and select Mail Merge. select the customer on the left and select the document template as "envelope".

some clients also use Labels for printing envelopes - see

http://amphisforum.websitetoolbox.com/post/Envelopes-4752304

Forum: Document templates

Multiple quote templates.

Chuck1j — Wed, 08 Feb 2012 18:38:01 GMT

Is there any way to have a drop down list of available templates. We use 6 very different quote templates and it would be very usefull to be able to select a template rather than having to rebuild or reset default everytime we want to change the quote.

Forum: Quotes and Invoices

price list report

amphis — Fri, 03 Feb 2012 21:35:52 GMT

here is the SQL for a price list report

select c.description as category, p.id,p.description,p.unitprice as price
from category c, product p
where c.id = p.categoryid
order by c.description, p.id

just go to Reports->Custom Report Builder and copy and paste this SQL and click Run Report

Forum: Custom Reports

Refunds

jaro — Wed, 01 Feb 2012 07:49:29 GMT

Refunds are not displayed in the customer acc. You can see receipts and credit notes but no refunds.

Forum: Amphis Customer

Custom fields

jaro — Wed, 01 Feb 2012 07:44:05 GMT

Can you please allow support of all the custom fields in the custom report. At the moment you can use only up to no 22.

Thanks.

Forum: Custom Reports

Does your CRM software work on a Mac ?

amphis — Sun, 29 Jan 2012 10:01:26 GMT

Does your CRM software work on a Mac ?

Forum: CRM FAQ

Moving between pc's

jaro — Sun, 29 Jan 2012 09:32:24 GMT

What would be a safe way to move the software from one pc to another within one license?

Forum: Amphis Customer

supplier invoices for the last year

amphis — Thur, 26 Jan 2012 21:03:01 GMT

this SQL can be used to show supplier invoices for the last 365 days

SELECT invoicedate,suppliername, description, subtotal,tax,total from supplier_invoice si, supplier s
where si.supplierid=s.supplierid and invoicedate > date() -365
order by invoicedate,suppliername

Forum: Custom Reports

How to backup the data and documents ?

amphis — Fri, 20 Jan 2012 21:45:53 GMT

How to backup the data and documents ?

Forum: CRM FAQ

how to assign multiple customers to a group ?

amphis — Fri, 20 Jan 2012 21:42:01 GMT

How to assign multiple customers to a group ?

Forum: CRM FAQ