Author Topic: Syntax for retrieving value in a custom field  (Read 2369 times)

andre

  • Guest
Syntax for retrieving value in a custom field
« on: October 29, 2010, 09:40:39 AM »
According to the dev manual:

If [ 0 = PCQB_RsOpenFirstRelatedRecord( "DataExtRet" ) ]
  Loop
    #the name of the custom field
    Set Field[ N_Field ; PCQB_RsGetFirstFieldValue( "DateExtName" ) ]
    #the value of the custom field
    Set Field[ D_Field ; PCQB_RsGetFirstFieldValue( "DataExtValue" ) ]
    #get next custom field/exit if there are no more
    Exit Loop If[ 0 <> PCQB_RsOpenNextRelatedRecord ]
  End Loop
  Set Field[ SomeField ; PCQB_RsCloseRelatedRecord ] End If ...

Perhaps I'm just tired today, but where in the above steps do I specify the name of the custom field whose value I want to retrieve? In this case, I'm doing an inventory query, and the custom fields whose values I want are called Pick_bin and Overstock.

After I open the first related record, don't I do something like this:

Set Field[ Products::Pick_bin ; PCQB_RsGetFirstFieldValue( "Pick_bin" ) ]
Set Field[ Products::Overstock ; PCQB_RsGetFirstFieldValue( "Overstock" ) ]
Set Variable[ $Result ; Value:PCQB_RsCloseRelatedRecord ] …

Isn't the parameter for PCQB_RsGetFirstFieldValue( QBFieldName ) the name of the field?

Any suggestions appreciated.
Thank you.



Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 470
Re: Syntax for retrieving value in a custom field
« Reply #1 on: October 29, 2010, 03:44:15 PM »
You wrote:

> Isn't the parameter for PCQB_RsGetFirstFieldValue( QBFieldName ) the name of the field?

It is, but DataExtRet is a special case. When your query returns DataExtRet structure, you'll have one repetition per custom field. Each custom field is akin to its own related record with two elements: the name of the custom field (DataExtName) and the custom field's value (DataExtValue). So you'll need to use those values as the QBFieldName in PCQB_RsGetFirstFieldValue functions. The _value_ of DataExtName is Pick_bin, Overstock, or whatever.

Assuming you're using FMP 10 or higher, and that the publicly accessible Custom Fields in QuickBooks have the same names as the FileMaker versions of the fields, you could use the Set Field By Name with the Field Name calc set to "Products::" & PCQB_RsGetFirstFieldValue( "DateExtName" ) and the calculated result calc set to PCQB_RsGetFirstFieldValue( "DataExtValue" ). Loop through the DataExtRet elements and you're good to go.

Note that if you have other custom fields defined in QuickBooks and you don't want their values assigned or they don't have matching names in your Products table, you'll need to do some extra work in your FileMaker Loop.

HTH!

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

andre

  • Guest
Re: Syntax for retrieving value in a custom field
« Reply #2 on: October 30, 2010, 08:27:08 AM »
Aha!

I know there are more custom fields in QuickBooks than corresponding fields in FileMaker.  I want to ignore those custom fields without a match.

So in my loop, could I do something like this:

If ( PCQB_RsGetFirstFieldValue( "DateExtName" ) = "Pick_bin" )
  Set Field [ Products::Pick_bin ; PCQB_RsGetFirstFieldValue( "DataExtValue" ) ]
  Exit Loop If[ 0 <> PCQB_RsOpenNextRelatedRecord ]
Else if ( PCQB_RsGetFirstFieldValue( "DateExtName" ) = "Overstock" )
  Set field [ Products::Overstock ; PCQB_RsGetFirstFieldValue( "DataExtValue" ) ]
  Exit Loop If[ 0 <> PCQB_RsOpenNextRelatedRecord ]
Else
  Exit Loop If[ 0 <> PCQB_RsOpenNextRelatedRecord ]
End if

Or perhaps more succinctly, like this:

Set Field By Name [ Case (
  PCQB_RsGetFirstFieldValue( "DateExtName" ) = "Pick_bin" ; Products::Pick_bin ;
  PCQB_RsGetFirstFieldValue( "DateExtName" ) = "Overstock" ; Products::Overstock ) ;
  PCQB_RsGetFirstFieldValue( "DataExtValue" ) ]
Exit Loop If[ 0 <> PCQB_RsOpenNextRelatedRecord ] ...

Just making sure also that to get the DataExtRet values in the first place, I have to include in the query this step:
PCQB_RqAddFieldWithValue( "OwnerID" ; "0" )

Does the order of where I place the step matter? That is, before of after these steps
PCQB_RqAddFieldWithValue( "IncludeRetElement" ; "QuantityOnHand" )
PCQB_RqAddFieldWithValue( "IncludeRetElement" ; "QuantityOnSalesOrder" ) etc.

Thanks for the help.

Geoffrey Gerhard

  • Guru
  • Member
  • ****
  • Posts: 470
Re: Syntax for retrieving value in a custom field
« Reply #3 on: November 02, 2010, 04:59:19 PM »
Order matters. PCQB_RqAddFieldWithValue( "OwnerID" ; "0" ) follows the final "IncludeRetElement" call.

Your "more succinct" version will work. I'm obsessive about performance, so I'd probably enclose the Set Field by Name step in If/End If with this argument:

Let ( [
$x = PCQB_RsGetFirstFieldValue( "DateExtName" ) ;
y = PatternCount ( "¶Pick_bin¶Overstock¶" ; ¶ & $x & ¶ ) ;
$stopper = $stopper + y
] ;
y
)

The current DataExtName evaluates only once, and because the custom field name is captured in $x, your Set Field by Name argument becomes:

Set Field By Name [ Case (
  $x = "Pick_bin" ; Products::Pick_bin ;
  $x = "Overstock" ; Products::Overstock ) ;
  PCQB_RsGetFirstFieldValue( "DataExtValue" ) ]

or even more succinctly:

Set Field By Name [ "Products::" & $x ; PCQB_RsGetFirstFieldValue( "DataExtValue" ) ]


I'd also amend the Exit Loop If to this:

( 0 <> PCQB_RsOpenNextRelatedRecord ) or ( $stopper = 2 )

which stops processing the DataExt elements as soon as you've processed all targeted custom fields.

HTH!

Geoffrey Gerhard
Creative Solutions Incorporated
14000 Creekside Drive
Matthews, NC  28105
704) 814-6852
« Last Edit: November 02, 2010, 05:10:20 PM by Geoffrey Gerhard »