# PeopleTools PsoftQL - Examples

> PsoftQL  Examples for PeopleSoft PeopleTools Tables

---

LLMS index: [llms.txt](/llms.txt)

---

In this section we will show detailed PsoftQL examples of how to extract PeopleTools information using SWS PsoftQL Syntax. Each example includes a sample response so you can see the field shape before deciding which to wire up.

## Users and Their Roles

This example shows how to pull PSOPRDEFN and PSROLEUSER information and join them together to get a list of users and their roles. We also exclude some fields that are not needed. Additionally, we include the descriptions for the LANGUAGE_CD and CURRENCY_CD fields.

```json
{
    "isDebugMode": false,
    "includeFieldTypes": false,
    "includeAllDescriptions": false,
    "includeKeyFieldIndicators": false,
    "includeAllFieldLabels": false,
    "rowLimit": 10,
    "pageNumber": 1,
    "records": [
    {
        "recordName": "PSOPRDEFN",
        "includeDescriptionsFor": ["LANGUAGE_CD", "CURRENCY_CD"],
        "excludeFields": ["VERSION", "EXPENT", "OPERPSWD", "PTOPERPSWDV2", "OPERPSWDSALT", "ENCRYPTED"],
        "criteriaFields": [

        ]
    },
    {
        "recordName": "PSROLEUSER",
        "parentRecordName": "PSOPRDEFN",
        "doNotAutoJoinToParent": true,
        "joinFields": [
            {"parentField": "OPRID", "childField": "ROLEUSER"}
        ],
        "excludeFields": ["ROLEUSER"]
    }

    ]
}

```

Sample response — one user with their nested role list. `LANGUAGE_CD_description` appears because `LANGUAGE_CD` was listed in `includeDescriptionsFor`; `CURRENCY_CD_description` would appear too if the field had a non-blank value. The child `PSROLEUSER.fields` array carries each role assignment for the parent OPRID, joined via `OPRID = ROLEUSER`:

```json
{
  "data": {
    "PSOPRDEFN": {
      "objectType": "record",
      "objectName": "PSOPRDEFN",
      "fields": [
        {
          "rowNumber": 1,
          "OPRID": "AADAMS",
          "OPRDEFNDESC": "Andrew Adams",
          "LANGUAGE_CD": "ENG",
          "LANGUAGE_CD_description": "English",
          "CURRENCY_CD": "",
          "LASTUPDDTTM": "2021-09-08-05.01.17.440969",
          "LASTUPDOPRID": "PPLSOFT",
          "PSROLEUSER": {
            "objectType": "record",
            "objectName": "PSROLEUSER",
            "fields": [
              { "ROLENAME": "CS - Student",                "DYNAMIC_SW": "N" },
              { "ROLENAME": "CS - Student Applicant",      "DYNAMIC_SW": "N" },
              { "ROLENAME": "EOPP_USER",                   "DYNAMIC_SW": "N" },
              { "ROLENAME": "PeopleSoft User",             "DYNAMIC_SW": "N" },
              { "ROLENAME": "Standard Query Permissions",  "DYNAMIC_SW": "N" }
            ]
          }
        }
      ]
    }
  },
  "responseCode": 200,
  "nextPageNumber": 2
}
```

## PeopleSoft Record Definitions

Export PeopleSoft Record Definitions and their fields. This example shows how to pull PSRECDEFN, PSRECFIELDDB, and PSDBFIELD information and join them together to get a list of record definitions and their fields.

```json

{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "includeAllDescriptions": true,
  "includeKeyFieldIndicators": false,
  "includeAllFieldLabels": false,
  "rowLimit": 2,
  "records": [
    {
      "recordName": "PSRECDEFN",
      "includeDescriptionsFor": []
    },
    {
      "recordName": "PSRECFIELDDB",
      "parentRecordName": "PSRECDEFN"
    },
    {
      "recordName": "PSDBFIELD",
      "parentRecordName": "PSRECFIELDDB"
    }
  ]
}

```

Sample response (heavily trimmed — one parent record, one child field, one grand-child field). The full response for two records is ~60 KB because every PSRECDEFN explodes into dozens of PSRECFIELDDB rows, each with its own PSDBFIELD child. Filter aggressively with `criteriaFields` on the parent (`RECNAME = 'YOUR_RECORD'`) when you only need one record's structure:

