Author Topic: Returning only Customer records modified since last sych  (Read 772 times)

donaldc500

  • Member
  • *
  • Posts: 4
Returning only Customer records modified since last sych
« on: March 27, 2012, 09:36:26 PM »
I have successfully imported the customer list into Seedcode Complete, and now want to pull a list of customers that have been modified since the last time I pulled the entire list.  I have a date/time stored since the last date, and am using this code to pull the modified records:

setVariable [$result; Value:PCQB_RqNew("CustomerQuery")
setVariable [$result; Value:PCQB_RqAddFiledWithValue("ModifiedDateRangeFilter::FromModifiedDate"; $time)]

The $time variable is in this format:  2012-03-27T12:49:12

I get all records instead of just the modified records.

I also tried to use metaData as argument in the first line, looking for retCount as a return, but kept getting zero as there returned count, even when all the records were returned.

Thanks in advance for your help!



Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 322
Re: Returning only Customer records modified since last sych
« Reply #1 on: March 28, 2012, 08:41:33 AM »
Not sure why you have a "ModifiedDateRangeFilter" parent element in it, but from the OSR, the second setVariable should be...

setVariable [$result; Value:PCQB_RqAddFiledWithValue("FromModifiedDate"; $time)]

HTH!

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

donaldc500

  • Member
  • *
  • Posts: 4
Re: Returning only Customer records modified since last sych
« Reply #2 on: March 28, 2012, 10:42:15 AM »
I tried what you suggested first. When that returned all files, I found the "ModifiedDateRangeFilter::" line of code on another posting. 

Any ideas why the request would still be returning all records? 

Also, I tried this to get a return count:

setVariable [$result; Value:PCQB_RqNew("CustomerQuery"; "metaDataAndResponseData")

I then tired to capture the retCount, but must have done something wrong, as the count cam back "0" when all of the data was actually returned.  Would you please show me the proper way to ask for and capture the count?

Thanks

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 322
Re: Returning only Customer records modified since last sych
« Reply #3 on: March 28, 2012, 02:26:20 PM »
You wrote...

> Any ideas why the request would still be returning all records?

Because all records have been modified? I believe a Customer TimeModified field is updated when any transaction is added or modified. Adding or modifying a Payment, Credit Memo, or Invoice will update the Customer's Balance Due, and that in turn will update the TimeModified field.

As for getting the value of the retCount attribute, you'll have to resort to XPath because the two functions that would let you get it directly are listed as "Still under development" in the FM_Books_Connector Functions Guide. The PCQB_ZParseXML( XML ; IsPath ; Xpath ; optFilePath ) function can give you what you want. I can't double-check at the moment, but it looks something like this...

PCQB_ZParseXML( PCQB_SGetXML( "Response" ; "" ) ; "" ; "/CustomerQueryRs/@retCount" )

...and will return...

retCount="999"

...if there are 999 Customer records in the Response.

NOTE: you can get a count with much less effort by applying PatternCount like this:

PatternCount ( PCQB_SGetXML( "Response" ; "" ) ; "<CustomerRet>" )

Neither of these will help you understand why you're getting all records in the Response. I'm wondering whether the $time element is really in the format you posted. You'd get all records if that element is empty or invalid.

Are you using FileMaker Advanced? If so, open the script debugger and step through the script, stopping after you evaluate the PCQB_RqCloseRelatedRecord function but before you evaluate the PCQB_RqExecute function. Open your Data Viewer and evaluate this expression PCQB_SGetXML( "Request" ; "" ) and copy/paste the result back here.


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

Chris

  • God
  • Member
  • *****
  • Posts: 83
Re: Returning only Customer records modified since last sych
« Reply #4 on: May 31, 2012, 03:26:00 PM »
Use...

setVariable [$result; Value:PCQB_RqAddFiledWithValue("FromModifiedDate"; $time)]

where $time is a filemaker formatted timestamp (not the format you provided)

Chris