Jump to content

Value List from xml return


rogermax
 Share

This topic is 6591 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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=" '

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 6591 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.