The Edit with Excel feature in iTopia works a little differently with the P/O Purchasing Information (ibis_popi.po13) table than with all other tables in iTopia. This topic explains the reasons for the different behaviour and walks you through examples using Excel to create new supplier/product records.

Overview

As we know, the Purchasing Information table is used to link a supplier to a product for the purposes of storing purchasing data for the specific supplier-product combination.

When you create new purchasing records in Purchasing Information, the system is designed to prompt you for information regarding the supplier that will be assigned to the product. Specifically, there are 3 messages that the system may display when you are saving a new product/supplier record in Purchasing Information using Save or Submit:

1. "Product ABC is not set up for this supplier. Do you wish to copy the pack information from primary supplier SUPP1?"

2. "Supplier SUPP1 with product ABC is assigned as the primary supplier. Do you want to make the current supplier and product the primary record?"

3. "Do you wish to perform an update even though the product code is flagged for deletion?"

When you create new product/supplier records using the Edit with Excel feature, there is no interaction between the system and the user and these messages/prompts are not answered. As a result, new product/supplier records must be handled differently when they are created in Edit with Excel.

A flag has been added to Company Control Initialization (ibis_cc00/Purchasing) in order to accommodate the Edit with Excel functionality and Purchasing Information:

• The Copy Pack Info when Using Edit with Excel check box allows you to identify whether pack information from Product Pack Information (ibis_im23) is to be copied or not. When this check box is selected, the pack information from the current supplier/product record flagged as the primary supplier will be copied to a new supplier for the same product in Edit with Excel.

Creating Records in Edit with Excel

In this section, we will walk through 4 examples in which new purchasing information records are created in Excel.

1. Copy Pack Info/New Record to be Primary Supplier:

   In CC00/PO, the 'Copy Pack Info when Using Edit with Excel' flag is selected.

   In this example, the current supplier is flagged as the primary supplier for the product and the product/supplier has a GTIN in the Pack Information (ibis_im23) view.

   In Excel we must save the record twice when the new product/supplier record is to be the primary supplier record. Saving the record two times is equivalent to creating a supplier/product record and then updating that record to be the primary supplier record in P/O Purchasing Information.

   Here is the record we are making a copy of from P/O Purchasing Information:

• The supplier is BIG (which is flagged as the primary supplier) and the product is PENS.

Using the Edit with Excel option, the record is opened in Excel. Here is an example of the record in Excel:

In Excel, we will update the supplier column to create the new supplier/product record and we must change the primary flag to No for the new supplier because a primary supplier record already exists. (This is part one of our two saves.)
• We will change the Supplier field to A1 and we must manually change the Primary/Primary Code to No. When you change the Primary Code from Y to N, the Primary field updates accordingly. Here is an example of the updated record:

Once the record is modified, click Save found on the TECSYS iTopia folder in Excel. Here is an example of the new supplier/product record in Excel:

At this point, we can change the Primary Code on the Excel spreadsheet to Y and then save the record again by clicking Save. Here is an example of the updated record:

The new supplier/product record (A1/PENS) now exists in iTopia as the primary supplier record and the original record (BIG/PENS) is no longer flagged as the primary supplier. Here is an example of the two records in P/O Purchasing Information:

In addition, the pack information from the GTIN is copied to the new record correctly. Here is an example from Product Pack Information for the original supplier/product (BIG/PENS) record and the new supplier/product (A1/PENS) record:

2. Pack Info to be Copied/New Record NOT Primary Supplier:

   In Company Control Initialiation (ibis_cc00/PO), the 'Copy Pack Info when Using Edit with Excel' flag is selected.

   In this example, the current supplier is flagged as the primary supplier for the product and the product/supplier has a GTIN in the Pack Information (ibis_im23) table.

   Here is an example of the record we're working with from P/O Purchasing Information:

• The supplier is A1 (which is flagged as the primary supplier) and the product is PENS.

Using the Edit with Excel option, the record is opened in Excel. Here is an example of the record in Excel:

In Excel, we will update the supplier column to create the new supplier/product record and we must change the primary flag to No for the new supplier because a primary supplier record already exists.
• The new supplier is ABCCO, and we must manually change the Primary/Primary Code to No. When you change the Primary Code from Y to N, the primary field updates accordingly. Here is an example of the updated record:

Once the record is modified, click Save found on the TECSYS iTopia folder in Excel. Here is an example of the new supplier/product record in Excel:

The new supplier/product record (ABCCO/PENS) now exists in iTopia as the secondary supplier record and the original record (A1/PENS) remains the primary supplier. Here is an example of the two records in Purchasing Information:

And the pack information from the GTIN is copied to the new record correctly. Here is an example from Product Pack Info for the original supplier/product (A1/PENS) record and the new supplier/product (ABCCO/PENS) record:

3. Do Not Copy Pack Info/New Record to be Primary Supplier:

   The 'Copy Pack Info when Using Edit with Excel' in CC00/PO is not selected.

   In this example, the current supplier is flagged as the primary supplier for the product and the product/supplier has a GTIN in the Pack Information (ibis_im23) table.

   In Excel we must save the record twice when the new product/supplier record is to be the primary supplier record. Saving the record two times is equivalent to creating a supplier/product record and then updating that record to be the primary supplier record in P/O Purchasing Information.

   Here is the record we are making a copy of from P/O Purchasing Information:

• The supplier A1 (which is flagged as the primary supplier) and the product is PENS.

Using the Edit with Excel option, the record is opened in Excel. Here is an example of the record in Excel:

In Excel, we will update the supplier column to create the new supplier/product record and we must change the primary flag to No for the new supplier because a primary supplier record already exists. (This is part one of our two saves).
• We will change the Supplier field to B1 and we must manually change the Primary/Primary Code to No. When you change the Primary Code from Y to N, the Primary field updates accordingly. Here is an example of the updated record:

Once the record is modified, click Save found on the TECSYS iTopia folder in Excel. Here is an example of the new supplier/product record in Excel:

At this point, we can change the Primary Code on the Excel spreadsheet to Y and then save the record again by clicking Save. Here is an example of the updated record:

The new supplier/product record (B1/PENS) now exists in iTopia as the primary supplier record and the original record (A1/PENS) is no longer flagged as the primary supplier. Here is an example of the two records in P/O Purchasing Information:

The GTIN information is not copied to the new record because the flag in CC00 is not selected.

4. Copying Products Flagged for Deletion:

   You cannot create or update records using Edit with Excel for suppliers with a product that is flagged for deletion. The message below displays on the Excel Spreadsheet.

If you still want to create/update the record, you must do so using the P/O Purchasing Information (ibis_popi.po13) view.