Method:API Q&A

Re: Writing two linked tables

Method_David — Wed, 18 Jul 2012 15:33:10 GMT

Hi Jason L,

Glad to hear you got it working. I've been trying to reproduce the error for the past 2 days without any luck. I was actually starting to write a post asking you to elaborate on how you set up your linked fields.

Dave

Re: Writing two linked tables

Jason_L — Wed, 18 Jul 2012 15:17:46 GMT

I finally got this to work. I needed to add a linked field along with the drop down, then not pass any parameters to the insert call dealing with the linked field. I was counting on the dropdown to hold Table A's record ID, but I had to add an actual linked field to the record ID, and then it worked. Thanks for the help.

Re: Writing two linked tables

Jason_L — Mon, 16 Jul 2012 21:47:15 GMT

That was the original failing case. The field in Table B that is linked to Table A's record ID cannot be written if I write a new row to table A and then pull the record ID from that new row. It appears that Table B does not know that the record ID for the new row is valid, and so balks at my insert. For example (using brief pseudo code)

MethodAPI.MethodAPIInsertV2(all the right stuff, writing a new row to table a)

MethodAPI.MethodAPISelect_XMLV2(get table A record Id for new row)

MethodAPI.MethodAPIInsertV2(writing to Table B, including the result from the line above) <-Failure

Re: Writing two linked tables

Method_David — Mon, 16 Jul 2012 21:31:07 GMT

Jason L,

Sorry to keep you waiting...it's been kinda a manic Monday....anyway...

It sounds like you've added an extra field for recordid to table B, and have made it required. So it should be part of your insert list.

Extending my example from above

