Hi, hope somone can offer me some advice.

I am developing an application to process orders (a bit like MRP), and want it to work closely with Sage (Line 50 - v10), sharing info about customers, suppliers, PO's, SO's - but then generating more info. about product lead-times, and operation steps.

My dilemna is that the company I'm developing this for are a design-to-order company, so having standard BOM's isn't practical, and a big aspect of my app is generating custom BOM's for end products. This poses a real problem however, when allocating products to SO's it is the end item that is allocated, whereas the goods in are the raw materials. Hence I need to decrement the amount of input stock, together with incrementing the amount of finished goods in stock.

The easiest way of ahieving this would be to link to the Sage 'STOCK_TRAN' table and adding the relevant product changes. This table doesn't seem to have a primary key, so there shouldn't be a problem of data inconsistency.

However, the stock Movements all have an associiated 'TYPE', and I am unsure what code to use for these movements?

I would be most grateful if anyone could shed some light on what all the 'TYPE' codes mean, and which I should use, or if it is a bad idea to consider 'playing with fire', in this way - and I should just leave the tables alone, and not risk destroying the data integrity?!

Thanks, kdm3

Yes, totally

I have had a dig around the Sage ODBC before and its not something I would mess with without having a database diagram or an API of some sort. I believe you can register as a devloper with Sage but AFAIK it costs a bit £2-300.

You can import using CSVs into Sage which we currently do for invoices and credit notes so that may be enough for you?

To kinda clarify what I intend to do...

In Sage there is a table 'STOCK_TRAN' - which basically contains a list of stock transactions - including fields for stock-code, quantity, date, and a field marked 'TYPE', which references what type of transaction it is - GI=GoodsIn, GO=GoodsOut, GR=GoodsReturned and another AI which I think is an artificial entry, i.e. an imposed transaction. The GI and GO are created automatically when a Purchase Order or Sales Order is confirmed.

However, I am writing an app to control the manufacture/assembly of custom parts, and hence the built-in Sage BOM is not suitable, so I intended on bypassing it. But, because the goods out are the finished products, and the goods in are the raw materials, hence I want to write entries to the 'STOCK_TRANS' table, to essentially move the raw material to the finished goods - i.e. decrease the amount of raw materials by the amount required to make the required number of finished products, and then increase the number of finished products by the amount produced. Hence, finished stock can be dealt with as usual, and the records remain consistent.

Is this feasible, I don't want to find that inputting entries in this way adversely affects the integrity of data. As I said I don't think there is a PK in the table (because there isn't any unique field) - I think it is used as the basis of a query to return the SUM or total of each stock item - and hence this will identify the table. But as I also mentioned, I don't know what 'TYPE' code to use...

Any ideas/views gratiously received!

Just a quick note, we are members of the Sage Line 50 developers club and Sage will not support the Sage ODBC connection. I recently asked for a diagram showing the relations between tables and was told one was not available.

Hi Z0,

Can you tell me how you are importing your invoices and credit notes into Sage. I am using Sage Line 50 Accountant Plus.

I have been able to import customer, product, supplier.....but not invoices.

Any help will be well apreciated.

Thanks
Billy

Here is at least a basic overview of the relations between the Sage ODBC tables

http://www.sage.co.uk/kbase2/viewart...p?pubnum=W6433

This is a list of all the Sage ODBC knowledgebase articles:

http://www.sage.co.uk/kbase2/viewart...p?pubnum=W6670

-dan

I am quite new to Sage. As far as I am aware the ODBC driver provided by Sage Line 50 is read-only. So how can I insert bulk Invoices into Sage?

Is there some way of doing it using csv files????


Billy

We have writen an application for Sales Quotes, Orders and Invoices, as well as Purchase Orders, to be imported into Sage Line 50 / Line 100 or MMS. TripleXchange can either prompt you for missing customers, suppliers or products or just automatically create them using the information in the CSV, XML document or ODBC source and user defined defaults. This is all done via mappings and expressions so nearly anything is possible ! See
Tour of XML to Sage transaction creation for further details on how we are doing it.

Thanks for the post. I have managed to write an interface from our system that generates various csv files (Invoices, Credit notes, Products, Suppliers, Customers, Payments) for importing into Sage.

Any records from our system which are ever exported a second time prompts the user telling them that the record has already been exported for sage!

This seems to be sufficient for what we are doing.

Thanks

-------------------------------------------------------------------------------
The second of the above links goes nowhere. Can this be resolved please?

Rick

Probably not for a thread that is more than three years old...
I suggest you do some googling, and if you find more recent references post them back here.

I'd suggest you contact Sage Knowledgebase yourself....