Solved

Displaying Data

Posted on 2002-07-17
26
177 Views
Last Modified: 2013-12-24
Is there a way for me to manipulate the display of the following data in my subscriber database in a way that
says of the 96 people who have signed up, x sell on auctions full time, or x sell a certain category?
Here's what I currenly have:

<cfoutput>
Number of Entries: #listview.recordcount#
</cfoutput>

<CFOUTPUT QUERY="listview">
<A HREF="deleted.cfm?ID=#ID#" OnClick="return test()">#Fname# #Lname#</a>
<br>
Title: #title#
Company: #company#
Address:
#Address# #DeptMailStop# - #City#, #State#
#ZipCode# #FCountry#
Phone: #Phone#
Fax: #Fax#
Email: #Email# <br>
Categories of Products Sell Online:
<cfloop from="1" to="36" index="i">
<cfif IsDefined("Products#i#")>
#Evaluate("Products#i#")#
<cfelse>
&nbsp;
</cfif>
</cfloop><br>
Other Categories, if any):
<cfif #ProdDesc# is not "">
#ProdDesc#
<cfelse>
No other category selected.
</cfif><br>
Online auction selling primary business?
<cfif #PrimBus1# is not "">
#PrimBus1#
<cfelse>
#PrimBus2#
</cfif><br>
Amount of Sales:
<cfloop from="1" to="5" index="i">
<cfif IsDefined("AmountSell#i#")>
#Evaluate("AmountSell#i#")#
<cfelse>
&nbsp;
</cfif>
</cfloop><br>
</CFOUTPUT>
   

Thank you,
Ansia
0
Comment
Question by:Ansia
  • 14
  • 9
  • 3
26 Comments
 
LVL 17

Expert Comment

by:anandkp
ID: 7161281
can u pls brief up ur q's a bit more.

forget the code - just tell me what u want to do & what r ur db fields.

just give me a brief flow of what u have - thats all & i'll get back to u asap.

K'Rgds
Anand
0
 

Author Comment

by:Ansia
ID: 7161840
I have a database with subscriber information like name, address, lots of categories ....

I am wondering if there is a way that I could display something that says of the 96 people who have signed up, x sell on auctions full time, or x sell a certain category?

Can you do seperate listmessages for the cfoutput?  I have about 30 categories though.  I'm not quite sure how to approach this.

Thanks, :)
0
 
LVL 17

Expert Comment

by:anandkp
ID: 7161881
hi ansia,

from what i can make out is that u r having a display problem with online & primary selling.

the loop u have currently is proper.

Except that u r running a loop for each 36 [online] & 5 [prim business]times.

can u tell me what exactly r u storing in the DB for the above 2 values.

if i can get to know it - i can suggest a better way - whihc would remove the need of the inner loop & also facilitate a better display.

let me know.

K'Rgds
Anand
0
 

Author Comment

by:Ansia
ID: 7163201
Actually, my display page is correct.

I want to make another page to summarize the data.

I am looking for some means for to look at the all the data collected in summary form. So, can we see in a tabular fashion the answers to any one of the questions answered in the sub form. For example, can we look at all the subs at once and see how many answered the question "Is this your primary business?" Can we see, visually, how many of the 105 answered yes and how many answered no? Otherwise, we need to do it by hand.


Thanks! :)
Ansia
0
 

Author Comment

by:Ansia
ID: 7163561
For example:

People who sell online ... number
People who don't sell online ... number

Under 1,000 ... number
5,000 - 10,000 ... number
10,000 - 20,000 ... number
20,000 - 100,000 ... number
Over 10,000 ... number

etc.

I tried using recordcount but I'm not quite getting it.

Thanks,
Ansia
0
 
LVL 17

Expert Comment

by:anandkp
ID: 7164025
Hi Ansia,

This is simple
eveytime u display ur record keep a check for Y/N

<Cfset yescount = 0>
<cfet nocount = 0>

<cfquery ....>
  ur query here
</cfquery>

<cfloop> <!--- ie when ur displayign ur report --->

  <cfif recordvalue = 'Y'>
    <cfset YesCOunt = Incrementvalue(yescount)>
  <cfelse>
   <cfset NoCount = incrementvalue(noCount)>
  </cfif>

</cfloop>

then use this - instead of recordcount to display individual counts of yes & No.

Total Yes = #YesCount#
Total No = #NoCount#

hope this helps

===========================================================

else easy way out woudl be ..

U'll need to use 2 queries to display ur data in that case ... one whihc filters all who answered yes & other for all who answered No.

then use record count for displaying total count at the end .. .so u know how many of 105 said yes & how many NO.

===========================================================

K'Rgds
Anand
0
 

Author Comment

by:Ansia
ID: 7164473
Thanks!  I'll go give this a try! :)
0
 

Author Comment

by:Ansia
ID: 7164656
I get this error:
Error Diagnostic Information
Attribute set validation error in tag CFLOOP

The tag has an invalid attribute combination: the most likely attribute combination is

