Make sure its UTF-8 and not UTF-8 BOM. Its a long shot but BOM adds hidden characters, and typically once you hit an error it blows the import. There should be an import log also in FA
Does the import start and then stop later or it doesn’t start at all?
Also was mistaken, errors should show up in the PHP error log. Make sure these two lines are enabled in import_transactions.php
error_reporting(E_ALL);
ini_set("display_errors", "on");
Can you provide what version of PHP you’re using. All of this info is relevant if going to proceed with attempting SuiteCRM integration
This appears to be a good discussion also:
https://frontaccounting.com/punbb/viewtopic.php?id=10395
LOL thanks @AeroDonkey thats me on the thread from frontaccounting. Basically when I press the import button nothing happens. Ive yried on a bunch of PHP versions back to 7.4 nothing seems to help.
I just installed a fresh copy in another instance. Poking through it now
is your db empty as far as records. As in could you provide a SQL file for import so the structure my test db is correct based on the data you provided from your post on front accounting assuming the below csv data is also yours. This way we are tackling the issue from a common base
customer_id,branch_No,reference,date,payment_id,sales_type_name,dimension_id,dimension2_id,item_code,item_description,quantity,unit,price,discountpercentage,freightcost,delfrom,deldate,delto,deladdress,contactphone,email,custref,shipvia,comments,ex_rate
1,1,009/2017,2017/12/20,4,Retail,,,202,Maintenance,2,Each,35,0,,DEF,2017/12/20,Mr A,1 Abe Street Glasgow G32 FHT,984578545,customer@example.in,,,,
1,1,010/2017,2017/12/21,4,Retail,,,202,Maintenance,2,Each,15,0,,DEF,2017/12/21,Mr A,1 Abe Street Glasgow G32 FHT,984578545,customer@example.in,,,,
Minus user data and company config etc… Make sure no confidential info is preset
another thing to do for sure is to enable debugging in the config.php. Mine is set to 2
$go_debug = 2; // set to 1 for basic debugging, or 2 to see also backtrace after failure.
Hmmmm, Disregarding the import error issues, replicating data FROM SuiteCRM to Front Accounting may be viable using an after_save logic hook by directly inserting the data in the fa database, this will need to exist for each module that you are trying to replicate from and is kind of crude, if it even works. I’ll test when I have some more time, given nobody beats me to it. 
Rough guesstimate (untested):
<?php
if (!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
class InsertIntoFrontAccounting {
public function afterSave($bean, $event, $arguments) {
// Im assuming that the fields names are the same in both the SuiteCRM module you are using or have created and Front Accounting
$data = [
':order_no' => $bean->order_no,
':trans_type' => $bean->trans_type,
':version' => $bean->version,
':type' => $bean->type,
':debtor_no' => $bean->debtor_no,
':branch_code' => $bean->branch_code,
':reference' => $bean->reference,
':customer_ref' => $bean->customer_ref,
':comments' => $bean->comments,
':ord_date' => $bean->ord_date,
':order_type' => $bean->order_type,
':ship_via' => $bean->ship_via,
// For address and email, you need to add some logic for grabbing the related email from whatever module youre using for customers. Address may require concatenation, not sure.
':delivery_address' => '', //Concatenate address to single field from SuiteCRM if required
':contact_phone' => $bean->phone_office,
':contact_email' => '', //Haven't thought about this part yet
':deliver_to' => $bean->name, // or $bean->first_name . ', ' . $bean->last_name
':freight_cost' => $bean->freight_cost,
':from_stk_loc' => $bean->from_stk_loc,
':delivery_date' => $bean->delivery_date,
':payment_terms' => $bean->payment_terms,
':total' => $bean->total,
':prep_amount' => $bean->prep_amount,
':alloc' => $bean->alloc,
];
$this->insertData($data);
}
protected function retrieveEmailAddress($email) {
//logic here
}
protected function retrieveConcatAddress($address) {
//logic here
}
protected function insertData($data) {
$dsn = 'mysql:host=localhost;dbname=fa;charset=utf8';
$user = 'root';
$password = '********'; // Use your actual password here
try {
$pdo = new PDO($dsn, $user, $password);
$sql = "INSERT INTO 0_sales_orders (order_no, trans_type, version, type, debtor_no, branch_code, reference, customer_ref, comments, ord_date, order_type, ship_via, delivery_address, contact_phone, contact_email, deliver_to, freight_cost, from_stk_loc, delivery_date, payment_terms, total, prep_amount, alloc) VALUES (:order_no, :trans_type, :version, :type, :debtor_no, :branch_code, :reference, :customer_ref, :comments, :ord_date, :order_type, :ship_via, :delivery_address, :contact_phone, :contact_email, :deliver_to, :freight_cost, :from_stk_loc, :delivery_date, :payment_terms, :total, :prep_amount, :alloc)";
$stmt = $pdo->prepare($sql);
$stmt->execute($data);
} catch (PDOException $e) {
// Error check
error_log("PDO Error: " . $e->getMessage());
}
}
}
Bear in mind this is very conceptual. An entry point will need to be registered as per usual. And Im assuming both SuiteCRM and Front Accounting are running on the same server. Adjust MySQL host as necessary. Also is a one way solution so keep that in mind.
Edit: I suppose I should include that this is for replicating to the 0_sales_orders table in FA
That’s so cool! Ok you’ve inspired me to put this back on the front burner and get my import working from QB.
Does it have to be an SQL file for import or a CSV? Maybe that’s where I’m going wrong, I’m tyring to import a CSV file like the demo file
No, the sql file exported from phpmyadmin or workbench would allow me to import your exact table structure into my instance to assist with debugging. Given theres no confidential info in them.
Ah OK, sorry I misunderstood. Thanks for the tip about the extra logging. I think I may be on to something. It’s not finding my customer ID. I assumed the customer_id column in the csv was the debtor number. But maybe that’s a wrong assumption. What is the customer_id in the import file?
Likely the field named customer_ref. Im out town. I dont know right off hand if its the id from the customer table or another reference. Ill remote into my machine tonight and take a peek
From the development page on manual import of customers:
CustomerID is defined as: Your Reference from the old accounting system, unique.
https://frontaccounting.com/fawiki/index.php?n=Devel.ManualImportOfCustomers
Thanks Chris, I’m not having an issue importing customers and contacts, it’s the transactions I’m struggling with. I think I’m going to have to resort to just importing them directly in the database. The only issue is each transaction creates GL entries and it would be nice to use the import feature as these get created for you. I’ve spend hours trying to get it to work even on a single transaction, so I’m kind of at the point of diminishing returns, I’ll just have to bite the bullet and create all the invoices, payments and GL transactions manually in the DB.
Paul, for a person like you who is already familiarized with SuiteCRM, working with beans, etc, I think any significant migration should be done with a bespoke PHP importer, not from the UI. It’s a bit more work, but eventually it will save you time, and you will get a much better database with all the relationships, security groups, etc in place.
This kind of thing, although you won’t need most of that, just bits and pieces.
Having full control over the process also helps
- doing other migrations for other companies
- handling subsequent migrations, if needed: extra data just coming in, making sure you can merge it into existing data without duplicates, etc
2 Likes