Value List from xml return

[rogermax]

using an xslt (with a good deal of help from Dan Brill) I can get a list of Customer names from QuickBooks imported to FMP.

What I would like to do is have that list in a return delimited format to use in a global for a value list, without making 100's of records.

I want to use that for the user to select from the list to update or sync the FM and QB files.

Can this be done? Perhaps using XPath functions or something to get the list in one chunk vs many records?

[andygaunt]

Well -

To get a return separated list from your imported records you could use the copy all records command.

In a script

Go To Layout (A layout with just your name field(s) on)

Copy All Records

Go To Layout (A layout with the global on)

Paste (Your Global Field)

This will not require any additional records over the ones you have imported.

[DanBrill]

Hey,

This does it. Almost...

<?xml version="1.0" encoding="UTF-8"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:template match="/">

<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">

<ERRORCODE>0</ERRORCODE>

<PRODUCT NAME="" BUILD="" VERSION=""/>

<DATABASE TIMEFORMAT="h:mm:ss a" NAME="" RECORDS="{count(/*/*)}" LAYOUT="" DATEFORMAT="M/d/yyyy">

<xsl:attribute name="RECORDS">

<xsl:value-of select="count(QBXML/QBXMLMsgsRs/EntityQueryRs)"/>

</xsl:attribute>

</DATABASE>

<METADATA>

<FIELD>

<xsl:attribute name="EMPTYOK">NO</xsl:attribute>

<xsl:attribute name="MAXREPEAT">1</xsl:attribute>

<xsl:attribute name="NAME">gFullName</xsl:attribute>

<xsl:attribute name="TYPE">TEXT</xsl:attribute>

</FIELD>

</METADATA>

<RESULTSET>

<xsl:attribute name="FOUND">

<xsl:value-of select="count(QBXML/QBXMLMsgsRs/EntityQueryRs)"/>

</xsl:attribute>

<xsl:for-each select="QBXML/QBXMLMsgsRs/EntityQueryRs">

<ROW MODID="1" RECORDID="1">

<xsl:attribute name="RECORDID"><xsl:value-of select="position()"/></xsl:attribute>

<xsl:attribute name="MODID">0</xsl:attribute>

<xsl:attribute name="RECORDID">0</xsl:attribute>

<COL><DATA>

<xsl:for-each select="./CustomerRet">

<xsl:value-of select="FullName"/>

<xsl:value-of select=" '

[rogermax]

Very interesting Dan. This will take some studying. Thanks

[Fenton]

Instead of

<xsl:value-of select=" '

[DanBrill]

Here's a work around: Instead of using the

[rogermax]

DanBrill said:

<COL><DATA>

<xsl:for-each select="./CustomerRet">

<xsl:value-of select="FullName"/>

<xsl:value-of select=" '

[rogermax]

should have refreshed the screen before posting I will also try Fenton's suggestion.

[DanBrill]

Hi Roger and Fenton,

I tried Fenton's suggestion and can't get it to work. I keep getting an error:

"XML Parsing Error: Invalid Character Reference"

There must be a way to do this. Fenton's suggestion seems to be on target.

Thanks,

Dan

[Fenton]

You're right. #11 isn't allowed. Sorry, I should have tested first. #10 and #13 are allowed; but that doesn't solve the problem. Substitution seems like the solution.

[DanBrill]

So we've got this working. But I'm still not convinced that it's the best way to go given that a global text field is only going to hold 64,000 characters. A customer list of 3,000 names might blow the limit. That's a pretty big list, but not unheard of. To be safe I'd have a db where each customer is a record and the value list was created from the name field.

Dan

[rogermax]

That is a good point, and in general I agree. I am struggling to understand each element to know the ramifications of each.

This particular issue for me is with installed user base, needing to find a way to sync the FM data with the QB data in a reasonably transparent and dynamic fashion.

SO- if user wants to post invoice, I need to check whether the QB ListID is available (stored) for that client, if so post with that data as key. If not then query QB for the list, user selects from list and compares the data, syncs, ListID is captured to FM and then on to post invoice.

Same methodology for other events. If anyone has a better way, I am all for looking at it.

My users tend to have between 100-600 client records.

[DanBrill]

I'd sync the lists at the begining of the user's session. Query QB for the entity list and import to FM, using the QB entity ID as the match field. Then the user can create invoices. The name on each invoice will be drawn from the refreshed list of customers in QB. If they don't exist on that list, then the customer needs to be created.

I'm speaking hypothetically of course, since in my experience my data has been flowing from QB to FM. -) But I'd think about that tactic.

A question that you'll need to answer -- and that I don't know the answer to -- is about the creation of QB ID numbers. If you are creating a transaction or a list item in FM and sending it to QB, how is the internal QB ID number assigned? Is it generated automatically by QB, or do you have to tell it what number to assign? If the former, no problem. If the latter, you'll have to take a look at how the ids are created. (In general they come in 2 parts. There is a serial number (in hexadecimal, just for fun) followed by a dash, and then another number that I think is some kind of time stamp, though I haven't figured that out completely.

Well, food for thought.

Enjoy,

Dan

[rogermax]

Thanks for the input. QB supplies the ListID for the Custoer record in QB. It accepts the FullName as a secondary match key (primary if it is all that is supplied) as well. If the FullName is used then spelling must match exact. The ID from QB does have a time stamp after the dash, but the number preceeding I did not reallize was hex. (does that mean I would be hexed if I just plug in that number to an FM field for use?)

At the start I can't rely on FullName because the to sets of data will likely not match exact and I won't have the ListID numbers yet. I don't know how I would get that done automatically.

[DanBrill]

I don't know of any way of automatically matching the db names to the qb names. I've always had to do it manually, matching each record in the database to its corresponding name in QB. If you know the names would be in the same format and always spelled right, you might be able to make it work. But in my case it won't. I might have 2 records in FM (e.g., Smith, John and Smith, Mary) that match to one record in QB (Smith, John and Mary).

If you get it automated, let us know.

Thanks,

Dan

[DanBrill]

By the way, look for Russ Bakers post on converting Hex to dec and back again. Search for 'hex' here in the forum and you'll find it. Very useful.