Spreadsheet Link User Guide

Back to User Guides

Spreadsheet Link Overview

The Spreadsheet Link (LINK) module has two functions that allow users to transfer information to or from a spreadsheet program such as Microsoft Excel:

• The Import function – Using the LINK import function, create journal entries and net period balances in the IFS IDEAS database by importing data from a spreadsheet. 

• The Export function – Using the LINK export function, export information from journal entries and net period balances contained in the IFS IDEAS database into a spreadsheet. 

The LINK module allows users to work with spreadsheets in their native format without having to convert them to any other file format.

Import Function Overview

The import functions IFS IDEAS database. There are two import options available: 1. Creation of journal batches, or 2. Direct update of the database.

The use of postable journal batches provides the IFS IDEAS journal entry trail, while the direct database update provides, upon request, an audit trail report.

The import process requires two basic steps:

1. Create a spreadsheet according to the required criteria (refer to spreadsheet documentation to determine how to enter data into the spreadsheet).

2. Import the spreadsheet data into journal batches or directly into the IFS IDEAS database.

Importing Spreadsheet Data into Journal Batches

The following general steps explain how to import spreadsheet data into journal batches. The first step only needs to be performed once. This is a setup step that will establish the journal to which the data will be posted.

1. Define the journal as an automated type by using the Journal Entry Processing function, accessed through the Journals menu in the General Ledger module. Refer to the Journal Entry Processing section of the General Ledger manual for instructions.

2. Unlock the period to be accessed using the Period Lock/Unlock Maintenance function, accessed through the Utilities menu in the General Ledger module. This step must be performed any time the period to be accessed is locked.

3. Create the spreadsheet containing the data to be imported.

4. Run the Link for Journal Entries Import function in LINK. The LINK process brings the information directly into the journal batch file.

5. Once the data is transferred, you may then validate and post the batch like any other IFS IDEAS batch.

Importing Spreadsheet Data to Directly Update the Database

Using Link for Account Balances Import, use the spreadsheet to update all defined periods for a segment in the database. The segment must have a security code of 6 – Unrestricted access in the General Ledger Database Definition screen. Because this process is designed for the loading of budgets, typically no audit trail is required.

Spreadsheet Configuration

Spreadsheet data to be imported must meet these criteria:

• At least one column must be an element derived from the account code (e.g., the descriptive element, typically the account element) and must be formatted as text.

• Description columns must be formatted as text if creating journal entries.

• One column must contain both the debit and credit. Credits must be prefaced with a minus sign (-). IFS IDEAS stores liabilities and revenue amounts as credits.

• At least one column must contain only the amounts to be updated. These amounts must be formatted as values (not formulas). They may contain decimals but may not contain dollar signs or commas.

• Rows containing data to be imported must be contiguous, i.e., must be without gaps, and must not contain text headings.

Define and save the spreadsheet, noting which columns contain data to be imported. Also, note the location of the beginning and ending rows of the data to be imported.

Export Function Overview

The Export function allows the user to download, or export, data from the IFS IDEAS database into spreadsheets.

Open

The Export process requires two basic steps:

1. Determine what data to transfer to a spreadsheet.

2. Export the journal entries or account balances.

IFS IDEAS exports journal entries and account balances from two different tables. For journal entries, IFS IDEAS exports information from the Journal_Lineitems table in the IFS IDEAS database to a spreadsheet. For account balances, IFS IDEAS exports information from the Balance_acctdetail table in the IFS IDEAS database to a spreadsheet.

Have ready the following information to perform an export:

Modules to Set Up Before Spreadsheet Link

IFS IDEAS requires that the General Ledger module be set up before settings and configurations in the LINK module can be defined.

Refer to the General Ledger manual for guidance on how the GL module should be configured.

Screen-by-Screen Instructions – Overview

The LINK module is an application interface with two import functions and two export functions:

Two import functions copy from a spreadsheet into the IFS IDEAS database:

1. Journal Entries Import

2. Account Balances Import

Two export functions copy information from the IFS IDEAS database to spreadsheets:

1. Journal Entries Export

2. Account Balances Export

In addition to accessing the functions in the LINK module from the Functions menu, users may also use the toolbar buttons below.

 Link for Journal Entries Import

