sorting list and getting unique items 1

[doyle9732]

I am trying to make changes to a huge application that I didn't write.

The application submits samples to multiple emails based on checkboxes on a form. Some checkboxes have multiple emails associated with it - in a database table, delimited by commas.

The problem is multiple emails. One poor soul has his name associated with every checkbox, so he gets 5 emails for the same submission. This is what I'm trying to fix.

The existing code is:
<cfquery name="Microscopymail2" DATASOURCE="EASwitch" dbtype="ODBC">
SELECT Projects.ProjectID, Projects.ProjectEmails, Projects.ProjectPeople, Projects.ProjectName
FROM Projects
WHERE Projects.ProjectID IN
(<cfloop index="MicroscopyprojectID" list="#finalSub.MicroscopySubmissions#" delimiters=",">
#MicroscopyprojectID#,</cfloop>0)
;
</cfquery>

I'm not entirely sure why it was done this way.

I've put the output into a sorted list, but can't think how I would compare them to delete any repetitions. (My reference book is Cold Fusion 4.0, so it didn't even have ListSort as a function!!)

Any help or direction would be appreciated....

Stephanie

 

[r937]

nice query

i see a nested loop there that appears to use the results of some other query, finalSub?

so when you say "I've put the output into a sorted list" which one are you talking about?

if you have a query that says

... where ProjectID in ( 3, 7, 9, 9, 11, 11, 13 )

then you should nevertheless only get 5 rows back

i'm not sure i understand what the problem is



rudy

http://r937.com/

SQL Consulting

 

[doyle9732]

The problem is that each of those ProjectID could have John Smith associated with each of them (3,7,9,11,13) - so poor John Smith gets 5 copies of the submission. The column for emails in the database (associated with ProjectID) is name@domain.com,name2@domain.com,name3@domain.com. That is what I'm trying to sort. I was trying to get the sorted list and then compare the first with the second, if they are the same, delete the second from the list, compare the first with the third, if they're the same, delete the third from the list, if they're different, move on and compare the third with the fourth etc. But I couldn't seem to make the code work. I don't often use cfloop, so I really don't know how to make it work for me. I don't suppose there's a ready made function 'ListUnique' or something like that?

Thanks

Stephanie

 

[r937]

i am totally lost

i still do not understand where finalSub comes from and why you aren't doing a join instead

did you understand this --

... where ProjectID in ( 3, 7, 9, 9, 11, 11, 13 )

the query will return 5 rows, not 7

i don't see where the problem is



rudy

http://r937.com/

SQL Consulting

 

[doyle9732]

finalSub is just the query that identifies which submission. On the form, there's 4 different categories that users can choose. In those 4 categories are various subcategories - each different from the other subcategories.

For example....let the categories be shapes.

Category 1 = triangles
Category 2 = squares
Category 3 = circles
Category 4 = rectangles

in Triangles are 2 subCats checkboxes = red and = blue
in Squares are 1 subCats checkbox = yellow
in Circles are 3 subCats checkboxes = green = purple and = orange
in Rectangles are 2 subCats checkboxes = white and = black.

Let's just take Circles....the user has checked off all 3 subCats for Circles...let them be projectID 1, 2 and 3. The email association in the database is:

ProjectID1
name: green
emails: John.Smith@domain.com,name1@domain.com
ProjectID2
name: purple
emails: name4@domain.com,John.Smith@domain.com,name1@domain.com
ProjectID3
Name: orange
emails: John.Smith@domain.com, name178@domain.com

So for this submission John Smith will get 3 copies, name1 will get 2 copies and the rest will just get one copy.

It is the emails variable that gets called in the cfmail, so I'd like to take the combined list and make the final list to cfemail instead of looping through them as they appear to be doing.

As for the joining....I have no idea why they chose to do things this way.....I barely understand the SQL statement with the loop in it. It is such a huge application that I shudder to change too many things for fear of having a domino effect of errors.

thanks!
Stephanie

 

[r937]

so the query that you showed in your first post is immaterial to this, right?

i shall ask a third time, did you understand that when you run this query --

... where ProjectID in ( 3, 7, 9, 9, 11, 11, 13 )

the query will return 5 rows, not 7

in other words, for this query, the fact that there's duplication inside the IN (...) does not matter

i don't see a CFMAIL anywhere, so i suspect that the CFMAIL is using the raw results from the finalSub query, which i cannot see

i shall therefore assume that if you use the keyword DISTINCT in the finalSub query, you will get unique emails

but it's only a guess



rudy

http://r937.com/

SQL Consulting

 

[doyle9732]

I don't get duplicates in the ProjectID I get the duplicates in the email column associated with the ProjectIDs.

I mentioned I wanted a way to get a list with unique items. I have a sorted list with this code:

<cfset MailList = "">
<cfoutput query="Microscopymail2">
<cfset MailList = "#MailList##ProjectEmails#,">
sorted is >#ListSort(MailList, "Text")#<

In a submission as above, with the 3 subcats checked, the first time in the query it finds the ProjectID1 with emails: John.Smith@domain.com,name1@domain.com,
and my MailList is "John.Smith@domain.com,name1@domain.com" then onto the second subcat with emails: name4@domain.com,John.Smith@domain.com,name1@domain.com and now my MailList is "John.Smith@domain.com,name1@domain.com,name4@domain.com,John.Smith@domain.com,name1@domain.com" and finally, the last subcat with emails: John.Smith@domain.com, name178@domain.com and my MailList now is "John.Smith@domain.com,name1@domain.com,name4@domain.com,John.Smith@domain.com,name1@domain.com,John.Smith@domain.com,name178@domain.com"

Then, I can sort the list alphabetically >#ListSort(MailList, "Text")#< to be:
SortedList = "John.Smith@domain.com,John.Smith@domain.com,John.Smith@domain.com,name1@domain.com,name1@domain.com,name178@domain.com,name4@domain.com"

The cfmail uses the Microscopymail2 query, so it's running into the duplication of emails. I want to make the list unique emails, drop the query from the CFmail and just send out one copy of the submission.

thanks!
Stephanie

 

[erwino]

assign the list of emails to a second list, check the second list using ListFind(list, value[,delimiters]) to check if the email is already in the (second) list, if not, add.


Erwin Oosterhoorn
Analyst Programmer,
ice hockey player/fan.

 

[TruthInSatire]

Rudy: she's saying that every one of the "five" rows returned have the same guys email in the email list because the emails are associated to the ID's.

Stephanie: erwino's solution isn't a bad one. i'll elaborate.

<cfset newMailList = "">

<cfloop from = "1" to = "listLen(mailList)" index = "uniqueMail">
<cfif listFind(newMailList, listGetAt(mailList, uniqueMail)) eq 0>
<cfset newMailList = newMailList & listGetAt(mailList, uniqueMail) & ",">
</cfif>
</cfloop>
<!--- remove the last comma --->
<cfset newMailList = mid(newMailList, 1, evaluate(len(newMailList)-1))>
<cfoutput>#newMailList#</cfoutput>

this should be unique list of email addresses, it may need a little tweeking, i'm tired.
-Travis

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1959-2001)

 

[doyle9732]

Thank you all for responding.....I have started implementing the suggestion, but got side-tracked with other work....but I wanted to acknowledge my thanks!

I am in the process of tweeking the code you provided (BombBoy) because it doesn't seem to pick up on the duplicate emails - but I'm just at the beginning of my 'tweek process' and I'm sure I will get it up and running.

And the real 'John Smith' at my work will surely be grateful for the decrease in emails!

thanks again!
Stephanie

 

[TruthInSatire]

sorry Stephanie,
I forgot a set of pound signs, I just tested it and it works great:

<cfset newMailList = "">

<cfloop from = "1" to = "#listLen(mailList)#" index = "uniqueMail">
<cfif listFind(newMailList, listGetAt(mailList, uniqueMail)) eq 0>
<cfset newMailList = newMailList & listGetAt(mailList, uniqueMail) & ",">
</cfif>
</cfloop>
<!--- remove the last comma --->
<cfset newMailList = mid(newMailList, 1, evaluate(len(newMailList)-1))>
<cfoutput>#newMailList#</cfoutput>


Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)

 

[doyle9732]

thanks Bombboy.....I actually had the pound signs, but had many other issues to follow through the code....but happily all appears to be working now.

You are a superstar! Thanks again.

Stephanie