Author Topic: Modifying Customer records already synced  (Read 1455 times)

fmpdev1389

  • Member
  • *
  • Posts: 2
Modifying Customer records already synced
« on: April 24, 2015, 11:06:44 AM »
Hey there,

I'm very new to QB and this plugin but I'm familiar with Filemaker. I'm using the plugin to push and pull customer data only (for now) and have got the scripts functioning for both pulling ALL customers from QB to FM (first script) and pushing all customers to QB from FM (second script). Ideally, the client I'm developing for will only need to use the pull script once and then use another script to pull records that are modified in QB but have already been pulled to FM. I've tried a few variations on such a script but have had no luck getting it to work. I'm just trying to pull in all records that have been modified in QB to FM, but I just want the record to be updated - not replaced - in FM.

The Pull script is one that I used from the demo file and modified it to fit my needs for the database.

Any help is appreciated, thank you!

fmpdev1389

  • Member
  • *
  • Posts: 2
Re: Modifying Customer records already synced
« Reply #1 on: April 25, 2015, 12:46:55 PM »
Okay, I managed to get a Push script working that modifies records changed in FM and syncs them with QB. That's great and will get used once in a while by the client but I really need a Pull script that pulls in modified records from QB, since that is the primary place that the client is changing their customer's information.

I did manage to create a script that Pulls in modified QB customer information, However, when it runs it returns only the customer company name and deletes all the old information from FM. Obviously I'd like it to return all of the information I'm requesting and leave the information alone that hadn't been modified in QB.

Here is the script:

Code: [Select]
PULL: MOD (Customer Info FROM QB)

Set Error Capture [ On ]
Set Field [ Contacts::gResult ; PCQB_EndSession ]
Set Field [ Contacts::gResult ; PCQB_BeginSession( "";"" ) ]
Go to Layout [ original layout ]
#Query Customers in QB (Request)
Set Variable [ $$Result ; Value:PCQB_RqNew("CustomerQuery" ; "" ) ]
If [ $$Result ≠ 0 ]
Set Field [ Contacts::gResult ; $$Result ]
Halt Script
End If
Set Variable [ $$Result ; Value:PCQB_RqAddFieldWithValue( "ActiveStatus" ; "All" ) ]
If [ GetAsNumber(PCQB_GetCurrentFileVersion) >= 4 ]
#The area below limits the returned response to FullName, TotalBalance and ListID for better performance.
Set Variable [ $$Result ; Value:PCQB_RqAddFieldWithValue( "IncludeRetElement" ; "FullName" ) ]
Set Variable [ $$Result ; Value:PCQB_RqAddFieldWithValue( "IncludeRetElement" ; "ListID" ) ]
Set Variable [ $$Result ; Value:PCQB_RqAddFieldWithValue( "IncludeRetElement" ; "EditSequence") ]
End If
If [ $$Result ≠ 0 ]
Set Field [ Contacts::gResult ; $$Result ]
Halt Script
End If
If [ 0 = PCQB_RqExecute ]
Set Field [ Contacts::gResult ; PCQB_RsOpenFirstRecord ]
Set Field [ Contacts::Edit Sequence ; PCQB_RsGetFirstFieldValue( "EditSequence" ) ]
Else
Set Field [ Contacts::gResult ; PCQB_SGetStatus ]
End If
Set Field [ Contacts::gResult ; PCQB_SGetStatus ]
Set Field [ Contacts::gResult ; True ]
#Execute
Set Variable [ $$Result ; Value:PCQB_RqExecute ]
#Close Session
Set Variable [ $$Result ; Value:PCQB_EndSession ]
#Create Customers in FileMaker (Response)
Set Field [ Contacts::gResult ; PCQB_BeginSession( "";"" ) ]
If [ 0 = PCQB_RqExecute ]
Set Field [ Contacts::gResult ; PCQB_RsOpenFirstRecord ]
Set Field [ Contacts::Edit Sequence ; PCQB_RsGetFirstFieldValue( "EditSequence" ) ]
Else
Set Field [ Contacts::gResult ; PCQB_SGetStatus ]
End If
Set Field [ Contacts::gResult ; PCQB_RqNew( "CustomerMod" ) ]
Set Field [ Contacts::ListID ; PCQB_RsGetFirstFieldValue("ListID") ]
Set Field [ Contacts::NameFull ; PCQB_RsGetFirstFieldValue("FullName") ]
Set Field [ Contacts::Address ; PCQB_RsGetFirstFieldValue("BillAddress::Addr2") ]
Set Field [ Contacts::City ; PCQB_RsGetFirstFieldValue("BillAddress::City") ]
Set Field [ Contacts::State ; PCQB_RsGetFirstFieldValue("BillAddress::State" ) ]
Set Field [ Contacts::Zip ; PCQB_RsGetFirstFieldValue("BillAddress::PostalCode") ]
Set Field [ Contacts::MainPhone ; PCQB_RsGetFirstFieldValue("Phone") ]
Set Field [ Contacts::WorkPhone ; PCQB_RsGetFirstFieldValue("AltPhone") ]
Set Field [ Contacts::Email ; PCQB_RsGetFirstFieldValue("Email") ]
Set Field [ Contacts::Notes ; PCQB_RsGetFirstFieldValue("Notes") ]
Set Field [ Contacts::gResult ; PCQB_SGetStatus ]
Set Variable [ $$Result ; Value:PCQB_RqNew("CustomerQuery" ; "" ) ]
Set Variable [ $$Result ; Value:PCQB_RqAddFieldWithValue( "ListID" ; eContacts::ListID) ]
Set Variable [ $$Result ; Value:PCQB_RqAddFieldWithValue( "EditSequence" ;Contacts::Edit Sequence) ]
Set Variable [ $$Result ; Value:PCQB_RqExecute ]
Set Field [ SampleContacts::gResult ; PCQB_SGetXML( "Response" ; "" ) ]
Set Field [ SampleContacts::gResult ; PCQB_SGetStatus ]
#Pull Customer info into FileMaker (Response)
Set Variable [ $$Result ; Value:PCQB_RsOpenFirstRecord ]
Go to Layout [ original layout ]