Export to Excel Feature

Functions Menu

Both Import options and both Export options reside in the Functions menu. The two import options require that specific criteria be met for the spreadsheet prior to executing the import process(es). Refer to the Spreadsheet Configuration section above for more details.

No specific spreadsheet setup is required for the Export processes beyond creating and saving an export place-holder.

Link for Journal Entries Import

Use the Import function to copy data from a spreadsheet into the IFS IDEAS database.

Processing Link for Journal Entries Import

Open

• Import From – Click the drop-down arrow. A Windows Explorer window opens. Enter or click the file name of the spreadsheet to import, then click the Open button. 

Open

• All Lines – This radio button is selected by default. This will import all lines of the spreadsheet. Any descriptive or informational lines must be removed from the spreadsheet.

• Range – Select this option to import only certain lines from the spreadsheet. When using this option, enter the beginning and ending line numbers. The range cannot contain gaps, nor can it contain descriptive or information lines.

• Ledger ID – Select the appropriate Ledger ID. If only one ledger is defined, it displays automatically

Open

• Entity – Select the desired entity from the drop-down list. If only one entity is defined, it displays automatically. The entity selected must have the journal predefined.

• Journal – Select the journal to be used for the journal entry. The journals displayed in the drop-down list are automated journal types.

• Period – Enter only one valid period number defined in the Calendar for the segment.

• Zero Suppress – Check this box to suppress lines with zero amounts. Leave this box blank to create line items for zero amounts.

• Convert – If the system is multi-currency, select this checkbox. If selected, a converted journal will also be created at posting if the entity/journal is set up on the Journal Table.

• Description – Enter the letter of the Spreadsheet column containing journal entry descriptions, or leave blank for no description.

• Amount – Enter the letter of the Spreadsheet column containing amounts.

  • Please remember that in the Amount text box, credits must have a minus sign (-). 

The next two columns supply account code element positions. For each element, choose either Derive Column or Specific Entry.

• Derive Column – Enter the letter corresponding to the spreadsheet column containing the appropriate element information or leave blank to use the Specific Entry field.

• Specific Entry – If the Derive Column is blank, enter a specific detail element to be used for all lines of the journal batch to be created.

• Vendor – If appropriate, Vendor information can also be included.

• Invoice – If appropriate, Invoice Information can also be included.

• Invoice Date – If appropriate, Invoice date can also be included.

• Date – If use the Currency Conversion module is installed, this field must be completed. Enter a letter of the spreadsheet column containing the date or enter a specific date.

Buttons located along the bottom of the screen allow users to perform certain actions:

• Edit – Click the Edit button to open the spreadsheet and allow editing of the information contained within.

• Preview – Click the Preview button to read information from the spreadsheet, validate the FQA, and display a sample of lines in the grid at the bottom of the screen. This is a recommended step prior to clicking the Run button.

• Run – Click the Run button to begin the import process. The information will be copied from the spreadsheet, validated and updated into JVAUTO.

• Print – Click the Print button (prior to clicking on the Run button) to print a report containing all the lines to be imported.

• Close – Click the Close button to exit the screen without saving changes.

Link for Account Balances Import

This Import program is used to import data from a spreadsheet into the IFS IDEAS database.

Processing Link for Account Balances Import

Open

• Import From – Select the drop-down arrow. A Windows Explorer window opens. Enter or click the file name of the spreadsheet to import, then click the Open button.

Open

• All Lines – This radio button is selected by default. This will import all lines of the spreadsheet. Any descriptive or informational lines must be removed from the spreadsheet.

• Range – Select this option to import only certain lines from the spreadsheet. When using this option, enter the beginning and ending line numbers. The range cannot contain gaps, nor can it contain descriptive or information lines.

• Ledger ID – Select the appropriate ledger ID. If only one ledger is defined, it displays automatically.

• Segment – Select from the drop-down list the segment to update. The LINK module will verify that the security code allows a direct update. A brief description of the segment you have chosen will appear automatically in the text box below.

• Period – Enter a period or range of periods to be updated. To enter a range of periods, use a dash between the beginning and ending periods in the range. Use the range of 1 – 12, to update the entire calendar year on a month by month basis. If necessary, specify a period of zero to update beginning balances.

