Import to Sage Line 50 using Access

[mond007]

Hi

(Incidentally, I am a very good VBA programmer so I can handle the programming side).

I am currently writing a very simple and I mean a very simple access database with the following tables.:

Customers
Products
Invoices

I am generating the INVOICES in Access with a next available invoice No and then I would like to be able to make a call to a routine that will insert records into the INVOICE and INVOICE_ITEM table...

Does anybody have any information about how to achieve this or read an article that demonstrates this. ?

Thanks in Advance.
Kuldip Mond

 

[johndon68]

You can only write data into Sage 50 using the Sage Data Objects (SDO). These are only available to those who join the Sage Developers Programme: http://www.sage.co.uk/developers/sage_developers_programme.aspx

Alternatively, you could speak to an existing Sage Developer who could do the integration work for you...

John

 

[mond007]

Thanks John,

Does that mean you you are a SAGE developer

Ok .... but from what I read so far, I am hearing that CSV imports are also possible ?

Could I export a CSV file for every invoice (or append) as I create in Access and then import them all up at the end of the day ?

Hey thanks for the speedy reply. Fastest repsonse in the west...

Kuldip

 

[johndon68]

Thanks John,

Does that mean you you are a SAGE developer

Indeed so and was Senior Analyst Programmer at Sage before I left...

Ok .... but from what I read so far, I am hearing that CSV imports are also possible ?

Could I export a CSV file for every invoice (or append) as I create in Access and then import them all up at the end of the day ?

It is possible to import certain data via CSV file. Based on your original post, I assumed that you'd be looking to create invoices in the Invoicing module in Sage. If this is the case then the SDO is the only option as the import routines in Sage do not cater for this.

If, however, you are simply looking to import the financial results of the invoices then you can use CSV to import transactions to the Audit Trail in Sage.

John

 

[mond007]

Thanks John, (you certainly know your stuff).

Oh ok... I see... then maybe I should go into more detail.

If I create an Invoice for a Customer with Accounting Ref CUST01 and the total for the invoice is £100 then I simply want to create the Invoice in Sage Line 50 and post the Debit/Credit transaction ?
(I do not need the individual items in the invoice in Sage just the total).

I hope I am saying that correctly.

** IF ** we had to go the other route of SDO is it very expensive code to have written ?

Thanks again in advance. Kuldip

 

[johndon68]

If I create an Invoice for a Customer with Accounting Ref CUST01 and the total for the invoice is £100 then I simply want to create the Invoice in Sage Line 50 and post the Debit/Credit transaction ?

If you simply want to post the transaction so that it appears on the customer account then that can be achieved using the built in CSV import routine. You add the invoice as a line in the import file and it will be created via the import routine. The same is true if you need to create a Customer account, again, this can be achieved using the import routine.

If, however, you wanted to create an invoice in the Invoicing module so it could be printed out in Sage then CSV is a non starter.

Up until v2010 of Sage 50, the import formats were fixed so your file had to be in the exact format that Sage expected. With v2010, import files can be 'mapped' during the import routine so the format is no longer as important, you only need to make sure that the pertinent details are in your CSV file somewhere.

** IF ** we had to go the other route of SDO is it very expensive code to have written ?

Not necessarily

John

 

[johndon68]

Just to add to my last post, the following is a line from a file that would import an invoice:

"SI","ALANB001","4000",7,"22/04/2010","154","",8.33,"T1",1.25

Where the columns are as follows:

Transaction Type
Customer Reference
Nominal Code
Department
Date
Reference
Details
Net Amount
Tax Code
Tax Amount

John

 

[mond007]

Thanks again...

I shall ascertain tomorrow what exactly is required in Sage as you have kindly clarified. This will determin which course of action will be adopted.

I strongly think that they want the former of the two mentioned in your previous reply i.e. "post the transaction so that it appears on the customer account" as I recall seeing a screen that has a heading "Batch Customer Invoices". with the following fields

A/C
Date
Ref
Ex.Ref
Dept.
Project Ref
Details
Net
T/C
VAT

and distinctly recall then saying they want to do exactly what they do now by manually enter into the Batch Customer Invoices Screen but do this "automatically". Hmmm... I eagerly wait to see ...

So I trust it will be a posting rather then to create and then print an Invoice in SAGE via the Invoice Module.

Let me see what they say tomorrow.

Regards Kuldip.

 

[mond007]

Wow great minds think alike so they say as we both thought of the mapping at the same time. Freeky...

 

