Author Topic: Edit Sequence "out of date"  (Read 9989 times)

rpaulh

  • Member
  • *
  • Posts: 22
Edit Sequence "out of date"
« on: June 23, 2011, 08:42:16 AM »
Trying to do an edit of existing QB data in 1 record. Modified the field in FMP that corresponds to the Customer Name (FullName) field in QB. When I run the script (Which I copied from the FMBooks Sample file and edited) to update QB it returns this error: "The provided edit sequence "XXXXXXXXX" is out of date". I need to do this to about 500 records/customers if I can get it working. Any help is appreciated.

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 470
Re: Edit Sequence "out of date"
« Reply #1 on: June 23, 2011, 10:17:50 AM »
Before you construct a Customer Mod Request, do a query for the Edit Sequence using the CustomerListID. Parse the Edit Sequence from the query result and use it in the Mod Request.

HTH!

Geoffrey Gerhard
Creative Solutions Incorporated
14000 Creekside Drive
Matthews, NC  28105
704) 814-6852

rpaulh

  • Member
  • *
  • Posts: 22
Re: Edit Sequence "out of date"
« Reply #2 on: June 23, 2011, 10:44:01 AM »
Thanks for the response, it's a little Greek to me, however now I am receiving another error: CODE: 500, SEVERITY: Warn, MESSAGE: The query request has not been fully completed. There was a required element ("Mark's Plumbing Parts - Ft Worth") that could not be found in QuickBooks.

The element in parens' is the new data in the FMP field that I am trying to put into the QB Customer Name field, of course it does not exist in QB, I haven't put it there yet? Help!

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 470
Re: Edit Sequence "out of date"
« Reply #3 on: June 24, 2011, 10:23:11 AM »
You can identify the target record in a CustomerQuery Request using the FullName or ListID element. The Name is not the same as the FullName element and only works within the NameFilter or NameRangeFilter in a CustomerQuery Request. It's confusing, but the OSR (intuit's On Screen Reference) can help you sort out the element names in the context of what you want to do.

I recommend using ListID to identify a List record in QB whenever possible, because that value is not editable internally or externally. It's required to create a CustomerMod Request, so I presume you have the ListID stored in a field in one of your tables.

HTH!

Geoffrey Gerhard
Creative Solutions Incorporated
14000 Creekside Drive
Matthews, NC  28105
704) 814-6852

rpaulh

  • Member
  • *
  • Posts: 22
Re: Edit Sequence "out of date"
« Reply #4 on: June 24, 2011, 01:11:02 PM »
Yes I have the ListIDs, it's the Customer Name fields that do not match in the two apps. I need to push the name field from FMP to QB. Have managed to get the ListIDs to FMP and now I would like to use them to take my data and push to the QB "Name" field which is Customer Name in QB. Can it be done? When I run the script that works good with a file I pushed to QB initially it is fine, but trying to do it with a record I brought the QB ListID into does not seem to cooperate. Any Ideas?

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 470
Re: Edit Sequence "out of date"
« Reply #5 on: June 25, 2011, 10:36:23 AM »
I use the so called "Z" functions, but I think you'd arrange the native "PCQB" functions like this:

PCQB_RqNew( "CustomerMod" ; "" ) &
PCQB_RqAddFieldWithValue (“ListID” ; yourCustomerTable::ListID ) &
PCQB_RqAddFieldWithValue (“EditSequence” ;
PCQB_RqNew( "CustomerQuery" ; "" ) &
PCQB_RqAddFieldWithValue (“ListID” ; yourCustomerTable::ListID ) &
RqExecute ( "" ; ".//CustomerQueryRq/EditSequence/text()" ; "" )
) &
PCQB_RqAddFieldWithValue (“Name” ; yourCustomerTable::FMPCustomerName ) &
// Any other data elements that need updating...
RqExecute ( "" ; "" ; "" )

This nests a QueryRequest inside the Edit Sequence that uses XPath to return just the Edit Sequence value for the CustomerList ID you are trying to Modify.

HTH!

Geoffrey Gerhard
Creative Solutions Incorporated
14000 Creekside Drive
Matthews, NC  28105
704) 814-6852

rpaulh

  • Member
  • *
  • Posts: 22
Re: Edit Sequence "out of date"
« Reply #6 on: June 27, 2011, 08:56:15 AM »
Geoffrey, thanks for the help, however I just found that the error is coming from a sub-script called "Get Customer Edit Sequence_ExistingSession" in the demo file. It has a line of code that says:
 "Set Variable [$$Result; Value:PCQB_ReqAddFieldWithValue( "FullName"; MyDBCompanyNameField)]"

I receive the error message after this sub-script runs and this is the only step that does anything with any "Name" field. The error says:
CODE: 500
SEVERITY: Warn
MESSAGE: The query request has not been fully completed. There was a required element ("AboveNet Communications - 1 Summer St") that could not be found in QuickBooks.

I'm at a loss. Is this subscript necessary?

