NewHaven Software Wiki - User contributions [en]

CMS Handling the Honorifics for the customers

2009-04-22T21:58:16Z

Emily barnes:

[[Category:Published]]
==Purpose==
At some point, you may need to use Honorifics other than the predefined, hard-coded list of Honorific values already in CMS. While this is not presently a feature within CMS, you can make it work by leveraging a slight difference between the way Honorifics work in Order Entry vs in the Contact Manager (Customers) sections. This article outlines the steps that are necessary to create and use your own Honorifics, until this feature is built into CMS.

==Explanation==

In the Customers Manager, you will only be allowed to select from the list of available Honorific values. Custom values will not be recognized and will not be saved.

Here is a way to use custom typed Honorific codes:

1. You can type the custom Honorific in ''Order Entry'' in '''any of the 3 customer addresses''', (Mailing/Shipping/Billing in the Customer/Shipping/Money screens, respectively), and that Honorific will save to the order's address in the database:

[[Image:HonorificInOE_Customer.jpg]]

[[Image:HonorificInOE_Shipping.jpg]]

[[Image:HonorificInOE_Billing.jpg]]

2. If the '''"Default Ship To"''' or '''"Default Bill To"''' flags are checked:

[[Image:HonorificDefaultShipTo.jpg]]

[[Image:HonorificDefaultBillTo.jpg]]

...then the changes done to the address will also post to the customer's record in Customers Manager. Example:

[[Image:HonorificInCM_Shipping.jpg]]

CMS Handling the Honorifics for the customers

2009-04-22T21:57:14Z

Emily barnes: /* Explanation */

[[Category:Validated]]
==Purpose==
At some point, you may need to use Honorifics other than the predefined, hard-coded list of Honorific values already in CMS. While this is not presently a feature within CMS, you can make it work by leveraging a slight difference between the way Honorifics work in Order Entry vs in the Contact Manager (Customers) sections. This article outlines the steps that are necessary to create and use your own Honorifics, until this feature is built into CMS.

==Explanation==

In the Customers Manager, you will only be allowed to select from the list of available Honorific values. Custom values will not be recognized and will not be saved.

Here is a way to use custom typed Honorific codes:

1. You can type the custom Honorific in ''Order Entry'' in '''any of the 3 customer addresses''', (Mailing/Shipping/Billing in the Customer/Shipping/Money screens, respectively), and that Honorific will save to the order's address in the database:

[[Image:HonorificInOE_Customer.jpg]]

[[Image:HonorificInOE_Shipping.jpg]]

[[Image:HonorificInOE_Billing.jpg]]

2. If the '''"Default Ship To"''' or '''"Default Bill To"''' flags are checked:

[[Image:HonorificDefaultShipTo.jpg]]

[[Image:HonorificDefaultBillTo.jpg]]

...then the changes done to the address will also post to the customer's record in Customers Manager. Example:

[[Image:HonorificInCM_Shipping.jpg]]

CMS Handling the Honorifics for the customers

2009-04-22T21:56:37Z

Emily barnes: /* Explanation */

[[Category:Validated]]
==Purpose==
At some point, you may need to use Honorifics other than the predefined, hard-coded list of Honorific values already in CMS. While this is not presently a feature within CMS, you can make it work by leveraging a slight difference between the way Honorifics work in Order Entry vs in the Contact Manager (Customers) sections. This article outlines the steps that are necessary to create and use your own Honorifics, until this feature is built into CMS.

==Explanation==

In the Customers Manager, you will only be allowed to select from the list of available Honorific values. Custom values will not be recognized and will not be saved.

Here is a way to use custom typed Honorific codes:

1. You can type the custom Honorific in ''Order Entry'' in '''any of the 3 customer addresses''' (Mailing/Shipping/Billing in the Customer/Shipping/Money screens, respectively) and that Honorific will save to the order's address in the database:

[[Image:HonorificInOE_Customer.jpg]]

[[Image:HonorificInOE_Shipping.jpg]]

[[Image:HonorificInOE_Billing.jpg]]

2. If the '''"Default Ship To"''' or '''"Default Bill To"''' flags are checked:

[[Image:HonorificDefaultShipTo.jpg]]

[[Image:HonorificDefaultBillTo.jpg]]

...then the changes done to the address will also post to the customer's record in Customers Manager. Example:

[[Image:HonorificInCM_Shipping.jpg]]

CMS Handling the Honorifics for the customers

2009-04-22T21:54:44Z

Emily barnes: /* Explanation */

[[Category:Validated]]
==Purpose==
At some point, you may need to use Honorifics other than the predefined, hard-coded list of Honorific values already in CMS. While this is not presently a feature within CMS, you can make it work by leveraging a slight difference between the way Honorifics work in Order Entry vs in the Contact Manager (Customers) sections. This article outlines the steps that are necessary to create and use your own Honorifics, until this feature is built into CMS.

==Explanation==

In the Customers Manager, you will only be allowed to select from the list of available Honorific values. Custom values will not be recognized and will not be saved.

Here is a way to use custom typed Honorific codes:

1. You can type the custom Honorific in ''Order Entry'' in '''any of the 3 customer addresses''' (Mailing/Shipping/Billing in the Customer/Shipping/Money screens, respectively) and that Honorific will save to the order's address in the database:

[[Image:HonorificInOE_Customer.jpg]]

[[Image:HonorificInOE_Shipping.jpg]]

[[Image:HonorificInOE_Billing.jpg]]

2. If the '''"Default Ship To"''' or '''"Default Bill To"''' flags are checked :

[[Image:HonorificDefaultShipTo.jpg]]

[[Image:HonorificDefaultBillTo.jpg]]

then the changes done to the address will also post to the customer's record in Customers Manager. Example:

[[Image:HonorificInCM_Shipping.jpg]]

CMS Handling the Honorifics for the customers

2009-04-22T21:54:18Z

Emily barnes: /* Explanation */

[[Category:Validated]]
==Purpose==
At some point, you may need to use Honorifics other than the predefined, hard-coded list of Honorific values already in CMS. While this is not presently a feature within CMS, you can make it work by leveraging a slight difference between the way Honorifics work in Order Entry vs in the Contact Manager (Customers) sections. This article outlines the steps that are necessary to create and use your own Honorifics, until this feature is built into CMS.

==Explanation==

In the Customers Manager, you will only be allowed to select from the list of available Honorific values. Custom values will not be recognized and will not be saved.

Here is a way to use custom typed Honorific codes:

1. You can type the custom Honorific in '''Order Entry''' in '''any of the 3 customer addresses''' (Mailing/Shipping/Billing in the Customer/Shipping/Money screens, respectively) and that Honorific will save to the order's address in the database:

[[Image:HonorificInOE_Customer.jpg]]

[[Image:HonorificInOE_Shipping.jpg]]

[[Image:HonorificInOE_Billing.jpg]]

2. If the '''"Default Ship To"''' or '''"Default Bill To"''' flags are checked :

[[Image:HonorificDefaultShipTo.jpg]]

[[Image:HonorificDefaultBillTo.jpg]]

then the changes done to the address will also post to the customer's record in Customers Manager. Example:

[[Image:HonorificInCM_Shipping.jpg]]

CMS Handling the Honorifics for the customers

2009-04-22T21:53:36Z

Emily barnes: /* Explanation */

[[Category:Validated]]
==Purpose==
At some point, you may need to use Honorifics other than the predefined, hard-coded list of Honorific values already in CMS. While this is not presently a feature within CMS, you can make it work by leveraging a slight difference between the way Honorifics work in Order Entry vs in the Contact Manager (Customers) sections. This article outlines the steps that are necessary to create and use your own Honorifics, until this feature is built into CMS.

==Explanation==

In the Customers Manager, you will only be allowed to select from the list of available Honorific values. Custom values will not be recognized and will not be saved.

Here is a way to use custom typed Honorific codes:

