x Want to participate? Join the group to interact
Options
  • State Verified Answer
  • Replies 7 replies
  • Subscribers 48 subscribers
  • Views 5605 views
  • Users 0 members are here
Browse Forums
Announcements
68
topics
93
replies
Customization and Integration
262
topics
822
replies
Financials, Projects, and Administration
382
topics
843
replies
General Discussion
327
topics
836
replies
Installation and Technical Issues
335
topics
1k
replies
Manufacturing, Distribution, and Applications
341
topics
696
replies
Reporting and Analytics
124
topics
482
replies
NadineHarco

Using Data Import Manager for importing Inventory Items

SOLVED
Posted By

We would like to use the DIM module to automate creating new Items in Sage500   -  I have not had much experience using it and cannot find much documentation on how to use it - or - sample import files for creating Items in SOA - unlike the IM Import Utiltiy for Items which has sample files in the Sage500 client folder.

We (hopefully) plan on having a developer create the file containing all required data fields for the new items... and then using a batch job to kick off running a DIM import Items job to bring items into Sage500 as needed.

Does anyone know of where some sample data is to start with?  Does this plan make sense?   Any advice or help will be greatly appreciated. - nml

  • 0
    We went a totally custom route for a couple of reasons. One we were importing into inventory at the same time and two we were importing into our custom fields table at the same time. Finally by writing it custom we were able to create our own rule checking for rules specific to our company. More work but WAY better. For something like this you'd want someone experienced with MAS 500 since they'll know all the data rules. Good luck.
  • 0

    If you use Excel as the import file, I believe there is a sample spreadsheet with the minimum amount of fields required to import an item in the C:\Program Files (x86)\Sage Software\Sage MAS 500 Client\DI\Samples directory called MinimumAllStgMappedInventoryItems.xls that can be used. DI can be a little tricky to set up, but once running, seems to work pretty smoothly for many users. It uses Sage supported APIs to import items, so most business rules are enforced.

    When setting up the mappings, you may wish to consider using the xml file in C:\Program Files (x86)\Sage Software\Sage MAS 500 Client\DI\Mapping called MinimumAllStgMappedInventoryItems.XML. Keep in mind that you can always add to the minimum mapping.

    While looking for these, I did notice that there is also support for importing custom fields as a separate import if that is a requirement, although I have not personally used this import.

    DI is often used internally at Sage to import base sets of data used for testing on a new database for tests that need specific data attributes on items and inventory items. The tool is not perfect, but it meets the needs of many.

    To determine what values to place in a given field, some are obvious like amounts, For others, if it fails, the output of the report often report the valid values. Yes/No fields can generally have a "yes", "no", 1, or 0 value. Fields with a dropdown on the UI will generally accept the values in the dropdown, or their database representation values. As most people will not know the DB internal values, the UI displayed values are used most often. For example, I generally use a "yes" instead of a "1", or a valuation method of "FIFO" rather than a "3".

    I assume you are trying to import new items, and not copy items in one warehouse to another. If you were doing that, then you might have wanted to consider the Create Inventory application.

    Good luck on your quest, regardless of the way you achieve it.

  • 0 in reply to JohnHanrahan
    We did consider this, but we really want to try and utilize this module... and possibly use it for other imports. But thanks for the suggestions.
  • 0 in reply to Ramon M.
    Yes, I did see the 'minimum' files - but was hoping for something a little meatier to try on SOA! I'll try and build one and then setup an import job. Your opinion on the 'whole' process of using a batch job to run the DIM job?
  • 0 in reply to NadineHarco
    SageU (Sage University) has all of the importing classes for free. You might want to review them:
    sageu.csod.com/.../search.aspx

    John
  • 0 in reply to NadineHarco
    verified answer
    Hi Nadine,
    I'm not sure what you mean by "meatier", but let me explain a little more about what these files do to help...
    If you set up an DIM job, using Maintain Import Job, you will notice when mapping fields, many of them say "Required", but the meaning of that is a little nebulous. For example, a numeric field might be required, but you don't necessarily have to provide a value if Sage 500 ERP has a default value for the field (often numeric fields have a default of 0). So, to help people get started with imports, we provided those minimum-mapping sample files to give you a mapping with only the fields that you would really need to map to get your data into the database. Starting from that file, you can then build out the other fields based on what you will have in your source system.
    Ramon mentioned the XML files, found under the ...\DI\Mapping\ folder. These layout the minimal mapping of the MS Excel sample files.
    I'm not sure if you were at Sage Summit 2016, but I did a presentation on this, so that presentation should be available after August 15th on the Sage Summit site.

    To me, these sample MS Excel files are a way of starting to understand the mapping requirements and get a "jump start" on laying out a mapping. You may wish to use SQL instead of Excel for example, but the mappings will be very similar. So in steps, what you do with these minimal mappings is (using Excel as the source data):
    1) Create a Source Connection and specify the minimal mapping SAMPLE file as the Sample. Copy that sample and put your data in it and point to that as the data file. NOTE: The sample file and the data file must be on a UNC accessible to the SQL Server where your Sage 500 ERP database resides, and accessible to the Sage 500 user - I suggest a domain user - that is specified in your credentials - in SQL Management Studio, under Security folder, subfolder Credentials, there is a MAS 500 credential. This is a Sage 500 user and an account that has rights on the SQL Server to access files (hence my suggestion to make this a domain user).
    2) To see a simple mapping to this sample file, when you open Maintain Import Job, you will see an "Import" button on the menu. Click this and select the XML file for minimum mapped inventory items as the import file (found in the mapping directory mentioned above). It will ask you to specify the Source Connection, pick the one defined in step 1 above. Give the job a name.
    3) Use this as a starting point; you now have a file of sample data and your file of your data, combined with a mapping defined in step 2.
    4) Use Import Job to specify a job to run the import. I suggest starting with just the "Extract" portion first to make sure it works.
    As you modify the Maintain Import Job settings to add additional mappings, you can build the import you truly wish. I suggest deleting the Import Job from step 4 when you want to modify the import job so that it doesn't complain (delete the job in step 4 before going back into Maintain Import Job).

    NOTE: In step one, you will have to have defined a user who has a password in Sage 500 ERP and who has the "DI Package User" and "DI Package Administrator" check boxes checked for the user so that they can open Maintain Import Job and make modifications to the import mapping. DI Users are only allowed to import data from pre-defined jobs.

    Darrick
  • 0 in reply to dbcoles
    This has been very informative - Thank you for your help and I am on my way!

*Community Hub is the new name for Sage City