string[ arrInsertFieldsArray = new string[ { "ArAccount", "Customer", "TxnDate", "MyExtraRecordIDField" };

string[ arrInsertValueArray = new string[ { "Accounts Receivable", "Amy Smith", "13/july/2012", "5" };

lblMethodAPIInsertV2.Text = _MethodAPI.MethodAPIInsertV2(CompanyAccount, Login, Password,"", "Invoice", arrInsertFieldsArray, arrInsertValueArray);

Dave

Re: Writing two linked tables

Jason_L — Mon, 16 Jul 2012 20:50:52 GMT

I'm just pinging on this. If I'm going about table construction or insertion wrong, please let me know. Thanks.

Re: Writing two linked tables

Jason_L — Fri, 13 Jul 2012 20:34:42 GMT

That is a dropdown in Table B that is pulled in from Table A. I'm trying to keep B synced with A, via A's record ID.

Re: Writing two linked tables

Method_David — Fri, 13 Jul 2012 20:23:12 GMT

Jason L

I need a little more information about this.

"<?xml version="1.0" encoding="windows-1252" ?><MethodAPI response = "Cannot insert the value NULL into column 'TableARecordID', table 'xxx.dbo.TableB'; column does not allow nulls. INSERT fails."

Is 'TableARecordID' a custom field? aka a field defined by you? All tables created in method have a recordid field in them automatically. If you have created your own "recordid" field and made it required. The above code will not populate it.

Dave

Re: Writing two linked tables

Jason_L — Fri, 13 Jul 2012 19:46:29 GMT

I've just tried that, and it doesn't seem to be working. Here's a code snippet that I've got (with correct variables and all), it's Java syntax, but not tough to follow:

private static final String[ METHOD_TABLE_A_FIELDS_TO_WRITE = {"Company", "Email", "NameFirst", "NameLast", "NameFull", "Notes"};

private static final String[ METHOD_TABLE_B_FIELDS_TO_WRITE = {"NameFirst", "NameLast", "NameFull", "email"};

private static String[ methodTableAValuesToWrite = {"Slappy, Inc.", "slappy@mcgee.net", "AAAA", "AAAA", "AAAA AAAA", "This is a test item"};

private static String[ methodTableBValuesToWrite = {"AAAA", "AAAA", "AAAA AAAA", "slappy@mcgee.net"};

...

String response = service.methodAPIInsertV2(METHOD_COMPANY_NAME, METHOD_USER_NAME, METHOD_USER_PASSWORD, "", METHOD_TABLE_A_NAME, METHOD_TABLE_A_FIELDS_TO_WRITE, methodTableAValuesToWrite );

System.out.println(response);

response = service.methodAPIInsertV2(METHOD_COMPANY_NAME,METHOD_USER_NAME, METHOD_USER_PASSWORD, "", METHOD_TABLE_B_NAME, METHOD_TABLE_B_FIELDS_TO_WRITE, methodTableBValuesToWrite);

System.out.println(response);

My sysout calls show me:

<?xml version="1.0" encoding="windows-1252" ?><MethodAPI response = "Success" RecordID="2331" ></MethodAPI>
<?xml version="1.0" encoding="windows-1252" ?><MethodAPI response = "Cannot insert the value NULL into column 'TableARecordID', table 'xxx.dbo.TableB'; column does not allow nulls. INSERT fails.
The statement has been terminated." ></MethodAPI>

So it looks to me that the API is automatically inserting null into the linked field, and so my insert is failing. The field is required, and I can try removing that restriction if the field will be updated by a chron job or something at a later time. If not, there's still something missing in this operation. By the way, if I add a known good record ID from table A (I used 1) to the values array, I get a success message.

Re: Writing two linked tables

Method_David — Fri, 13 Jul 2012 17:37:20 GMT

Hi Jason L,

In the second scenario, you don't need to use the recordid after the insert, just the text to write to B. This is because currently the api does a look up internally and grabs the recordid.

I'll illustrate this with an example (It's in C# and assumes you have all the references, etc added). I want to insert an invoice into my account. For brevity, I only want to insert into the required fields, ArAccount, Customer, and TxnDate. For ArAccount I want to insert Accounts Receivable (RecordID 5), the customer is Amy Smith (RecordID 19), and for TxnDate I'll use today's date

The following will not work

string[ arrInsertFieldsArray = new string[ { "ArAccount", "Customer", "TxnDate" };

string[ arrInsertValueArray = new string[ { "5", "19", "13/july/2012" };

lblMethodAPIInsertV2.Text = _MethodAPI.MethodAPIInsertV2(CompanyAccount, Login, Password,"", "Invoice", arrInsertFieldsArray, arrInsertValueArray);

The following will work

string[ arrInsertFieldsArray = new string[ { "ArAccount", "Customer", "TxnDate" };

string[ arrInsertValueArray = new string[ { "Accounts Receivable", "Amy Smith", "13/july/2012" };

lblMethodAPIInsertV2.Text = _MethodAPI.MethodAPIInsertV2(CompanyAccount, Login, Password,"", "Invoice", arrInsertFieldsArray, arrInsertValueArray);

Dave

Writing two linked tables

Jason_L — Fri, 13 Jul 2012 15:45:39 GMT

I have two tables, A and B, with a dropdown field in B that links to the automatically generated RecordID in A. I want to write to B and link it to A at the same time.

Through the API, my program flow works like this:

Take a variable passed in as an argument, and look that up in A, using methodAPISelect_XMLV2.

1) If I have a record ID, then I add that to the values being written into B using methodAPIInsertV2.

2) Else, I write details into A and then fetch the record ID from the field just written. Then add that record ID to the values being added to B.

Situation 1 works very well. Situation 2 does not work. The response shows the API balking on the recordID insert. I think, given other forum posts that it could be that the record just written into A hasn't had time to propigate into A->B lookup table, and so B doesn't understand that the new record does exist in A.

So, do I need to manually write the record ID into a middle lookup table somewhere, or do I need to make my program sleep for a little while to give A time to propigate its new value? If I need to write that record ID into a lookup table, how can I find the name of that table? It doesn't seem like something that would be exposed.