I will keep that in mind Scott, but at this point I am not ready to abandon the idea of importing csv files

The number of distributions is how many lines of detail  are on that one invoice. So if  invoice number 566 has 3 line of sales details, the number of distributions for all three lines is 3.

In Excel before you bring in a sales order or invoice the amount column must be a negative value. So you have to add a column and in that column use the SUM function to multiply the positive amount *-1. Once all the amounts are populated with negative amounts you must copy that column and paste the Values in a new column because Sage can’t import an Excel calculation. Then delete the columns that are now irrelevant.

Importing data is not easy so keep trying, you’ll get it. I always make a backup of my data, then restore to a New Company. Once you restore it, let Sage automatically open the new company, then go Maintain company info and type TEST before your company name. Save it, close the company. Now when you open Sage you can browse to your original and test company l.

Good luck,

Thank you Kim - this was very helpful!

Hi Meredith,

I hope you got this all squared away. I plan to write up more details on this on a blog entry - perhaps create a video - as I feel that Sage's documentation is... vague.

After several calls to Sage, getting a manager/higher level tech on the line, and some trial and error, I have sales orders importing correctly using CSV files. I am thankful for their support, even if they kept talking about not supporting "3rd party integration". My retort was, better documentation and we aren't having this conversation. But... c'est la vie.

For us, the use case is that we (my client) has a new B2B eCommerce platform. We are using the ODBC drivers and connect/retrieve Sage data directly into Excel - so we have a master sheet of customers (for customer ID and other things like shipping, terms, etc.) and a master list of products (Item ID, Description), etc.

• Retrieve orders via Power Automate via our ECommerce API
• Place orders (raw data) into an Excel file
• Run Excel VBA that connects/matches eCommerce Customer ID to Sage Customer ID, eCommerce SKUs to Sage Item IDs
• Use an Excel Sage Sales Journal import template and places the required data into that sheet
• Saves the sheet as a CSV
• Import into sage

 With the ODBC connection, we are able to include the customer's terms, shipping method, tax code, notes, etc.

Regarding negative/positive in unit price and amount, when importing a sales order, make the amounts negative to create a positive amount on the invoice lines and totals.

Why you ask?

It has to do with double-entry accounting, credits being debits and debits being credits, or common core math... or perhaps something to do with quantum entanglement or string theory. In the immortal words of Joe Dirt, "...might as well ask why is a tree good? Why is the sunset good?..." (I can't put the rest of the quote)

Regarding Number of Distributions and SO/Proposal Distribution

• Number of Distributions: The total number of lines in a given order. If you have 3 lines, all three lines will have the same number, 3.
• SO/Proposal Distribution: The individual line items for an order. 1, 2, 3 (in the above example)
  We were initially keeping the Sales Order/Proposal # field blank and including the line numbers ensured that multiple orders to the same customers in a single import file created new/individual orders.

If you want/need any additional assistance, I'm happy to offer what I can.

Thanks,
Matthew Moran

Thank you Matthew, and yes, we have been able to successfully import order information as a CSV file.  I am helping a very small company, so much of what we do is manual (even the collection of the order information) but I have found that importing definitely beats retyping a sales order (on excel) to create an invoice in Sage.

I'm still baffled by the need to load the unit price and extended amounts as negatives in order for them to show as positive values :-) but am not losing sleep over it.

I appreciate your offer and will reach out of we get stuck.  Thank you again,

Meredith Koerner

LT's Inc.

I figured you had things under control. In part, as there is little clear documentation, my response hopefully helps the next person as well.

Additionally, per my offer to assist, that's not as a service/provider or consultant. ie: I'm not seeking any clients or paid work.

Currently, I help one client using Sage but I'm contracted to Meta full time as a productivity application manager.

A few months ago I helped another company get their ODBC connectivity working in order to let them query custom data into Excel & Access for more flexible reporting.

Understood, and thank you