[johndon68]

The import routine does create the same entries that you get if you enter an invoice via the Batch Customer Invoice option with one exception (which may or may not be an issue).

When you use the Batch Invoice screen to enter an invoice, if you enter two items that have the same date and reference, Sage will 'group' the two items together as if they are two lines on a single invoice so when you look at the customer account you see a single invoice for the total amount although you can 'drill down' to the individual items.

However, if you enter exactly the same items using the import routine, you end up with 2 distinct invoices appearing on the customer account.

Again, this is something that can be avoided using the SDO as it allows you to exactly duplicate the entry as it would show had it been entered rather than imported.

John

 

[mond007]

Understood. Lets see what chips fall tomorrow.

I am suspecting that the issues mentijoned would not be a problem... as "2 distinct invoices appearing on the customer account" is what they want ... but you never know ...

Thx Kuldip

 

[jt196]

Funnily enough I'm right in the middle of this too. Thanks a bunch for the info about the invoice import.

What about importing invoice details? Come to think of it, is it necessary? We sell a range of products, but if I'm keeping track of them in my Access database, do I need to enter the information into the Sage invoice ledger?

OK this brings up another question, what about postage? How does that go on the invoice upon import? Does it just get totted up with the rest, or does there have to be some extra information as the postage is under a different nominal code, right?

 

[johndon68]

What about importing invoice details? Come to think of it, is it necessary? We sell a range of products, but if I'm keeping track of them in my Access database, do I need to enter the information into the Sage invoice ledger?

If products are being tracked in the Access database then there is no need to do so in Sage so importing just the transactions will be fine.

OK this brings up another question, what about postage? How does that go on the invoice upon import? Does it just get totted up with the rest, or does there have to be some extra information as the postage is under a different nominal code, right?

As for postage, if you have an invoice with a single item on it and postage, your import file would look something like this:

"SI","ALANB001","4000",7,"22/04/2010","154","",8.33,"T1",1.25
"SI","ALANB001","4905",7,"22/04/2010","154","",1.50,"T9",0

Where the second line contains the details for the postage. Be aware, as I mentioned earlier, if you were to manually such an invoice into Sage, the two lines would be grouped together so a single invoice would appear on the customer account.

If you use the import option, the two lines would show up as seperate invoices on the customer account.

John

 

[mond007]

John,

It is indeed the case that they only want to upload an Invoice total that will list againt their account. They currently manually upload using the "Batch Customer Invoices" screen so they simply want to automate this. So the CSV method will be surffice.

I am awaiting further details..

I was wondering is you would be available for pointers in case of any problems.

Thanks Kuldip

 

[johndon68]

I was wondering is you would be available for pointers in case of any problems.

I'll be around should the need arise.

John

 

[jt196]

Aaah that's brilliant. One other thing I was thinking about - what about the invoice numbers? I take it they're generated by the Sage program as you import them?

This brings up the question of how to reconcile the numbers between the access program and Sage. It would probably be quite dangerous to have an auto-number field in Access and just hope that the figures managed to match up correctly...

How have you managed this in the past? Is it a case of getting to know the Sage system so well that you know the numbers are going to line up, or is it just that importing the invoices directly into it (bypassing the Invoice module) doesn't generate invoice numbers - the data is merely dates, figures and customers.

Thanking you in advance.

 

[johndon68]

There is a reference column in the import (the one with 154 in my examples) so, you could populate this with the unique no from Access to maintan traceability.

John

 

[jt196]

aaah ok, thanks again!

I'm going to be moving all the invoices from Sage to Access so I guess I'll set up the autonumber as the invoice numbers imported from Sage. Then as they start going back across to Sage if that Reference is the Sage autonumber then it doesn't need to coincide as long as it's in there. At the moment the reference is the external PO number, but I guess it doesn't matter too much if we move the system around a little...

 

[johndon68]

Just to be absolutely clear, the import routine does not automatically assign invoice numbers, if you do not populate the Reference field (which is where the invoice number goes if entered in the Sage UI), then the import routine will not import it for you.

John

 

[jt196]

Aaaaaaah now that's clear. I was unsure as to what the reference was. There's a field in the invoices in Sage where you can place the PO number of the customer - so I just assumed it was that. So thanks for that.

Actually what about the 'order' field - don't see that in there...

J

 

[johndon68]

There's a field in the invoices in Sage where you can place the PO number of the customer