1. You can type the custom Honorific in [[Order Entry]] in '''any of the 3 customer addresses''' (Mailing/Shipping/Billing in the Customer/Shipping/Money screens, respectively) and that Honorific will save to the order's address in the database:

[[Image:HonorificInOE_Customer.jpg]]

[[Image:HonorificInOE_Shipping.jpg]]

[[Image:HonorificInOE_Billing.jpg]]

2. If the '''"Default Ship To"''' or '''"Default Bill To"''' flags are checked :

[[Image:HonorificDefaultShipTo.jpg]]

[[Image:HonorificDefaultBillTo.jpg]]

then the changes done to the address will also post to the customer's record in Customers Manager. Example:

[[Image:HonorificInCM_Shipping.jpg]]

CMS Handling the Honorifics for the customers

2009-04-22T21:50:21Z

Emily barnes: /* Purpose */

[[Category:Validated]]
==Purpose==
At some point, you may need to use Honorifics other than the predefined, hard-coded list of Honorific values already in CMS. While this is not presently a feature within CMS, you can make it work by leveraging a slight difference between the way Honorifics work in Order Entry vs in the Contact Manager (Customers) sections. This article outlines the steps that are necessary to create and use your own Honorifics, until this feature is built into CMS.

==Explanation==

In Customers Manager, the operator will be allowed to select from the list of available Honorific values. Custom values will not be recognized and will not be saved.

Here is a way to use custom typed Honorific codes:

1. You can type custom Honorific in Order Entry in '''any of the 3 customer addresses''' (Mailing/Shipping/Billing in the Customer/Shipping/Money screens) and that Honorific will save to the order's address in the database:

[[Image:HonorificInOE_Customer.jpg]]

[[Image:HonorificInOE_Shipping.jpg]]

[[Image:HonorificInOE_Billing.jpg]]

2. If the '''"Default Ship To"''' or '''"Default Bill To"''' flags are checked :

[[Image:HonorificDefaultShipTo.jpg]]

[[Image:HonorificDefaultBillTo.jpg]]

then the changes done to the address will also post and reflect on the customer's record in Customers Manager. Example:

[[Image:HonorificInCM_Shipping.jpg]]

CMS Customer Filter for customers with a specific item on Back Order

2009-04-22T21:27:59Z

Emily barnes:

[[Category:Published]]
==Customer filter overview==
Some of your operators may want to retrieve a list of all '''customers''' that have a specific '''product on Back Order'''.
They may also need to send a mass email to all these customers to let them know about:
* the availability of the Back Ordered Item
* the Date the back order is expected to be available
* the cancellation of an outstanding order for a Back Ordered item that has been discontinued

A mail list filter can be created to target such customers by using the 'Write Own SQL' option/checkbox at the bottom of the Mail List Filter screen. Simply copy the SQL below and paste it into the window that comes up after clicking 'Write Own SQL' and then Save.

Once the filter is created, it can be used for sending emails, printing letters, reports, and/or exporting. The filter can be edited in the future to work with other products/SKUs.

'''NOTE :''' This SQL cannot be used as-is. You'll need to replace text between the quotes in the last SQL line with the criteria you wish to use for product code, size, and color (where applicable).

==Actual SQL filters==
The following SQL filter is the simple version (with '''no email validation''').
It will return '''all''' customers with unfulfilled orders for the specified Back Ordered item, whether or not they have a valid email address.

<source lang="SQL">
SELECT DISTINCT C.*
FROM CUST C inner join orderhdr o on c.id = o.cust_num
inner join itemsord i on o.order_id = i.order_id
WHERE not o.proforma = 1
and not o.tempsave = 1
and i.cur_onbo > 0
and (i.prod_code = 'MyProduct' and sizename = 'XXL' and colorname = 'Red')
</source>

This second version of the SQL filter will return all customers with unfulfilled orders for the specified Back Ordered item '''who also have''' a valid email address ('''with email validation''')

