Sage Line 50 Zen export module

[Calljj]

The reasoning behind it, is to seperate what you charge for something and what it actually costs.

So for example you sold 3 products (at £1 each) online and charge the customer £3 postage, then your SALES would need to show that the total sale for that order was £6, or each product was sold for £1 + the postage, however you want to show it.

However your COSTS, (nominal 7501 for postage) for that order might have been 50p for each product sold, and the postage might have been only £1.50.

The reason for adding the postage sale price to the products is because, it is not a "stock item" nor is it a "product" it is just an additional charge during SALES that is applied to a product.

[[email protected]]

Hi everyone i am getting this error it is creating the files but they are empty has any one got any suggestion many thanks in advance

Sage file started 2010/06/22 22:56:07
Data file - /var/www/vhosts/ticmobiles.com/httpdocs/sage/sage_stock_transaction.csv
Data file - /var/www/vhosts/ticmobiles.com/httpdocs/sage/sage_sales_transaction.csv
1146 Table 'ticmobil_zc1.sage_update' doesn't exist
in:
[SELECT * FROM sage_update ]

[Calljj]

sounds like you need to run the sql script to add the sage_update table, or the table prefix (the table name part before 'sage_update') is incorrect.

J

[DigitalShadow]

Can't get it to work with Sage 50 Accounts 2010

The outputted stockforsage file doesn't have stock quantity.

The sage_stock_transaction file doesn't link the sale to the product

[Mammoth]

Did the issue with following error ever get resolved.....nothing definitive on the forum...
Error occurs when running the report from admin.

i.e.
1146 Table 'mammoth9_zc1.sage_update' doesn't exist

(Where mammoth9_zc1. is my database name)


What is required?
Correction to the SQL script?
Correction in the sage.php?

Any assistance gratefully received.

[Calljj]

have you run the sql script to create the database?
Can you check the database does exist?

Sounds like you haven't run the script, or that the script was not allowed to add tables due to server permissions.

[Mammoth]

have you run the sql script to create the database?
Can you check the database does exist?

Sounds like you haven't run the script, or that the script was not allowed to add tables due to server permissions.

Hi - Ran the script, claims script ran ok with no errors.....

------------------------
Here is the error when I try to export the reports from admin...

Sage file started 2011/01/18 05:57:16
Data file - /home/mammoth9/public_html/woodandknifesupply.com/sage/sage_stock_transaction.csv
Data file - /home/mammoth9/public_html/woodandknifesupply.com/sage/sage_sales_transaction.csv
1146 Table 'mammoth9_zc1.sage_update' doesn't exist
in:
[SELECT * FROM sage_update ]

-------------------------

• Tried it several times i.e. install and uninstall scripts....
  
• I've made Db script changes over the weekend for other mods....they executed ok...
  
• I am no alone with this......it appears that many others receive the same error where the database name is referred to as '[yourDbname].sage_update'
  
• Essentially the error always seems to be the name of the sage table prefixed with the name of your sql database...
  
• Is this error correct i.e.
• 1146 Table 'mammoth9_zc1.sage_update' doesn't exist
  
  does indeed mean table sage_update does not exist in database masmmoth9_zc1...?
  OR
  does the error mean table mammoth9_zc1.sage_update does not exist (As per the actual error)....
  
  Misleading is it not!
  
  Mind you a table name with a full stop in the naming is interesting....
  
  The script states 'CREATE TABLE sage_update'

When I try the following command in isolation....
CREATE TABLE sage_update
(last_update datetime);

I receive the message
ERROR: Failed 1
ERROR: Cannot create table sage_update because it already exists
Note: 1 statements ignored. See "upgrade_exceptions" table for additional details.



I am at a loss....I am no Db expert but there has to be a problem with table permissions or similar in the script, that makes a table that exist appear to not exist.
However the SAGE addon appears to ba looking for a table with a different name.

Help appreciated....


For reference below is the script...
CREATE TABLE sage_update
(last_update datetime);

Insert into sage_update Values ('0000-00-00 00:00:00');

SET @t7=0;
SELECT (@t7:=configuration_group_id) as t7
FROM configuration_group
WHERE configuration_group_title= 'SAGE';
DELETE FROM configuration WHERE configuration_group_id = @t7;
DELETE FROM configuration_group WHERE configuration_group_id = @t7;

