The Process I use to convert the raw data into a format I can upload is outlined below.
I will first export the data. The raw data file is what I will have from the export. I will take the data and copy all the invoice data and paste it into the invoice conversion file. “Attached file invoice conversion before has the data before running any macros.”. I will copy all the raw data and then delete the data in column G. Column G is only used for reference so I know what data to place in what file “invoice data, credit data”. Then I do the same copy and paste with all the credits into the credit conversion file.
I also attached a file called “invoice converted and credit converted”. That is in the format I need to upload. I have macros that do a lot already. They convert the customer names to match the names within the accounting program and also convert the items to match. I have one that add the correct template. “Template is a field in the accounting program I need”. Also add the class, calculate the discount along with other fields needed to do the import. The conversion files works great now. I received a lot of help from this forum to create them.
So what I need to do is convert the credit number to match the invoice number with a 9 in front. After I do that I will copy the data out and put it into the conversion files.
The attached file credit converted shows both how it will convert and also under that shows how I need it to be. The only difference is the credit number “column B”.
I have everything all working perfect.
It is just that the credit number does not match the credit number on the paper printout handed to the customer.
So I need the credit number on the paper print out to match within the accounting program. The paper print out with have the invoice number with a 9 in front of it to be used as a credit number.
In the raw data file column G has I and C. "I" means Invoice and "C" means Credit
Some of the macros reference other excel files to get item prices and convert data. I have one that looks at another excel file to find what to convert the names and items to. These are nice because it is easy to add new customers and items to when needed. Also to change prices is very simple. I attached a copy of those also so you know what the macros are referencing. Those 3 files are “flexReplaceDataInvoice.xlsx, FlexReplaceDataReturns.xlsx and Flexitemprices.xlsx”
Thank you all for the help. I have been working on this for a year with the ERP company. I am at the final stages.
Reply With Quote