<source lang="SQL">
SELECT DISTINCT C.*
FROM CUST C inner join orderhdr o on c.id = o.cust_num
inner join itemsord i on o.order_id = i.order_id
WHERE not o.proforma = 1
and not o.tempsave = 1
and i.cur_onbo > 0
and not (EMAIL <> '' AND NOT
(
CHARINDEX(' ',LTRIM(RTRIM([EMAIL]))) = 0
AND LEFT(LTRIM([EMAIL]),1) <> '@'
AND RIGHT(RTRIM([EMAIL]),1) <> '.'
AND datalength(LTRIM(RTRIM([EMAIL]))) - datalength(REPLACE(LTRIM(RTRIM([EMAIL])),'@','')) = 1
AND (CHARINDEX('.@',[EMAIL]) = 0 AND CHARINDEX('..',[EMAIL]) = 0)
))
and (i.prod_code = 'MyProduct' and sizename = 'XXL' and colorname = 'Red')
</source>

CMS Customer Filter for customers with a specific item on Back Order

2009-04-22T21:19:23Z

Emily barnes: /* Actual SQL filters */

[[Category:Validated]]
==Customer filter overview==
Some of your operators may want to retrieve a list of all '''customers''' that have a specific '''product on Back Order'''.
They may also need to send a mass email to all these customers to let them know about:
* the availability of the Back Ordered Item
* the Date the back order is expected to be available
* the cancellation of an outstanding order for a Back Ordered item that has been discontinued

A mail list filter can be created to target such customers by using the 'Write Own SQL' option/checkbox at the bottom of the Mail List Filter screen. Simply copy the SQL below and paste it into the window that comes up after clicking 'Write Own SQL' and then Save.

Once the filter is created, it can be used for sending emails, printing letters, reports, and/or exporting. The filter can be edited in the future to work with other products/SKUs.

'''NOTE :''' This SQL cannot be used as-is. You'll need to replace text between the quotes in the last SQL line with the criteria you wish to use for product code, size, and color (where applicable).

==Actual SQL filters==
The following SQL filter is the simple version (with '''no email validation''').
It will return '''all''' customers with unfulfilled orders for the specified Back Ordered item, whether or not they have a valid email address.

<source lang="SQL">
SELECT DISTINCT C.*
FROM CUST C inner join orderhdr o on c.id = o.cust_num
inner join itemsord i on o.order_id = i.order_id
WHERE not o.proforma = 1
and not o.tempsave = 1
and i.cur_onbo > 0
and (i.prod_code = 'MyProduct' and sizename = 'XXL' and colorname = 'Red')
</source>

This second version of the SQL filter will return all customers with unfulfilled orders for the specified Back Ordered item '''who also have''' a valid email address ('''with email validation''')

<source lang="SQL">
SELECT DISTINCT C.*
FROM CUST C inner join orderhdr o on c.id = o.cust_num
inner join itemsord i on o.order_id = i.order_id
WHERE not o.proforma = 1
and not o.tempsave = 1
and i.cur_onbo > 0
and not (EMAIL <> '' AND NOT
(
CHARINDEX(' ',LTRIM(RTRIM([EMAIL]))) = 0
AND LEFT(LTRIM([EMAIL]),1) <> '@'
AND RIGHT(RTRIM([EMAIL]),1) <> '.'
AND datalength(LTRIM(RTRIM([EMAIL]))) - datalength(REPLACE(LTRIM(RTRIM([EMAIL])),'@','')) = 1
AND (CHARINDEX('.@',[EMAIL]) = 0 AND CHARINDEX('..',[EMAIL]) = 0)
))
and (i.prod_code = 'MyProduct' and sizename = 'XXL' and colorname = 'Red')
</source>

CMS Customer Filter for customers with a specific item on Back Order

2009-04-22T21:16:56Z

Emily barnes: /* Actual SQL filters */

[[Category:Validated]]
==Customer filter overview==
Some of your operators may want to retrieve a list of all '''customers''' that have a specific '''product on Back Order'''.
They may also need to send a mass email to all these customers to let them know about:
* the availability of the Back Ordered Item
* the Date the back order is expected to be available
* the cancellation of an outstanding order for a Back Ordered item that has been discontinued

