by Business Fundamentals

SuiteCRM BI-Directional link to Xero
Automated BI-DIRECTIONAL synch between SuiteCRM and Xero, synch most recent record. Accounts, Contacts, Invoice and Products, connect and forget. Synch based on your criteria. This module also gives you the ability to manually update Accounts/Contacts/Invoice records to/from Xero

Cancel at any time!
Free Trial

#3409 - Installation Advise - SQL Import + internal SuiteCRM URL

Closed Installation created by FileSphere 8 months ago

Item 1 - Running repairSugarDB.sql in Mariadb

  1. Installed the Plugin & downloaded the "repairSugarDB.sql
  2. Connected to putty

mysql -u root -pXXXXXXXXX bitnami_suitecrm < repairSugarDB.sql

ERROR 1064 (42000) at line 7: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '
<!
<!
    <!
    
                        


            <sp...' at line 1

=======================================

This is using docker images:-

  • docker.io/bitnami/mariadb:"10.3-debian-10
  • docker.io/bitnami/suitecrm:7-debian-10

SuiteCRM
Version 7.11.15
Sugar Version 6.5.25 (Build 344)

Mariadb
Server Version: 10.3.23-MariaDB

I have below attached the .sql file so you can review & advise changes so it is compatible with MariaDB.

https://drive.google.com/file/d/1oG-0pSgwlwDvQag9gBQhIy2V69cr_v_1/view?usp=sharing

**
Item 2 - SuiteCRM URL only available internally but has internet access**

To minimize hacking etc the SuiteCRM URL is only available internally but the server has full access to the internet is this going to cause an issue with the plugins functionality + the functionality that coudl be affected?

(If this is going to cause issues not having the SuiteCRM URL being available externally can you advise what Xero external IP's might need access so I can allow them access at the firewall level)

  1. MartyP member avatar

    Business Fundamentals Provider Affiliate

    8 months ago

    Can you tell me why you're not running the SuiteCRM -> Admin -> Repair -> Quick Repair and Rebuild routine (scroll to the bottom of the screen and implement changes by clicking on the action button there)? If you have tried running the routine and have had an error could you tell me what that is please?

    Thanks for your time and patience

    • FileSphere member avatar

      FileSphere

      8 months ago

      Thank you I have run SuiteCRM --> Admin -< Repair --> Quick Repair and rebuild & no errors :-)

  2. MartyP member avatar

    Business Fundamentals Provider Affiliate

    8 months ago

    RE your second question; I'm not sure, I haven't ever run/installed it with the settings you have outlined HOWEVER it doesn't 'sound' as if it would work. Xero has to have access to your URL to validate your OAUTH 2 credentials. If, on installing the module and attempting the Xero Configuration Settings -> Actions menu -> Connect to Xero action you have problems (e.g. no connection settings found error) I'd suggest looking in your firewall logs to see if in fact an IP address from Xero has been blocked. Unfortunately I can't help you with what those IP addresses might be either, you'd need to contact Xero? :=[

    RE your sql, I have run up a test DB server (both MariaDB and MySQL) to test and both have no problem running the following required SQL:

    ALTER TABLE aos_products_quotes add COLUMN line_item_id char(36) NULL;

    Hope this is of some help to you, thanks for your time and patience

    • FileSphere member avatar

      FileSphere

      8 months ago

      Thank you I did run the below line

      ALTER TABLE aos_products_quotes add COLUMN line_item_id char(36) NULL;

      I did also follow steps:-

      5 Create your Xero Configuration settings. After saving, click on the Connect to Xero button in the Actions menu, Detail View

      See Screenshot:
      https://drive.google.com/file/d/1l_RFVvEvGCMDQFbkpoqa7ZcdGJ5G_5U9/view?usp=sharing
      (Received "Xero app connected successfully.")

      (Noticed I didn't see Invoices in the approval list)

      6 Go to the Admin -> Repair section of SuiteCRM and run a QRR (Quick Repair and Rebuild) When that process has completed, scroll down the page. You should see a section that asks you to run some SQL, you MUST complete this step for the Invoices/Products functionality to work

      Doing the below:-

      1) Click "Invoices" & open invoices
      2) Click "Actions" --> "Create an Invoice in Xero"

      Got this screenshot:-
      https://drive.google.com/file/d/1PoqdGrQ0oH0u6imugb-AzFoTeEYVuVNP/view?usp=sharing

      I think this should still work even if the SuiteCRM website is not public facing but please review & let me know.

      Please review and let me know what other information I can send.

    • FileSphere member avatar

      FileSphere

      8 months ago

      Sorry I meant I click "Invoices" & opened a single invoice.

    • FileSphere member avatar

      FileSphere

      8 months ago

      I noticed my invoice had no "Line Items" so I added one that matched the Xero item but got this new error message:-

      Screenshot attached:-
      https://drive.google.com/file/d/1eEiyuyfeu-_4g-8qeG9WqnFigdJzYWtH/view?usp=sharing

    • FileSphere member avatar

      FileSphere

      8 months ago

      Found out can attache it here instead :-)

      SuiteCRMXero-Screenshot2.png

  3. MartyP member avatar

    Business Fundamentals Provider Affiliate

    8 months ago

    Sorry, the SQL only requires the one line above, the extraneous comments can be stripped out..

    • FileSphere member avatar

      FileSphere

      8 months ago

      Thank you for all your assistance it is now successfully working & we are continuing to trail your software.

      • Added Contact Successfully to Xero using Xero Plugin
      • Added Company Successfully to Xero using Xero Plugin
      • Added Invoice which included new company/contact to Xero using Xero Plugin

      The "Service Line" as mentioned didn't work so had to be "Product Line" for the invoice to get added to Xero.

  4. MartyP member avatar

    Business Fundamentals Provider Affiliate

    8 months ago

    Well, from the error message you screen shot it is saying that the Invoice has NOTHING in it, no line items, just a blank? Is this correct? Also, make sure that the currencies you are using are in Xero AND SuiteCRM BEFORE you try to send/create Invoices. Can you try and send a single account or contact record to Xero first? Just to see if the module is working for ANY records going to Xero - so steps to take:

    • make sure you have all required currencies in Suite AND Xero. In Suite go to Admin -> Currencies, enter the correct ISO 4217 codes eg USD
    • go to a single Account or Contact record in Suite -> detailview -> Actions menu. Click on the Send to Xero menu listing and send me info on what occurs, successful, error etc
    • create an Invoice that has items in it. Make sure the items are listed as Products in Suite NOT Services. Do what you've done before with trying to send an invoice to Xero and send me the results

    Thanks for your time and patience

  5. MartyP member avatar

    Business Fundamentals Provider Affiliate

    8 months ago

    Have you completed the steps I outlined?

  6. MartyP member avatar

    Business Fundamentals Provider Affiliate

    8 months ago

    and send me a screen grab of the Invoice you're trying to send from Suite please, include line items, cost, dates etc

    • FileSphere member avatar

      FileSphere

      8 months ago

      Thank you for all your assistance it is now successfully working & we are continuing to trail your software.

      Added Contact Successfully to Xero using Xero Plugin
      Added Company Successfully to Xero using Xero Plugin
      Added Invoice which included new company/contact to Xero using Xero Plugin
      

      The "Service Line" as mentioned didn't work so had to be "Product Line" for the invoice to get added to Xero.

  7. MartyP member avatar

    Business Fundamentals Provider Affiliate

    8 months ago

    Cool... :=]

This case is public. Please leave out any sensitive information such as URLs, passwords, etc.
Saving Comment Saving Comment...