INSERT INTO configuration_group VALUES (NULL, 'Sage Configuration', 'Set Sage Options', '1', '1');
UPDATE configuration_group SET sort_order = last_insert_id() WHERE configuration_group_id = last_insert_id();

SET @t7=0;
SELECT (@t7:=configuration_group_id) as t7
FROM configuration_group
WHERE configuration_group_title= 'Sage Configuration';

INSERT INTO configuration (configuration_id, configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added, use_function, set_function) VALUES
(NULL, 'Output File Name', 'SAGE_OUTPUT_FILENAME', 'sage_stock_transaction.csv', 'Set the name of your sage stock output file', @t7, 1, NOW(), NULL, NULL),
(NULL, 'Output File Name', 'SAGE_OUTPUT_FILENAME2', 'sage_sales_transaction.csv', 'Set the name of your sage sales output file', @t7, 2, NOW(), NULL, NULL),
(NULL, 'Output Directory', 'SAGE_DIRECTORY', 'sage/', 'Set the name of your file output directory', @t7, 3, NOW(), NULL, NULL),
(NULL, 'Start Date', 'SAGE_STARTSDATE','2007-01-01', 'Set the start date of your output file - set to null to use last update, or date format yyyy-mm-dd', @t7, 4, NOW(), NULL, NULL),
(NULL, 'End Date', 'SAGE_ENDSDATE', 'null', 'Set the end date of your output file, or set to null for now - date format yyyy-mm-dd', @t7, 5, NOW(), NULL, NULL),
(NULL, 'Order Status', 'SAGE_STATUS',' 4', 'Set the Complete Status Number of your Order Processing to process all below this level', @t7, 6, NOW(), NULL, NULL),
(NULL, 'Transaction Code', 'SAGE_TX','GO', 'Stock Tranascation Code for Sage', @t7, 7, NOW(), NULL, NULL),
(NULL, 'Sales Code', 'SAGE_TX2','CR', 'Sales Tranascation Code for Sage', @t7, 8, NOW(), NULL, NULL),
(NULL, 'Paypal/Credit Card Nominal Code', 'SAGE_PAYPAL', '1205', 'Set the Sage Nominal code for Paypal/Credit Cards e.t.c', @t7, 9, NOW(), NULL, NULL),
(NULL, 'Bank/Cheque Nominal Code', 'SAGE_CHEQ', '1200', 'Set the Sage Nominal code for Cheques/Bank transfers e.t.c', @t7, 10, NOW(), NULL, NULL);

[Calljj]

ERROR: Cannot create table sage_update because it already exists

Ok, so from your post, the table has been written into the database.

In the sage.php file add the following after line 66

echo $sage_update_query;

so it looks like below

$sage_update_query = "SELECT *
FROM " . sage_update  . " ";
echo $sage_update_query;
$ordereds = $db->Execute($sage_update_query);

and post the result

As a side note, you would want to change the lines 230 and 234 to reflect the UK Vat of 20%, byt changing 1.175 to 1.2

[Mammoth]

Ok, so from your post, the table has been written into the database.

In the sage.php file add the following after line 66

echo $sage_update_query;

so it looks like below

$sage_update_query = "SELECT *
FROM " . sage_update  . " ";
echo $sage_update_query;
$ordereds = $db->Execute($sage_update_query);

and post the result

As a side note, you would want to change the lines 230 and 234 to reflect the UK Vat of 20%, byt changing 1.175 to 1.2

Thanks for that
To confirm, added the following line to sage.php after line 66
(Line 67=) echo $sage_update_query;


When I run Admin > Tools > Sage Export > Step 1: Click [HERE] to create your data file
Get the following message.

------------------------------------------
Sage file started 2011/01/19 05:23:37
Data file - /home/mammoth9/public_html/woodandknifesupply.com/sage/sage_stock_transaction.csv
Data file - /home/mammoth9/public_html/woodandknifesupply.com/sage/sage_sales_transaction.csv
SELECT * FROM sage_update
1146 Table 'mammoth9_zc1.sage_update' doesn't exist
in:
[SELECT * FROM sage_update ]
-----------------------------------------

Thanks for the help so far, appreciate someone looking at this, once running the export feature will really help

[Calljj]

Apologies for delay in response.

Could you give me the full table prefix that you are using,
i.e output showing "mammoth9_zc1.sage_update"
what should it read?
"mammoth9_zc1_sage_update"?

Sorry, but i've never used table prefixes on any of my installs, so need an example of where i need to get to!