Varo for Excel

1 Introduction

The release of 295 SP5 brings new Microsoft Excel functionality to FLM allowing a spreadsheet to start single form processes or the mass generation of forms and data.

These are normal Microsoft Excel spreadsheets that can be populated with data from the SAP back-end utilizing built in FLM reports.  In the spreadsheet a single column is used for each FLM form field and the spreadsheet output respects all the normal FLM functionality such as prepopulation user-exits, F4 user-exits etc. Once a spreadsheet has been filled in, it can be uploaded back into SAP and one or more FLM forms will be created with the data entered into the spreadsheet. These then become normal FLM forms and can continue through the workflow as PDF and/or HTML forms, and can be posted to the SAP back-end.

2 Creating Excel Upload Templates

2.1    Getting Started

‘Excel Upload Templates’ can be created via the ‘Generate Excel Upload Template’ transaction:


 
Figure 1.1 – The ‘Generate Excel Upload Template’ transaction

This will take users to the selection screen displayed in figure 1.2. The first four fields are the standard FLM fields and will determine the ‘Excel form’ which will be created. Any standard form type (which may also have a PDF or an HTML template uploaded) may be selected, with the exception of form types which contain in their definition nested repeating subforms which are not supported. It is also worth mentioning that unlike PDF or HTML forms, the template option and version will not be determined via the relevant user-exits (which will not be run) but by the selection screen. Once all fields have been filled and the user hits ‘Execute’ (F8), an Excel spreadsheet will be created and saved on the local machine.

Figure 1.2 The ‘Generate Excel Upload Template’ selection screen

2.2    Form groups

‘FLM for Excel’ may be used for mass data upload. The Excel templates may have a number of form groups. These will be new rows in the spreadsheet with a ‘form group’ field in column A indicating the form group number as is shown in Figure 1.3.


Figure 1.3 Excel template with multiple form groups pre-populated
 
Once the spreadsheet is uploaded (assuming there are no validation or other errors), each form group will create a new FLM form  which can then continue through the workflow as a pdf or html form, can be posted etc. Users may create new form groups when filling in the form, or they may wish for the form groups to be created and pre-populated at the point when the spreadsheet is created as is the case with the spreadsheet in Figure 1.3. This may be achieved in two different ways indicated by the radiobutton in the selection screen (Figure 1.2):

1) The user may manually specify the number of form groups to be created by entering the number in the relevant field.

2) The number of form groups may be determined by the new ‘External Document user-exit’. This user-exit receives the freely defined ‘External Document Reference’ and returns a table of ‘External Document References’. Each of these corresponds to one form group and will be used as the im_document import parameter in the user-exits for each form group. (If the external document user exit is used but returns a blank table, the system defaults to one form group without an im_document). For example, the ‘parent document’ might be an organizational unit and the user-exit might fill the table of ‘child documents’ with the personnel numbers of all the employees belonging to that organizational unit. One form group will then be created for each employee and the personnel number will be available in the pre-population user-exits in order to retrieve information about that employee. It is worth noting however that for technical reasons, the F4 user-exit will only be run for the first form group and the dropdown entries  for all form groups will be specified by this run.

2.3    Field and Subform Descriptions

As shown in figure 1.3, the spreadsheet displays descriptions for the form subforms and fields in the top two rows. These can be maintained via the form wizard as shown in figures 1.4 and 1.5, or via the new ‘Maintain Field and Subform Captions for HTML and Excel Forms’ transaction as shown in figures 1.6 and 1.7. If no caption is maintained, the technical name of the subform or the field will be displayed in the spreadsheet.


Figure 1.4 Maintaining Subform Captions via the Form Wizard


Figure 1.5 Maintaining Field Captions via the Form Wizard


Figure 1.6 Maintaining Captions via the ‘Maintain Field and Subform Captions for HTML and Excel Forms’ transaction


Figure 1.7 Maintaining Captions via the ‘Maintain Field and Subform Captions for HTML and Excel Forms’ transaction  


2.4    Limitations

As mentioned in Section 2.2, nested repeating subforms (ie repeating subforms which have a repeating subform as a parent) are not supported for Excel forms. It is also recommended that radiobuttons are avoided. These can be included, but they will be just plain cells with no formatting and if a value is entered that is not one of the allowed options of the pdf or html form at the next variant (if there is one), this will cause a render error. Checkboxes are supported however, although they are displayed as dropdowns with an ‘on’ and ‘off’ value. On upload, the ‘on’ value will be translated to ‘X’ and the ‘off’ value to blank, so it is crucial that these are the ‘on’ and ‘off’ values of the checkboxes of the pdf or html form at the next variant (if there is one). Finally, in order to prevent the system from using up too much memory an upper limit has been set on the size of the spreadsheet that can be generated. This is calculated as the number of rows multiplied by the number of characters of the ‘longest’ row and cannot exceed 30,000,000. If a user attempts to generate a template which exceeds this limit, the system will produce an error message and processing will stop.
 

