How to return unique values from XML Custom Web Publishing...???

I'm using the following call to return email addresses from our database based on buyers of a certain product in our database:

https://server.com/fmi/xml/fmresultset.xml?-db=Invoices&-lay=waGetEmails&-sortfield.1=Leads_Clients::Email_Address&Invoice_Items::Description="&ProductDescription&"&-find

This returns results just fine, however, if the same person bought the same type of item more than once their email is getting returned multiple times.

How can I filter this so that it only returns unique values?  Basically, I  need the equivalent of a SQL SELECT DISTINCT statement.

Any information would be greatly appreciated.  thanks!
LVL 11
Andrew AngellCo-Owner / DeveloperAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Computer101Connect With a Mentor Commented:
PAQed with points refunded (500)

Computer101
EE Admin
0
 
lesouefCommented:
you should issue the query to the customers table, not the invoices. this assumes you have a link in between the customers and the invoice_items table, which you may not have
0
 
Andrew AngellCo-Owner / DeveloperAuthor Commented:
I don't see what that would help...???  The criteria I'm searching on is in Invoice_Items.  The data that I'm actually returning is in Leads_Clients (customers).  These are related based on an invoice.  Invoices::ID relates to Invoice_Items::ID and Leads_Clients::ID relates to Invoices::CustomerID.  That's the whole point of a relational database, isn't it?  If I have to keep creating relationships everything then I'm confused.

Also, would that even help my problem?  If just select from the Invoice_Items with a relationship between the 2, say Leads_Clients::CustomerID and Invoice_Items::CustomerID, I'd still get an email address back for every item in Invoice_Items where there was a match.  Which would still be multiple.

You've kinda gotten me all twisted here.  I'm a little lost.
0
 
lesouefCommented:
I was just trying to find a way to get a single record as the result, and I only see the client table to do this. Ignore my idea if it confuses you. I am always thinking what I would do with a regular FM client, I am not using theses web things as it makes everything more difficult.
0
 
Andrew AngellCo-Owner / DeveloperAuthor Commented:
I got it to work by creating a recordset and adding each email to it within the parsing loop.  Then I can use sorting and only print emails that aren't equal to each other.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.