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&"&-fi
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!
https://server.com/fmi/xml/fmresultset.xml?-db=Invoices&-lay=waGetEmails&-sortfield.1=Leads_Clients::Email_Address&Invoice_Items::Description="&ProductDescription&"&-fi
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!
Member_2_908359
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
ASKER
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,
You've kinda gotten me all twisted here. I'm a little lost.
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,
You've kinda gotten me all twisted here. I'm a little lost.
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.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.