Author Topic: Help understanding OSR  (Read 1620 times)

GreenLion

  • Member
  • *
  • Posts: 4
Help understanding OSR
« on: December 19, 2013, 03:57:30 PM »
I am fairly new and having some trouble understanding how to get certain info from QuickBooks using the OSR.  For example, using EmployeeQuery, I can retrieve most general info but something like Rate from the EmployeePayrollInfo I cant make the connection.  I'm not understanding how the BEGIN OR and OR works in the OSR. If someone could explain these to me that would be great as well as give me the RsGetFirstFieldValue for Rate so I make the connection.  Thanks.

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 468
Re: Help understanding OSR
« Reply #1 on: December 20, 2013, 07:09:54 AM »
There are several potential ways Employees can earn their pay, but only one is selected for each Employee. BEGIN OR and END OR always bracket a set of elements, of which only one will be returned, in the Query's Response.

If the Employee you're targeting is paid by ClearEarnings (whatever that is) or RatePercent, there will not be a Rate element.

It's often helpful to look at the full qbXML Response package, which you can get using the PCQB_SGetXML( "Response" ; "" ) function.

HTH!

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

GreenLion

  • Member
  • *
  • Posts: 4
Re: Help understanding OSR
« Reply #2 on: January 08, 2014, 04:25:53 PM »
Wow, that's great.  What a great tool.  Thanks.

So I am able to pull all the info I now need for FM from QB. Now I am having trouble figuring out how to only pull employee info that is new since my last pull, or info from only new employees. Any help with this would be greatly appreciated.

Thanks again.

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 468
Re: Help understanding OSR
« Reply #3 on: January 08, 2014, 05:13:57 PM »
If you are tracking the date/time of your last query, you can create a QB-formatted timestamp value that's one second later and pass it in your query using the FromModifiedDate element. That will pickup only Employee records created/modified on or after that value. It returns all the data for each record returned, not just the data for those elements that changed.

A trick I often use when I'm planning to query QB for new/modified records is to store the value of the TimeModified element in a timestamp field in each FMP record. You can then use the Max () function across a cartesian-join TO to pull the most recent value and pass it as the FromModifiedDate value like this...

 PCQB_RqAddFieldWithValue( "FromModifiedDate" ; PCQB_SFormatString( Max ( xTO::TimeModifiedField ) ; "DATETIMETYPE" ) )

 ...and so be guaranteed to have at least one record returned. If there's only one record, and its TimeModified value matches the value you passed, you're done.

HTH!

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

GreenLion

  • Member
  • *
  • Posts: 4
Re: Help understanding OSR
« Reply #4 on: January 09, 2014, 01:57:41 PM »
Thanks Geoffrey.  I will have to play with this a bit more to get a better handle on it.  For now, what could I do to be sure that every time I run my script, I don't get duplicate records of employees I have pulled in the past?

Also I am having trouble pulling what looks like related records.  I have attached a portion of the response from the PCQB_SGetXML that I was using to write my script.  I have also attached a portion of the script that I am using in an attempt to pull the related records.  I successfully pull the first line of the script I have attached (EmployeePayrollInfo::PayPeriod), but I am unable to pull any of the related records that the PCQB_SGetXML response shows.  Do you see my error?

Thanks again.

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 468
Re: Help understanding OSR
« Reply #5 on: January 10, 2014, 10:15:05 AM »
I presume you actually want to see an Employee's payroll data at regular intervals, not just once, but only when it has changed. If you extract and store the TimeModified element's value for each Employee, you can compare the returned value to the stored value and proceed only when they differ.

As for your other question, I use XPath to extract related record data and thus am not 100% sure that your problem would be solved by inserting PCQB_RsCloseRelatedRecord into the Set Variable step that follows the internal Loop's End Loop step. Change the argument to...

PCQB_RsCloseRelatedRecord & PCQB_RsOpenNextRelatedRecord

...and see if that solves it.

HTH!

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

GreenLion

  • Member
  • *
  • Posts: 4
Re: Help understanding OSR
« Reply #6 on: January 10, 2014, 06:04:45 PM »
Thanks again.  I finally got the script to work properly after some experimenting and hair pulling. I was unaware that the PCQB_RsOpenFirstRelatedRecord had to be broken down.  I have attached the new script.

I would like to learn more about using XPath.  Is there anywhere to get more info on how to use this?

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 468
Re: Help understanding OSR
« Reply #7 on: January 13, 2014, 09:51:25 AM »
I learned by reading the tutorial at http://www.w3schools.com/xpath/ and viewing the raw xml that QB returns in its Responses.

HTH!

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