Edit with Excel – More Details & Examples

There are 3 options available on the search results page of views that allow you to use the Edit with Excel functionality:

1. The Edit with Excel and Edit All Records With Excel options are available from 'Use Selected Records as Criteria for Action' icon in the header section of the search results.

2. The Edit with Excel option is available from the 'Use Record as Criteria for Action' icon to the left of each record on the search results page.

3. The Create Records with Excel option is available from the More Actions button at the bottom right of the search results page.

When you use the Edit with Excel feature to edit records in iTopia, the Edit with Excel--Webpage Dialog window opens. This window is the link between the Excel spreadsheet and iTopia.

• The Dialog window displays the following message: "Loading the records into a spreadsheet…".

• Once the load is complete, an Excel spreadsheet opens that contains the selected records and the following message displays: "The records have been transferred to the spreadsheet. Save/Close". You can now make the necessary edits on the spreadsheet.

• The Save button on the dialogue window can be used instead of using the Save on the Excel spreadsheet. Note: The Save button on the Excel spreadsheet is only active if you have setup Excel and your browser with the proper parameters. For more information on configuring Excel and your browser, see the Compatibility Requirements topic.

• If you use the Save button on the Webpage Dialog window, all edits on the spreadsheet are saved. A number of messages display:
  1. The 'Updating Records...' message displays first.

  2. When all of the records are updated, the following message displays: "Number of records Updated: XXX". If there are any records that did not update, the following message also displays: "Number of Invalid Records: XXX". Note: If there are invalid records, you must check the Excel spreadsheet to determine why the record(s) were deemed invalid.

• When you click on the Close button, one of the following two actions occurs:
  • If you have not updated the spreadsheet by clicking Save on the Webpage Dialog window or on the Excel spreadsheet, the following message displays: "Are you sure you want to close the window before saving the records? OK/Cancel". Select Cancel to close the message and leave the dialog window and link to the spreadsheet open, or select OK to close the message and the dialog box thereby breaking the link to the spreadsheet. In this case, the spreadsheet remains open, however the Save button is removed and any changes made to the spreadsheet cannot be saved back into the iTopia view. If you have inadvertently broken the link between Excel and iTopia, please continue reading. Your situation can be corrected!

  • If you have saved the changes on the spreadsheet, the changes have been transferred back to the iTopia view. The Webpage Dialog closes and the link between the spreadsheet and iTopia is broken. You will notice that the spreadsheet remains open, but the Save button is removed. If you make any further modifications on the open spreadsheet, you cannot save these changes back to the view.

    Note: Closing the Webpage Dialog window by clicking the X in the upper right corner closes the dialog window. No message will display even if you have modified the Excel spreadsheet. The Save button on the spreadsheet is NOT removed but the link between iTopia and the spreadsheet is broken and you cannot save the changes to iTopia.

• If you close the Webpage Dialog window before saving the spreadsheet modifications, and the spread sheet is still open, follow these steps to save your work from the current spread sheet into iTopia:
  • Save the spreadsheet.

  • In the iTopia view, select at least one record from the view that was originally linked to Excel.

  • Open the link between iTopia and Excel again by selecting 'Edit with Excel' or 'Edit all Records With Excel'.

  • Once the link is established and a new spreadsheet is open:
    • Copy the records from the saved spreadsheet to the new spreadsheet. Note: only select the columns and rows that you want saved back to iTopia. Do not copy the header row or any data in column A to the new spreadsheet.

    • Click the Save button (either on the spreadsheet or the Webpage Dialog window) to save the records back to the iTopia view.

The spread sheet that opens in Excel has a number of unique characteristics:

• The cells are colour coded, for example:
  • Dark blue – business key columns are coloured dark blue and cannot be modified. From the diagram below, business key columns include Company Number, Customer Code and iBIS Client.

  • Aqua – columns pertaining to relationships between tables are coloured aqua and cannot be modified. From the diagram below, the Currency field was obtained from the AR Bank Codes (AR02) view, but is related to the base table.

  • Black – columns that are maintainable, or that can be edited. From the example below, the Customer Name column is a maintainable column.

• Comments are located on the required columns or columns with Domain values. Comments are denoted by a red triangle in the right corner of the cell. From the example below, the Customer Code column has a comment 'triangle' (as do the Company Number and iBIS Client columns).

