Collection Data Type PMQL Properties

Use these ProcessMaker Query Language (PMQL) properties for Collection record PMQL search queries.

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

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

Examples

Example 1

Purpose of the search: Find Collection records created on or after March 14, 2020.

created >= "2020-03-14 00:00:00"

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.

(created < NOW -1 day) AND (lower(data.AcademicInterest) LIKE "%engineering%") AND (lower(data.AcademicInterest) != "software engineering")

data object: Search Collection Data for Specific Record Information

Description

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.

Examples

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.

(data.Major = "Business") AND (data.TotalCredits >= 18)

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.

((data.CodingLanguages LIKE "%JavaScript%") AND (data.CodingLanguages LIKE "%Python%")) AND (data.Jobs.JobTitle LIKE "%Manager%")

id: Collection Record ID Number

Description

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.

Examples

Example 1

Purpose of the search: Find Collection records that are newer than record ID 7.

id > 7

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").

(id > 7) AND (created > "2020-03-14 00:00:00")

modified: Datetime Record Last Modified

Description

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

Examples

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.

modified < NOW -30 minute

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.

(modified < NOW -1 day) AND (CAST(data.GraduationYear as number) = 2022) AND (dat

Related Topics

What is PMQL?PMQL Syntax to Search ProcessMaker Platform DataPMQL Data Types: Request, Task, and CollectionSearch Records in a CollectionRequest Data Type PMQL PropertiesTask Data Type PMQL PropertiesSpecial Characters in API Call PMQL SearchesWhat is Request Data?What is a Request Variable?