1 IFS Data Migration Excel Add-In User Manual for IFS Data Migration Excel Add-In Contents Figures... 2 IFS Data Migration Excel Add-In Overview User Interface Ribbon Migration Jobs Pane Field Mappings tab / Field Details tab Work sheet Migrating from data filled excel sheets Search Dialog Validating and Executing Validate Execute Settings Import Language Files System Effects Switch User System Effects Enable and Configure Debugging System Effects Set write chunk size to Excel (rows) General Rules Working In the Worksheet Session Language A Saved Excel Sheet using the Add-In Working with Multiple Migration Jobs... 11
2 User Manual for IFS Data Migration Excel Add-In (11) FIGURES Figure 1 - IFS Applications Ribbon... 3 Figure 2 - Add-in before connecting... 3 Figure 3 - Add-in after connecting No job selected... 3 Figure 4- Add-in after connecting Job selected... 4 Figure 5 - Migration Jobs pane... 4 Figure 6 - Field Mappings / Field Details tabs... 5 Figure 7 - Work sheet Excel table... 6 Figure 8 - Work Sheet Field Comments... 6 Figure 9 - Dropdown list for IID values Figure 10 - Query Dialog - Standard... 7 Figure 11 - Query Dialog - Advanced... 7
3 User Manual for IFS Data Migration Excel Add-In (11) IFS DATA MIGRATION EXCEL ADD-IN 1.1 OVERVIEW IFS Data Migration Excel Add-In is an extension to the FNDMIG functionality in IFS applications. A migration job with procedure type Excel Migration could be executed through MS Excel and an authorized end user could search / manipulate data in the specific logical units related to the given migration job. Once a migration job of procedure type Excel Migration is created, configured and assigned to an end user, the relevant migration job will be visible to the end user once he/she is logged on to IFS Applications from MS Excel through the IFS Data Migration Excel Add-in. Usually, it is an administrator or an advanced user who will create such a job. 2 USER INTERFACE 2.1 RIBBON Figure 1 - IFS Applications Ribbon Once installed, the add-in should be displayed in the Add-In section in Excel Options > Add-Ins as a COM Add-In with the name IFS Data Migration. When you open MS excel, the add-in should be displayed in the ribbon item IFS Applications along with any other IFS add-ins Figure 2 - Add-in before connecting Figure 3 - Add-in after connecting No job selected
4 User Manual for IFS Data Migration Excel Add-In (11) Figure 4- Add-in after connecting Job selected The Connect button (1), upon connecting to IFS Applications changes to Disconnect. Connect button will not be enabled if there is no work sheet open in MS Excel. The Help button (7) will open up user documentation for the Add-In. Search (2), Validate (3) and Execute (4) buttons will only get enabled once a user selects a migration job. For the query button to get enabled there is also an additional pre-requisite; which is that the selected job should be configured to support data extraction. This must be configured by the person creating the migration job. 2.2 MIGRATION JOBS PANE Figure 5 - Migration Jobs pane Once connected to IFS system, the Excel migration jobs assigned to the user will be displayed in the Migration jobs pane. There are two main nodes, Migration Jobs and the Recent Jobs. Migration jobs node contain the migration jobs under their respective job groups and the last five executed migration jobs will be listed in the Recent Jobs node.
5 User Manual for IFS Data Migration Excel Add-In (11) 2.3 FIELD MAPPINGS TAB / FIELD DETAILS TAB Figure 6 - Field Mappings / Field Details tabs Once a job is selected in the Migration Jobs pane, the relevant fields will be populated in the Field Mappings tab / Field Details tabs. It is possible to search a full text using the search (1), from whichever the tab that you have focused on. The connection between the database fields and the excel columns could be made in the Field Mappings tab (2). Only the fields with a source column value specified in the migration job will be displayed in this tab. However, there could be many other fields involved in a particular migration job where the administrator is not expecting any input from the user. These fields along with the ones having source columns will be listed in the Field Details tab (3) and this would carry more information about the fields as well. Upon selecting a migration job, the system will automatically map the Excel columns with the respective migration job columns (4). If required this can be changed. You can move data into a new column in order to write formulas. If you have done any column movements in the Excel worksheet, the corresponding Excel Column mappings shown in Field Mappings tab might be incorrect. When this happens, press the Rebuild Mappings button (5) to synchronize the changes. If you have done any complex changes to the columns, the mappings might need to be set manually.
6 User Manual for IFS Data Migration Excel Add-In (11) 2.4 WORK SHEET Figure 7 - Work sheet Excel table Figure 8 - Work Sheet Field Comments When a job is selected, the active work sheet will be populated with the relevant fields and will get automatically formatted as an excel table. The field headers will contain information related to the columns. The flags used are the same as in the migration jobs in IFS EE and are retrieved from the view definition in IFS/Foundation P = Parent Key. (Means that this column is retrieved from somewhere else) K = Key. (Means that this is a key column in this view) M = Mandatory. (Column must have a value) I = Insert allowed U = Update allowed The field headers will be followed by an * for the Key and Mandatory fields for the convenience. Note: If you try to update a non-updatable field, the changes will be ignored. If a particular field carries a default value, it will be displayed in the relevant field. If a particular field contains IID values, they will be listed as a dropdown when such a cell is selected. Figure 9 - Dropdown list for IID values. Date and number fields will automatically be formatted respectively and the rest of the fields will be formatted as text. If the user wants to use any excel formulas on them, the fields should be formatted as general.
7 User Manual for IFS Data Migration Excel Add-In (11) 3 MIGRATING FROM DATA FILLED EXCEL SHEETS If you need to use already available data from an excel sheet which has no previous connection to a job (i.e.: generated excel sheets), you can follow this procedure to avoid copying data back into a new excel sheet generated for the job. When you select a job from the migration job pane inside an excel sheet with data, the following dialog box will be shown. Select Load Job Details only, this loads job details into the sheet but skips loading the job related excel table in to the sheet. Therefore you will not get a formatted table like in other two options. Without that table, automatic column remapping is not possible and you have to manually do that in the Field Mappings tab. 4 SEARCH DIALOG Some jobs can be configured to search data from the database and populate the Excel sheet. When you select one of these jobs, the Search button will be enabled. When you click the button the Search dialog will be shown. 1 Figure 10 - Query Dialog - Standard Figure 11 - Query Dialog - Advanced 2
8 User Manual for IFS Data Migration Excel Add-In (11) The search dialog has two modes, Standard and Advanced. In the Standard mode you will see the available fields as defined in the Field Mappings tab (Figure 10). You can enter either a query for a specific value (e.g. AB12), or use wild card characters % and _(underscore) (e.g. AB% or A ). If a purticular field contains an IID value, then a drop down will be available showing the available values. If you need to write more advance queries you need to switch to the Advance tab (Figure 11). The upper text box (1) contains the basic select statement, then depending on it you may extented the query by typing in the SQL User Where text box (2).
9 User Manual for IFS Data Migration Excel Add-In (11) 5 VALIDATING AND EXECUTING 5.1 VALIDATE Before saving changes to the database, you may need to validate it first. To do this, select the rows you need to save the database and press Validate. Once the processing is done, the outcome will be shown in the Result Field. Validating runs the checks in the destination logical unit(s). Data will not be saved when validating. Possible outputs Validation OK The validation process has done the possible validations for the data and did not find any errors. You may now proceed to saving these changes to the database. Error message There are errors in the data. Modify the data and try again. No Operation No operation took place because; conditions which were required to execute the Migration job were not met. E.x. The Migration job has only allowed to enter data for site 10. But you have tried to enter data to another site. 5.2 EXECUTE Selecting some rows and pressing execute will save the changes to the database. You may encounter additional errors when executing. Depending on the error you can change your data and try again. The result of the process will be shown in the Results column. Possible outputs Inserted Data was inserted to the database. Updated Existing data in the database was updated. In this process, if you try to update a nonupdateable filed, the changes will be ignored. Inserted/Updated Data was both inserted and updated. This happens when the Migration job saves data into two or more locations (logical units). In one location the data was inserted and in the other it was updated. Processed The operation performed was not an insert or updated operation. Rather it was a call to a method. This method could have inserted, updated or removed data. But the end result was that the method was executed successfully. Error message There were errors in the data. Modify the data and try again. No Operation No operation took place because; conditions which were required to execute the Migration job were not met. Object does not exist You were trying to update an object that does not exist. Check if the key values are correct
10 User Manual for IFS Data Migration Excel Add-In (11) 6 SETTINGS In the settings dialog box, the supported languages will be displayed in the Available Languages group box. These are the only languages that an end user can select while logging on to IFS Applications through the add-in. This dialog box can be used to; Import language files. Switch user. Enable and configure debugging. Set write chunk size to Excel (rows) 6.1 IMPORT LANGUAGE FILES. Click Import SYSTEM EFFECTS Browse window will be opened. Once a correct XML file is opened, the relevant language will be supported by the add-in from the next log-on onwards. Otherwise, a message would be returned indicating that it is an invalid file. 6.2 SWITCH USER Click Switch User (restart). Switch user action requires a restart of the add-in SYSTEM EFFECTS This will bring the end user a fresh log-on dialog box despite the fact the passwords have been saved. 6.3 ENABLE AND CONFIGURE DEBUGGING This option may not be available if you are not granted the DEBUGGER system privilege for this to work. Click the check box to enable debugging. Set the path to save the log file SYSTEM EFFECTS System will create a debug log file on the destination. All debug information will be appended to one log file. If you need to debug to a new file, un-tick and re-tick Enable Trace check box. 6.4 SET WRITE CHUNK SIZE TO EXCEL (ROWS) This can be used to set the no of rows (chunk) that will be written to excel sheet when populating data in Migration Jobs. Decreasing the chuck size will lead to more writes to excel, which will decrease performance when populating large amount of data. Changes to this field will require restarting of excel for the change to take effect.
11 User Manual for IFS Data Migration Excel Add-In (11) 7 GENERAL RULES 7.1 WORKING IN THE WORKSHEET Currently when you are working in an Excel worksheet, make sure that the column headings (See Figure 7 - Work sheet Excel table) are in the first row. You should not remove column headers, nor edit the column header comments. Always make sure before updating data, that your changes are based upon the latest data queried to excel from the database. 7.2 SESSION LANGUAGE Language cannot be changed in the middle of a session. 7.3 A SAVED EXCEL SHEET USING THE ADD-IN If the user opens an excel file which was saved through the add-in while being connected to IFS Applications; Exception: The last migration job would automatically be selected in the jobs pane. Excel column mappings would be the same as it were by the time of disconnecting in the previous session of the file. If a particular user does not have access to a particular migration job that is related to a work sheet in the work book, the system will not load that migration job in the navigator. 7.4 WORKING WITH MULTIPLE MIGRATION JOBS As an end user, if you want to work with multiple migration jobs, you can achieve this with different work sheets per migration job. When you select different migration jobs, they will be loaded to different work sheets as you opted. Once you have such a set of work sheets, when you navigate between those work sheets, the relevant migration jobs will be loaded / highlighted accordingly in the migration jobs pane as well as in the field Mappings / Field Details tabs.