• The Save button updates information from the Excel spread sheet back into iTopia. The Save button is an optional feature. For more information on setting up the Save feature in Excel as well as your Internet Explorer to enable the Export to Excel feature, please see the Compatibility Requirements document. The following examples utilize the Save button.

  Note: When you execute the Edit with Excel feature, you may encounter some Internet Explorer messages. If this occurs, just select 'Yes' in response to any messages to continue the operation. An 'Edit with Excel – Webpage Dialog' panel will be displayed indicating that the records are being loaded. Once the records are loaded into the Excel spreadsheet, the message in the dialog box indicates the load is complete. There are also Save and Close actions buttons on this dialog box. Selecting the Save button in the dialog box saves the changes you have made on the spreadsheet in the same manor as the Save action button on the spreadsheet does. Selecting the Close action button invokes another message panel that requires you to select either the OK or Cancel button. Selecting OK disables the Save action button on the Excel spreadsheet and any modifications to the spreadsheet cannot be imported back into iTopia. Selecting Cancel aborts the Close action. The dialog box also will indicate the number of records that have been successfully updated when the Save action button is selected either from the Excel spreadsheet or from this dialog box.

  

 
Example 1: Creating a New Record in Excel

In this example, we will use an existing customer record from the Customer Master (SU13) view in iTopia to create a new customer record using Edit with Excel. We will be using the Customer Master (SU13)-Quick Entry view which includes all of the required fields for the Customer Master. Note: You must have all of the required fields in the Excel spread sheet in order to ‘save’ a new record back into iTopia.

1. In iTopia, we will open the Customer Master (SU13)-Quick Entry view and we will locate an existing record that has fields similar to the new customer record we wish to create. Here is a screen capture of the customer record we will be using:

2. Using the 'Use Record as Criteria for Action' icon , select the Edit with Excel option.
   • If any Internet Explorer messages display, click Yes to continue.

   • When the record is loaded into the spread sheet, the following message displays: "The records have been transferred to the spread sheet. Save/Close".

     Note: The Save/Close buttons are used when the Save button in Excel has not been enabled. For more information on enabling the Save button, see the Compatibility Requirements document.

     In our example, the Save button in Excel is enabled, therefore we will leave this message window open and go directly to the spread sheet that has automatically opened.

   On the Excel spread sheet, the selected record is available for copy/edit. The following screen shot displays the record to be copied:

• Note: You will see the iBIS Client column in the spread sheet. This column is not displayed in iTopia, but the information (in this example, WINDY611) is part of the "key" that defines each record in the SUCU (or SU13) table. This column is for display purposes only and cannot be modified. If you make changes to this field, once the Save action is invoked, this field reverts back to its original value.

• Note: You cannot delete records using Edit with Excel The delte action (if available for the selected view) must be executed from either the 'Use Records as Criteria for Action' or 'Use Selected Records as Criteria for Action icons or the Delete action button on the details page of a view.

3. On the spread sheet, there are two methods that can be used to create a new record. You can override the information that was exported to the Excel spreadsheet or you can copy the information from the exported record to a new row and edit the information on this row.

   In this example, we will use the copy method: copy the existing record and paste it in the line below:

• Once the record is copied, make all of the changes required. In this case, we will change the following fields:
  • Customer Code – changed from EAST003 to EAST007
  • Customer Name – changed from Smart Shoppers Inc. to Best Shopper
  • AR Terms – changed from 30/60 to 2%10

• Note: The Customer Code field in the dark blue text is a "business key column ... and cannot be modified". In this case, we are creating a new record. This is not the same as modifying an existing record. When you are creating a new record, you can change "business key column" text.

• When the changes are complete, tab out of the last field edited and then click Save. The Status of the original record that was imported is Updated (even though no changes were made to the record) and the Status of the new record is Created. The Edit with Excel-Webpage Diaglog box indicates that one record was updated. Here is what the Excel spread sheet looks like after we click Save:

The next 2 bullets provides some additional information about Edit with Excel and is not part of our example:
• If any of the required fields are missing from the spread sheet, the record will be flagged as Invalid and the Invalid Comments, found by clicking on the comment triangle, will identify the fields that are missing. For example, here is a record that is missing many required fields:

  