That's when you enter an invoice in the Invoicing Module - if you enter an invoice using the Customer Batch Invoice routine (which is what the import routine effectively duplicates) that field is not available.

John

 

[mond007]

I was wondering

When i create the database I am having a pretty major decision to make.

Owing to the design of the tables. I have one of two ways I can go.

I am creating a structure for shoe company and they have lots of shoe designs and many colours within a particular design of shoe.

a) I could have product_table (holds shoe designs) and a colours table and that way I can link colours to shoes. This will make the code probably a little more complicated than need be :

Product_Table
pro_style_code
pro_description
pro_usual_carton_qty
pro_default_unit_price

Colour_Table
col_style_code
col_colour

b) but the other option is have a Product_table and add an extra column to the table to store the colour. That was I will have 10 shoe lines for the same product_code with a different colour for each if you see what I mean.

Product_Table
pro_style_code
pro_colour_code
pro_description
pro_usual_carton_qty
pro_default_unit_price

What do you think ?

Thanks in advance Kuldip

 

[johndon68]

Personally I'd go for the second option as it allows for a bit more expandability in the long term.

John

 

[mond007]

Hi John,

I was just wondering if you might like to comment on the below specs...

www specialistwebdesign.com/access_invoice_stock.pdf
(replace space with dot)

Firstly, I am wondering what the overhall project would be - chargeable to a client.

If you feel you can perhaps comment on the database structure...
Its like a hybrid between an Invoicing system and Stock Management System.

This started out life as a Invoicing system with teh SAGE update but the client soon realised that they wanted a Stock Management System built into it so that when they invoice a buyer the stock level is adjusted.

If time is short just look at the last 4 pages of screen shots which are very draft indeed.

Thanks in Advance Kuldip

 

[johndon68]

I've sent you a PM.

John

 

[Deleted member 74014]

I am new to sage line 50. why do i have to clear the balances on my debtores and creditors control accounts after inputting opening balances of my suppliers and customers. can i enter opening balances of nominal ledger codes and bank accounts using journal

 

[johndon68]

I am new to sage line 50. why do i have to clear the balances on my debtores and creditors control accounts after inputting opening balances of my suppliers and customers.

Have a look at this thread: http://sageforum.co.uk/viewtopic.php?t=12981 where I discussed this process at length.

can i enter opening balances of nominal ledger codes and bank accounts using journal

Yes you can.

John

 

[Deleted member 74014]

if i havealread entered supplier/customer invoices for current year, how do i perfom the task of opening balances fo outstanding invoices from previous year and reversing them

 

[johndon68]

There are examples in the thread I linked to but, once you have entered the customer and supplier opening balances, you enter journals to clear the Debtors Control, Creditors Control ans Suspense Nominals. You then enter the nominal opening trial balance.

John

 

[Deleted member 74014]

thank you John. I posted some cash and carry purchases as bank payments as they were paid by visa debit. when doing my vat ,these hesebalances are not reconciled. the vat analysis shows N N on these entries

 

[johndon68]

thank you John. I posted some cash and carry purchases as bank payments as they were paid by visa debit. when doing my vat ,these hesebalances are not reconciled. the vat analysis shows N N on these entries

I take it these were entered by going to Bank -> Payments in which case, what tax code did you use?

John

 

[Deleted member 74014]

Hi,John . The problem is solved.The entries have been reconciled in the Vat return although the abalysis report shows something else.
thanks

 

[Naughty Vend]

..or you could have bought Sage Link which would have created all the tables for you, then adapted the output to import to your application...

I've been using it for a few years now with Actinic.

 

[johndon68]

..or you could have bought Sage Link which would have created all the tables for you, then adapted the output to import to your application...

I've been using it for a few years now with Actinic.

I'm not sure what that has to do with anything in this thread :|

John

 

[johndon68]

Hi,John . The problem is solved.The entries have been reconciled in the Vat return although the abalysis report shows something else.
thanks

Glad to hear you got it sorted.

John

 

[Naughty Vend]

I'm not sure what that has to do with anything in this thread :|

John

Maybe I missed the point but wasn't the OP trying to created a system which created an MSA record from Sage data, isn't that what Actinic Link for Sage Line 50 does already and all you'd need to do is change the source paths / format?

Anyway, he has sorted it...

 

[johndon68]

The OP was asking about creating records in Sage from an Access database, the other poster added an additional issue neither of which would be solved using the Actinic link.

John