PPaT Wiki
DocumentationPathwayForumBlogContact
Week streakFOUNDATION0 badges
Language: English
  • English
  • Nederlands
  • Français
  • Deutsch
  • Lea faka-Tonga
  • Te reo Māori
  • မြန်မာ (Burmese)
Ask the community

Your PaT Pathway

0/46
Foundation
0/43
Practitioner
0/37
Advanced
0/20
Super User
  • Company Structure: adding a new user
  • Company Structure: customer companies
  • Company Structure: departments, job titles and roles
  • Company Structure: modules for a site or a country
  • Company Structure: process categories
  • Connections: linking PaT to Microsoft 365
  • Forms - API Integration
  • How to create a campaign for the PaT Lite training portal
  • How to edit an existing Bulletin Board Post
  • How to set up your SD Map
  • LMS Feedback: learner ratings and comments
  • LMS Metrics - If you can't measure it, you can't manage it
  • Manage users
  • Pacing recertifications and escalations
  • PaT Business Unit Template Overview
  • SD Workflows - Multi Submit
  • SD Workflows - Workflow Settings
  • System Activity: escalations, logins, emails and more
  • System Config: company settings
  • The org chart
  • Add any Image as a Section Header Icon
  • Assigning an SD to a new parent
  • C2E Attachments
  • Chat reminder
  • Chat to email to chat
  • Chat to SD
  • Chat to task to project
  • Comprehension Audits
  • Customer-specific training: how it works
  • Different Form Uses
  • Docked Chats
  • Docking SDs and Updating Images
  • Email to BFF
  • Email to chat to email – non PaT users
  • Embedding live data in an SD (graphs and spreadsheets)
  • Embedding SDs in SDs
  • Evidential audits: completing one
  • Filing and retrieving SDs
  • Form Wall
  • Health and safety templates
  • How PaT Manages Inactive Users
  • How SD chats work with SD Members and External Users
  • How to unlock a module for a staff member
  • Info Icons
  • Inline chats
  • Invite Attendees
  • LMS Architecture
  • Moving Section Headers
  • Multi-Tasks
  • PaT AI
  • Pop-Out Chat Feature
  • Recurring tasks: create and edit
  • Running Meetings with SDs
  • Safe Walks
  • SD Element Categorisation
  • SD Form Responses: finding and exporting form responses
  • SD Owner Functions
  • SD Task wall view
  • Share and record your screen
  • Task Integrity: what it is and why it drops
  • Track Engagement
  • Using SDs as a report or meeting minutes app
  • Video rooms: guests joining and asking to record
  • Activity Notifications
  • Add members and set permissions
  • AI Summary: a quick summary of an SD
  • Capture and Manage Accidents
  • Capture and Manage Hazards
  • Chat to task
  • Chat Wall
  • Company wiki: searching your company's own modules
  • Contextual Communication
  • Convert a Note to a Task
  • Create a new SD
  • Create a Task
  • Critical Chrome Extensions
  • Document Store: shared links, your folders and favourites
  • Fast Notes
  • Getting help: the PaT Helpdesk
  • Global Search
  • Group Chats
  • How to embed Outlook onto your PaT
  • How to resize your browser to best view PaT
  • How to use the PaT Lite training portal
  • Insert links
  • Module Learning - Desktop
  • Module Learning - Mobile
  • My Profile
  • Newbie PaT Intro – Chats and SDs
  • Notes
  • Out Of Office: turning it on, and the calendar on your Task Wall
  • Outlook 2FA Issue
  • PaT Chat Overview
  • PaT Mobile App – Overview
  • PaT service status: the status page, status alerts and status notices
  • PaT Wiki: finding your way around
  • PaT Wiki: searching, the AI answer and rating it
  • Preview, download and share files
  • Publish a Bulletin Board Post
  • Reading an SD in another language
  • Release-note alerts: a Sys Bot chat when PaT's release notes change
  • SD Elements
  • SD Main page useful features
  • SD Overview
  • Section Headers
  • Security measures for multiple users on a device
  • Share Feedback
  • Signing in to PaT and resetting your password
  • Signing in with MFA: the second step
  • Task Management
  • Task Management System
  • Task Wall
  • The connection icon on your PaT desktop
  • Traffic light progress
  • Upload file attachments
  • Video calls on the mobile app
  • AI Playbook: checking for other tickets with the same number
  • Auto Escalation
  • Build a reporting system
  • Calculated fields and calculated columns
  • Charts: options for lines, bars, axes and single values
  • Chat Management - Pro Tips
  • Conditional Formatting
  • Configuring a UDF: columns and calculated columns
  • Configuring a UDF: filters, grouping and endpoint details
  • Customer-specific training: setting it up
  • Data sources: stage filters, dynamic dates and page values
  • Endpoint Manager: finding endpoints and building your own UDF
  • Form builder: field types
  • Form Builder: layout at each screen size
  • Form Builder: tools and shortcuts
  • Form buttons, PDFs and what happens after submit
  • Form fields: defaults, read-only and other settings
  • Form Test Mode
  • Forms - Extract Data (the AI form builder)
  • Grids linked to a reporting SD: history charts and review comments
  • Grids: columns, formats and filters
  • Grids: conditional formatting
  • Grids: flatten and transpose data
  • Grids: rows, default values and editing rules
  • How managers can turn on OOF for their staff
  • How to build modules in the Module publisher
  • How to create module comprehension audits
  • How to create module evidential audits
  • How to create module quizzes
  • How to embed an SD into the LMS
  • How to make form templates live for everyone to use
  • How to upload form elements to an SD
  • How to use the PAR SD
  • Live H&S Reporting
  • Mastering the Business Strategy Template
  • Module Admin: finding and managing modules
  • Module Completion Notification Setup
  • Module Publisher Content Options
  • PaT SD Map Overview
  • PaT SD Map Presentation
  • PaT SD Templates Overview
  • Playbook Admin: the tickets behind each Quality and Cost category
  • Playbook Admin: when your shadow run is ready to review
  • Playbook editor: adding a portal lookup step
  • Playbook editor: adding a step that cancels an order
  • Pure Forms
  • Required fields, validation and containers
  • Rich text, file uploads and speech to text
  • SD Dashboard: building a dashboard
  • SD Forms
  • SD Security Settings
  • SD Template Helper Functions
  • Stacked views, flip cards, tabs and stages
  • Strategy System
  • Throttling
  • Using Chat to SD to go from being a good manager to a great one
  • Using SDs as the LMS Document
  • PaT Lite: Key aspects of PaT you need to understand
  • What is PaT and why it matters
  • PaT Lite: Chat to Task
  • PaT Lite: Introducing Strategy Documents
  • PaT Lite: Core Philosophy - Standards and Tactical Execution
  • PaT Lite: Intelligent tools that think with you
  • PaT Lite: The Future of business is Culture
  • Security
  • Integrations
