Collection Data Type PMQL Properties

Overview
The following ProcessMaker Query Language (PMQL) properties apply to the Collection data type to perform PMQL search queries from any of the pages accessible from the Collections sidebar icon in the Admin top menu option. PMQL search queries apply to records within the displayed Collection. Selecting any PMQL search result displays the Collection record for that result.
​created property​
​data object​
​id property​
​modified property​
created: Record's Creation Date
Description
Examples
created property represents when the record was created from the currently displayed Collection.
Datetime Format
Enter the datetime in the PMQL search query within quotation marks in the following format: YYYY-MM-DD HH:MM:SS using 24-hour time. Example: "2020-07-01 14:25:15".
NOW Keyword
Use the NOW keyword to dynamically compare the current datetime with a specified number of chronological units. Use the Less Than operator (<) followed by the number of chronological units to search for Requests of that age. Note that the interval unit of time is singular. PMQL supports the following units of time:
second
minute
Example 1
Purpose of the search: Find Collection records created on or after March 14, 2020.
1
created >= "2020-03-14 00:00:00"
Copied!
Example 2
Purpose of the search: Find Collection records created one day ago for newly registered Freshmen students in which their academic interests include engineering-related majors except software engineering.
This example uses the data object. The data object stores the accumulated data from Screen controls used to create and edit records in its Collection by referencing the controls' Variable Name setting values.
A Select List control in which its Variable Name setting value is AcademicInterests stores multiple options in a JSON object that each Freshman student selected as academic interests.
Note the following:
This example uses the AND operator to require multiple property values for the search.
This example uses LIKE operator with the % wildcard to search for any Task name that includes the characters engineering as an academic interest, but excludes those records with software preceding any academic interest.
PMQL search queries are case sensitive. This example uses the lower function to ensure all options from AcademicInterests are evaluated regardless of their case sensitivity.
​Note when PMQL search queries are case sensitive.
1
(created < NOW -1 day) AND (lower(data.AcademicInterest) LIKE "%engineering%") AND (lower(data.AcademicInterest) != "software engineering")
Copied!
data object: Search Collection Data for Specific Record Information
Description
Examples
Use the data JSON object to search for sought Collection records. The data object stores the accumulated data from Screen controls used to create and edit records in its Collection by referencing the controls' Variable Name setting values.
 All JSON objects and arrays within the JSON object named data is the accumulation of data for each Collection. Each Request also stores its Request data in a data JSON object. The key names for each JSON object or array derive from the Variable Name setting values in the Screens used for that Collection or any data injected into that Collection's JSON data model by Scripts or calls to the RESTful Application Program Interface (API).
Using the data JSON object in PQML search queries helps answer the question "Which Collection records have specific information in them that I seek?" Use operators to compare the value for a particular Screen control to find records that only contain the value(s) you seek.
To determine what the Variable Name setting is for a control that stores Collection record information you seek, view the Screen that contains the control into which the sought data was entered and/or edited. Collections use as many as two Screens: one into which to enter data when a record in that Collection is created, and one from which record data is edited. The same Screen may be used for both purposes. Note that your user account or group membership must have the Screens: View Screens permission.
The data JSON object precedes the Variable Name setting value, as noted above. Use JSON dot notation to reference sub-properties in the referenced Screen control if necessary. 
Example 1
Purpose of the search: Find Collection records in which Business majors have enrolled in at least 18 credits this semester.
When submitting which courses to enroll that semester, each student must enter the following information into a Screen that becomes data in that Collection record when that Screen is submitted:
A Select List control of which its Variable Name setting is Major stores the JSON object value for the option that each student selects as a major.
A Line Input control of which its Variable Name setting is TotalCredits stores as an integer the sum of all credits that the student selected for enrollment.
Note the following:
This example uses the AND operator to require multiple property values for the search.
​Note when PMQL search queries are case sensitive.
1
(data.Major = "Business") AND (data.TotalCredits >= 18)
Copied!
Example 2
Purpose of the search: Find Collection records in which employment candidates had programming experience in at least JavaScript and Python and had "Manager" in a job title.
When submitting a job application, each candidate must enter the following information into a Screen that becomes data in that Collection record when that Screen is submitted:
A Select List control of which its Variable Name setting is CodingLanguages stores the JSON object value of programming languages in which the employment candidate has experience.
 A Loop control contains a Line Input control that stores the job title for each position the employment candidate held. The Variable Name setting values for each control are Jobs and JobTitle, respectively.
1
((data.CodingLanguages LIKE "%JavaScript%") AND (data.CodingLanguages LIKE "%Python%")) AND (data.Jobs.JobTitle LIKE "%Manager%")
Copied!
id: Collection Record ID Number
Description
Examples
id property represents the ID number for the sought record(s) from the currently displayed Collection.
This id property only applies to Collection record-related PMQL search queries, and is distinct from the id property for the Request data type or for the id property for the Task data type.
Example 1
Purpose of the search: Find Collection records that are newer than record ID 7.
1
id > 7
Copied!
Example 2
Purpose of the search: Find Collection records newer than Task ID 7 that were created after March 14, 2020.
Note the following:
This example uses the AND operator to require multiple property values for the search.
This example uses the created property to search for which Collections were created by a particular date. Note that PMQL interprets strings in the format YYYY-MM-DD HH:MM:SS as dates and can be used in comparative queries if that string is in quotation marks ("2020-03-14 00:00:00").
1
(id > 7) AND (created > "2020-03-14 00:00:00")
Copied!
modified: Datetime Record Last Modified
Description
Examples
modified property represents when the record was last modified from the currently displayed Collection. A record modifies when the contents of the a record changes from any of the following:
A record is manually changed by a user that has permission to edit records in that record's Collection.
A Script adds or modifies data in a record.
A record is modified via the RESTful API.
Datetime Format
Enter the datetime in the PMQL search query within quotation marks in the following format: YYYY-MM-DD HH:MM:SS using 24-hour time. Example: "2020-07-01 14:25:15".
NOW Keyword
Use the NOW keyword to dynamically compare the current datetime with a specified number of chronological units. Use the Less Than operator (<) followed by the number of chronological units to search for Requests of that age. Note that the interval unit of time is singular. PMQL supports the following units of time:
second
minute
Example 1
Purpose of the search: Find Collection records modified less than 30 minutes ago.
Note that this example uses the NOW keyword to represent the current datetime, and then uses the Less Than operator (<) to determine the datetime 30 minutes ago that the applicable Collection records were modified. Note that the unit of chronological measurement (minute) is singular.
1
modified < NOW -30 minute
Copied!
Example 2
Purpose of the search: Find Collection records modified one day ago associated with students whose graduation year is 2022 and who last met their advisor more than six months ago.
This example uses the data object. The data object stores the accumulated data from Screen controls used to create and edit records in its Collection by referencing the controls' Variable Name setting values. The following Screen controls store data that this PMQL search query references:
GraduationYear: The GraduationYear control stores at which year a student graduates. This value is stored as a string.
AdvisorMeetingDate: The AdvisorMeetingDate control stores at which date a student met with her or his advisor.
Note the following:
This example uses the AND operator to require multiple property values for the search.
This example uses the CAST function to convert the string to a number for comparison in the PMQL search.
​Note when PMQL search queries are case sensitive.
1
(modified < NOW -1 day) AND (CAST(data.GraduationYear as number) = 2022) AND (dat
Copied!
Related Topics
What is PMQL?
PMQL Syntax to Search ProcessMaker Data
PMQL Data Types: Request, Task, and Collection
Search Records in a Collection
Request Data Type PMQL Properties
Task Data Type PMQL Properties
What is Request Data?
What is a Request Variable?