Questions Adding/Changing Sales Categories using Import Scenarios

Hi Michael,

I'm also interested in Tim's feedback on this one. I worked on the same problem some months back and got something functional and good enough for our needs, but not perfect. I couldn't get it to work by the Category Description, just by CategoryID (I use an excel sheet to cross reference the CategoryID to the Category Description before uploading the file). I suspect it's something to do with the way the UI field for Categories works on the Stock Item screen, it's not "searchable" by typing in the field you have to select the category from a tree view.

Sadly, my example does not change existing categories, it only adds new ones. Your example with the line number might work if you know which category line needs to be changed for the specific inventoryID and set the line number as a column in the data provider.

*example working as of Acumatica 6.10.0133

- Casval

Thanks Casval. Yes it is a perplexing problem. I have continued to work on it since posting but still no success. Hopefully we may hear from Tim soon with a solution.

Michael

Sorry for the delay on this. Maja in support at Acumatica is much more knowledgeable on these then me, but I did ask her about this scenario a while back.

This is what I have saved in my Import Scenarios folder.

Here is what the source Excel import looks like. The key is the "/" that separates levels of the tree:

And here is what the import scenario looks like:

Give it a try and let me know how it goes.

Thanks for the reply Tim.

I can see how the 'Folder' concept would help in allocating the correct sales Category but I am unclear on details of your Import Scenario. For example, how are the Target Object 'Folders' and '->FolderID' achieved? These are not options for me when entering details into my scenario. Likewise, Field/Action Name 'Path' is not an option. 

Are these defined elsewhere?

Michael

Darn, I thought I'd be able to get away with a few lazy screenshots from a long time ago 🙂

But, alas, I guess I'll have to get off my butt and try this.

I tried it in my SalesDemo environment that runs Acumatica 6.10.0472 and it seems to work.

Here is what I did:

1. Created an Excel file with this data in it:
   
2. Created a data provider called Item Sales Categories in the Data Providers screen like this:
   
3. Created an import scenario called Item Sales Categories in the Import Scenarios screen like this:
   
   Note: I was able to select everything in the Target Object, Field / Action Name, and Source Field / Value columns by using the drop downs without having to do anything special or weird.
4. Loaded everything into the Import by Scenario screen using the PREPARE button, then imported everything using the IMPORT button
   
5. Checked the Item Sales Categories screen and it seems like everything imported correctly
   

Thanks Tim. I'll give it a try and see how it goes. Appreciate it!!

Then I realized that this whole time maybe you weren't talking about how to create the categories, but how to put things into them.

So I tried doing that too:

1. Created an Excel file with this data in it:
   
2. Created a data provider called Stock Items Sales Categories in the Data Providers screen like this:
   
3. Created an import scenario called Stock Items Sales Categories in the Import Scenarios screen like this:
   
4. Loaded everything into the Import by Scenario screen using the PREPARE button, then imported everything using the IMPORT button
   
5. Checked the Item Sales Categories screen and it seems like everything imported correctly
   
   

Also, note that you can view the imported information in the Stock Items screen, even though we didn't actually import into the Stock Items screen:

 Many Thanks Tim.

Yes, I WAS trying to modify existing Sales Cats so your detailed second explanation has worked perfectly. Areas where I was going wrong in my initial efforts were:
- Using screen IN202500 instead of IN204060 and therefore not seeing the 'Folder' and 'Path' options
- Not including the full path when defining my Sales Category, just using the actual sales Category

After being shown the correct screen and path information, brilliant!

Thanks again.  Michael

Awesome. Glad to hear that it worked out.

You can thank Maja, she's the one who showed me how to do it 🙂

This thread has been extermely helpful!  Thanks you.

Definitely a helpful explanation of this rather opaque screen!

I do want to add one additional quirk that I noticed while testing imports for Item Sales Categories: invalid paths. Using Tim's above example, something unexpected happens when you attempt to import a new item into a path that doesn't exist, such as adding TIMTEST01 to "COMPANY/Tim Test 01/Sub Level 04". Instead of throwing an error, as one might expect for an invalid path, the item is added to the deepest level of the path that does exist, in this case "COMPANY/Tim Test 01"

On one hand, this allows you to be lazy with dynamically created paths. If, like me, you have a lot of Sales Categories and some have sub-sub-sub categories while others don't, then you can have one path format which applies to all items without needing to remove sub-... category name placeholders.

On the other, more sensible hand, this can lead to unexpected entry locations if you have not created a Sales Sub Category and attempt to load it with items. One must be careful to create new Sales Categories before populating them because there is no error or warning to indicate that the import did not go exactly as specified.

Relatedly, I continue to search for a single import scenario that can create a new category if the path doesn't already exist and populate it with an item, similar to how Stock Item imports work. I do not believe it is currently possible, though.