Creating Record Joins


Concept


Steps:

  1. Begin by navigating to the Records page.

     

    Click the Reporting Tools link.

    Step 1
  2. Click the Query Manager link.

    Step 2
  3. Click the Create New Query link.

    Step 3
  4. Use the Records page to view existing records. Select an existing record to create a new query.

    Step 4
  5. The first step in creating a query is to find an existing record for the query. In this example, you will locate and use the Class Table record.

     

    Enter the desired information into the begins with field. Enter

     a valid value
     e.g.
     "CLASS_TBL"
    .

    Step 5
  6. Click the Search button.

    Step 6
  7. Click an entry in the Add Record column.

    Step 7
  8. The Query page lists all the fields for the selected record. You use this page to select the fields that you want to use in the query.

    Step 8
  9. For the purpose of this exercise, the fields have already been selected for you.

    Step 9
  10. In this example, you next need to select fields related to meeting information from the Class Meeting Pattern Table record.

     

    To join records that share a common high-level key, simply select the Hierarchy Join link.

     

    Click the Hierarchy Join link.

    Step 10
  11. Use the Select record for hierarchy join page to select the record to be joined to your existing query.

    Step 11
  12. Note that the hierarchy on this page is not related to the hierarchy of the Dictionary Tree. Rather, the hierarchy shown is defined in the PeopleSoft Application Designer with the Parent Record Name feature.

     

    Click the CLASS_MTG_PAT - Class Meeting Pattern Table link.

    Step 12
  13. Your newly joined record and its fields are displayed below the first record. Notice that each record added to your query is assigned an incremental letter that represents a correlation, or alias, of the record. The second record denotes that it was joined with the first record. In this example, CLASS_MTG_PAT (B) was joined with CLASS_TBL (A).

    Step 13
  14. Now you can select the fields from the joined record.

     

    Click the MEETING_TIME_START - Meeting .. option.

    Step 14
  15. Click the MEETING_TIME_END - Meeting ... option.

    Step 15
  16. Click the vertical scrollbar.

    Step 16
  17. Click the STND_MTG_PAT - Standard ... option.

    Step 17
  18. In this example, you next need to select the Facility Description field from the Facility Table record.

     

    Related records are specific to a field in the current record. If a field has a related record, you will see the record displayed as a hyperlink next to the field.

     

    Click the Join FACILITY_TBL - Facility Table link.

    Step 18
  19. Use the Select join type page to create either a standard join or a left outer join. In a left outer join, all rows of the first (left) record are present in the result set, even if there are no matches in the joining record.

    Step 19
  20. In this example, use a standard join.

     

    Click the OK button.

    Step 20
  21. Click the OK button.

    Step 21
  22. Notice that the newly joined record appears below the other two records and has been given the alias of “C”.

    Step 22
  23. Click the DESCR - Description option.

    Step 23
  24. Click the Fields tab.

    Step 24
  25. Use the Fields page to view how fields are selected for output, view the properties of each field, change headings, change column and sort orders, and apply aggregate values.

    Step 25
  26. In this example, want to sort the results by subject and catalog number and modify the heading text for the Facility Description field so that there are not two columns with the Descr heading.

     

    Click the Reorder / Sort button.

    Step 26
  27. Use the Edit Field Ordering page to modify the order of the columns.

    Step 27
  28. Click in the New Order By field.

    Step 28
  29. Enter the desired information into the New Order By field. Enter

     a valid value
     e.g.
     "1"
    .

    Step 29
  30. Click in the New Order By field.

    Step 30
  31. Enter the desired information into the New Order By field. Enter

     a valid value
     e.g.
     "2"
    .

    Step 31
  32. Click the OK button.

    Step 32
  33. You want to modify the heading text for the Facility Description field. Notice that there are two Description fields listed. You determine which field to modify by referring to the alias (in this example, A, B, or C) preceding the field name. You joined to the Facility Table last and it was given the alias of C. That is the field you want to modify.

     

    Click the Edit button.

    Step 33
  34. Use the Edit Field Properties page to change the column heading and apply the aggregate function to this query.

    Step 34
  35. Click the Text option.

    Step 35
  36. Click in the Heading Text field.

    Step 36
  37. Enter the desired information into the Heading Text field. Enter

     a valid value
     e.g.
     "Facility Descr"
    .

    Step 37
  38. Click the OK button.

    Step 38
  39. Click the Save As link.

    Step 39
  40. Use the Enter a name to save this query: page to name and describe your query.

    Step 40
  41. Enter the desired information into the Query field. Enter

     a valid value
     e.g.
     "MEETINGS_FALL_2002"
    .

    Step 41
  42. Click in the Description field.

    Step 42
  43. Enter the desired information into the Description field. Enter

     a valid value
     e.g.
     "Class Meetings Fall 2002 Term"
    .

    Step 43
  44. The Query Type field enables you to choose from User Query Type, Process Query Type, or Role Query Type. Standard queries are defined as User types, and queries that use workflow are defined as Process or Role types.

    Step 44
  45. Use the Owner field to specify the access to this query. Private indicates that only the user ID that created the query can open, run, modify, or delete the query. Public indicates that any user with access to the records used by the query can run, modify, or delete the query. For this example, you want to make it a private query.

    Step 45
  46. Click the OK button.

    Step 46
  47. Finally, view the results of the query.

     

    Click the Run tab.

    Step 47
  48. Use the Run page to view the results of your query.

    Step 48

You have successfully created a query using a record hierarchy join and a related record join.

Table of Contents  Start Topic