0/46
Foundation
0/43
Practitioner
0/37
Advanced
0/20
Super User
  1. Documentation
  2. Advanced
  3. Data sources: stage filters, dynamic dates and page values

Data sources: stage filters, dynamic dates and page values

Summary This lesson is for the people who build UDFs in the SD Endpoint Manager, and the forms and dashboards that use them. It shows how to filter a UDF by a date that moves with the calendar, such…

Check yourself

Checking your PaT sign-in…

How To

On this page
  • Opening the Query Builder of a UDF
  • Step 1
  • Step 2
  • Adding a rule on a date column
  • Step 1
  • Step 2
  • Step 3
  • Step 4
  • Step 5
  • Step 6
  • Inserting a function from the Dynamic Functions Library
  • Step 1
  • Step 2
  • Step 3
  • Step 4
  • Step 5
  • Step 6
  • Step 7
  • Step 8
  • What each date function gives
  • Step 1
  • Step 2
  • Step 3
  • Step 4
  • Step 5
  • Step 6
  • Filtering by a date range, such as this quarter
  • Step 1
  • Step 2
  • Step 3
  • Step 4
  • Filtering form responses by stage
  • Step 1
  • Step 2
  • Step 3
  • Step 4
  • The default values a data source sends
  • Step 1
  • Step 2
  • Step 3
  • Step 4
  • Step 5
  • Step 6
  • Values the page passes to a data source
  • Step 1
  • Step 2
  • Step 3
  • Purpose / Intent of the Module
  • Header One

Summary This lesson is for the people who build UDFs in the SD Endpoint Manager, and the forms and dashboards that use them. It shows how to filter a UDF by a date that moves with the calendar, such as the start of the quarter or the year, with the Dynamic Functions Library in the Query Builder. It also covers filtering form responses by stage, the default values a data source sends, and the values a page passes to its data sources.

