Migrating from Sage50

[pgr]

@brian_pond

SET @TableName = ‘tabCustomer’; SELECT COLUMN_NAME AS Column_Name ,DATA_TYPE AS SQL_Data_Type ,CASE WHEN CustomField.label is NOT NULL THEN 1 ELSE 0 END AS Is_Custom ,IFNULL(tabDocField.label, CustomField.label) AS DocField_Label ,IFNULL(tabDocField.fieldtype, CustomField.fieldtype) AS DocField_FieldType FROM information_schema.COLUMNS LEFT JOIN tabDocField ON tabDocField.parent = TRIM(LEADING ‘tab’ FROM TABLE_NAME ) AND tabDocField.fieldname = COLUMN_NAME LEFT JOIN – SELECT * FROM tabCustom Field AS CustomField ON CustomField.fieldname = COLUMN_NAME AND CONCAT(‘tab’, REPLACE(CustomField.dt, ’ ', ‘’)) = TABLE_NAME WHERE TABLE_NAME = @TableName ORDER BY COLUMN_NAME;

great @brian_pond thank you very much!

[pgr]

Ok @casesolved-co-uk

It seems it finally worked with all the good help here.

I discovered, that the status fields are not relevant for the import. Undependent of what I import, it is either Draft or Submitted (based on the checkmark in the import “Submit after import”).

Furthermore it is necessary to use this order:

1. All master data like customers, cost centers etc.
2. Purchase Invoice
3. Payment
4. Bank Transaction

The Bank Transaction can establish the association to the Payment on import, if you know what @casesolved-co-uk s told me with the primary key ID (technical name).

So if I don’t let the Payment generate it’s own ID, but import the legacy ID, which gets the primary key, then the dependent records (in my case Bank Transaction) can simply be associated to it.

→ So that is easy so far - at least in general

But there’s still one thing, I am not really sure about:
But it seems to be more a generic ERPNext Accounting question than regarding the import… but perhaps not?

When importing the Payments (it’s the same as when I create it manually out of the invoice), then the Invoice gets State = Paid (without any plausibilization, if the payment is backed).

I am wondering about that a bit, as I understood the Payment is the connection between Invoice and Bank Transaction… but when there is no associationed Bank Transaction available, why is the Invoice getting Paid automatically?

I noticed (by full-export), that the only difference between a Payment, which has a backed Bank Transaction and a Payment, that has none, is the Clearance Date in the Payment.

So for the Unreconciled Bank Transactions there is the Reconciliation Tool, which helps me to map all the Bank Transaction.

Is there somewhere another Report, to get an overview over the Payment Entries, which don’t have fully allocated Bank Transactions AND are no Cash Payments (Mode of Payment)?

[casesolved-co-uk]

I’m not really sure why the bank transactions are not just imported as Payment Entries, but like you say, Payment Entries do not need a bank transaction. A Payment Entry is how you mark an Invoice as paid when the allocated amount matches the invoice total.

Suspect Accounts Receivable/Payable report is what you’re looking for… unpaid invoices.

[francis1]

So the issue is on Sage cloud failing to export not on ERPNext import and exporting only those few fileds in doable in sage 50

[pgr]

sorry for iteration based on the consense

[envero]

@casesolved-co-uk Are you available to do a data migration from Sage50 (US) to ERPNext? I used the email form on your website but wasn’t sure it it went through. Thanks in advance!

[casesolved-co-uk]

Just so that everyone is aware, Sage 50 UK and US editions are completely different from what I understand. The UK edition is cloud-based and has an API. The US edition used to be called Peachtree, is desktop software (though there is a remote feature) and you have to export each table to CSV separately.