• Until all of the required fields are added to the spread sheet, the new record cannot be saved back into iTopia.

4. You can delete the original record, or you can leave it in the spread sheet if no changes have been made (it will be marked as Updated). When all of the changes are complete, we’ll close the spread sheet. The following message displays: "Do you want to save the changes you made to Book #. Yes/No". Select No if you do not wish to save the spreadsheet under a new name for future reference or select Yes to save the spreadsheet.

5. When you return to the Customer Master (SU13)-Quick Entry view in iTopia, the following message displays at the top of the view: "The data was modified using an external source. Please redo the search to view the latest changes." The 'external source' refers to the changes we just made using Excel.
   • To view the changes made, simply execute the search again. The updated records are now displayed on the view. Here is what our new record looks like (along with the original record):

 
Example 2: Editing Multiple Records in Excel

Using a modified version of the Customer Master (SU13), we will walk through an Edit with Excel example. The Customer Master (SU13)-Quick Entry view is used as the basis for our example, but this view has been personalized to include the Address 1, Address 2, City, Province/State, Country and Postal/Zip Code fields.

In this example, we have 7 records in the Customer Master (SU13)-Quick Entry view that do not have any address information. In a separate spread sheet, we have the address information for each client. What we will do is copy the address information from our spread sheet into the iTopia spread sheet in order to update our Customer Master (SU13)-Quick Entry view.

The following steps walk through this process:

1. In iTopia, we will open the personalized Customer Master (SU13)-Quick Entry view and we will search for the customer records with no address. Here is a screen capture that includes a portion of our of our iTopia SU13 view:

2. Using the check boxes beside each record, select the Customer Codes with no addresses. Then click on the 'Use Selected Records as Criteria for Action' icon in the header section, and select the Edit with Excel option.
   • If any Internet Explorer messages display, click Yes to continue.

   • When the records are loaded into the spread sheet, the following message displays: "The records have been transferred to the spread sheet. Save/Close."

     Note: The Save/Close buttons are used when the Save button in Excel has not been enabled. For more information on enabling the Save button, see the Compatibility Requirements document.

     In our example, the Save button in Excel is enabled, therefore we will leave this message window open and go directly to the spread sheet that has automatically opened.

   On the Excel spread sheet, all of the selected records are available for edit. Here is a screen shot of a portion of the spread sheet:

• Note: You will see the iBIS Client column in the spread sheet. This column is not displayed in iTopia, but the information (in this example, WINDY611) is part of the "key" that defines each record in the SUCU (or SU13) table. This column is for display purposes only and cannot be modified. If you make changes to this field, once the Save action is invoked, this field is reverted back to its original value.

3. To make our editing as simple as possible, we have created another spread sheet that includes the address information for each of these Customers. Here is an example of that spread sheet:

• You can see that we have kept the client list in the same order as iTopia.

• In addition, you will notice that the address for Smart Shoppers Inc. (the second line on our spread sheet) exceeds the maximum field length of 30 characters for the Address 1 field.

4. We will highlight the complete address information found in columns B, C, D, E, F and G on our spread sheet and copy this information into our Edit with Excel spread sheet. When the copy is complete, this is what our Edit with Excel spread sheet looks like:

• When we click on Save, we can see that the Status for row 2 (Smart Shoppers Inc.) is Invalid. You can see a red triangle in the Address 1 cell for this customer indicating that there is a problem with the data. If we hover over this triangle, the following comment displays:

• In this case, the maximum field length has been exceeded. If you hover over the Comment indicator (the red triangle) in the top cell of the Address 1 field, you will see that the maximum length for this field is 30 characters. The Comment indicators across the first row of the spread sheet indicate whether the field is required and the data length of the field (or the Domain and Column Values for each field).

• We will correct the Address 1 for our 'offending' record, and when it is 30 characters or less, we will click Save again. The Invalid Status changes to Updated.

5. Once all of the records are updated, we’ll close the Edit with Excel spread sheet. The following message displays: "Do you want to save the changes you made to Book #. Yes/No". Select No if you do not wish to save the spreadsheet under a new name for future reference or select Yes to save the spreadsheet.

