ERP Systems

[wakabayashi]

I just wanted to add: If you are thinking of ERP, yout try out advanced stock management. It offers some features, which I was looking for. Probably I will change it a bit for our needs and then I can live with it.

[30knees]

@wakabayashi But it's extremely buggy, isn't it? I think it messed up stuff when I first tried it out.

[wakabayashi]

Yes it is! But actually this shouldn't prevent us, from using it. It's time to improve it. That's was general problem of ps. Many good ideas and functions, but too many bugs. I hope we can improve here.

I have already reported some bugs: https://github.com/thirtybees/thirtybees/issues

[nickon]

Better late then never. I talked to the developer of the ERP system on connecting it to tb and now we are at the stage of connecting. He asked me where some tables and since I am not a developer I thought I ask here. The goal to make the intergration as seamless as possible. These are the data he needs to extract from tb:

ORDERREFERENCE BILLINGADDRESS BILLINGCITY BILLINGCOMPANYACTIVITY BILLINGCOMPANYNAME BILLINGCOUNTRY BILLINGFIRSTNAME BILLINGLASTNAME BILLINGPHONE BILLINGPOSTALCODE BILLINGREGION BILLINGTAXNUMBER BILLINGTAXOFFICE COMMENTS CREATEDAT DISCOUNT MODIFIEDDATE MEMBEREMAIL MEMBERID BILLINGCOST PAYMENTMETHOD SHIPPINGADDRESS SHIPPINGCITY SHIPPINGCOST SHIPPINGCOUNTRY SHIPPINGFIRSTNAME SHIPPINGLASTNAME SHIPPINGMETHOD SHIPPINGPHONE SHIPPINGPOSTALCODE SHIPPINGREGION STATUS TOTALCOST TRACKINGCODE

Can anyone point me to the tables these data exist?

[30knees]

@nickon You could download eg https://www.heidisql.com to check for the tables.

What system is it?

[Chandra]

Another vote for ErpNext - it is intuitive, rich in functionality, has a good community and is truly open source. I have been using it for a while now though I have not integrated it with TB except for export-import of contacts through extracts.

There has been discussion on Erpnext forums to integrate with a good ecommerce system and it would be great to join forces to build a Erpnext-TB ecosystem that will be compelling to any business.

[nickon]

Hi @mdekker Orderreference, Billingaddress, Billingcity, Billingcompanyactivity, Billingcompanyname, Billingcountry, Billingfirstname ,Billinglastname, Billingphone, Billingpostalcode, Billingregion, Billingtaxnumber, Billingtaxoffice, Comments, Createdate, Discount, Modifieddate, Memberemail, Memberid, Billingcost, Paymentmethod, Shippingaddress, Shippingcity, Shippingcost, Shippingcountry, Shippingfirstname, Shippinglastname, Shippingmethod, Shippingphone, Shippingpostalcode, Shippingregion, Status, Totalcost, Trackingcode

Hope this is better. We need the corresponding data (not the same names)

[nickon]

@mdekker, any progress on this ?

Kind Regards

[Suprazz]

I vote for erpnext too!

[nickon]

@mdekker hi,

So after a lot of digging I found what needs to be exported from the database. Question is how do I export it? Is there a query command to export all order data (eg what is displayed in the BO in the order detail) Should something else be used ?

Kind Regards Nick

[Pedalman]

I suggest to have a look at Datakick module or to contact Petr from Datakick (search forum). This module seems to shine in export and import processes and data manipulation of fields and tables.

[nickon]

@Pedalman I can't use a module for that. I need the query because the erp system will execute it on the db to get the data it needs.

I already found this: SELECT d.idorder, os.name AS tila, d.productname, d.productreference, d.productprice, d.productquantity, o.payment, o.dateupd, g.lastname, g.firstname, g.idcustomer, ad.address1, ad.address2, ad.postcode, ad.city, ad.other, ad.phone, ad.phonemobile, ai.address1, ai.address2, ai.postcode, ai.city, ai.other, ai.phone, ai.phonemobile, g.email FROM psorderdetail d LEFT JOIN psorders o ON (d.idorder = o.idorder) LEFT JOIN psaddress ad ON (o.idaddressdelivery = ad.idaddress) LEFT JOIN psaddress ai ON (o.idaddressinvoice = ai.idaddress) LEFT JOIN psstockavailable s ON (d.productid = s.idproduct) LEFT JOIN pscustomer g ON (o.idcustomer = g.idcustomer) LEFT JOIN psgrouplang gl ON (g.iddefaultgroup = gl.idgroup) AND gl.name LIKE 'piiri%' LEFT JOIN psorderstatelang os ON (o.currentstate = os.idorderstate) WHERE os.id_lang = 1

But it is missing some data. I am sure @datakick knows more on this What I basicly need is all data that is displayed in the Backoffice on the order detail page

[datakick]

@nickon you can install my module and create list that matches your criteria. Once you are satisfied, you can add

&debug-mode=1

the the url and my module will tell you the query it used to retrieve the data. That could help you a bit.

[nickon]

Thanks @datakick nice tip. But what is the direct url to the list ?

[datakick]

@nickon there isn't one. You need to install my module, and create a new list from scratch. Or you can try it on my demo account