Skip to main content
Solved

IFS Excel Add on tool

Curious_User
Sidekick (Customer)
Forum|alt.badge.img+8

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.

Best answer by david.harmer

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

View original
Nipun Gunaratne
1 person likes this
  • Nipun Gunaratne
    Nipun Gunaratne
Did this topic help you find an answer to your question?

7 replies

D
Hero (Customer)
Forum|alt.badge.img+11
  • david.harmer
  • Hero (Customer)
  • 142 replies
  • Answer
  • March 2, 2020

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

RickBucchino
Curious_User
2 people like this
  • RickBucchino
    RickBucchino
  • Curious_User
    Curious_User
S
Superhero (Customer)
Forum|alt.badge.img+28
  • ShawnBerk
  • Superhero (Customer)
  • 1482 replies
  • March 3, 2020

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
1 person likes this
  • Curious_User
    Curious_User
Curious_User
Sidekick (Customer)
Forum|alt.badge.img+8

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
Hero (Customer)
Forum|alt.badge.img+11
  • david.harmer
  • Hero (Customer)
  • 142 replies
  • March 3, 2020

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

S
mjezek
RickBucchino
4 people like this
  • S
    ShawnBerk
  • mjezek
    mjezek
  • RickBucchino
    RickBucchino
  • Curious_User
    Curious_User
eqbstal
Superhero (Partner)
Forum|alt.badge.img+21
  • eqbstal
  • Superhero (Partner)
  • 677 replies
  • March 4, 2020

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

Thanks for using the community, it helps me. I hope I can help you.
mjezek
1 person likes this
  • mjezek
    mjezek
Curious_User
Sidekick (Customer)
Forum|alt.badge.img+8
eqbstal wrote:

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
Hero (Partner)
Forum|alt.badge.img+11
  • pwlm
  • Hero (Partner)
  • 70 replies
  • October 25, 2020
eqbstal wrote:

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
eqbstal
1 person likes this
  • eqbstal
    eqbstal

Reply


Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings