Within the Method 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. 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 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 - for the RecordID value of 100
- FirstName=‘Jason’ - for 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) - for RecordID values of 500, 501, and 505
- SalesRep IN (‘DDC’, ‘JM’) - for both sales reps DDC and JM
To filter for records that are greater than or less than the specified value:
- DueDateStart > ‘2013-04-30’ - for all due dates after April 30th 2013
- RecordID < 500 - for all RecordID values under 500
To filter for records that match a specified pattern:
- FirstName LIKE ‘%an%’ - for names such as Andrea, Brittany, and Adrian
- BillAddressAddr1 LIKE ‘%King%’ - for addresses such as King St. or King Rd.
To filter for records that are between two specified values:
- JobStartDate BETWEEN ‘2013-04-15’ AND ‘2013-05-15’ - for dates between April 15th 2013 and May 15th 2013
- RecordID BETWEEN 200 AND 300 - for 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.