A mail list filter can be created to target such customers by using the 'Write Own SQL' option/checkbox at the bottom of the Mail List Filter screen. Simply copy the SQL below and paste it into the window that comes up after clicking 'Write Own SQL' and then Save.

Once the filter is created, it can be used for sending emails, printing letters, reports, and/or exporting. The filter can be edited in the future to work with other products/SKUs.

'''NOTE :''' This SQL cannot be used as-is. You'll need to replace text between the quotes in the last SQL line with the criteria you wish to use for product code, size, and color (where applicable).

==Actual SQL filters==
The following SQL filter is the simple version (with '''no email validation''').
It will return '''all''' customers with unfulfilled orders for the specified Back Ordered item, whether or not they have a valid email account.

<source lang="SQL">
SELECT DISTINCT C.*
FROM CUST C inner join orderhdr o on c.id = o.cust_num
inner join itemsord i on o.order_id = i.order_id
WHERE not o.proforma = 1
and not o.tempsave = 1
and i.cur_onbo > 0
and (i.prod_code = 'MyProduct' and sizename = 'XXL' and colorname = 'Red')
</source>

This second version of the SQL filter will return all customers with unfulfilled orders for the specified Back Ordered item '''and also''' have a valid email account ('''with email validation''')

<source lang="SQL">
SELECT DISTINCT C.*
FROM CUST C inner join orderhdr o on c.id = o.cust_num
inner join itemsord i on o.order_id = i.order_id
WHERE not o.proforma = 1
and not o.tempsave = 1
and i.cur_onbo > 0
and not (EMAIL <> '' AND NOT
(
CHARINDEX(' ',LTRIM(RTRIM([EMAIL]))) = 0
AND LEFT(LTRIM([EMAIL]),1) <> '@'
AND RIGHT(RTRIM([EMAIL]),1) <> '.'
AND datalength(LTRIM(RTRIM([EMAIL]))) - datalength(REPLACE(LTRIM(RTRIM([EMAIL])),'@','')) = 1
AND (CHARINDEX('.@',[EMAIL]) = 0 AND CHARINDEX('..',[EMAIL]) = 0)
))
and (i.prod_code = 'MyProduct' and sizename = 'XXL' and colorname = 'Red')
</source>

CMS Customer Filter for customers with a specific item on Back Order

2009-04-22T21:12:45Z

Emily barnes: /* Actual SQL filters */

[[Category:Validated]]
==Customer filter overview==
Some of your operators may want to retrieve a list of all '''customers''' that have a specific '''product on Back Order'''.
They may also need to send a mass email to all these customers to let them know about:
* the availability of the Back Ordered Item
* the Date the back order is expected to be available
* the cancellation of an outstanding order for a Back Ordered item that has been discontinued

A mail list filter can be created to target such customers by using the 'Write Own SQL' option/checkbox at the bottom of the Mail List Filter screen. Simply copy the SQL below and paste it into the window that comes up after clicking 'Write Own SQL' and then Save.

Once the filter is created, it can be used for sending emails, printing letters, reports, and/or exporting. The filter can be edited in the future to work with other products/SKUs.

'''NOTE :''' This SQL cannot be used as-is. You'll need to replace text between the quotes in the last SQL line with the criteria you wish to use for product code, size, and color (where applicable).

==Actual SQL filters==
The following SQL filter is the simple version (with '''no email validation''').
It will return '''all''' customers who've ordered the specific Back Ordered item, whether or not they have a valid email account.

<source lang="SQL">
SELECT DISTINCT C.*
FROM CUST C inner join orderhdr o on c.id = o.cust_num
inner join itemsord i on o.order_id = i.order_id
WHERE not o.proforma = 1
and not o.tempsave = 1
and i.cur_onbo > 0
and (i.prod_code = 'MyProduct' and sizename = 'XXL' and colorname = 'Red')
</source>

