where filter helper

[judgej]

A class to help build a where filter is sorely missing. I can find neither any classes that do this in other projects, nor full documentation from Xero on how to create a where filter. There are sporadic examples around, and various issues on help forums where people have discovered features. But core documentation? Nope.

Here is a list of what I've managed to find:

• Matching a GUID needs to use the GUID function: Contact.ContactID==Guid("e51117d5-500d-466d-9660-32e4410d8329")
• String matching:
  • Name.Contains("Peter")
  • Name.StartsWith("P")
  • Name.EndsWith("r")
  • Name=="Jones"
  • Name!="Fred"
• Using string matching functions with any columns that may be null will result in an error being thrown. This must be used with checks that the column is not null: EmailAddress!=null&&EmailAddress.StartsWith("boom")
• Numeric and date operators:
  • >
  • =
  • <
  • !=
• Booleans are not quoted: IsReconciled==true
• Dates must be supplied with the DateTime() function, unless they are the last update date, in which case that goes into a header: Date >= DateTime(2015, 01, 01) && Date < DateTime(2015, 12, 31). I have not found any examples with the time being used, and have no idea what timezone the DateTime() function generates the date as.
• The IDs parameter and the where filter can probably not be used together. Whichever comes first in the query parameters seems to take over the query filter completely, leaving the other ignored. That's wrong on multiple levels IMO.
• Case sensitivity on column names? Not idea, and not tested.
• The only boolean operator for joining expressions is and == &&. Some examples hint that the word AND can be used too.
• Quotes in strings presumably need to be escaped, but it is not clear how.
• Some posts hint that the OR joining condition is supported as well as AND. It's not clear how that works, as presumably parenthesis will be needed too, and there are no examples of that I can find.