Spreadsheet Data Manager (SDMan)

The about SDMan is split into the following chapters:

• The About Spreadsheet Data Manager (SDMan), i.e., this chapter.
• Using SDMan
• Supported Activities
• Tips and Tricks

Data is often created and manipulated in third-party spreadsheet applications, such as Microsoft® Excel. The Spreadsheet Data Manager (SDMan) makes it possible to import data from such applications to IFS Applications. (By using output channels, you can export data from IFS Applications to applications such as Excel.)

Importing data using SDMan will keep data integrity as the same business logic is used as when updating the data base using other user interfaces such as IFS Applications for Windows or Web.

Using SDMan, you can enter or modify almost all data for the following items:

• Purchase parts, inventory parts, and part catalog parts
• Suppliers
• Supplier for parts
• Supplier agreement quotations
• Supplier agreements

With SDMan, you also can perform mass updates to data connected to parts, suppliers, supplier for parts, and supplier agreements. For example, you can:

• Create new parts, or modify existing parts.
• Create or modify suppliers.
• Import supplier agreements and supplier agreements quotations into IFS Applications.
• Change buyers, quality control (QC) analysts, and/or technical coordinators for parts.
• Update planning parameters.
• Update lead times for parts and suppliers for parts.
• Assign and reassign inventory part asset classes, ABC codes, and/or planners.
• Update supplier agreements information, add parts to supplier agreements, and modify supplier agreements.
• Perform many other data modification activities.

Using the IFS Application output channel feature in conjunction with SDMan provides a very efficient method for analyzing and performing batch updates to data for suppliers, parts, and supplier agreements. SDMan enables you to perform numerous administrative tasks for these types of items.

Note: SDMan is not intended to support batch update when installing or upgrading databases.

Using SDMan

The Using SDMan chapter is split into the following sections:

• Installing SDMan
• Logging on Prerequisites
• Logging on to SDMan
• Selecting Activity
• Entering the Data Range
• Mapping the Fields

Installing SDMan

SDMan is installed on your local computer by double-clicking the SpreadsheetDataManager.msi file, and then following the instructions within the installation program.

Logging on Prerequisites

You will log on to SDMan using your IFS Foundation1 Extended Server user account. However, when you log on to the IFS Applications Windows client, you use an IFS Foundation1 user account. Before you can use SDMan, you must match your IFS Foundation1 Extended Server user account and your IFS Foundation1 user account. To do this, log on to the Foundation1 Administration tool as the application owner and perform the following steps:

1. Click the Users folder.
2. In the Find User field, enter your username and click the arrow.

Note: Contact your system administrator if you need more information on logging on prerequisites.

It is now possible to log on to the SDMan tool with your Windows account, having the same privileges and data setup as when working with data in IFS Applications as Foundation1 user ALAIN.

Logging on to SDMan

You will access SDMan from within Excel. To open and log on to SDMan, follow these steps:

• Open Excel and open the spreadsheet containing the data you wish to import into IFS Applications. From the IFS Applications menu, click Spreadsheet Data Manager.
• Log on to SDMan using your IFS Foundation1 Extended Server user account. (You need to log on only once per Excel session.)

Selecting Activity

Select the radio button that match the activity to perform. In next step select type of activity, e.g., new or modify.

Entering the Data Range

Enter the data range that contain the data to be imported to IFS Applications. Either select the lines, or type the range value directly in the range field.

Mapping the Fields

The purpose of mapping columns is to instruct SDMan where to find (in which column) specific data, e.g., site information is found in column C.

It is required to map the key and mandatory fields. Mapping other fields are optional. It is recommended to also map the Result column. Columns that are not mapped will be ignored. The column order has no impact on the SDMan process.

Use the Generate button to let SDMan map the SDMan fields to title values in the first row of the spreadsheet. Columns that are not automatically mapped or columns that are not mapped correctly may be adjusted by mapping them manually. The Clear button will clear mappings for the current activity.

SDMan recalls the latest mapping.

Supported Activities

The Supported Activities chapter cover the following activities:

• Entering Purchase Part
• Modifying Purchase Part
• Entering Purchased Inventory Part
• Modifying Purchased Inventory Part
• Entering Inventory Part
• Modifying Inventory Part
• Entering Supplier for Purchase Part
• Modifying Supplier for Purchase Part
• Retrieve Price from Supplier for Purchase Part
• Entering Supplier
• Modifying Supplier
• Entering Agreement Quotation Data
• Entering Supplier Agreement, Included Parts
• Entering Supplier Agreement, Included Supplier Assortments

Entering Purchase Part Using SDMan

The enter purchase part activity will create new purchased part, and part catalog entries.

