Method Community

 

Is there any db schema documentation?

Last post 01-20-2014 6:44 PM by kenlyle. 3 replies.
Page 1 of 1 (4 items)
Sort Posts: Previous Next
  • 12-19-2013 12:26 PM

    Is there any db schema documentation?

    I'm having a very difficult time understanding how the db is organized. Quite simply, I'm trying to add Contacts via the MethodAPIInsertV2 call, and running into multiple issues.

    It seems to me that data is duplicated between Customer, Contact and Entity records, and I'm not sure where the data should go. Furthermore, I understand that a Customer supposedly references one to many Contacts, but I'm finding strange behavior when adding Contacts where the CustomerName seems to be a random Customer in the db, not the one passed. Finally, I'm frustrated that so many fields on the Contact table do not support Insert, but do support Edit.

    What is the proper path forward here? A little documentation would, I feel, go a long way here.

    Thanks for any help or light you can shed.

    Aaron.

  • 12-20-2013 12:46 PM In reply to

    Re: Is there any db schema documentation?


    Hi aaronglamb,

     

    Gonna point you at the knowledge base article http://www.methodintegration.com/method/kb.aspx?folder=methodcrm&article=Contacts&id=299 about contacts and a forum post about the entity table http://method.me/cs/forums/t/454.aspx

    The customers table is pretty much a view on the entity table....where the entitytype field is equal to customer. Hence the duplication you mentioned... refer to http://method.me/cs/forums/t/5328.aspx

    In the contacts table you have the field entity_recordid (think foreign key) of the customer that the contact belongs to. So you need to have an existing customer(entity), before you can assign it to a contact.

    As for supports add/edit refer to http://method.me/cs/forums/t/3622.aspx

     

    Dave

     

  • 12-26-2013 4:20 PM In reply to

    Re: Is there any db schema documentation?

    So, that's *slightly* helpful, thank you.

    Really, a little documentation would go a long way here; or are you determined to be one of the most diffcult APIs to integrate with on the market?

    Here's where I'm stuck now: inserting new contacts for existing customers.

    Here's my workflow:

    IF Contact does not exists in Method for a given Email

    IF Customer does not exist in Method for given Name

    INSERT new Customer in Method

    ELSE

    SELECT RecordID of Customer from Method

    INSERT new Contact in Method with Entity_RecordID = CustomerRecordID

    UPDATE Contact (to populate all those read only fields)

    ELSE Update Contact

    If I end up creating a Customer record, there is no problem. However, if I create a Contact, I don't see anything new show up in the Method UI, although I do have a db insert id for the Contact record. 

    My confusion stems from the Contact / Customer / Entity relationship: I understand that the Customer table is a view on the Entity table with EntityType = Customer. I understand that the records in the Contacts table use the RecordID of the Entity table as a foreign key, either on the field EntityRecordID or the field Entity_RecordID (see where some documentation might be helpful - what fields are PK, FK, and to what do the FK reference?). Why then, when I insert a new Contact do I not see a result in the UI? What am I missing?


    Thanks again,

    Aaron

  • 01-20-2014 6:44 PM In reply to

    Re: Is there any db schema documentation?

    Hey Aaron,

    The team I was on had a lot of the same questions and issues.

    Keep in mind that the schema is largely the schema from Quickbooks, not something that the Method guys created to be cruel.

    There are some online resources from Quickbooks related companies : http://doc.qodbc.com/qodbc/Qodbc_20_us.html 

    Along those lines, if you can insert records in QB Desktop using ODBC or QB Online via the IPP, you could assume that the sync engine would pick them up.  Could that work for you?

Page 1 of 1 (4 items)