File Layout Examples
If your data is hierarchical in nature, or based on existing PeopleSoft records or pages, you want to use a File Layout definition for reading and writing your data, rather than doing it line by line (or field by field.)
For example, suppose you wanted to write all the information from a record to a file. You can use the WriteRecord method to write all the data from the record, instead of having to loop through every field, find the value, and write it to the file.
In addition, you could write all the information from a transaction (or several transactions) from a page to a file. Each transaction can be considered a rowset. A rowset can contain more than one record and is generally composed in a hierarchical structure. You could create a File Layout definition that has the same structure as the page (or component), and use the WriteRowset method. If you have a file that contains data in the correct format, you can use the ReadRowset method to read the data from the file to the page.
Each file layout is associated with a format. This format specifies the type of data in the files. You specify the format as part of the File Layout Properties. You can only specify one format for a file layout. Available formats are:
FIXED (default)
CSV
XML
The file layout methods and properties use this information to handle each file type in a transparent manner. Generally, you don’t need to do anything different based on file type. Any exceptions are noted in the documentation.
Note: Unlike other PeopleTools definitions, records and field are copied to a File Layout definition. There are no pointers. This means if you change a record definition (add or remove a field) you must change the File Layout definition also. The changes are not automatically propagated. This is why the documentation refers to these elements as file records, file fields, and so on, to show that they are no longer part of the original definition they were created from.
PeopleSoft recommends regenerating all file layout definitions after any upgrade, to avoid any corruption caused by changes to the database.
See Using Standalone Rowsets for more examples of writing from and reading to files using File Layout and standalone rowsets.
Image: Example File Layout definition (ABS_HIST)
In the following example, the File Layout definition is based on the record ABSENCE_HISTORY, and looks like this:
You should note the following about the using the WriteRecord method:
Not all the fields in the File Layout definition and the record have to match. The WriteRecord method, like all File Layout methods, applies only to the like-named fields. If there are additional fields in the record or in the File Layout definition, they are ignored.
The WriteRecord method writes only to like-named records. If you rename a record after you use it to create a File Layout definition, you must rename it to the exact same name in your File Layout. Because WriteRecord writes like-named records, the same file layout definition can contain more than one record.
The WriteRecord method takes a record object as its parameter. A populated record object references a single row of data in the SQL table. This is why a SQL Fetch statement is used in a condition around the WriteRecord method. This fetches every row of data from the SQL table, then writes it to the file.
The following code writes the ABSENCE_HIST record to the file record.txt.
Local Record &RecLine;
Local File &MYFILE;
Local SQL &SQL2;
&MYFILE = GetFile("record.txt", "A");
If &MYFILE.IsOpen Then
If &MYFILE.SetFileLayout(FileLayout.ABS_HIST) Then
&RecLine = CreateRecord(RECORD.ABSENCE_HIST);
&SQL2 = CreateSQL("%Selectall(:1)", &RecLine);
While &SQL2.Fetch(&RecLine)
&MYFILE.WriteRecord(&RecLine);
End-While;
Else
/* do error processing -; filelayout not correct */
End-If;
Else
/* do error processing -; file not open */
End-If;
&MYFILE.Close();If you wanted to write only changed records to the file, you could add the following code that is set in bold font:
While &SQL2.Fetch(&RecLine)
If &RecLine.IsChanged Then
&MYFILE.WriteRecord(&RecLine);End-If;
End-While;The following is the first part of a sample data file created by the previous code. The field format is FIXED:
8001 VAC 09/12/1981 09/26/1981 14 0 P Y
8001 VAC 03/02/1983 03/07/1983 5 0 P Y
8001 VAC 08/26/1983 09/10/1983 13 0 P Y
8105 CNF 02/02/1995 ??/??/ 0 0 U N
8516 MAT 06/06/1986 08/01/1986 56 0 P Y
8516 SCK 08/06/1988 08/07/1988 1 0 P Y
8516 VAC 07/14/1987 07/28/1987 14 0 P Y
8553 JUR 12/12/1990 12/17/1990 5 0 Local Jury Duty P N
8553 MAT 02/20/1992 10/01/1992 224 0 Maternity Leave U N
8553 MAT 08/19/1994 03/01/1995 194 0 Maternity-2nd child U Y
8553 PER 04/15/1993 04/19/1993 4 0 U N Personal Day required
8553 SCK 01/28/1987 01/30/1987 2 0 Hong Kong Flu P N
8553 SCK 08/02/1988 08/03/1988 1 0 Sick P N
8553 SCK 09/12/1995 09/13/1995 1 0 P N
8641 VAC 06/01/1988 06/15/1988 14 0 P Y
8641 VAC 07/01/1989 07/15/1989 14 0 P Y
G001 MAT 07/02/1991 09/28/1991 88 0 3-month Maternity leave P Y Maternity will be paid as 80% of Claudia's current salary. If a record in the File Layout definition has a File Record ID, each line in the file referencing this record is prefaced with this number. The File Record ID is not a field in the data itself. It is also referred to as the rowid. File Record IDs can be useful when the file you’re producing refers to more than one record.
File Record IDs can be used only with File Layout definitions that have a type of FIXED. If a File Layout definition has only one level then File Record IDs can be omitted. But for File Layout definitions with more than one level, you must use File Record IDs.
The following is sample file, produced with the same code, but with a File Record ID of 101 added:
101 8001 VAC 09/12/1981 09/26/1981 14 0 P Y
101 8001 VAC 03/02/1983 03/07/1983 5 0 P Y
101 8001 VAC 08/26/1983 09/10/1983 13 0 P Y
101 8105 CNF 02/02/1995 ??/??/ 0 0 U N
101 8516 MAT 06/06/1986 08/01/1986 56 0 P Y
101 8516 SCK 08/06/1988 08/07/1988 1 0 P Y
101 8516 VAC 07/14/1987 07/28/1987 14 0 P Y
101 8553 JUR 12/12/1990 12/17/1990 5 0 Local Jury Duty P N
101 8553 MAT 02/20/1992 10/01/1992 224 0 Maternity Leave U N
101 8553 MAT 08/19/1994 03/01/1995 194 0 Maternity-2nd child U Y
101 8553 PER 04/15/1993 04/19/1993 4 0 U N Personal Day required
101 8553 SCK 01/28/1987 01/30/1987 2 0 Hong Kong Flu P N
101 8553 SCK 08/02/1988 08/03/1988 1 0 Sick P N
101 8553 SCK 09/12/1995 09/13/1995 1 0 P N
101 8641 VAC 06/01/1988 06/15/1988 14 0 P Y
101 8641 VAC 07/01/1989 07/15/1989 14 0 P Y
101 G001 MAT 07/02/1991 09/28/1991 88 0 3-month Maternity leave P Y Maternity will be paid as 80% of Claudia's current salary. Note: File positions start at 1, not 0. When you specify a File Record ID, make sure that the starting position of the Record ID is greater than 0.
This following example uses the same File Layout definition as the previous example. The file format is CSV. The fields are separated by commas and delimited by double-quotes.
"8001","VAC","09/12/1981","09/26/1981","14","0","","P","Y",""
"8001","VAC","03/02/1983","03/07/1983","5","0","","P","Y",""
"8001","VAC","08/26/1983","09/10/1983","13","0","","P","Y",""
"8105","CNF","02/02/1995","??/??/","0","0","","U","N",""
"8516","MAT","06/06/1986","08/01/1986","56","0","","P","Y",""
"8516","SCK","08/06/1988","08/07/1988","1","0","","P","Y",""
"8516","VAC","07/14/1987","07/28/1987","14","0","","P","Y",""
"8553","JUR","12/12/1990","12/17/1990","5","0","Local Jury Duty","P","N",""
"8553","MAT","02/20/1992","10/01/1992","224","0","Maternity Leave","U","N",""
"8553","MAT","08/19/1994","03/01/1995","194","0","Maternity-2nd child","U","Y",""
"8553","PER","04/15/1993","04/19/1993","4","0","","U","N","Personal Day required"
"8553","SCK","01/28/1987","01/30/1987","2","0","Hong Kong Flu","P","N",""
"8553","SCK","08/02/1988","08/03/1988","1","0","Sick","P","N",""
"8553","SCK","09/12/1995","09/13/1995","1","0","","P","N",""
"8641","VAC","06/01/1988","06/15/1988","14","0","","P","Y",""
"8641","VAC","07/01/1989","07/15/1989","14","0","","P","Y",""
"G001","MAT","07/02/1991","09/28/1991","88","0","3-month Maternity leave","P","Y","Maternity will be paid as 80% of Claudia's current salary."To read in the previous CSV file we use the following PeopleCode. It reads the file into a temporary record. First each line of the file is read into a string. The string is split into an array, with the value of each field in the array becoming an element in the array. The value of each field in the record is assigned a value from the array. After additional processing (for example, converting strings into dates or numbers, verifying data, and so on) the record can be inserted into the database. To insert the final data into the database, this code must be associated with a PeopleCode event that allows database updates, that is, SavePreChange, WorkFlow, SavePostChange, and so on. This code could also be used as part of an Application Engine program.
Local File &MYFILE;
Local Record &REC;
Local array of string &ARRAY;
&MYFILE = GetFile("c:\temp\vendor.txt", "R", %FilePath_Absolute);
&REC = CreateRecord(RECORD.ABS_HIST_TEST);
&ARRAY = CreateArrayRept("", 0);
If &MYFILE.IsOpen Then
If &MYFILE.SetFileLayout(FILELAYOUT.ABS_HIST) Then
While &MYFILE.ReadLine(&STRING);
&ARRAY = Split(&STRING, ",");
For &I = 1 To &REC.FieldCount
&REC.GetField(&I).Value = &ARRAY[&I];
End-For;
/* do additional processing here for converting values */
&REC.Insert();
End-While;
Else
/* do error processing - filelayout not correct */
End-If;
Else
/* do error processing - file not open */
End-If;
&MYFILE.Close();Note: You can't read a file that contains a thousands separator for numeric fields. You must strip out the separator before you try to read in the file.
Image: Example File Layout definition (EMPL_CHECKLIST)
In the following example, the File Layout definition is based on the component EMPL_CHECKLIST, and looks like this:
Image: EMPLOYEE_CHECKLIST Component structure
Here’s the structure of the component EMPLOYEE_CHECKLIST:
Note that:
Every field in the two structures don’t have to match (that is, every field or record that’s in the file layout doesn’t have to be in the component, and vice versa.)
The two structures must be the same. That is, if the component has PERSONAL_DATA at level zero, and EMPL_CHECKLIST at level one, the file layout must have the same hierarchy.
The following example uses the previous File Layout definition to copy data from the EMPL_CHECKLIST page into a file.
The CreateRowset function creates an empty rowset that has the structure of the file layout definition. The GetRowset function is used to get all the data from the component and copy it into the rowset. The GetLevel0 function copies all like-named fields to like-named records. The WriteRowset method writes all the component data to the file. Because this code runs on the server, an absolute file path is used.
See GetRowset.
Example
The following is the PeopleCode for this example.
Local File &MYFILE;
Local Rowset &FILEROWSET;
&MYFILE = GetFile("c:\temp\EMP_CKLS.txt", "A", %FilePath_Absolute);
If &MYFILE.IsOpen Then
If &MYFILE.SetFileLayout(FILELAYOUT.EMPL_CHECKLIST) Then
&FILEROWSET = &MYFILE.CreateRowset();
&FILEROWSET = GetLevel0();
&MYFILE.WriteRowset(&FILEROWSET, True);
Else
/* file layout not found, do error processing */
End-If;
Else
/* file not opened, do error processing */
End-if;
&MYFILE.Close();The following is a sample data file created by the previous code:
8113 Frumman,Wolfgang
08/06/1999 000001 8219 Going to London office
100 000015 I 08/06/1999
200 000030 I 08/06/1999
300 000009 I 08/06/1999
400 000001 I 08/06/1999
500 000011 I 08/06/1999
600 000002 I 08/06/1999
700 000021 I 08/06/1999
800 000024 I 08/06/1999
900 000004 I 08/06/1999
1000 000006 I 08/06/1999
09/06/1999 000004 7707 What to do after he arrives
100 000022 I 08/06/1999
200 000008 I 08/06/1999
300 000018 I 08/06/1999
400 000019 I 08/06/1999
8101 Penrose,Steven
07/06/1999 000006 8229 New hire
1 000033 I 08/06/1999
2 000034 I 08/06/1999
3 000035 I 08/06/1999
4 000036 I 08/06/1999
5 000037 I 08/06/1999
6 000038 I 08/06/1999
7 000039 I 08/06/1999
8 000040 I 08/06/1999
9 000041 I 08/06/1999
10 000042 I 08/06/1999 When you create the File Layout definition, you can choose for each field whether to inherit a value from a higher level record field. If a value is inherited, it is written only once to the file, the first time it's encountered.
In the previous data example, there are three records: PERSONAL_DATA, EMPL_CHECKLIST, and EMPL_CHKLST_ITM. The field EMPLID is on all three records, but is written to the file only the first time a new value is encountered. So, the value for EMPLID is inherited by EMPL_CHECKLIST, and the value for EMPL_CHKLST_ITM is inherited from PERSONAL_DATA. The field CHECKLIST_DT is on EMPL_CHECKLIST and EMPL_CHKLST_ITM. However, the field value appears only once, in the parent record, and not in the child record.
If a record in the File Layout definition has a File Record ID, each line in the file referencing this record will be prefaced with this number. The File Record ID is not a field in the data itself. It is also referred to as the rowid. File Record IDs can be useful when the file being created refers to more than one record. File Record IDs can be used only with File Layout definitions that have a type of FIXED.
The following is sample file, produced with the same code, but with a File Record IDs added to all the records. 001 was added to the first level, 002 to the second, and 003 to the third.
001 8113 Frumman,Wolfgang
002 08/06/1999 000001 8219 Going to London office
003 100 000015 I 10/13/1999
003 200 000030 I 10/13/1999
003 300 000009 I 10/13/1999
003 400 000001 I 10/13/1999
003 500 000011 I 10/13/1999
003 600 000002 I 10/13/1999
003 700 000021 I 10/13/1999
003 800 000024 I 10/13/1999
003 900 000004 I 10/13/1999
003 1000 000006 I 10/13/1999
002 09/06/1999 000004 7707 What to do after he arrives
003 100 000022 I 10/13/1999
003 200 000008 I 10/13/1999
003 300 000018 I 10/13/1999
003 400 000019 I 10/13/1999
001 8101 Penrose,Steven
002 10/13/1999 000006 8229 New hire
003 1 000033 I 10/13/1999
003 2 000034 I 10/13/1999
003 3 000035 I 10/13/1999
003 4 000036 I 10/13/1999
003 5 000037 I 10/13/1999
003 6 000038 I 10/13/1999
003 7 000039 I 10/13/1999
003 8 000040 I 10/13/1999
003 9 000041 I 10/13/1999
003 10 000042 I 10/13/1999 The following program reads all the rowsets from a file and updates a work scroll with that data. The work scroll isn’t hidden on the page: it’s created in the Component buffer from existing records using CreateRowset. The structure of the work scroll and the file layout are identical: that is, they’re composed of two records, and the names of the records in the file layout are exactly the same as the names of the record definitions.
Local File &CHARTINPUT_F;
Local Rowset &INPUT_ROWSET, &TEMP_RS, &WORK_DATA;
Local Record &WRK_DATA;
&filename = "c:\temp\test.txt";
If FileExists(&filename, %FilePath_Absolute) Then
&CHARTINPUT_F = GetFile(&filename, "R", "A", %FilePath_Absolute);
Else
Exit;
End-If;
&CHARTINPUT_F.SetFileLayout(FileLayout.CHART_INFO);
/* Create rowset to be read into
NOTE that you have to start at LOWEST level of rowset */
&TEMP_RS = CreateRowset(RECORD.CHART_ITEM);
&WORK_DATA = CreateRowset(RECORD.CHART_DATA, &TEMP_RS);
&INPUT_ROWSET = CreateRowset(RECORD.CHART, &WORK_DATA);
While &INPUT_ROWSET <> Null
&INPUT_ROWSET = &CHARTINPUT_F.ReadRowset();
&INPUT_ROWSET.CopyTo(&WORK_DATA);
/* do processing -- Though file may contain more than one level zero
Component processor only allows one level zero at a time */
End-While;The following are considerations for when you use rowsets with files.
Although you can create a File Layout definition with more than four levels of hierarchy, a rowset created from Component buffer data can contain only four levels (level zero through 3). Any additional levels of data are ignored.
ReadRowset populates the rowset with one transaction from the file. (A transaction is considered to be one instance of level zero data contained in a file record, plus all of its subordinate data.) WriteRowset writes one transaction to a file.
Image: Application Engine example program
You can also use PeopleCode in an Application Engine program to either write to or read from files. This example isn't a proper Application Engine program: it contains the PeopleCode only for opening and reading from a file. However, it's included here as starting point for your own Application Engine programs.
Here is the Application Engine program:
Here is the PeopleCode in the step 1.
Local File &FILE;
Local Record &REC;
Local Rowset &FRS;
&FILE = GetFile("TEST.txt", "R");
&REC = CreateRecord(Record.QEPC_FILE_REC);
&SQL = CreateSQL("%Insert(:1)");
If Not &FILE.IsOpen Then
Error ("TEST: failed file open");
Else
If Not &FILE.SetFileLayout(FileLayout.QEPC_FILE_REC) Then
Error ("TEST: failed SetFilelayout");
Else
&FRS = &FILE.ReadRowset();
While &FRS <> Null
&FRS.GetRow(1).QEPC_FILE_REC.CopyFieldsTo(&REC);
&SQL.execute(&REC);
&FRS = &FILE.ReadRowset();
End-While;
End-If;
&FILE.Close();
End-If;The example Application Engine program reads the following CSV file:
"TEST2","1ST","01/01/1901",10
"TEST2","2ND","01/01/1902",20
"TEST2","3RD","01/01/1903",30
"TEST2","4TH","01/01/1904",40Note that the last field has no qualifier.
Image: QEPC_FILE_REC File Layout
The File Layout used to read this record has the following form:
Image: File Layout Definition Properties
The properties for the QEPC_FILE_REC File Layout are as follows:
Note that the Definition Qualifier is double-quotes ("), while the separator is a comma.
Remember, the last field didn't have a qualifier. To account for that, the field properties for this field must be edited, and a blank must be put in the Field Qualifier property.
Image: File layout Field Properties
The following image illustrates the way properties can be defined for the fields in the File layout.
