ADVANCED SEARCH

Advanced searches provide a way of describing a request for specific data from the endpoints managing the object data.

You can do a direct JSON (or XML) formatted request or if building using Javascript, you use the open source 1blankspace script 1blankspace.advancedsearch-2.0.3.js - which includes a AdvancedSearch namespace (object) for building and sending a search request.  Notes below are in reference to this namespace (object).

Using advanced search with custom object structures (eg extra attributes)

JSON format for direct POST (or GET)

Standard data control attributes

   AS
  PARAMETERS  
  async true / false - sets whether getResults returns aysnchronously (ie other events can happen in the interim), or synschronously (all other events are blocked)
  categoryId

Note: This is only required when using returnParameters.  For normal searches, it automatically works out the category from the passed structure fields
  forceMore Y / N [default]. By default, the data required to call CORE_SEARCH_MORE (moreId) is only saved if there are more records returned by the search than the number of rows returned. Set this to Y to force this to occur.
  returnParameters If set, rather than doing an actual search, this will return all fields available for the method. You could use this to build ageneric search page / report. See RETURN PARAMETERS below for more information.
  rf Return format.  Can be XML, JSON, or CSV.  For CSV the main part of the response is in JSON, just the data element is in CSV.  This can be used to minimise data transfer.
  rows number of rows to return (default 20)
  sort 2 parameters:
  • fieldname
  • direction, either asc for ascending, or desc for descending
  startRow First row to return.  Can be used for scrolling through large number of results
  OBJECT METHODS  
  addBracket If you are building a complex query, for eg you want all contacts who are male and born after 1950 and all femailes and born after 1960, you will need to use brackets in the query.
  addOperator Again, if your query is complex, you will need to add 'and', or 'or' between each filter.  If not set, 'and' is assumed. 
  addField Add a field to be returned.  Can be a comma delimited list.  To find the available fields please refer to the Available End Points below.  By specifying *, all available fields will be returned.  As a note, for performance reasons, you should only specify the fields that you want.  Finally, you can add a summaryField to perform grouping - see example 7 below. Click here to see all standard audit fields that are available.
  addSummaryField Add a summary field to be returned.  Can be a comma delimited list. 
  • The available fields are count(*), min(fieldname), max(fieldname), and sum(fieldname)
  • When adding a field, it needs to be aliased in order for it to return a json/xml compliant field. For eg you could add 'count(*) productcount', and the return will be in 'productcount'
  • For xml you will get a row at the top: 178
  • For json you will get data at the end: "summary": {"productcount": "178"}
  addFilter

