By using this website, you agree to our Terms of Use (click here)
I've found one thread regarding this with all of my googling. And the answer they gave (uncheck Break on incorrect target) didn't work for me. Here's what I'm trying to do:
We receive a price sheet from a vendor. That price sheet has 5000 items on it. We only carry 1000 of these. I'm trying to find a way (without a separate cross reference step, which so far seems to be my only option) to let a buyer load this file in as a vendor price worksheet.
If I load it directly into a Vendor Price Worksheet with the Upload button, it'll take everything. Then when I try to save or release it, it starts erroring, telling me that stock item X doesn't exist. I can remove X from the list, and then it tells me stock item Y doesn't exist. And so on for 4000 records. The luckiest I've gotten so far is that it loads the line with the inventory ID being left blank. I can filter based on that...but then I have to delete all 4000 lines one at a time.
I don't have an issue having the user use an import scenario, but that has it's problems as well. The import scenario is adding lines to a single price worksheet, and when it hits a line that has a non existent stock item, it doesn't fail the line--it fails the entire price worksheet since it's one document. This is with "Break on Error" unchecked.
I'd be perfectly happy if the import scenario failed 4000 of the 5000 lines, as long as it creates a price worksheet with the 1000 lines I care about on it But I can't figure out a way to prevent it from failing the entire document. Our VAR recommended just doing a unique vendor price worksheet for every line item, but the only way I know of to release them is one at a time.
Can you upload a screenshot of the import scenario you are using?
There might be a way to finagle the Import Scenario to do what you're trying to do, but it might be easier to just handle it in Excel using Power Query.
1st Power Query connection - Pull the Acumatica Items in using OData.
2nd Power Query connection - Pull the data in that you're trying to import.
3rd Power Query connection - Join the first 2 connections together to find the ones where the Item doesn't exist and filter them out.
Then you could drop the 3rd Power Query connection onto a new Sheet and import that Sheet into Acumatica.
That's essentially what I did. Having the GI available and refreshable via oData is really nice. What I have is:
A single "Central" file that links both to:
* oData exposed Product GI
* The price list file that I always drop into the same folder (thankfully the vendor always names it the same)
The file has a vlookup column added to the imported data of the price file that gives me a true/false if the item exists in the product GI.
I do a Refresh all in the file, close it, and drop it into the import scenario. The scenario has a source restriction that only pulls in the lines that had a True match.
@tlaird only as a form of a sanity check, we do the same thing for virtually all our imports since it seems that saving an Import Scenario with the "Break on incorrect target" unchecked doesn't stick. The following is our general process for any import initiatives we may require.
Within Excel...
- Worksheet "Data" is where the user can enter their data.
- Power Query connection 1 captures the data stored within worksheet "Data"
- Power Query connection 2 connects to the appropriate GI that will help qualify the contents of worksheet "Data"
- Power Query connection 3 joins the connections 1 and 2 and runs through various ETL and validation centric steps that will be loaded into another worksheet named "Output".
- All of rows of data within worksheet "Data" will be rendered in worksheet "Output" but the additional "Import" column (true/false) is used to identify those that will imported or not within the Import Scenario.
- At times, there may be an additional columned named "Insights" that is used to identify known issues that may have resulted during the the execution of this connection.
- Additional columns may be added based on the unique requirements of an import
- We've been told that Import Scenarios may encounter issues accessing worksheets that have an excessive number of calculations on them so we copy the values only of "Output" into a third worksheet named "Manual Copy" which is what the Data source will be mapped to. I'm not certain this is absolutely necessary but we do this as a cautionary step.
😉
Online Members
