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
Rate this page
Checking your PaT sign-in…