Results 1 to 12 of 12

Thread: Sage Line 50

  1. #1
    Join Date
    Jul 2004
    Posts
    34

    Question Sage Line 50

    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

  2. #2
    Join Date
    Jul 2004
    Posts
    14
    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?

  3. #3
    Join Date
    Jul 2004
    Posts
    34

    What I am thinking of doing?!

    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!

  4. #4
    Join Date
    Jan 2005
    Posts
    1

    Sage Developer Program

    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.

  5. #5
    Join Date
    Jul 2002
    Posts
    4

    z0

    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

  6. #6
    Join Date
    Jan 2005
    Posts
    2
    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

  7. #7
    Join Date
    Jul 2002
    Posts
    4
    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

  8. #8
    Join Date
    Feb 2005
    Posts
    2
    Quote Originally Posted by billy
    Can you tell me how you are importing your invoices and credit notes into Sage. I am using Sage Line 50 Accountant Plus.
    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.

  9. #9
    Join Date
    Jul 2002
    Posts
    4
    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

  10. #10
    Join Date
    Aug 2008
    Posts
    1

    Dead Link in this previous post

    Quote Originally Posted by dbriley
    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
    -------------------------------------------------------------------------------
    The second of the above links goes nowhere. Can this be resolved please?

    Rick

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Quote Originally Posted by Rick_3021
    -------------------------------------------------------------------------------
    The second of the above links goes nowhere. Can this be resolved please?

    Rick
    I'd suggest you contact Sage Knowledgebase yourself....
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •