Solved

IFS Excel Add on tool

  • 2 March 2020
  • 7 replies
  • 3365 views
Curious_User
Rank
Userlevel 2
Badge +7

Hello Everyone, 

I have been working with the IFS software for the past 7 years and our team does a lot of data entry manually. E.g. 5 users 100 lines per Customer order and with a minimum of 5 orders a day. Consultants have asked us why we are not using the IFS Excel Add-in (Also called the Excel migration Tool). Which is a excellent question which imitatively raised major concerns from IT due to the tool being called a migration and accessing IFS directly from Excel (read/write/delete?) .

I was wondering if any users in this forum have some stories on how it helped their company and what some of the downsides of the tool are.

  • Successfully implementations
  • To which extend is the Excel Migration tool being used (simple/complex jobs/ Data repair?)
  • Restrictions of the tool
  • Security concerns (e.g.can users destroy the tables of the database using this tool)
  • IFS User feedback (greatly increased process and or productivity or used as a last resort due to time crunch) 

Looking forward to the replies, and understanding the capabilities of the Tool.

icon

Best answer by david.harmer 3 March 2020, 00:19

View original

7 replies

D
Rank
Userlevel 6
Badge +10

Hi 

A company I worked used this tool extensively. It is a great tool. It is permission based so there should be no concerns about what the users can/cannot do. For example they can only update stuff they could do in the IFS client, but just a lot faster. 

It has a great validation tool that validates that the data is correct before you execute, which is helpful, however this can fail in a parent/child migration. i.e. loading PO header then line, as the validation fails, as there is no header when it attempts to validate the lines. 

The best use of this tool is for repetitive upload or data cleanse, i.e. we created a quick report that exports the columns in the correct format, these can be cut/pasted into the tool, then updated a reloaded. I believe you can do the load direct from the migration tool but I never got that far. 

I’d recommend it for anyone that has users that need to load lots of lines frequently or are in need to cleanse data. 

The other advantage the tool has is that it allows multiple users to load data at the same time, as each time it creates is own separate load table, unlike the traditional data migration tool inside IFS IEE. Therefore you can ensure that users do not accidentally load/reload another users data. 

Dave

S
Rank
Userlevel 7
Badge +28

The Excel add on tool can be controlled such that only information is added to tabs or to an overview if data entry is allowed. It isn’t the same as the data migration tool where underlying data can be changed directly.  It is best suited for adding detail lines to an object.  We’ve used it as part of the standard process for creating Supplier Agreements where the Agreement Lines number in the hundreds.  Once the format is derived in the tool, the additional items can be added and imported.  I refer to it as an import tool rather than a data migration tool.  An equivalent can also be approximated using copy and paste, so it is not an excessively damaging or dangerous tool.

 

Curious_User
Rank
Userlevel 2
Badge +7

Thank you for the info.

I have security question related to the Excel tool.

Is the Tool accessing the Oracle Database directly from Excel to write/update/Delete the tables data or is it using the API calls just like the IFS software interface the users are seeing when interacting within IFS?

D
Rank
Userlevel 6
Badge +10

Hi - The tool can only run Migration Jobs that are setup within the Data Migration tool, so everything must be done via an API. There is no way to access the IFS database directly for insert/update/delete as this would cause all sorts of referential integrity issues. 

This ensures IFS permissions are also taken into consideration when using the tool. 

Dave

eqbstal
Rank
Userlevel 7
Badge +21

Hi,

At a customer they use it to load opportunities in CRM. It builds the heading and the part lines. This is due to the fact that header and line info are in the same Excel row. Not only is it used to import new information, also updating the data is done in the same way. In this situation, the responsible key user of an opportunity uses Excel Migration Tool only for their own opportunities.

Another situation is to load supplier for purchase part price lists. One Excel is used to retrieve the open lines (using search icon), the lines get a valid until date and are updating the data in IFS. The second Excel migration job is used to generate new price list lines. Works great and highly efficient.

There is also a situation that sales prices are loaded.

The downside is that there is no audit trail when someone has used this tool. Using a standard migration job, the last usages is reported in the information of the migration job and there is the history when several times the same job was used. This is not available. I have an open issue for this at a customer so if anyone has an idea how to prepare an audit trail every time data is loaded or updated, I like to hear it.

Regards,

Steve

Please like this response if it helped you
Curious_User
Rank
Userlevel 2
Badge +7

Hi,

At a customer they use it to load opportunities in CRM. It builds the heading and the part lines. This is due to the fact that header and line info are in the same Excel row. Not only is it used to import new information, also updating the data is done in the same way. In this situation, the responsible key user of an opportunity uses Excel Migration Tool only for their own opportunities.

Another situation is to load supplier for purchase part price lists. One Excel is used to retrieve the open lines (using search icon), the lines get a valid until date and are updating the data in IFS. The second Excel migration job is used to generate new price list lines. Works great and highly efficient.

There is also a situation that sales prices are loaded.

The downside is that there is no audit trail when someone has used this tool. Using a standard migration job, the last usages is reported in the information of the migration job and there is the history when several times the same job was used. This is not available. I have an open issue for this at a customer so if anyone has an idea how to prepare an audit trail every time data is loaded or updated, I like to hear it.

Regards,

Steve

@eqbstal ,

have you tried using the Tool after activating the history log of the areas that the data gets updated in?

From what i am understanding, the tool triggers a migration Job which uses APIs to update the data which is similar to a user adding data through the normal IFS interface and a History Log entry should be created when it is active.

This is me just guessing but give it it try if you haven't already.

pwlm
Rank
Userlevel 4
Badge +11

Hi,

At a customer they use it to load opportunities in CRM. It builds the heading and the part lines. This is due to the fact that header and line info are in the same Excel row. Not only is it used to import new information, also updating the data is done in the same way. In this situation, the responsible key user of an opportunity uses Excel Migration Tool only for their own opportunities.

Another situation is to load supplier for purchase part price lists. One Excel is used to retrieve the open lines (using search icon), the lines get a valid until date and are updating the data in IFS. The second Excel migration job is used to generate new price list lines. Works great and highly efficient.

There is also a situation that sales prices are loaded.

The downside is that there is no audit trail when someone has used this tool. Using a standard migration job, the last usages is reported in the information of the migration job and there is the history when several times the same job was used. This is not available. I have an open issue for this at a customer so if anyone has an idea how to prepare an audit trail every time data is loaded or updated, I like to hear it.

Regards,

Steve

A little late to the party but...

You could add a custom method call to the Method List in the Excel Migration Job to populate a Custom Logical Unit with the execution details you require.

Cheers,

Pete

Pete McDermott - https://linkedin.com/in/pwlmcdermott

Reply


Terms & ConditionsCookie settings