Opening the Query Builder of a UDF

Step 1

In the SD Endpoint Manager, find your UDF and click Edit Endpoint on its card. The Configure Data Source window opens. To build a UDF first, see the lesson Endpoint Manager: finding endpoints and building your own UDF.

Step 2

Click the Query Builder tab, the third along, after Endpoint and Columns. Its first section, WHERE Clause, holds the rules that filter the data. The lesson Configuring a UDF: filters, grouping and endpoint details covers rules, sections and grouping.

Adding a rule on a date column

Step 1

A dynamic date is a function that stands for a date, such as BOQ(), the first day of the current quarter. PaT works out the date each time it fetches the data, so a filter such as from the start of this quarter stays right without anyone changing it.

Step 2

Under WHERE Clause, click Add Rule, or Add First Rule while there are no rules. A new rule appears, with Field, Operator and a value box.

Step 3

Under Field, choose the date column to filter on. Each column in the list shows its type under its name, and the functions are offered for a column of type date or datetime.

Step 4

A column of another type, such as string, offers other operators, such as Equals and Contains, and no books icon, so the functions cannot be used on it.

Step 5

Under Operator, choose how to compare: On date, Not on date, After, Before, On or after, On or before, or Between dates. Is null and Is not null find rows with no date, or with one.

Step 6

PaT puts today's date in the value box of a new date rule. Insert Function replaces that date with the function, so you do not need to delete it first.

Inserting a function from the Dynamic Functions Library

Step 1

At the right of the value box is a books icon. Point at it to see Open Dynamic Functions Library, then click it.

Step 2

The library opens, titled Dynamic Functions and Variables Library, on the Single Date Functions: 16 functions, each on its own card. Each card shows the function, the date it gives, an Example and a Usage line.

Step 3

A card for a function with n in its brackets, such as BOM(n), also shows Parameters, with examples of the number to use.

Step 4

To find a function quickly, type a word in the search box at the top of the library, for example quarter: only the four quarter functions stay. Under the box, Found says how many functions match.

Step 5

Click Insert Function on the card you want. The library closes and the function goes into the value box, inside double curly brackets. A message at the top right of the screen confirms what was inserted.

Step 6

If the function has n in its brackets, replace the n with a number in the value box. A number below zero counts back, and a number above zero counts forward: BOM(-1) is the first day of last month.

Step 7

To copy a function instead, click the copy icon on its card (Copy to clipboard), then paste it into a value box. Close, at the bottom right of the library, shuts it without changing anything.

Step 8

Under the rule, Preview shows the rule in words. Click Apply to Data, at the top right of the Configure Data Source window, to save the rule and see the filtered data.

What each date function gives

Step 1

now() is today's date. now(n) is n days from today: now(-30) is 30 days ago, and now(7) is a week from today.

Step 2

BOW() is the Monday of the current week, and EOW() is its Sunday.

Step 3

BOM() is the first day of the current month, and EOM() is its last day. BOM(n) and EOM(n) move by n months: EOM(-1) is the last day of last month.

Step 4

BOQ() is the first day of the current quarter, and EOQ() is its last day: in August, they are 1 July and 30 September. BOQ(n) and EOQ(n) move by n quarters: BOQ(-4) is the start of the same quarter last year.

Step 5

BOY() is 1 January of the current year, and EOY() is 31 December. BOY(n) and EOY(n) move by n years: BOY(-1) is 1 January last year. The year functions follow the calendar year, not a financial year.

Step 6

If you type a function by hand, type it as its card shows it, capitals and brackets included. Each function stands for a date written as year, month and day, such as 2026-07-01.

Filtering by a date range, such as this quarter

Step 1

For a range, use two rules on the same date column. In the first rule, set Operator to On or after and insert the start of the range, such as BOQ().

Step 2

Click Add Rule (AND). In the new rule, choose the same column under Field, set Operator to On or before, and insert the end of the range, such as EOQ(). Together the two rules keep the rows of the current quarter.

Step 3

More ranges: last month is On or after BOM(-1) with On or before EOM(-1). The last 30 days is On or after now(-30) with On or before now(). This year is On or after BOY() with On or before EOY().

Step 4