1. Open Excel and start SDMan.
2. Select the Purchase Part activity, click Next.
3. Select New and then click Next.
4. Click the Data Range field, then enter the data range by typing the range value directly in the field or select the columns (range) in the Excel sheet.
5. Map the Excel columns that contain the required data to enter new purchased parts. You can optionally map additional Excel columns of interest. Click the Generate button to automatically map the Excel columns. Alternatively click the field to map, then type the column value in the Select Placement for [Field] field (the cursor focus is automatically shifted to this field.)
6. Click Next and then click Finish.

Modifying Purchase Part Using SDMan

The modify purchase part activity will modify purchased parts.

1. Open Excel and start SDMan.
2. Select the Purchase Part activity, click Next.
3. Select Modify and then click Next.
4. Click the Data Range field, then enter the data range by typing the range value directly in the field or select the columns (range) in the Excel sheet.
5. Map the Excel columns that contain the required data to modify purchased parts. You can optionally map additional Excel columns of interest. Click the Generate button to automatically map the Excel columns. Alternatively click the field to map, then type the column value in the Select Placement for [Field] field (the cursor focus is automatically shifted to this field.)
6. Click Next and then click Finish.

Entering Purchased Inventory Part Using SDMan

The enter purchased inventory part activity will create new purchased inventory parts, and part catalog entries.

1. Open Excel and start SDMan.
2. Select the Purchased Inventory Part activity, click Next.
3. Select New and then click Next.
4. Click the Data Range field, then enter the data range by typing the range value directly in the field or select the columns (range) in the Excel sheet.
5. Map the Excel columns that contain the required data to enter new purchased inventory parts. You can optionally map additional Excel columns of interest. Click the Generate button to automatically map the Excel columns. Alternatively click the field to map, then type the column value in the Select Placement for [Field] field (the cursor focus is automatically shifted to this field.)
6. Click Next and then click Finish.

Note: You have the possibility to create a GTIN (Global Trade Item Number) for the part. If you want to create a GTIN, enter values for all the GTIN attributes (E.g. GTIN number, GTIN series, etc.). If you want to create a part without a GTIN, keep the GTIN attribute fields blank despite being key and mandatory fields. The key and mandatory fields related to GTIN act differently to other key and mandatory fields in SDMAN.

Modifying Purchased Inventory Part Using SDMan

The modify purchased inventory part activity will modify purchased inventory parts.

1. Open Excel and start SDMan.
2. Select the Purchased Inventory Part activity, click Next.
3. Select Modify and then click Next.
4. Click the Data Range field, then enter the data range by typing the range value directly in the field or select the columns (range) in the Excel sheet.
5. Map the Excel columns that contain the required data to modify purchased inventory parts. You can optionally map additional Excel columns of interest. Click the Generate button to automatically map the Excel columns. Alternatively click the field to map, then type the column value in the Select Placement for [Field] field (the cursor focus is automatically shifted to this field.)
6. Click Next and then click Finish.

Note: If you created a GTIN for the part, then it is possible for you to modify the GTIN attributes. If you created a normal part (without GTIN) then it is not possible to modify the GTIN attributes despite being key and mandatory fields. The key and mandatory fields related to GTIN act differently to other key and mandatory fields in SDMAN.

Entering Inventory Part Using SDMan

The enter inventory part activity will create new inventory part, and part catalog entries.

1. Open Excel and start SDMan.
2. Select the Inventory Part activity, click Next.
3. Select New and then click Next.
4. Click the Data Range field, then enter the data range by typing the range value directly in the field or select the columns (range) in the Excel sheet.
5. Map the Excel columns that contain the required data to enter new inventory parts. You can optionally map additional Excel columns of interest. Click the Generate button to automatically map the Excel columns. Alternatively click the field to map, then type the column value in the Select Placement for [Field] field (the cursor focus is automatically shifted to this field.)
6. Click Next and then click Finish.

Note: You have the possibility to create a GTIN (Global Trade Item Number) for the part. If you want to create a GTIN, enter values for all the GTIN attributes (E.g. GTIN number, GTIN series, etc.). If you want to create a part without a GTIN, keep the GTIN attribute fields blank despite being key and mandatory fields. The key and mandatory fields related to GTIN act differently to other key and mandatory fields in SDMAN.

Modifying Inventory Part Using SDMan

The modify inventory part activity will modify existing inventory parts.

1. Open Excel and start SDMan.
2. Select the Inventory Part activity, click Next.
3. Select Modify and then click Next.
4. Click the Data Range field, then enter the data range by typing the range value directly in the field or select the columns (range) in the Excel sheet.
5. Map the Excel columns that contain the required data to modify inventory parts. You can optionally map additional Excel columns of interest. Click the Generate button to automatically map the Excel columns. Alternatively click the field to map, then type the column value in the Select Placement for [Field] field (the cursor focus is automatically shifted to this field.)
6. Click Next and then click Finish.