Required attributes: 'FROM,INDEX,TO'. Optional attributes: 'STEP'.
Other possible combinations are
Required attributes: 'COLLECTION,ITEM'. Optional attributes: None.
Required attributes: 'INDEX,LIST'. Optional attributes: 'DELIMITERS'.
Required attributes: 'QUERY'. Optional attributes: 'ENDROW,STARTROW'.
Required attributes: 'CONDITION'. Optional attributes: None.
The error occurred while processing an element with a general identifier of (CFLOOP), occupying document position (27:1) to (27:8).

0
 
LVL 17

Expert Comment

by:anandkp
ID: 7164684
Hi ansia ....

uve got to set the attribute for the cfloop - right ???

i just gave a eg of how to go abt with this logic ... the cfloop was just a dummy to replicate ur code there ... i hope this clears ur doubt

let me know
K'Rgds
Anand
0
 

Author Comment

by:Ansia
ID: 7164729
yikes .. boy do I feel silly ... I'll be back ... :)
0
 
LVL 17

Expert Comment

by:anandkp
ID: 7164757
OK i'll wait for u :)

0
 

Author Comment

by:Ansia
ID: 7165257
Actually, I don't think that I want to CFLOOP the queries but display each field seperately.

Here's my code for what I'm trying to achieve for just one of the fields:
Number of Subscribers who sell under $1,000:
      <cfset yescount = 0>
      <cfquery datasource="aartra14.data" name="AmountSell1">
      SELECT AmountSell1 FROM subscribers
      </cfquery>
<cfoutput query="AmountSell1">
        <cfif recordvalue = 'Under $1,000'>
        <cfset YesCount = Incrementvalue(yescount)>
        </cfif>
   Total Yes = #YesCount#
</cfoutput>

But alas! ..... it doesn't work .........
0
 

Author Comment

by:Ansia
ID: 7165294
Sorry ... that was stupid ...
I'm using this code now:

      <cfset yescount = 0>
      <cfquery datasource="aartra14.data" name="AmountSell1">
      SELECT AmountSell1 FROM subscribers
      </cfquery>
<cfoutput query="AmountSell1">
        <cfif recordvalue = 'Y'>
        <cfset YesCount = Incrementvalue(yescount)>
        </cfif>
   Total Yes = #YesCount#
</cfoutput>

And I get this error:
Error in expression:

 recordvalue = 'Y'

 Invalid conditional expression. Perhaps you are using = instead of EQ or IS to compare values.

 The error occurred while processing an element with a general identifier of (CFIF), occupying document position (27:9) to
 (27:32).
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:Ansia
ID: 7165598
I messed up.

For one thing, in my access database, these field names that are checkboxes are not set as Yes/No fields. They are text fields.

And on the input form the checkboxes are named for example,

Checkbox Field name = AmountSell1  value= $Under 1,000
Checkbox Field name = AmountSell2  value= $1,000-$2,000

and so on ..........

My page already has 225 entries.  I can't rewrite this database and lose all my entries.  Given this, is there still a way that I can recordcount the VALUES of each field?

0
 
LVL 17

Accepted Solution

by:
anandkp earned 300 total points
ID: 7166241
Hi Ansia,

Now i get u ...
This is even simpler

<CFSET YESCOUNT = 0>
<CFSET NOCOUNT = 0>

<CFSET YESLIST = "$Under 1,000~$1,000-$2,000"><!--- insert all values in a list here - whihc u want to appear in YES list
Since ur list is predefined - u can use whatever vlues here u want --->
<CFSET NOLIST = "$6,000-$7,000~$7,000-$8,000"><!--- insert all values in a list here - whihc u want to appear in NO list
Since ur list is predefined - u can use whatever vlues here u want --->


<CFQUERY datasource="aartra14.data" name="AmountSell1">
     SELECT AmountSell1 FROM subscribers
</CFQUERY>

<CFOUTPUT query="AmountSell1">
     <CFIF LISTCONTAINSNOCASE(YESLIST,AMOUNTSELL1,'~') GT 0>
          <CFSET YESCOUNT = INCREMENTVALUE(YESCOUNT)>
     <CFELSEIF LISTCONTAINSNOCASE(NOLIST,AMOUNTSELL1,'~') GT 0>
          <CFSET NOCOUNT = INCREMENTVALUE(NOCOUNT)>
     </CFIF>
</CFOUTPUT>

<CFOUTPUT>
Total Yes : #YesCOunt#
Total No : #NoCount#
</CFOUTPUT>

<!--- Now i suppose this is what u wanted right ... let me know incase u ahve something else --->
0
 

Author Comment

by:Ansia
ID: 7166421
With this code, I'm getting no output display at all. And the total yes and total no all say 0.


<CFSET YESCOUNT = 0>
<CFSET NOCOUNT = 0>

<CFSET YESLIST = "$Under 1,000">
<CFSET NOLIST = "$1,000 - $5,000~$5,000 - $10,000~$10,000 - $25,000~Over $$25,000">