Use this to limit the rows returned.  If using the object, it has 6 parameters:

  • FieldName - see end points for available fields
  • Comparison - see below for valid values
  • Value1 - what you are comparing to.  If you want to compare to another field, then use field:XXXXXX, eg field:ContactPerson.Surname
  • Value2, Value3 - optional second parameter for certain comparisons.  See below for more information
  • ApplyToSubSearchJoin - Y/N [default]. Very rarely required. It is useful if you want to include all parent records, but only those fields from a child record if the child record passes the filtering
  addCustomOption Some searches have custom options. To use you pass the name of the option and the value.  All objects that can have snapshots, have an option of IncludeSnapshots, with values of Y / N [default]. All searches have FormatDecimal.  A value of 1 [default] indicates that all decimal / currency fields should be returned with commas and decimals, for e.g. 1,234,567.89.  A value of 2 indicates that it should not be formatted, for e.g. 1234567.89.  For this option, if the decimal component are zeroes, it is not returned, for e.g. 123456
  getResults Use this once you have set all the properties / methods.  It has two parameters.  The first parameter is optional, and is the xml, or json string to search with (if you are not using the object).  The second parameter is the callback function
  reset Use this to clear all brackets, operators, fields etc
  COMPARISONS  
  EQUAL_TO  
  NOT_EQUAL_TO  
  GREATER_THAN  
  GREATER_THAN_OR_EQUAL_TO  
  LESS_THAN  
  LESS_THAN_OR_EQUAL_TO  
  IN_LIST Comma delimited list of values
  NOT_IN_LIST Comma delimited list of values
  IS_NULL A value has never been set.  For text values, you will probably need to use TEXT_IS_EMPTY instead
  IS_NOT_NULL  
  APPROX_EQUAL_TO  For a date field, within a month either side.  For a numeric field, within 80 - 120% of the value.  Not available for text fields.
  IS_FAVOURITE Limits the results to those items that have been saved as favourites. Not available on all searches. When using this filter, please leave the control blank.
     
  Text Specific Comparisons:  
  TEXT_IS_LIKE Contains the comparison value, eg 'surname', 'TEXT_IS_LIKE', 'RON' would return rows including RONALD, ARMSTRONG, and TRON
  TEXT_IS_NOT_LIKE  
  TEXT_IS_EMPTY Is blank
  TEXT_IS_NOT_EMPTY Is not blank
  TEXT_STARTS_WITH Field starts with value, eg 'surname', 'TEXT_IS_LIKE', 'RON' would return rows including RONALD, but NOT ARMSTRONG
     
  Date Specific Comparisons:  
  WEEK_TO_DATE Date is in this week.  First day of week is Sunday
  MONTH_TO_DATE Date is in this month
  CALENDAR_YEAR_TO_DATE  
  CALENDAR_THIS_WEEK Date is in this week -Sunday to Saturday
  CALENDAR_LAST_WEEK  
  CALENDAR_NEXT_WEEK  
  CALENDAR_LAST_MONTH  
  CALENDAR_NEXT_MONTH  
  CALENDAR_LAST_YEAR  
  CALENDAR_NEXT_YEAR  
  END_OF_LAST_MONTH All dates up until the end of last month
  END_OF_NEXT_MONTH All dates up until the end of next month. Due in production early Nov 2011
  AGED_THIRTY From 30 days agao until now
  AGED_SIXTY From 60 to 31 days ago
  AGED_NINETY From 90 to 61 days ago
  AGED_NINETY_PLUS 91 of more days ago
  TODAY  
  YESTERDAY  
  LAST_FINANCIAL_QUARTER Date is in the last quarter, with the quarters starting 1 July, 1 Oct, 1 Jan, 1 Apr.  For eg, the last financial quarter as of 10 Feb 2011 would be 1Oct - 31 Dec 2010
  LAST_52_WEEKS Any point in the last 52 weeks
  IN_MONTH Date is in the month.  Pass through a complete date, eg 1st July 2011.  It will return all records for July for all years
  ON_DAY_MONTH Date is on a particular day of month.  Pass through a complete date.  It will return all records on that day in that month for all years.  A good filter for birthdays.
  THIS_MONTH  
  NEXT_MONTH  
  BETWEEN On or Between the two provided dates.
  BETWEEN_DAY_MONTH Search between and day of the month and month, excluding the year.  eg good for finding birthdays within a period.  See example 6 below.
  DATE SPECIFIC VALUES
  Rather then specifiying a specific date (eg '1 Jan 2012'), a number of special values are available. Value 1 can be 'year', 'month', 'day', 'hour', 'minute', or 'second'. Value 2 is an integer for the number of periods to add (or subtract if negative). Value 3 is the date which can be 'now' (default), 'start_of_today', 'end_of_today', 'start_of_month', or 'end_of_month'. See the example 5 below
  SUB SEARCHES
 

Some advanced searches can be joined together to give extended functionality. For example, from a business search you can reference fields from Contact Person, Relationships, and Status Changes. When using this, you need to use the prefix for both the primary and sub fields. For example, when doing a CONTACT_BUSINESS_SEARCH you could specify ContactBusiness.TradeName,ContactBusiness.ContactPerson.Surname. For more information see the specific search's help.  You can use a full stop (.), underscore (_), or colon (:) as the delimter, for eg. you could do ContactBusiness.TradeName, ContactBusiness_TradeName, or ContactBusiness:TradeName.

