Specifying Effective Date Criteria


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. 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 ADDRESSES record.

     

    Enter the desired information into the begins with field. Enter

     a valid value
     e.g.
     "ADDR"
    .

    Step 4
  5. Click the Search button.

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

    Step 6
  7. Click the Add Record link.

    Step 7
  8. A dialog box appears that indicates that the effective date criteria has been automatically added to this effective dated record.

     

    Click the OK button.

    Step 8
  9. 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 9
  10. For this example, the fields have already been selected for you.

    Step 10
  11. Now, view the fields of the query on the Fields page.

     

    Click the Fields tab.

    Step 11
  12. Use the Fields page to view the field properties of the fields you selected.

    Step 12
  13. In this example, specify that the data will be sorted by ID first, and then by effective date.

    Step 13
  14. Click the Reorder / Sort button.

    Step 14
  15. Use the Edit Field Ordering page to modify the order of the columns.

    Step 15
  16. Click in the New Column field.

    Step 16
  17. Enter the desired information into the New Column field. Enter

     a valid value
     e.g.
     "2"
    .

    Step 17
  18. Click the OK button.

    Step 18
  19. You next need to add criteria to the query.

     

    Click the Criteria tab.

    Step 19
  20. Use the Criteria page to view existing criteria or to add additional criteria to a query.

    Step 20
  21. When you choose a record that has EFFDT as a key field, Query Manager automatically creates default criteria. You want to edit this field to define specific dates. When you use effective-date comparisons, you will be returning one effective-dated row of information per item, and you must choose with what you want the effective date compared. The default is to use the effective date that is less than or equal to the current system date. There are other options you can use for comparison.

     

    You use the Edit Criteria Properties page to enter or modify selection criteria for the query.

     

    Click the Edit button.

    Step 21
  22. Use the Edit Criteria Properties page to enter or modify selection criteria for the query.

    Step 22
  23. The Condition Type drop-down lists the operators available for the Effective Date field. The value selected for this field determines how PeopleSoft Query will compare values for expressions.

     

    Click the Condition Type list.

    Step 23
  24. The options include:

    • EFF Date <; Returns rows for dates less than the specified date.

    • Eff Date <=; Returns rows for dates less than or equal to the specified date.

    • EFF Date >; Returns rows for dates greater than the specified date.

    • Eff Date >=; Returns rows for dates greater than or equal to the specified date.

    • First Eff Date; Returns the row that contains the lowest (oldest) effective-date value.

    • Last Eff Date; Returns the row that contains the highest (most recent) effective-date value.

    • No effective date; Does not use the effective date logic.

     

    Note that the effective date operators do not always return a single effective-dated row. For example, Eff Date <= returns the one row whose EFFDT value is most recent. Whereas, a not greater than operator returns the currently active row and all history rows.

    Step 24
  25. If you choose one of the comparison options, choose to compare each row's effective date against today's date or a date other than today.

     

    Select the Current Date option to use today's date.

    Step 25
  26. Select a Constant option when you want to see the rows that were effective as of a past date or that will be effective on some future date.

    Step 26
  27. Select a Field option when you want to see the rows that were effective at the same time as some other record.

    Step 27
  28. Select an Expression option if you want to prompt users for an effective date when they run the query.

    Step 28
  29. For this example, you will use the default criteria.

     

    Click the Cancel button.

    Step 29
  30. In this example, you want to add some additional criteria.

     

    Click the Add Criteria button.

    Step 30
  31. First, you want to specify campus address types.

     

    Click the Select Record and Field button.

    Step 31
  32. Click the A.ADDRESS_TYPE - Address Type link.

    Step 32
  33. Click in the Constant field.

    Step 33
  34. Enter the desired information into the Constant field. Enter

     a valid value
     e.g.
     "CAMP"
    .

    Step 34
  35. Click the OK button.

    Step 35
  36. Click the Add Criteria button.

    Step 36
  37. Next, specify California addresses.

     

    Click the Select Record and Field button.

    Step 37
  38. Click the A.STATE - State link.

    Step 38
  39. Click in the Constant field.

    Step 39
  40. Enter the desired information into the Constant field. Enter

     a valid value
     e.g.
     "CA"
    .

    Step 40
  41. Click the OK button.

    Step 41
  42. Click the Save button.

    Step 42
  43. Use the Enter a name to save this query: page to specify a name and description for the new query you created.

    Step 43
  44. Enter the desired information into the Query field. Enter

     a valid value
     e.g.
     "CA_CAMPUS_ADDRESS"
    .

    Step 44
  45. Click in the Description field.

    Step 45
  46. Enter the desired information into the Description field. Enter

     a valid value
     e.g.
     "IDs with addresses in CA"
    .

    Step 46
  47. Use the Query Type field to specify the type of query as User, Process, or Role. Standard queries are defined as User types, and queries that use workflow are defined as Process or Role types. For the exercise, retain the default query type.

    Step 47
  48. 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 48
  49. Click the OK button.

    Step 49
  50. Next, view the query results.

     

    Click the Run tab.

    Step 50
  51. Use the Run page to view the results of your query.

    Step 51
  52. The query has returned only the current effective-dated rows. Notice that there are 12 rows of data.

    Step 52
  53. Next, see what happens if you remove the effective-dated criteria.

     

    Click the Criteria tab.

    Step 53
  54. Click the Delete button.

    Step 54
  55. Click the Run tab.

    Step 55
  56. Notice that there are now 14 rows of output, three of which are addresses for one ID, AA0012. In other words, all effective-dated rows appear for ID AA0012.

    Step 56

You have successfully created an effective-dated query.

Table of Contents  Start Topic