When you get a list of records, you can specify search criteria to filter records. This section explains what kind of criteria you can use.
First of all, all search criteria work only on searchable Columns. You need to make a Column searchable if you want to search on values for the Column. To make a Column searchable, make a field associated with the Column searchable on Platio Studio.
The simplest query is foo. This query matches Records
containing a Value “foo” in any of its Values. A query containing
multiple terms separated by spaces means logical conjunction. So
foo bar matches Records containing “foo” and “bar”. When
you want to use terms with spaces, quote them in "". For
example, "foo bar" baz matches Records containing “foo bar”
and “baz”.
If you want to query on a specific Column, prefix a query with a
Column name with :. For example, Name:foo
matches Records whose “Name” Column contains “foo”. Quote a Column name
in "" when it contains spaces or other spacial characters.
Name:"foo bar" "Body Temperature":36.4 matches Records
whose “Name” contains “foo bar” and whose “Body Temperature” is
36.4.
You can specify a Column by its ID instead of its name by prefixing
the ID with $. For instance, $c002c2c0:foo
matches Records whose Column of the ID $c002c2c0 contains
“foo”. Note that you need to quote a Column name in "" to
specify a Column by its name starting from $.
Please don’t put whitespace characters around :. It’ll
make the query have a different meaning.
You can specify more detailed queries when you use a comparison
operator instead of : to specify a Column. For example,
Name=foo matches Records whose “Name” Column is “foo”, and
"Body Temperature">=37.0 matches Records whose “Body
Temperature” is greater than or equal to 37.0.
Use an empty value to check if a Value is specified or not.
Name=, for example, matches Records whose “Name” Column has
no Value, and Name!= matches Records whose “Name” Column
has some Value.
Please don’t put whitespace characters around a comparison operator. It’ll make the query have a different meaning.
You can use these comparison operators.
It matches when a Value for the Column equals to the specified Value. However, it matches when no Value is set for the Column when the specified Value is empty.
It matches when a Value for the Column doesn’t equal to the specified Value. However, it matches when any Value is set for the Column when the specified Value is empty.
It matches when a Value for the Column is less than the specified Value.
It matches when a Value for the Column is less than or equal to the specified Value.
It matches when a Value for the Column is greater than the specified Value.
It matches when a Value for the Column is greater than or equal to the specified Value.
When you use whitespaces and special characters
(:=!<>()+/|\"'$) in query text or column names, you
need to quote them with "".
To use " in a quoted text, place \ in front
of ". To use \ itself, place \ as
well. For instance, "\"\\1,000\"" means ‘“\1,000”’.
You can also query Records by their updated time. To search Records
that are newer than specific time, use $from like
$from:2017-01-13. Use YYYY-MM-DD format to
specify a date, and YYYY-MM-DD'T'HH:mm:ss to specify a date
and time. For example, $from:2017-01-13 matches Records
newer than 13th January 2017, 12am (inclusive), and
$from:2016-10-03T15:00:00 matches Records newer than 3rd
October 2016, 3pm (inclusive).
You can also use YYYY-MM-DD'T'HH:mm:ss.SSS'Z' format
when you want to specify milliseconds. The timezone will always be UTC
in this case.
Similarly, use $to like $to:2017-01-13 to
search Records that are older than specific time. Use
YYYY-MM-DD format to specify a date, and
YYYY-MM-DD'T'HH:mm:ss to specify a date and time. For
example, $to:2017-01-13 matches Records older than 14th
January 2017, 12am (exclusive), and $to:2016-10-03T15:00:00
matches Records older than 3rd October 2016, 3pm (exclusive). Note that
it matches Records older than 12am of the next day when you specify a
date.
A specified date and time is interpreted that it’s specified in a
timezone specified by timezone parameter. It uses the
application’s timezone if you don’t specify timezone
parameter.
Of course, you can specify both of them such as
$from:2017-01-13 $to:2017-10-08T13:30:00 that matches
Records updated between 13th January 2017, 12am and 8th October 2017,
1:30pm.
You can combine these queries with ordinal queries. For example,
Name:"foo bar" "Body Temperature":36.4 $from:2017-01-13
matches Records whose “Name” contains “foo bar”, whose “Body
Temperature” is 36.4 and updated after 13th January 2017.
You can query Records by their timestamp as well as their updated time when you query records via Platio API.
A timestamp of a Record is updated when the Record is updated on the server in contrast to its updated time. If you saved a Record on Platio App while it was offline and the Record was synchronized to the server later, for example, its update time will be when you saved it and its timestamp will be when the server received it.
You can use the same format as queries on updated time to query
records on timestamp, but use $fromTimestamp instead of
$from, and $toTimestamp instead of
$to. For example, $from:2016-10-03T15:00:00
matches Records whose timestamp is newer than 3rd October 2016, 3pm
(inclusive).
You can query records on timestamp only when you query records via Platio API. These queries aren’t available on Platio App nor Data Viewer.
You can combine multiple queries by separating them by whitespaces.
It’ll match records containing all the queries. foo bar,
for example, will match records containing both “foo” and “bar”.
Use OR when you want to query records that match one of
the queries. For example, foo OR bar will match records
containing either “foo” or “bar”.
Use parentheses to combine these queries.
( foo OR bar ) baz will match records containing either
“foo” or “bar”, and “baz”. Note that you need to put whitespaces around
parentheses.
It depends on a Column type and an operator how a value specified in a query matches against a Column value.
A value specified in a query is converted to a value of a type of a
target Column, then compared. For example, whey you specify
30 in a query, it’ll be compared as text “30” when it
matches against a String Column, and compared as a number 30 when it
matches against a Number Column.
The former matches a String Value containing “30”, and the later matches a Number Value 30 when you don’t use a comparison operator. With a comparison operator, values are compared according to the comparison operator.
It never matches when a specified value cannot be converted to a Column type.
For String Columns, it compares a value specified in a query with their Value.
Without a comparison operator, it matches when the Column Value
contains the specified value in case-insensitive manner. For example,
"foo", "foo bar", "aFoo" matches
a query foo.
With a comparison operator, they’re compared in lexicographic order, in case-sensitive manner.
Text:Tokyo
Text:"Scott Tiger"
Text<KFor Number Columns, it converts a value specified in a query into a number, and compares the number with their Value.
Without a comparison operator, it matches when the Column Value
equals to the specified value. For example, 30 matches a
query 30, and 16.3 matches a query
16.3.
With a comparison operator, they’re compared as numbers.
Number:30
Number=16.3
Number>10For Boolean Columns, it converts true to true, and
false to false, and compares the boolean value with their
Value.
Without a comparison operator, it matches when the Column Value equals to the specified value.
With a comparison operator, it treats true greater than false.
Switch:true
Switch:falseFor DateTime Columns, you can specify a datetime or a date.
To specify a datetime, you can use ISO8601
YYYY-MM-DD'T'HH:mm:ss.sss'Z' format in UTC. You can also
specify duration from now. To specify duration, specify it in the
ASP.NET TimeSpan format starting from + or -.
For instance, -00:30 means 30 minutes ago, and
+00:00:25 means 25 seconds later.
To specify a date, you can use YYYY-MM-DD format. You
can also use today, tomorrow, or
yesterday.
Without a comparison operator, it matches when a Column Value equals
to the specified datetime when you specify a datetime. When you specify
a date, it matches when a date taken from a Column Value equals to the
specified date. It uses a specified timezone when taking a date from a
Column Value. For example, it’ll be 2024-01-01 when the
Column Value is 2023-12-31T20:00:00.000Z in
Asia/Tokyo, which matches a query
2024-01-01.
With a comparison operator, it converts a specified datetime or date
to a datetime and compare it with a Column Value. It uses a specified
datetime directly if it’s a datetime. When you specify a date, it’ll be
treated as 0:00:00 in a specified timezone. For example, when you use
DateTime>=2020-01-01 and the timezone is
Asia/Tokyo, it’s the same query as
DateTime>=2019-12-31T15:00:00.000Z.
In any cases, a timezone of the application is used if you don’t specify a timezone.
DateTime:today
DateTime>=-00:30
DateTime:2020-01-01
DateTime>"2020-01-01T00:00:00.000Z"For Date Columns, it converts a value specified in a query into date
using ISO8601 YYYY-MM-DD format, and compares the date with
their Value.
You can also specify today, tomorrow, or
yesterday which will be treated as today, tomorrow and
yesterday respectively.
To specify duration from today, specify an integer starting from
+ or -. For instance, +5 means 5
days later, and -30 means 30 days ago.
Without a comparison operator, it matches when the Column Value equals to the specified value.
With a comparison operator, they’re compared as dates.
Date:yesterday
Date>=-3
Date>2020-01-01For Date Columns, it converts a value specified in a query into time
using ISO8601 HH:mm:ss format, and compares the time with
their Value.
Without a comparison operator, it matches when the Column Value equals to the specified value.
With a comparison operator, they’re compared as time.
Time:10:00:00
Time>9:00:00Values in Location Columns cannot be searched.
For Attachment Columns, it compares a value specified in a query with their attachment name.
Without a comparison operator, it matches when the attachment name
contains the specified value in case-insensitive manner. For example,
Attachments whose names are "foo.jpeg",
"FOO.PNG" or "xfoo.txt" match a query
foo.
With a comparison operator, they’re compared in lexicographic order, in case-sensitive manner.
Image:image.jpegFor User Columns, it compares a value specified in a query with their user name.
Without a comparison operator, it matches when the user name contains
the specified value in case-insensitive manner. For example, Users whose
name are "foo", "Foo", "foobar"
match a query foo.
With a comparison operator, they’re compared in lexicographic order, in case-sensitive manner.
In either case, you can specify _self which matches the
current user.
User:_self
User:MichealValues in Object Columns cannot be searched.
For Array Columns, it compares a value specified in a query with each element in an Array value.
Without a comparison operator, it matches when any of the elements matches the specified value. It compares each element based on its type. For example, if a type of the elements is String, it matches when any of the elements contains the specified value in case-insensitive manner.
With a comparison operator, it never matches except when the
comparison operator is = or != and without a
value (such as "Multiple Text Selection"= and
"Multiple Text Selection"!=). The former matches when no
Value is specified and the latter matches when some Value is
specified.
"Multiple Text Selection":"New York"For ReferenceString Columns, it compares a value specified in a query with their referring Value. The comparison will be done in the same way as a String Column.
For ReferenceNumber Columns, it converts a value specified in query into a number, and compares the number with their referring Value. The comparison will be done in the same way as a Number Column.
For ReferenceUser Columns, it compares a value specified in a query with their referring Value. The comparison will be done in the same way as a User Column.