I read another thread that said this could have something to do with the IncludeRetElement but, where do you go from there?

Any help is appreciated, Thank you!

EDIT: I'd like to also mention that this script does not return any errors, it just doesn't return the results I'd like.
« Last Edit: April 25, 2015, 12:54:32 PM by fmpdev1389 »

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 468
Re: Modifying Customer records already synced
« Reply #2 on: April 28, 2015, 07:00:05 AM »
The script you uploaded several elements that can create issues for you, and some that serve no function in their current location.

The first If/End IF chunk (that reads "If [ $$Result ≠ 0 ]" ) should follow the "Set Field [ Contacts::gResult ; PCQB_BeginSession( "";"" ) ]" step.

The second If/End IF chunk (that reads "If [ $$Result ≠ 0 ]" ) does nothing useful and should be deleted.

The first If [ 0 = PCQB_RqExecute ] should be exiting in the Else condition.

Every subsequent reference to PCQB_RqExecute ( except the one that follows second "Set Variable [ $$Result ; Value:PCQB_RqNew("CustomerQuery" ; "" ) ]" ) reruns the first Query and should be eliminated. Not sure why the latter is there--it's a great query if you were simply looping through your FMP Customer records and updating each in turn, but it does nothing in the context shown.

The step "Set Field [ Contacts::gResult ; PCQB_RqNew( "CustomerMod" ) ]" that precedes your run of SetField steps does nothing and should be deleted.

Your original post implied that you wanted only records modified in QB, but the "Set Variable [ $$Result ; Value:PCQB_RqAddFieldWithValue( "ActiveStatus" ; "All" ) ]" step will return ALL active Customer records.

The other part of your Question:

The "IncludeRetElement" node acts as a filter for which nodes/elements will be returned from QB. Dropping those from your Query Request will give you a complete set of Customer data for each returned record, but will also mean QB, FMP, and the plug-in will need to gather, pass, and parse a potentially HUGE amount of data that is irrelevant to what you need.

You could reduce the dataset a little by adding additional IncludeRetElement nodes so you get only the parts of the Customer record that you want, but I think you'd be better off dropping the ActiveStatus filter and instead filtering your Query Request using the FromModifiedDate element. You can then limit the returned records to only those that were modified since the last time you ran the Query.

Or, as said above, looping through your FMP Customer table and running the last CustomerQuery Request and updating FMP values from the response seems to get close to what you want.

HTH!

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