<CFQUERY datasource="aartra14.data" name="AmountSell1">
    SELECT AmountSell1 FROM subscribers
</CFQUERY>

<CFOUTPUT query="AmountSell1">
    <CFIF LISTCONTAINSNOCASE(YESLIST,AMOUNTSELL1,'~') GT 0>
         <CFSET YESCOUNT = INCREMENTVALUE(YESCOUNT)>
    <CFELSEIF LISTCONTAINSNOCASE(NOLIST,AMOUNTSELL1,'~') GT 0>
         <CFSET NOCOUNT = INCREMENTVALUE(NOCOUNT)>
    </CFIF>
</CFOUTPUT>

<CFOUTPUT>
Total Yes : #YesCOunt#
Total No : #NoCount#
</CFOUTPUT>

0
 
LVL 17

Expert Comment

by:anandkp
ID: 7166433
Hi Ansia,

try this

<CFOUTPUT query="AmountSell1">
   <CFIF LISTCONTAINSNOCASE(YESLIST,AMOUNTSELL1,'~') NEQ 0>
        <CFSET YESCOUNT = INCREMENTVALUE(YESCOUNT)>
   <CFELSEIF LISTCONTAINSNOCASE(NOLIST,AMOUNTSELL1,'~') NEQ 0>
        <CFSET NOCOUNT = INCREMENTVALUE(NOCOUNT)>
   </CFIF>
</CFOUTPUT>

===========================================================

can u send me the data contained in AMOUNTSELL1 !!!
just use output query & print me the data.

K'Rgds
Anand
0
 

Author Comment

by:Ansia
ID: 7166444
you can view the page that has the output display here:

http://www.aartrageous.com/OAB/list.cfm


I want to make a seperate page with the summary info.  
What I'm going to want to summarize is ....

Amount the sell each month ...
Primary Business yes or no .....
Categories they sell ...

You can view the sign up page here:
http://www.aartrageous.com/OAB/bottom.cfm


Thanks! :)

0
 
LVL 17

Expert Comment

by:anandkp
ID: 7166503
Hi Ansia,

can u do one thing - pls send me the code of the display page u have - this is imp for me.

+ can u send me a HTML of how u want to display the summary.

if u can do this - i'll write teh complete page for U & then explain u with comments - on how the page was written.

let me know.

u could mail me anand@direct2s.com

K'Rgds
Anand
0
 

Author Comment

by:Ansia
ID: 7166594
cool~!! thanks .. it's on its way ...
0
 
LVL 1

Expert Comment

by:lpkuah
ID: 7176396
Since you're looking for a summary, how about trying these queries :

-- to get total for primary business
select 'People who sell online', count(PrimBus1) as online
from subscribers
union
select 'People who don't sell online', count(PrimBus2) as online
from subscribers


-- to get total for each category
select 'Under $1,000' as category, count(amountSell1) as amt
from subscribers
union
select '$5,000 - $10,000', count(amountSell2)
from subscribers
union
select '$10,000 - $20,000', count(amountSell3)
from subscribers
union
select '$20,000 - $100,000', count(amountSell4)
from subscribers
union
select 'Over $100,000', count(amountSell5)
from subscribers


"count" function gives us the recordcount of rows that meet the stated criteria
0
 
LVL 17

Expert Comment

by:anandkp
ID: 7176416
Hi lpkuah,

yes that can help out - but dont u think - thats hitting tooooooo many queries on a page ???

K'Rgds
Anand
0
 
LVL 1

Expert Comment

by:lpkuah
ID: 7176803
Basically it's just 2 queries to get the summary data -

1) to get the total for primary business
2) to get the total for each category

If you'll notice, the second query is actually just one connection to the database because I've used "union"
0
 

Author Comment

by:Ansia
ID: 7177489
and then to output the queries, would you list it as:

#PrimBus1.count#

etc?
0
 
LVL 1

Expert Comment

by:lpkuah
ID: 7177637
It would appear something like this ->

<cfquery name="Query1" datasource="#DSN#">
select 'People who sell online' as desc, count(PrimBus1) as amt
from subscribers
union
select 'People who don't sell online', count(PrimBus2)
from subscribers
</cfquery>

<cfoutput query="Query1">
    #desc# = #amt#
</cfoutput>


<cfquery name="Query2" datasource="#DSN#">
select 'Under $1,000' as category, count(amountSell1) as amt
from subscribers
union
select '$5,000 - $10,000', count(amountSell2)
from subscribers
union
select '$10,000 - $20,000', count(amountSell3)
from subscribers
union
select '$20,000 - $100,000', count(amountSell4)
from subscribers
union
select 'Over $100,000', count(amountSell5)
from subscribers
</cfquery>

<cfoutput query="Query2">
    #category# = #amt#
</cfoutput>
0
 

Author Comment

by:Ansia
ID: 7189330
Thank you very much for all of your help, Anand~! :)
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now