Between dates gives a rule two boxes, each with its own books icon. Insert Function puts the function only in the box whose books icon you clicked, in place of its date. The other box keeps its own.

Filtering form responses by stage

Step 1

Some forms are filled in by stages: each container with Enable multi-submit form workflow for this section switched on is one stage. See the lesson SD Workflows - Multi Submit.

Step 2

A Form Responses UDF can be filtered by stage, so that it sends the responses saved at the stage you choose. There is no stage setting in the Query Builder: add stage= followed by the stage's id to the end of a grid's Address of endpoint, or give the data source a query string with the Key stage and the stage's id as its Value.

Step 3

PaT knows each stage by its Multi-Submit stage id, a long code under Multi-Submit Workflow on the General tab of the container's settings in the Form Builder. Copy it from there: the filter needs exactly that code, and ignores anything else.

Step 4

Address of endpoint is just under Endpoint in the grid's Edit form field window. Put a question mark before stage= if the address has none yet; if it already has one, put an ampersand before stage= instead, as between the other Key=Value pairs. Then click the green Save and Close button at the bottom right of the window.

The default values a data source sends

Step 1

A data source can carry query strings: pairs of a Key and a Value that it sends each time it fetches data. An administrator sets them in the SD Endpoint Manager: on the data source's card, click the three dots, then Edit Details.

Step 2

The person building a form or dashboard connects a grid to a data source in the grid's Edit form field window: on the General tab, set Data Mode to Endpoint pulled from a data source, then choose the Endpoint. See the lesson SD Dashboard: building a dashboard.

Step 3

PaT then fills in Address of endpoint, just under Endpoint, with the data source's address. Any query strings are added at the end, after a question mark, each as Key=Value; one with an empty Value is left out. These are the default values the grid sends.

Step 4

To send a different value from one grid only, change the value after its equals sign in Address of endpoint, then click the green Save and Close button at the bottom right of the window. Choosing the Endpoint again puts the defaults back.

Step 5

Whether a value changes the data depends on the data source: a value counts only if the data source's query reads it.

Step 6

Charts, single value displays and dropdowns have no Address of endpoint in their settings, so a value cannot be changed for one of them alone.

Values the page passes to a data source

Step 1

When a page opens, PaT passes the SD the page is open on to the data source of each grid on the page, as primaryLink. If Address of endpoint already sets primaryLink, PaT keeps that value instead.

Step 2

A name in double curly brackets in Address of endpoint is a placeholder for a value from the page.

Step 3

If PaT cannot find a value for a placeholder, it leaves it blank and shows a warning that starts Error fetching data. Error replacing placeholder in endpoint. Check the name in the address.

Purpose / Intent of the Module

A brief description of what this module is about

Header One

Ready? Back to the quiz

Rate this page

Checking your PaT sign-in…

PreviousCustomer-specific training: setting it upNext Endpoint Manager: finding endpoints and building your own UDF

On this page

  • Opening the Query Builder of a UDF
  • Step 1
  • Step 2
  • Adding a rule on a date column
  • Step 1
  • Step 2
  • Step 3
  • Step 4
  • Step 5
  • Step 6
  • Inserting a function from the Dynamic Functions Library
  • Step 1
  • Step 2
  • Step 3
  • Step 4
  • Step 5
  • Step 6
  • Step 7
  • Step 8
  • What each date function gives
  • Step 1
  • Step 2
  • Step 3
  • Step 4
  • Step 5
  • Step 6
  • Filtering by a date range, such as this quarter
  • Step 1
  • Step 2
  • Step 3
  • Step 4
  • Filtering form responses by stage
  • Step 1
  • Step 2
  • Step 3
  • Step 4
  • The default values a data source sends
  • Step 1
  • Step 2
  • Step 3
  • Step 4
  • Step 5
  • Step 6
  • Values the page passes to a data source
  • Step 1
  • Step 2
  • Step 3
  • Purpose / Intent of the Module
  • Header One
PPaT Wiki

Documentation, knowledge base and community support for People and Tasks.

Documentation

  • Getting started
  • Core concepts
  • Integrations
  • API reference Coming soon

Support

  • Knowledge base
  • Release notes
  • Community forum
  • Contact us
  • Service status

Company

  • PaT
  • Blog
  • Terms
  • Privacy

© People and Tasks. All rights reserved.