This second version of the SQL filter will return all customers who've ordered the specific Back Ordered item '''and also''' have a valid email account ('''with email validation''')

<source lang="SQL">
SELECT DISTINCT C.*
FROM CUST C inner join orderhdr o on c.id = o.cust_num
inner join itemsord i on o.order_id = i.order_id
WHERE not o.proforma = 1
and not o.tempsave = 1
and i.cur_onbo > 0
and not (EMAIL <> '' AND NOT
(
CHARINDEX(' ',LTRIM(RTRIM([EMAIL]))) = 0
AND LEFT(LTRIM([EMAIL]),1) <> '@'
AND RIGHT(RTRIM([EMAIL]),1) <> '.'
AND datalength(LTRIM(RTRIM([EMAIL]))) - datalength(REPLACE(LTRIM(RTRIM([EMAIL])),'@','')) = 1
AND (CHARINDEX('.@',[EMAIL]) = 0 AND CHARINDEX('..',[EMAIL]) = 0)
))
and (i.prod_code = 'MyProduct' and sizename = 'XXL' and colorname = 'Red')
</source>

CMS Customer Filter for customers with a specific item on Back Order

2009-04-22T21:07:27Z

Emily barnes: /* Customer filter overview */

[[Category:Validated]]
==Customer filter overview==
Some of your operators may want to retrieve a list of all '''customers''' that have a specific '''product on Back Order'''.
They may also need to send a mass email to all these customers to let them know about:
* the availability of the Back Ordered Item
* the Date the back order is expected to be available
* the cancellation of an outstanding order for a Back Ordered item that has been discontinued

A mail list filter can be created to target such customers by using the 'Write Own SQL' option/checkbox at the bottom of the Mail List Filter screen. Simply copy the SQL below and paste it into the window that comes up after clicking 'Write Own SQL' and then Save.

Once the filter is created, it can be used for sending emails, printing letters, reports, and/or exporting. The filter can be edited in the future to work with other products/SKUs.

'''NOTE :''' This SQL cannot be used as-is. You'll need to replace text between the quotes in the last SQL line with the criteria you wish to use for product code, size, and color (where applicable).

==Actual SQL filters==
The following SQL filter is the simple version (with '''no email validation''').
It will return all customers having the specific item on Back Order, no matter that they have an email or not.

<source lang="SQL">
SELECT DISTINCT C.*
FROM CUST C inner join orderhdr o on c.id = o.cust_num
inner join itemsord i on o.order_id = i.order_id
WHERE not o.proforma = 1
and not o.tempsave = 1
and i.cur_onbo > 0
and (i.prod_code = 'MyProduct' and sizename = 'XXL' and colorname = 'Red')
</source>

This second version of the SQL filter will return all customers having the specific item on Back Order and that also have a valid email account ('''with email validation''')

<source lang="SQL">
SELECT DISTINCT C.*
FROM CUST C inner join orderhdr o on c.id = o.cust_num
inner join itemsord i on o.order_id = i.order_id
WHERE not o.proforma = 1
and not o.tempsave = 1
and i.cur_onbo > 0
and not (EMAIL <> '' AND NOT
(
CHARINDEX(' ',LTRIM(RTRIM([EMAIL]))) = 0
AND LEFT(LTRIM([EMAIL]),1) <> '@'
AND RIGHT(RTRIM([EMAIL]),1) <> '.'
AND datalength(LTRIM(RTRIM([EMAIL]))) - datalength(REPLACE(LTRIM(RTRIM([EMAIL])),'@','')) = 1
AND (CHARINDEX('.@',[EMAIL]) = 0 AND CHARINDEX('..',[EMAIL]) = 0)
))
and (i.prod_code = 'MyProduct' and sizename = 'XXL' and colorname = 'Red')
</source>

CMS Customer Filter for customers with a specific item on Back Order

2009-04-22T21:05:41Z

Emily barnes: /* Customer filter overview */

[[Category:Validated]]
==Customer filter overview==
Some of your operators may want to retrieve a list of all '''customers''' that have a specific '''product on Back Order'''.
They may also need to send a mass email to all these customers to let them know about:
* the availability of the Back Ordered Item
* the Date the back order is expected to be available
* Back Ordered item has been discontinued and the outstanding order for this item will be canceled

A mail list filter can be created to target such customers by using the 'Write Own SQL' option/checkbox at the bottom of the Mail List Filter screen. Simply copy the SQL below and paste it into the window that comes up after clicking 'Write Own SQL' and then Save.

Once the filter is created, it can be used for sending emails, printing letters, reports, and/or exporting. The filter can be edited in the future to work with other products/SKUs.

'''NOTE :''' This SQL cannot be used as-is. You'll need to replace text between the quotes in the last SQL line with the criteria you wish to use for product code, size, and color (where applicable).

==Actual SQL filters==
The following SQL filter is the simple version (with '''no email validation''').
It will return all customers having the specific item on Back Order, no matter that they have an email or not.

<source lang="SQL">
SELECT DISTINCT C.*
FROM CUST C inner join orderhdr o on c.id = o.cust_num
inner join itemsord i on o.order_id = i.order_id
WHERE not o.proforma = 1
and not o.tempsave = 1
and i.cur_onbo > 0
and (i.prod_code = 'MyProduct' and sizename = 'XXL' and colorname = 'Red')
</source>

This second version of the SQL filter will return all customers having the specific item on Back Order and that also have a valid email account ('''with email validation''')

<source lang="SQL">
SELECT DISTINCT C.*
FROM CUST C inner join orderhdr o on c.id = o.cust_num
inner join itemsord i on o.order_id = i.order_id
WHERE not o.proforma = 1
and not o.tempsave = 1
and i.cur_onbo > 0
and not (EMAIL <> '' AND NOT
(
CHARINDEX(' ',LTRIM(RTRIM([EMAIL]))) = 0
AND LEFT(LTRIM([EMAIL]),1) <> '@'
AND RIGHT(RTRIM([EMAIL]),1) <> '.'
AND datalength(LTRIM(RTRIM([EMAIL]))) - datalength(REPLACE(LTRIM(RTRIM([EMAIL])),'@','')) = 1
AND (CHARINDEX('.@',[EMAIL]) = 0 AND CHARINDEX('..',[EMAIL]) = 0)
))
and (i.prod_code = 'MyProduct' and sizename = 'XXL' and colorname = 'Red')
</source>

FTP Upload

2009-04-06T17:45:44Z

Emily barnes:

[[Category:Published]]

Here are the instructions for making a backup copy of your CMS database files and and how to upload these files to our FTP server using a secure TLS/SSL connection.

'''NOTE:''' This is accomplished on the CMS Server computer.

# MAKE A BACKUP IMAGE OF CMS DATABASE FILES
## Select the Admin menu.
## Select Database Maintenance. (Note the settings on the Daily Backup tab so you can restore them, if necessary.)
## Select 'Perform the daily backup'
## Select and delete any text in the 'Folder in which to create a backup...' field.
## Click the Browse button, select Desktop in the window that opens, and click OK.
## Click the 'Immediately create a Backup Image...' button. This will create a copy of your database and log files onto the Windows desktop.
## Click the Close button.
## Quit CMS if it is still open.
# COMPRESS YOUR CMS BACKUP IMAGE FILES
## On the Windows Desktop, there should be two files with the same name as your CMS database (SoloData for Solo users). Depending on how you have Windows configured, you may or may not see the .db and .log file extensions.
## Compress these two files together
### In windows select both files using your mouse and then right click and select 'Send To -> Compressed (zipped) Folder"
## Rename the compressed file with your company name if it's not already.

For the protection of your data we required all FTP transfers to be sent over a TLS/SSL connection.

In order to setup FTP over SSL you need an FTP client that supports this. IE does not support FTPS. For the time being we recommend Filezilla, it’s an open source ftp client: http://filezilla-project.org/ Please follow their directions for installing. Once installed continue with the instructions below:

* open '''Filezilla'''
* goto '''File->Site Manager'''
* Click on '''New Site'''
* Type '''NHS FTP''' or similar
* On the right side enter the following:
** Host: '''ftp.newhavensoftware.net'''
** Port: '''990'''
** Servertype: '''FTPS - FTP over implicit TLS/SSL'''
** Logontype: '''Normal'''
** User: '''uploads'''
** password: '''n0rthern!'''
* Click '''Connect'''
* a window will pop up once connected called '''Unknown certificate''' this window shows you details of the SSL certificate. The host should read '''ftp.newhavensoftware.net:990'''
* Check the box '''Always trust certificate in future sessions'''
* Click '''OK'''

This will save your settings, and you can open that site anytime to connect again by going back to the site manager. Please be aware the uploads password changes from time to time. If you need to regularly update information on our FTP server please send an email to support@newhavensoftware.com requesting a permanent FTP account. If all settings are accurate, you will be connected to the server over TLS/SSL, securing all data being sent and retrieved from our FTP server.

# Locate your file on your desktop in the left window pane.
# Right click on the compressed file and select '''Upload'''
# This will start the upload process and you will see the progress near the bottom of the screen.
# Please email us at support@newhavensoftware.com when the file has been successfully uploaded so that we can move it off of the FTP Server for added security.

FTP Upload

2009-04-06T17:44:50Z

Emily barnes:

[[Category:Validated]]

Here are the instructions for making a backup copy of your CMS database files and and how to upload these files to our FTP server using a secure TLS/SSL connection.

'''NOTE:''' This is accomplished on the CMS Server computer.

# MAKE A BACKUP IMAGE OF CMS DATABASE FILES
## Select the Admin menu.
## Select Database Maintenance. (Note the settings on the Daily Backup tab so you can restore them, if necessary.)
## Select 'Perform the daily backup'
## Select and delete any text in the 'Folder in which to create a backup...' field.
## Click the Browse button, select Desktop in the window that opens, and click OK.
## Click the 'Immediately create a Backup Image...' button. This will create a copy of your database and log files onto the Windows desktop.
## Click the Close button.
## Quit CMS if it is still open.
# COMPRESS YOUR CMS BACKUP IMAGE FILES
## On the Windows Desktop, there should be two files with the same name as your CMS database (SoloData for Solo users). Depending on how you have Windows configured, you may or may not see the .db and .log file extensions.
## Compress these two files together
### In windows select both files using your mouse and then right click and select 'Send To -> Compressed (zipped) Folder"
## Rename the compressed file with your company name if it's not already.

For the protection of your data we required all FTP transfers to be sent over a TLS/SSL connection.

In order to setup FTP over SSL you need an FTP client that supports this. IE does not support FTPS. For the time being we recommend Filezilla, it’s an open source ftp client: http://filezilla-project.org/ Please follow their directions for installing. Once installed continue with the instructions below:

* open '''Filezilla'''
* goto '''File->Site Manager'''
* Click on '''New Site'''
* Type '''NHS FTP''' or similar
* On the right side enter the following:
** Host: '''ftp.newhavensoftware.net'''
** Port: '''990'''
** Servertype: '''FTPS - FTP over implicit TLS/SSL'''
** Logontype: '''Normal'''
** User: '''uploads'''
** password: '''n0rthern!'''
* Click '''Connect'''
* a window will pop up once connected called '''Unknown certificate''' this window shows you details of the SSL certificate. The host should read '''ftp.newhavensoftware.net:990'''
* Check the box '''Always trust certificate in future sessions'''
* Click '''OK'''

This will save your settings, and you can open that site anytime to connect again by going back to the site manager. Please be aware the uploads password changes from time to time. If you need to regularly update information on our FTP server please send an email to support@newhavensoftware.com requesting a permanent FTP account. If all settings are accurate, you will be connected to the server over TLS/SSL, securing all data being sent and retrieved from our FTP server.

# Locate your file on your desktop in the left window pane.
# Right click on the compressed file and select '''Upload'''
# This will start the upload process and you will see the progress near the bottom of the screen.
# Please email us at support@newhavensoftware.com when the file has been successfully uploaded so that we can move it off of the FTP Server for added security.