3    Filling in an Excel Template

The template shown in figure 1.3 has a number of form groups and fields pre-populated. This need not be the case however, as users may wish to create a template without any prepopulation which they can then fill in as shown in figure 3.1:


Figure 3.1 Excel Template without pre-population

In the above template, cells after column G and row 7 will be non-modifiable. FLM for Excel also respects the standard ‘field and subform attributes’ configuration (as well as the relevant user-exit) hence any fields can be set to ‘read only’ through the back end. Any number of form groups can be added by selecting a row, right clicking and then clicking ‘Insert’ providing that a form group is added to column A as in Figure 1.3. If on upload the system detects any values in fields which belong to a non-repeating section without a corresponding form group in the same row, it will throw an error message. Formating and validation are added to the cells in the ‘data entry area’ so that they accept text, numbers, dates, etc., according to their definition in the data schema. For dates, the UK MM/DD/YYYY formating is added, so that dates will always appear in that format after they have been entered. They must be entred in the format specified by the users’ ‘Regional settings’ however, or Excel will not be able to interpret the entrered value as a date and will throw a validation error.   

 

4    Uploading Excel Templates

4.1    The upload process

After the template has been filled in, it can be uploaded to the system via the ‘Upload Form Data from Excel’ transaction shown in figure


Figure 4.1 The ‘Upload Form Data from Excel’ transaction

This transaction will take users to the selection screen shown in figure 4.2. Here users can set the customer code, form type etc. and browse to the file they wish to upload. The ‘Test Mode Only’ checkbox allows users to run the upload in test mode where the system will check which form groups pass validation. Both the field and the form level validation user-exits will be executed.


Figure 4.2  The ‘Upload Form Data from Excel’ selection screen

Once the template has been uploaded (in live mode) the user will be presented with the ‘Submission Summary’ presented in figure 4.3. Here the user can see the form id created for each form group which uploaded successfully as well as the error (validation or general) for those that did not. On the top right corner, the user can see the tracking ID associated with this upload. This is a unique 10 digit code associated with the upload. The uploader will also receive an email with the above details. At this point the user may select to display the data of any of the form groups or to correct the form groups which uploaded in error by ‘Downloading Errors’. As shown in figure 4.4, this will create a new spreadsheet with only the forms that uploaded in error and the same tracking ID as the one associated with the original file will be associated with it. This process can occur any number of times in sequence and all new spreadsheets will have the same tracking ID as the original one.  


Figure 4.3 The Submission Summary


Figure 4.4 The ‘Error’ Spreadsheet

4.2    Limitations

In order to prevent the system from using up too much memory, some limitations have been imposed. Firstly, no single cell can hold more than 128 characters. If a spreadsheet which contains a cell with more than 128 characters is uploaded, only the first 128 characters of that call will be saved on the system. The rest will be ignored. Moreover, users can not upload spreadsheets with more than 1,000,000 cells containing data. If this is attempted, the system will produce an error message and stop processing.
 

5    Monitoring Uploads

5.1    The Excel Dashboard

Users can monitor their uploads via the ‘Excel Dashboard’ transaction shown in figure 5.1:


Figure 5.1 The Excel Dashboard

This will take users to the selection screen shown in figure 5.2. Here, after entering the tracking ID associated with the upload, users may go to the ‘Submission Summary’ for that upload, or they may download any errors relevant to it (see section 4.1).


Figure 5.2 The Excel Dashboard Selection screen

5.2    The Forms Dashboard

As has already been mentioned every form group that is successfully uploaded will create a new form which may (or may not) then continue through the workflow as a pdf or html form. Forms that have originated from Excel forms can be searched for in the dashboard based on the tracking ID of their parent spreadsheet. The first variant of such forms will be displayed as an Excel spreadsheet as shown in figure 5.3. Please note however that this is not the original spreadsheet that was uploaded – it is the data of one form group displayed in Excel format.


Figure 5.3 Displaying an Excel form in the dashboard

5.3    The Cleanup report

In order to facilitate downloading and re-uploading Excel forms that uploaded in error, data is written to two new tables: /FLM/EXCEL_DATA and /FLM/EXCEL_MESS. A new field called ‘Max Excel Life’ has been created in the ‘System Specific Settings’ transaction which controls how old (in days) the data in the above tables must be before it is deleted by the standard ‘Form Cleanup Report’. The cleanup report will delete all table entries which correspond to a specific tracking ID if the latest table entry corresponding to that tracking ID is older than the ‘Max Excel Life’. If no value is maintained in the ‘Max Excel Life’ field, the cleanup report will assume a default value of 45 days.