TIP: If you are using a full stop as your delimeter, and your return format (rf) is JSON and want to reference the object using the jQuery .each() function, then need to use, by example:

this["contactbusiness.tradeName"] 

rather than:

this.contactBusiness.tradeName
  RETURN PARAMETERS
 

You can pass an '*' in this field, in which case it will return all available sub searches.  Alternatively, you can pass it a comma delimited string of the sub-searches that you are interested in to minimise data transfer. 

For example, using contact_business_search, passing '*' will return sub searches, including contactbusiness.secondaryrelationshipcontactbusiness,contactbusiness.customerstatuschange etc.  Passing a value of 'contactbusiness.customerstatuschange' will just return the fields from contactbusiness and contactbusiness.customerstatuschange

Sending request using JSON
  The following fields are returned:
  • name
  • inputtype, either textbox, textarea, or select
  • datatype, either numeric, date, or text
  • searchendpoint, for selects, the endpoint to get the list
  • searchmethod, for selects, the method to get the list
  EXAMPLES
   
 1. Return some fields for a specific contact (based on CONTACT_PERSON_SEARCH)
   
  var oSearch = new AdvancedSearch();
oSearch.endPoint = 'contact';
oSearch.method = 'CONTACT_PERSON_SEARCH';
oSearch.addField('firstname,surname,contactbusiness,contactbusinesstext,title,titletext');
oSearch.addFilter('id', 'EQUAL_TO', '1');
oSearch.rf = 'JSON';
oSearch.getResults(function(asData) { GetResultsComplete(asData);});
 2. Return the tradename for all businesses where their customer status is 1,
and where the tradename starts with 'arg' or 'er'. 

Return the first 5 rows, do the processing synchronously, and return the results in JSON format using the CONTACT_BUSINESS_SEARCH method.
   
  var oSearch = new AdvancedSearch();
oSearch.endPoint = 'contact';
oSearch.method = 'CONTACT_BUSINESS_SEARCH';
oSearch.rows = 5;

oSearch.addField('tradename');
    
oSearch.addFilter('customerstatus', 'EQUAL_TO', '1');
oSearch.addBracket('(');
oSearch.addFilter('tradename', 'TEXT_STARTS_WITH', 'arg');
oSearch.addOperator('or');
oSearch.addFilter('tradename', 'TEXT_STARTS_WITH', 'er');
oSearch.addBracket(')');

oSearch.rf = 'JSON';
oSearch.getResults(function(asData) { GetResultsComplete(asData);});
   
 3. jQuery looping through JSON return
 

$.each(oJSON.data.rows, function() 
{ 
      aName.push(this.firstname + ' ' + this.surname);
});
 4. Sample XML direct post
  Set advanced=1 and method=[method] on URL and set the POST data payload as per advancedsearch_example.xml
   
 5. Date filtering
 

Records created this month:

oAdvancedSearch.addFilter('createddate', 'GREATER_THAN_OR_EQUAL_TO', 'day', '0', 'start_of_month');
 

Records due either today or tomorrow

oAdvancedSearch.addFilter('due', 'GREATER_THAN_OR_EQUAL_TO', 'hour', '0', 'start_of_today');
oAdvancedSearch.addFilter('due', 'LESS_THAN_OR_EQUAL_TO', 'hour', '48', 'start_of_today');
   
6. Find birthdays between 2 dates.
  oSearch.addFilter('dateofbirth', 'BETWEEN_DAY_MONTH', '25 Dec 2010', '04 Jan 2011');
   
7. Grouping.
 

For example, if you want all businesses who have had an invoice, and how many they have had, and what the total is:

oSearch.endPoint = 'financial';
oSearch.method = 'financial_invoice_search';

oSearch.addField('ContactBusinessSentTo,ContactBusinessSentToText,count(id) SentToCount,sum(amount) SentToTotal');

This could be used for preparing a set of tabs, or an accordian, with the actual records being retrieved as required.