rpaulh

  • Member
  • *
  • Posts: 22
Re: Edit Sequence "out of date"
« Reply #7 on: June 27, 2011, 08:59:39 AM »
Geoffrey, also on your post you listed the code with ampersands "&". Do I enter it as a string, or is each command individual? Sorry I don't have any know how in this area.

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 470
Re: Edit Sequence "out of date"
« Reply #8 on: June 27, 2011, 02:04:04 PM »
I'm not familiar with the demo file--I'd been integrating FileMaker with QuickBooks for years before FMBooks Connector was introduced. The line of code you cite is using the current value of the MyDBCompanyNameField in the Query. Because there's no match, I presume that field's value is not the same as the FullName value in QB. If you know the ListID, replace that line with one like I supplied earlier:

PCQB_RqAddFieldWithValue (“ListID” ; yourFMPTableWithTheListID::ListID )

Assuming the sub-script is supplying the Edit Sequence as a script result, you should be all set.

As for the code I wrote with ampersands, it was a "down and dirty" example that you could temporarily put into the Data Viewer of FMP Advanced to see if your CustomerMod request would evaluate properly. If you try that, DO NOT STORE IT in the Data Viewer (hit "Cancel", NOT "Monitor") once you've tested it.


HTH!

Geoffrey Gerhard
Creative Solutions Incorporated
14000 Creekside Drive
Matthews, NC  28105
704) 814-6852

rpaulh

  • Member
  • *
  • Posts: 22
Re: Edit Sequence "out of date"
« Reply #9 on: June 27, 2011, 02:56:51 PM »
OK, beginning to understand a little more. My sub-script that is called "Get Customer Edit Sequence__ExistingSession" does not return anything. Hence FMP telling me there is a missing element "EditSequence". It is just not pulling that field. Would it help if I included the sub-spript (12 steps)? You mentioned earlier about a request for the EditSequence and then parsing it, but I do not know how to parse it. There is a request in the sub-script that is only supposed to include the EditSequence:

Set Variable[$$Result; Value:PCQB_RqAddFieldWithValue( "IncludeRetElement" ; "EditSequence" )]

It is returning the missing element error. Thank you.

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 470
Re: Edit Sequence "out of date"
« Reply #10 on: June 28, 2011, 05:37:10 AM »
You wrote:

> Would it help if I included the sub-spript (12 steps)?

That should help us get a bead on the "missing element" error.

rpaulh

  • Member
  • *
  • Posts: 22
Re: Edit Sequence "out of date"
« Reply #11 on: June 28, 2011, 06:30:18 AM »
Here it is, I included the comments just in case. All of my actual DB info is being used so there is no confusion.

Another anonmally is that at one point yesterday I was able to update 2 out of 3 customers with the full script/sub-script in a loop. It would not update the first record but the following 2 each time. I realized that my global gPushCustomerEditSequence field was not set to global and when I did it not longer updates at all. The test customer I am using is one that was pushed from FMP to QB a few days ago.

Thank you.

Code: [Select]
#Setup
Set Error Capture [On}
Allow User About[On]
Freeze Window
#Prep Customer info (Request)
Go to Layout ["Data Entry HOME" (!HOME)]
Set Variable [$$Result; Value:PCQB_RQNew("CustomerQuery"; "")]
Set Variable [$$Result; Value:PCQB_RQAddFieldWithValue("FullName"; !HOME::Company)]
#Ask QB to include only the EditSequence in the response
Set Variable [$$Result; Value:PCQB_RQAddFieldWithValue("IncludeRetElement"; "EditSequence")]
If [0 = PCQB_RqExecute]
  #Capture the EditSequence (Response) 
  Set Variable [$$Result; Value:PCQB_RsOpenFirstRecord]
  Set Field [!HOME::gPushCustomerEditSequence; PCQB_RsGetFirstFieldValue( "EditSequence")]
  Exit Script [Result: 0]
Else
  Exit Script [Result: PCQB_SGetStatus
End If

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 470
Re: Edit Sequence "out of date"
« Reply #12 on: June 28, 2011, 01:18:37 PM »
This is the problem:

Set Variable [$$Result; Value:PCQB_RQAddFieldWithValue("FullName"; !HOME::Company)]

Replace the value for the above Set Variable to this...

PCQB_RqAddFieldWithValue (“ListID” ; yourFMPTableWithTheListID::ListID )

...being sure to replace "yourFMPTableWithTheListID::ListID" with a valid reference to the field that contains your ListID.

HTH!

Geoffrey Gerhard
Creative Solutions Incorporated
14000 Creekside Drive
Matthews, NC  28105
704) 814-6852

rpaulh

  • Member
  • *
  • Posts: 22
Re: Edit Sequence "out of date"
« Reply #13 on: June 28, 2011, 03:48:01 PM »
That was the fix (for now!). Geoffrey, I can't convey how much I appreciate your help. If I run into any other issues I'm sure I will be posting again. Thank you!
Paul