The next two columns supply account code positions. For each element choose either Derive Column or Specific Entry. 

• Derive Column – Enter the letter corresponding to the spreadsheet column containing the appropriate element information or leave blank to use the Specific Entry field.

• Specific Entry – If the Derive Column is blank, enter a specific detail element to be used for all lines of the journal batch to be created.

• Amount – Enter the letter corresponding to the spreadsheet column containing the first amount. If more than one column is to be transmitted, the system looks at the number or range of periods to determine how many columns to import.

Buttons located along the bottom of the screen allow users to perform certain actions:

• Edit – Click the Edit button to open the spreadsheet and allow editing of the information contained within.

• Preview – Click the Preview button to read information from the spreadsheet, validate the FQA, and display a sample of lines in the grid at the bottom of the screen. This is a recommended step prior to clicking the Run button.

• Run – Click the Run button to begin the import process. The information will be copied from the spreadsheet, validated and updated into JVAUTO.

• Print – Click the Print button (prior to clicking on the Run button) to print a report containing all the lines to be imported.

• Close – Click the Close button to exit the screen without saving changes.

Link for Journal Entries Export

The purpose of the Journal Entries Export is to export data from the IFS IDEAS database to a spreadsheet. This particular function exports data from the Journal_Line Items table to the spreadsheet specified.

The columns from the Journal_LineItems table downloaded to the spreadsheet include the following information:

• Entity Name

• Journal Name

• Period Number

• Batch Number

• Line Item Number

• Account ID           

• Line Description    

• Line Debit 

• Line Credit

• All elements from the account code

Using the Link for Journal Entries Export Screen

Click the  icon or select Link for Journal Entries Export from the Functions menu.

Open

• Export to – Select the drop-down arrow. A Windows Explorer window opens. Enter or click the file name of the spreadsheet to export, then click the Open button.

Open

• Ledger ID – Select the ledger ID to export. If only one ledger is defined, it displays automatically.

• Entity – Select an entity from the drop-down list. If only one entity is defined, it displays automatically.

• Journal – Select the journal to be used for the export. The journals displayed are automated journal types.

• Period – Select the period number to be exported.

• FQA – Include Only – If only certain element IDs are to be included in the export, enter them in the available fields.

Buttons located along the bottom of the screen allow users to perform certain actions:

• Edit – Click the Edit button to open the spreadsheet and allow editing of the information contained within.

• Preview – Click the Preview button to read information from the spreadsheet, validate the FQA, and display a sample of lines in the grid at the bottom of the screen. This is a recommended step prior to clicking the Run button.

• Run – Click the Run button to begin the import process. The information will be copied from the spreadsheet, validated and updated into JVAUTO.

• Print – Click the Print button (prior to clicking the Run button) to print a report containing all the lines to be imported.

• Close – Click the Close button to exit the screen without saving changes.

Link for Account Balances Export 

The purpose of this program is to export data from the IFS IDEAS database to a spreadsheet where this data can be manipulated to create an import spreadsheet for the Import Link function. This particular function exports data from the Balance_Detail table to the spreadsheet.

The columns from the Balance_Detail table downloaded to the spreadsheet include the following information:

• Ledger ID  

• FQA

• Segment

• Period Number

• Debits

• Credits      

• All elements from the account code

Using the Link for Account Balances Export Screen

Click the  icon or select Link for Account Balances Export from the Functions menu.

Open

• Export to – Select the drop-down arrow. A Windows Explorer window opens. Enter or click the file name of the spreadsheet to export, then click the Open button.

Open

• Ledger ID – Select the ledger ID to export. If only one ledger is defined, it displays automatically.

• Segment – Select the appropriate segment. This refers to the database segment used to store currency conversion data.

• Period – Select the period number to be exported.

• FQA – Include Only – If only certain element IDs are to be included in the export, enter them in the available fields.

Open

Buttons located along the bottom of the screen allow users to perform certain actions:

• Edit – Click the Edit button to open the spreadsheet and allow editing of the information contained within.

• Run – Click the Run button to begin the import process. The information will be copied from the spreadsheet, validated and updated into JVAUTO.

• Close – Click the Close button to exit the screen without saving changes.