Within the Method:CRM export tool, if you click Advanced Filter Options you will have the option to Filter with script. This allows you to export specific parts of your data (e.g., customers with the first name Jason).
- Oldest first / Newest first: When the records export, you can choose to have the oldest records first, or the latest records first.
- Filter with RecordID greater than: When a record is inserted into a table, their Record ID is the next available number after the last record's Record ID. You can select this to quickly grab the last few records in the table, if you know the Record ID to start from.
- Filter with script: You can filter use SQL syntax
Scripts are written in a 'language' called SQL. SQL has statements which allow the users to pull information from an SQL database. If you don’t know what SQL is, or how to use it, here is a list of some common SQL scripts that can be used with the Method:CRM export tool. We’ll use the Customer table in our examples.
To filter for records based on the exact values of a specified field:
RecordID=100- This will export the one record with the RecordID value of 100.
FirstName=‘Jason’- This will export all customers with the first name Jason.
To filter for records based on multiple exact values of a specified field:
RecordID IN (500,501,505)- Three records will be exported, the three with RecordID values of 500, 501, and 505
SalesRep IN (‘DDC’, ‘JM’)- Exports all records with their SalesRep field set to either DDC or JM.
To filter for records that are greater than or less than the specified value:
DueDateStart > ‘2013-04-30’- Exports all records with due dates after April 30th 2013.
RecordID < 500- Exports all records with their RecordID under 500.
To filter for records that match a specified pattern:
FirstName LIKE ‘%an%’- Exports all records with a first name that contains '
an'. For example: Andrea, Brittany, and Adrian.
BillAddressAddr1 LIKE ‘%King%’- Exports records where addresses has '
king' in it. For example: King St., King Rd., Kingston Blvd.
To filter for records that are between two specified values:
JobStartDate BETWEEN ‘2013-04-15’ AND ‘2013-05-15’- Exports all records with a job start date between April 15th 2013 and May 15th 2013.
RecordID BETWEEN 200 AND 300- Exports all records with RecordID values between 200 and 300.
To choose multiple filters:
- BillAddressCountry=‘Canada’ AND BillAddressCity=‘Toronto’ - if you use an ‘AND’ operator both statements must be true.
- RecordID IN (299,300,305) OR RecordID > 350 - if you use an ‘OR’ operator only one of the statements must be true.
These are just a few of many filter scripts you can use, but it should get you going for now!
If you would like to look for more information on creating your own scripts, start with SQL Where Clause.