Sage Line 50

[kdm3]

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 achieving 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

 

[Pat Hartman]

It is dangerous to update tables in a complicated application unless you know what you are doing. You should be searching the Sage documentation and their website for your answere. Why would Access people know anything about what transaction types a Sage database supports.

FYI, Access cannot update an ODBC table unless that table has a primary key. So, unless you can add pk's to the Sage tables, you won't be able to update them. Access will allow you to create psuedo keys to get around this problem but do so at your own risk. If there really is no unique combination of fields that you can define as a pk, you are likely to corrupt the Sage table if you attempt to update it.

 

[kdm3]

In Response...

Hi,

A little more info.
I believe that the stock_tran table is a repository for data (it contains all stock movements in and out) and as such it itself does not really do anything but act as a data store. However, when all the movements in and out are summed up, the current amount of each part in stock are shown - with the stock_code being the reference, identifying the amount of that particular part.

Hence, the info. stored in the table isn't linked to anything else (hence no need for a primary key field), and as long as the stock_code, quantity change, and type of movement (AI - Adjustment In, or AO - Adjustment Out) are included, that should be all the info. required?