Note: If you created a GTIN for the part, then it is possible for you to modify the GTIN attributes. If you created a normal part (without GTIN) then it is not possible to modify the GTIN attributes despite being key and mandatory fields. The key and mandatory fields related to GTIN act differently to other key and mandatory fields in SDMAN.

Entering Supplier for Purchase Part Using SDMan

The enter supplier for purchase part activity will create the connection between supplier and purchase part. Both supplier and purchase part has to exist.

1. Open Excel and start SDMan.
2. Select the Supplier for Purchase Part activity, click Next.
3. Select New and then click Next.
4. Click the Data Range field, then enter the data range by typing the range value directly in the field or select the columns (range) in the Excel sheet.
5. Map the Excel columns that contain the required data to enter new supplier for purchase part connections. You can optionally map additional Excel columns of interest. Click the Generate button to automatically map the Excel columns. Alternatively click the field to map, then type the column value in the Select Placement for [Field] field (the cursor focus is automatically shifted to this field.)
6. Click Next and then click Finish.

Modifying Supplier for Purchase Part Using SDMan

The modify supplier for purchase part activity will modify the connection between supplier and purchase part.

1. Open Excel and start SDMan.
2. Select the Supplier for Purchase Part activity, click Next.
3. Select Modify and then click Next.
4. Click the Data Range field, then enter the data range by typing the range value directly in the field or select the columns (range) in the Excel sheet.
5. Map the Excel columns that contain the required data to modify supplier for purchase part connections. You can optionally map additional Excel columns of interest. Click the Generate button to automatically map the Excel columns. Alternatively click the field to map, then type the column value in the Select Placement for [Field] field (the cursor focus is automatically shifted to this field.)
6. Click Next and then click Finish.

Retrieve Price from Supplier for Purchase Part Using SDMan

The retrieve price from supplier for purchase part activity will fetch the part price from supplier for purchase part.

1. Open Excel and start SDMan.
2. Select the Supplier for Purchase Part activity, click Next.
3. Select Retrieve price and then click Next.
4. Click the Data Range field, then enter the data range by typing the range value directly in the field or select the columns (range) in the Excel sheet.
5. Map the Excel columns that contain the required data to retrieve price from supplier for purchase part. Click the Generate button to automatically map the Excel columns. Alternatively click the field to map, then type the column value in the Select Placement for [Field] field (the cursor focus is automatically shifted to this field.)
6. Click Next and then click Finish.

Entering Supplier Using SDMan

The enter supplier activity will create new suppliers. It is possible to create a supplier that is useable throughout the process from creating purchase order to payment of purchase.

1. Open Excel and start SDMan.
2. Select the Supplier activity, click Next.
3. Select New and then click Next.
4. Click the Data Range field, then enter the data range by typing the range value directly in the field or select the columns (range) in the Excel sheet.
5. Map the Excel columns that contain the required data to enter new supplier. You can optionally map additional Excel columns of interest. Click the Generate button to automatically map the Excel columns. Alternatively click the field to map, then type the column value in the Select Placement for [Field] field (the cursor focus is automatically shifted to this field.)
6. Click Next and then click Finish.

Modifying Supplier Using SDMan

The modify supplier activity will modify attributes for existing supplier. It is possible to modify combinations of almost all attributes connected to a supplier.

1. Open Excel and start SDMan.
2. Select the Supplier activity, click Next.
3. Select Modify and then click Next.
4. Click the Data Range field, then enter the data range by typing the range value directly in the field or select the columns (range) in the Excel sheet.
5. Map the Excel columns that contain the required data to modify supplier. You can optionally map additional Excel columns of interest. Click the Generate button to automatically map the Excel columns. Alternatively click the field to map, then type the column value in the Select Placement for [Field] field (the cursor focus is automatically shifted to this field.)
6. Click Next and then click Finish.

Entering Supplier Agreement Quotation Data Using SDMan

The enter supplier agreement quotation data activity will enter supplier agreement quotations into IFS Applications. Part quotations and assortment quotations are entered using separate activities.

1. Open Excel and start SDMan.
2. Select the Supplier Agreement Quotation activity, click Next.
3. Select Modify Part Lines or Modify Assortment Lines and then click Next.
4. Click the Data Range field, then enter the data range by typing the range value directly in the field or select the columns (range) in the Excel sheet.
5. Map all the columns to modify part or assortment supplier agreement lines. Click the Generate button to automatically map the Excel columns. Alternatively click the field to map, then type the column value in the Select Placement for [Field] field (the cursor focus is automatically shifted to this field.)
6. Click Next and then click Finish.

Entering Included Parts Using SDMan

