You can also use a field from a record as criteria even if you have not chosen that field for query output. Defining a Criteria with Tree Prompt in PeopleSoft Query In addition to the option of selecting the tree value at design time for a criteria, Tree Prompts option will allow the user an option to select the tree and node value at run time. Image: Select A Constant page This example illustrates the field and controls on the Select A Constant page. The Edit Criteria Properties page appears with the selected value in the Define Constant page. This may cause subtle problems depending on what you are trying to do. Value Type Action Field The value in the selected field is compared to the value in another field, usually a field in another record component. The Edit Criteria Properties page appears enabling you to edit Expression 1 and Expression 2 fields.
You might want to see all the rows, regardless of their effective dates. To select from a list of values, click the Search button to display the Select a Constant page. Use the Edit List page to build a list of values for PeopleSoft Query to compare to the value from the first expression. This will give you different results - specifically 3 will exclude any cases where the maximum effective dated row is inactive while 2 will not. When a list of criteria includes parentheses, PeopleSoft Query evaluates the criteria inside the parentheses before the criteria outside the parentheses. It looks for rows where the customer is not friend and where the state is California or rows where the state is Florida. History Data rows that have effective dates earlier than the current data row.
For object codes for non-reg earnings, refer to the Earnings Code Table available through the attached link. The default effective sequence is 0, and it keeps on increasing by 1 whenever a new row with same effective date is added. For example, not equal to returns all rows that equal to would not return. Then in subquery, I used Expression to make the EffDt an aggregate one so that I can select Max on Field Edit menu. If you are comparing one field to another, select the second record field; if you are comparing the rows to a constant value, enter the constant.
I can do a sum and take the max but only that data element is the correct one while the rest are the last record in the database matching the other criteria. Fletcher This message has been edited. Condition Types Return Values between The value in the selected record field falls between two comparison values. Query Manager also offers a not option that reverses the effect of each condition type. The Edit Criteria Grouping page appears. While you can write a check today with a date last month, the payment will be made in the near future, not the past. The procedure for entering comparison values differs depending on what kind of value you are entering.
Effective dating allows you to store historical data, see changes in your data over time, and enter future data. This list results because PeopleSoft Query evaluates criteria in the order of appearance. Most probably it is looking at the latest row of empl job tablw which does not satisfy the criteria. To accomplish this task, add parentheses to the list of criteria. Select this option when you want to see the rows that were effective at the same time as some other record.
In List The value in the selected field is compared to a list of values that you enter. Note that for point 3 max effective dated row if it is active , you can have no data returned at all. The Select A Constant page appears again with the selected value in the field. Thanks for your time and thoughts. But what if someone forgot to key in a new hire in a month prior to the previous month say August.
Yes, points 2 and 3 are different. Tree Prompt Option The value in the selected field enables you to select a tree value as a tree prompt, when the query is run. Is a code example in the hr sqr section in nhirene. You can add a prompt to the expression that you define in the Define Expression page. When you enter new information that is related to existing data in this case about an employee , such as a transfer or pay rate change, you do not want to lose or overwrite the data already stored in the database.
The results returned from trees with this combination of details may be inaccurate. Note: The grid, containing the selected value, appears when a value is selected. For example, you may want to track several events in the career of Tom Sawyer: when he was hired, transferred, and promoted. Use the Look Up button to display the Edit List page and search for the desired values. Effective date — It helps you to maintain historical, current and future-dated data in PeopleSoft, with the help of effective sequence. PeopleSoft Payroll, on the other hand, is much like your personal checking account in which you can only make payments today or in the future. That is, until 1 January 2009, this address isn't of any consequence.