6. When you return to the Customer Master (SU13)-Quick Entry view in iTopia, the following message displays at the top of the view: "The data was modified using an external source. Please redo the search to view the latest changes." The 'external source' refers to the changes we just made using Excel.
   • To view the changes made, simply execute the search again. The updated records are now displayed on the view. Here is what our updated addresses now look like:

 
Example 3: Assigning a New Territory based on a Postal Code Sub-Set

This example walks you through ALL of the steps required to assign a new Territory to Customers based on a sub-set of Postal Codes. This is an advanced example that covers off many components of iTopia. There will be no screen captures to assist you in visualizing this example. If you require more assistance with this topic, please contact your Business Consultant for additional training.

Note: In this example, your user id must be assigned to a role that has permission to customize the Customer Master resource.

The requirements for our new Territory are:

• New Territory = TOR (Toronto)
• All clients with a Postal Codes beginning with "M" are to be included in this new territory (previously part of the Ontario Territory)

Here are the steps required to assign a new Territory based on a Postal Code sub-set:

1. In order to assign a new territory to select customers, we will be using the Customer Master (SU13) view. The Customer Master (SU13) view must be personalized in order to assign the new Territory. When you click Personalize on the Customer Master (SU13) view, you will need to make the following changes:
   • On the Search Criteria folder, the Postal/Zip Code field is included. We must also add the Territory.

     Note: When you search for "Territory", there are multiple fields found. We only need to work with the Territory code, so we will only include the field labelled Territory.

   • On the Search Results folder, the Postal/Zip Code is included. We must also add the Territory.

     We can also limit the fields that display on the results page to include the Company Number, Customer Code, Customer Name, and Postal/Zip code and Territory. This is not a necessary step, however we do recommend that you keep only the essential fields in this view.

   • When we 'save' our new view, we’ll do a Save As and give it a new name, for example Customer Master-New Territory.

2. We must also verify that the Territory code has been created in Territories (SU09). In this example, the Territories (SU09) includes the following record:
   • Territory – TOR
   • Territory Name – Toronto

3. On our Customer Master-New Territory view, we will search for all Customers with a Postal Code that begins with 'M'.

   Note: If you have multiple Companies, be sure to search for the correct Company Number.

4. Once our search results are returned on the Search Criteria/Grid page, we want to edit the records with Excel.
   • Select all rows using the 'Select or Deselect All Rows' check box.

   • Using the 'Use Selected Records as Criteria for Action' icon in the header section, select the Edit with Excel option.

   Note: If you have more than one page of Customer records that match your search criteria, then you must click 'Show All' in order to have all records displayed prior to clicking on the 'Select or Deselect All Rows' check box.

   • If any Internet Explorer messages display, click Yes to continue.

   • When the records are loaded into the spread sheet, the following message displays: "The records have been transferred to the spread sheet. Save/Close".

     Note: The Save/Close buttons are used when the Save button in Excel has not been enabled. For more information on enabling the Save button, see the Compatibility Requirements document.

     In our example, the Save button in Excel is enabled, therefore we will leave this message window open and go directly to the spread sheet that has automatically opened.

   On the Excel spread sheet, all of the selected records are available for edit.

   • Note: You will see the iBIS Client column in the spread sheet. This column is not displayed in iTopia, but the information (in this example, WINDY611) is part of the "key" that defines each record in the SUCU (or SU13) table. This column is for display purposes only and cannot be modified. If you make changes to this field, once the Save action is invoked, this field reverts back to its original value.

5. You can edit the spread sheet in Excel manually or you can copy and paste from another source. All we want to do is update the Territory column to TOR.
   • When all of the records are updated, tab out of the last field edited and then click Save.

   • All of the records should now have a Status of 'Updated'.

6. Once the Save action is complete, we’ll close the spreadsheet. The following message displays: "Do you want to save the changes you made to Book #. Yes/No". Select No if you do not wish to save the spreadsheet under a new name for future reference or select Yes to save the spreadsheet.

7. When you return to the Customer Master-New Territory view in iTopia, the following message displays at the top of the view: "The data was modified using an external source. Please redo the search to view the latest changes." The 'external source' refers to the changes we just made using Excel.
   • To view the changes made, simply execute the search again. The updated records will now be displayed in the view. In this case, all Customers with a Postal Code beginning with 'M' now have a Territory of 'TOR'.