Tip 036: Introduction to File Layouts

PeopleTools | Defining a file layout and then accessing it in PeopleCode

A. What is a File Layout?

As the name suggests, a file layout is used for defining the structure of an external file. This can be a file that you intend to read into the system, or it could be a file that will be created as part of a data export routine. Once you set up the file layout object in App Designer, you can then go ahead and access the file layout as part of a PeopleCode programme. This post will cover both the creation and the use of file layouts.

B. Defining the File Layout

Firstly, to set up the file layout, go into App Designer, and select File -> New, followed by ‘File Layout’. Then open up the properties for the file layout and go into the ‘Use’ tab. Set these fields depending on what your requirement is. The file layout format can be set to ‘FIXED’, ‘CSV’ or ‘XML’. The format you select will determine the other options that open up in the ‘Use’ tab:

The ‘Excel Format’ flag should be ticked if you suspect the CSV is likely to be opened in Excel (which is probably the case around 99% of the time).

After you’ve exited the properties by clicking ‘OK’, select Insert -> Record from the main App Designer menu. Then select the record that you wish to use for the structure of the file layout. I have used the ‘PERSONAL_PHONE table in the example below:

For the purpose of keeping this example simple, I haven’t made any changes to the above layout. However, you could add new fields by right-mouse-clicking on the file layout. Select ‘Insert Database Field…’ and choose the appropriate field. To delete a field, highlight the field, right-mouse-click and select ‘Delete’. The field order can be changed by simply clicking and dragging a field from one position to another within the record.

Also at the point, you can continue to insert other database records, even setting up a parent-child relationship between the records by using the arrow buttons in the toolbar. In the example below, both records have been inserted at ‘Level 1’:

C. Using the File Layout in PeopleCode

Once the file layout has been created, you can then go ahead and create a CSV file based on the layout. The following example uses a parent-child relationship to select and then output the data:

/* Create CSV file in /finance directory */
&file_path = GetCwd() | "/finance/CHEQUES.csv";
&ChequeFile = GetFile(&file_path, "w", "a", %FilePath_Absolute);
   
/* Set the file layout */
&ChequeFile.SetFileLayout(FileLayout.FILE_LAYOUT_NAME);
   
/* Populate the header rowset within the file layout */
&ChequeHeader_RS = &ChequeFile.CreateRowset();
&ChequeHeader_RS.Fill("where OPRID = :1", &oprid);
   
/* For each header record, set the line rowset within the file layout */
For &i = 1 To &ChequeHeader_RS.ActiveRowCount
   &ChequeHeader_Row = &ChequeHeader_RS.GetRow(&i);
   &ChequeHeader_Rec = &ChequeHeader_Row.REC_NAME_PARENT;
      
   &ChequeLine_RS = &ChequeHeader_Row.GetRowset(Scroll.REC_NAME_CHILD);
   &ChequeLine_RS.Fill("where OPRID = :1 and EMPLID = :2 and SEQNUM = :3",
                       &oprid, &ChequeHeader_Rec.EMPLID.Value,
                       &ChequeHeader_Rec.SEQNUM.Value);
End-For;
   
/* Write the entire rowset to the CSV File */
&ChequeFile.WriteRowset(&ChequeHeader_RS, True);
   
&ChequeFile.Close();

There are numerous other aspects of file layout we can get into, such as the options available when specifying a ‘fixed width’ file. However, as this tip is designed more an intro to file layouts, we’ll stick to a simpler CSV example for now.

<< Previous | Next >>