The enter included parts using SDMan activity will enter supplier agreement parts into IFS Applications.

1. Open Excel and start SDMan.
2. Select the Supplier Agreement activity, click Next.
3. Select Part and then click Next.
4. Click New to enter a new supplier agreement part line, click Modify to change an existing supplier agreement part line, click Retrieve Supplier Agreemnet price to fetch supplier agreement part price, or click Retrieve current quantity per part to fetch purchased quantity per part. Click Next.
5. Click the Data Range field, then enter the data range by typing the range value directly in the field or select the columns (range) in the Excel sheet.
6. Map the Excel columns that contain the required data to perform the selected activity. You can optionally map additional Excel columns of interest. Click the Generate button to automatically map the Excel columns. Alternatively click the field to map, then type the column value in the Select Placement for [Field] field (the cursor focus is automatically shifted to this field.)
7. Click Next and then click Finish.

Entering Included Supplier Assortments Using SDMan

The enter included supplier assortments using SDMan activity will enter supplier agreement assortments into IFS Applications.

1. Open Excel and start SDMan.
2. Select the Supplier Agreement activity, click Next.
3. Select Assortment and then click Next.
4. Click New to enter a new supplier agreement assortment line, click Modify to change an existing supplier agreement assortment line, click Retrieve discount per assortment to fetch supplier agreement assortment discount, or click Retrieve current amount per assortment to fetch purchased amount per assortment. Click Next.
5. Click the Data Range field, then enter the data range by typing the range value directly in the field or select the columns (range) in the Excel sheet.
6. Map the Excel columns that contain the required data to perform the selected activity. You can optionally map additional Excel columns of interest. Click the Generate button to automatically map the Excel columns. Alternatively click the field to map, then type the column value in the Select Placement for [Field] field (the cursor focus is automatically shifted to this field.)
7. Click Next and then click Finish.

Tips and Tricks

Boolean Values

When you enter TRUE and FALSE values into an Excel cell, Excel might recognize these as Boolean values. If Excel treats those values as Boolean, you will then receive an error when you use SDMan. To avoid this, make sure the Excel cell has a text format.
A method to force Excel to treat the cell as a text cell is to first enter any text in the cell, e.g., Donald Duck, and then press Enter. Verify the cell property by using the Excel menu path Format/Cells/Number tab.. Change the cell property to Text if necessary. Finally change the content of the cell to TRUE (or FALSE).

Note: Field values are language sensitive! If you are not sure what values to use, open an appropriate overview window, e.g., Suppliers Analysis, or Part Information Analysis then select a supplier or part carrying the value you look for.

Base Windows

The field order and field titles in SDMan are defined for use with specific windows in the IFS Applications for Windows client. This means that the Generate button in SDMan will work the best using the proposed windows. Therefore it is also a good idea to use the proposed windows as base for any customized window.

Part activities: The SDMan field order and field titles for the part activities (Purchase Part, Inventory Part, and Part Catalog) are defined for use with the Part Information Analysis window.

Supplier for Purchase Part activities: The SDMan field order and field titles for the supplier for purchase part activities are defined for use with the Supplier for Purchase Parts window.

Supplier activities: The SDMan field order and field titles for the supplier activities are defined for use with the Suppliers Analysis window.

Supplier Agreement Quotation activities: The SDMan field order and field titles for the supplier agreements quotation activities are defined for use with the  Agreement Quotation Lines window.

Supplier Agreement activities: The SDMan field order and field titles for the supplier agreement activities are defined for use with the Supplier Agreement Lines window.

Customizing Windows

When you use IFS Applications output channels and SDMan together to enter or modify large quantities of data, it is a good idea to create a customized overview window for the specific task. By using tailor made queries, hiding columns that are of no interest, and rearranging the remaining columns in a logical order, the exporting and importing process will be easier for you.

To create a customized window for a specific task, do the following;

1. In the IFS Navigator, right-click an appropriate overview window, e.g., Part Information Analysis, or Suppliers Analysis, and then click Copy.
2. Right-click the folder where you want to paste the window, e.g., the same folder as the original window, then click Paste.
3. Right click the copied window, then click Properties. Change the Name to something that is appropriate for your purpose.
4. Select the Own Process check box. Click OK.

Note: The Own Process check box is the field that makes the copied window unique, allowing you to save the column order and save queries that are connected to only this window.

4. Open the new window and customize it by hiding columns of no interest, changing the column order, and creating and saving smart queries.
5. Enable output channels and export the selection to Excel.
6. Manipulate the data in MS Excel, and then use SDMan to import the data back into IFS Applications.

Excel Sample File

When using the Excel Sample File choose to save the file on disk first and then open the file to ensure that the Excel Sample File is opened in Excel.