```json
{
  "data": {
    "PSRECDEFN": {
      "objectType": "record",
      "objectName": "PSRECDEFN",
      "fields": [
        {
          "rowNumber": 1,
          "RECNAME": "PSROLEDEFN",
          "RECTYPE": 0,
          "PSRECFIELDDB": {
            "objectType": "record",
            "objectName": "PSRECFIELDDB",
            "fields": [
              {
                "RECNAME": "PSROLEDEFN",
                "FIELDNAME": "ALLOWLOOKUP",
                "FIELDNUM": 17,
                "DEFFIELDNAME": "Y",
                "USEEDIT": 8396800,
                "PSDBFIELD": {
                  "objectType": "record",
                  "objectName": "PSDBFIELD",
                  "fields": [
                    {
                      "FIELDNAME": "ALLOWLOOKUP",
                      "FIELDTYPE": 0,
                      "LENGTH": 1,
                      "OBJECTOWNERID": "PPT",
                      "DESCRLONG": "Allow Recipient Lookup"
                    }
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  },
  "responseCode": 200
}
```

This three-level nest is a useful pattern for "schema discovery" integrations — building a client-side model of every field on a record, including its underlying type from `PSDBFIELD`.

## Recently Modified Projects

This example shows how to use `orderByFields` to get the most recently modified App Designer projects. By ordering by `LASTUPDDTTM` in descending order, you get the most recently updated projects first.

```json
{
  "isDebugMode": false,
  "rowLimit": 10,
  "records": [
    {
      "recordName": "PSPROJECTDEFN",
      "sqlWhereClause": "LASTUPDDTTM is not null",
      "orderByFields": [
        {
          "fieldName": "LASTUPDDTTM",
          "sortOrder": "DESC"
        }
      ]
    }
  ]
}
```

Sample response (top 3) — note the timestamps step backward as you walk the array, confirming the DESC order is applied. This pattern is the standard "audit/recent-changes" query for any record that carries `LASTUPDDTTM`:

```json
{
  "data": {
    "PSPROJECTDEFN": {
      "objectType": "record",
      "objectName": "PSPROJECTDEFN",
      "fields": [
        { "rowNumber": 1, "PROJECTNAME": "CHG_CI_TEST",       "LASTUPDDTTM": "2026-05-08-23.22.42.036849" },
        { "rowNumber": 2, "PROJECTNAME": "CMALEK_NODE",       "LASTUPDDTTM": "2026-04-23-02.15.14.806879" },
        { "rowNumber": 3, "PROJECTNAME": "CHG_TOKEN_GEN_POC", "LASTUPDDTTM": "2026-04-22-16.53.29.852062" }
      ]
    }
  },
  "responseCode": 200,
  "nextPageNumber": 2
}
```

## Recently Modified Roles

Get the most recently modified PeopleSoft roles, sorted by last update date descending. This is useful for auditing recent security changes.

```json
{
  "isDebugMode": false,
  "rowLimit": 20,
  "records": [
    {
      "recordName": "PSROLEDEFN",
      "excludeFields": ["RECNAME", "FIELDNAME", "PC_EVENT_TYPE", "QRYNAME_SEC", "VERSION", "QRYNAME"],
      "orderByFields": [
        {
          "fieldName": "LASTUPDDTTM",
          "sortOrder": "DESC"
        }
      ]
    }
  ]
}
```

## Roles Sorted by Status and Last Update

This example demonstrates compound sorting with multiple `orderByFields`. Roles are sorted first by status (Active roles first alphabetically), then by last update date with most recent first.

```json
{
  "isDebugMode": false,
  "rowLimit": 50,
  "includeAllDescriptions": true,
  "records": [
    {
      "recordName": "PSROLEDEFN",
      "excludeFields": ["RECNAME", "FIELDNAME", "PC_EVENT_TYPE", "QRYNAME_SEC", "VERSION", "QRYNAME"],
      "orderByFields": [
        {
          "fieldName": "ROLESTATUS",
          "sortOrder": "ASC"
        },
        {
          "fieldName": "LASTUPDDTTM",
          "sortOrder": "DESC"
        }
      ]
    }
  ]
}
```