Hello everyone,
It was great learning stuff today. Let me recap
1) Thanks Dean for pointing out not to store dbname in client var and
instead store in appl var
2) Mischa, your input really got it all started. As per your last email, I
have made some changes and I think thats what I need atleast for now. By the
way instead of using your step 3, I sacrified one column and used the state
column in its place. Reason see below.
3) Thanks Charlie for pointing out query of queries. I used the state column
as a custom1 field and after records are returned by the collection for a
search criteria, I do QOQ on the collection query and filter the results for
the state selected by the user. If state is not selected, then show all the
records from collection.
<!--- Retrieve all StateRegs to be searched --->
<cfquery name="GetStateRegs" datasource="#client.dbname#">
SELECT reg.Reg_ID, elig.Eligibility_No, note.Note_No, reas.Reason_No,
reg.Reg_Name,
reg.State,reg.MaxDuration, reg.Concurrent_Policies, reg.Link,
elig.Eligibility_Text, reas.Reason_Text, note.Note_Text,
IsNull(substring(note.Note_Text,1,30),'') + '...' + IsNull(substring(
elig.Eligibility_Text,1,30),'') + '...' AS ContentsOfCustomField1
FROM (( (Regulations reg left outer join Reason reas ON reg.Reg_ID =
reas.Reason_Reg_ID)
left outer join Eligibility elig on reg.Reg_ID = elig.Elig_Reg_ID )
left outer JOIN Notes note on reg.Reg_ID = note.Note_Reg_ID )
WHERE reg.Country = 'US'
ORDER BY 1,4,2,3
</cfquery>
<!--- Build 'custom' index on query result above --->
<cfindex action="refresh"
collection="StateRegs"
key= "Reg_ID"
type="custom"
title="Reason_Text"
query="GetStateRegs"
body="Reg_Name,MaxDuration,Eligibility_Text,Reason_Text,Note_Text"
custom1="ContentsOfCustomField1"
custom2="state"
custom3="Reason_No"
custom4="Eligibility_No">
Finally, the filter for state in main page -->
<cfif StateList is not "" and StateList is not "ALL">
<!---Mar 5,2007. We do query of query i.e query the collection
to filter by state--->
<cfquery dbtype="query" name="qoq_Coll_StateRegs">
select * from Coll_StateRegs
where custom2 = '#statelist#'
order by score desc
</cfquery>
<cfelse>
<!---Mar 5,2007. show all records from the collection--->
</cfif>
Thank you all of you.
Ajas Mohammed.
On 3/5/07, Mischa Uppelschoten ext 10 <[EMAIL PROTECTED]>
wrote:
My whole suggestion about combining State and Reason_Text in one custom
field, was so that you wouldn't have to sacrifice the Reason_Text
Without trying to understand too much of your query...
step 1: run query
SELECT field1, field2, ..., Reason_Text + "|" + State AS
ContentsOfCustomField1
...
step 2: index query
<cfindex action="refresh"
..
custom1="ContentsOfCustomField1"
... >
step 3: Fire verity query
<cfset myCriteria = form.UserSubmittedCriteria> <- probably need to
filter this for abuse
<cfif form.State NEQ "All">
<cfset myCriteria = myCriteria & "AND custom1 <CONTAINS> " &
form.State> <- not sure if you need angle brackets around "contains"
</cfif>
<cfsearch collection="bla"
criteria = "#myCriteria#"
...>
I hope that makes sense.
/m
: Change the search page so that if the user chooses a state other than
all, the
: following would be added to your Verity query: custom1 CONTAINS [state]
: By verity query, do you mean the query which is used for building
collection?
: If it is, then dont you think it will be too much to index the
collection
: everytime based of the state? Sorry if I am missing something obvious
here...
:
: how do you combine 2 columns in custom field? Like this maybe
: custom1="Reason_Text" contains "Eligibility_Text" custom2="state"
: custom3="whatever" custom4="anythying"
: Thanks
:
:
:
: On 3/5/07, Mischa Uppelschoten ext 10 <[EMAIL PROTECTED]><[EMAIL PROTECTED]>
: wrote:
:
: Yes, what youre suggesting is a good idea, however, I would go a bit
further
: and place both Reason_Text and State in that first custom field by
simply
: delimiting the data. Change the search page so that if the user chooses
a
: state other than all, the following would be added to your Verity
query:
:
: custom1 CONTAINS [state]
:
: (Depending on the value of Reason_Text, you may get too many hits and
you can
: resolve that by including the delimiter char in the search (you may
have to
: escape it) or query of query or something).
: /m
:
:
:
: : Hi,
: : I have CF application which I have designed using verity search
feature.
:
: : Overview :
: : The search application lets user search data in tables based of
keywords and
: : the user could also filter by state or just say ALL ie. all states.
I can
: : get results when search is based of ALL STATES since I dont have to
do
: : anything apart from displaying results from the collection. The issue
is
: when
: : user searches by a particular state(only one state at a time is
allowed at
: : this time in appl). I need to somehow filter the results in the
collection
: : based of the state selected by theuser. Any suggestions would be
great. I
: : need some ideas. Look at code below and code marked with my comment
in red.
: : Is it a good idea ?
: : Here is the code just in case :
: : Code for Collection
: : <!--- Retrieve all StateRegs to be searched --->
: : <cfquery name="GetStateRegs" datasource="#client.dbname#">
: : SELECT reg.Reg_ID, elig.Eligibility_No, note.Note_No, reas.Reason_No,
: : reg.Reg_Name ,
: : reg.State,reg.MaxDuration, reg.Concurrent_Policies, reg.Link,
: : elig.Eligibility_Text, reas.Reason_Text, note.Note_Text
: : FROM (( (Regulations reg left outer join Reason reas ON reg.Reg_ID =
: : reas.Reason_Reg_ID )
: : left outer join Eligibility elig on reg.Reg_ID = elig.Elig_Reg_ID)
: : left outer JOIN Notes note on reg.Reg_ID = note.Note_Reg_ID )
: : WHERE reg.Country = US
: : ORDER BY 1,4,2,3
: : </cfquery>
: : <!--- Build custom index on query result above --->
: : <cfindex action="refresh"
: : collection="StateRegs"
: : key= "Reg_ID"
: : type="custom"
: : title="Reg_Name"
: : query="GetStateRegs"
: : body="Reg_Name,MaxDuration,Eligibility_Text,Reason_Text,Note_Text"
: : custom1="Reason_Text" <<<<-- Shall I use State over here
: : custom2="Eligibility_Text"
: : custom3="Reason_No"
: : custom4="Eligibility_No">
: : Let me know if you need any more input from my side.
: : Thanks,
: : --
: : <Ajas Mohammed />
: : http://ajashadi.blogspot.com
: : -------------------------------------------------------------
: : To unsubscribe from this list, manage your profile @
: : http://www.acfug.org?fa=login.edituserform
: :
: : For more info, see http://www.acfug.org/mailinglists
: : Archive @ http://www.mail-archive.com/discussion%40acfug.org/
: : List hosted by FusionLink
: : -------------------------------------------------------------
: Mischa Uppelschoten
: The Bankers Exchange, Inc.
: 2020 Hills Avenue NW
: Atlanta, GA 30318
: Phone: (404) 605-0100 ext. 10
: Fax: (404) 355-7930
: Web: www.BankersX.com
: Follow this link for Instant Web Chat:
: http://www.bankersx.com/Contact/chat.cfm?Queue=MUPPELSCHOTEN
:
: ----------------------- Original Message -----------------------
:
: From: "Ajas Mohammed" <[EMAIL PROTECTED]> <[EMAIL PROTECTED]>
: To: [email protected]
: Date: Mon, 5 Mar 2007 10:36:34 -0500
: Subject: [ACFUG Discuss] CF application using verity
:
: Hi,
: I have CF application which I have designed using verity search
feature.
: Overview :
: The search application lets user search data in tables based of keywords
and
: the user could also filter by state or just say ALL ie. all states. I
can
: get results when search is based of ALL STATES since I dont have to do
: anything apartfrom displaying results from the collection. The issue is
when
: user searches by a particular state(only one state at a time is allowed
at
: this time in appl). I need to somehow filter the results in the
collection
: based of the state selected by the user. Any suggestions would be
great. I
: need some ideas. Look at code below and code marked with my comment in
red.
: Is it a good idea ?
: Here is the code just in case :
: Code for Collection
: <!--- Retrieve all StateRegs to be searched --->
: <cfquery name="GetStateRegs" datasource="#client.dbname#">
: SELECT reg.Reg_ID, elig.Eligibility_No, note.Note_No, reas.Reason_No,
: reg.Reg_Name ,
: reg.State,reg.MaxDuration, reg.Concurrent_Policies, reg.Link,
: elig.Eligibility_Text, reas.Reason_Text, note.Note_Text
: FROM (( (Regulations reg left outer join Reason reas ON reg.Reg_ID =
: reas.Reason_Reg_ID )
: left outer join Eligibility elig on reg.Reg_ID = elig.Elig_Reg_ID )
: left outer JOIN Notes note on reg.Reg_ID = note.Note_Reg_ID )
: WHERE reg.Country = US
: ORDER BY 1,4,2,3
: </cfquery>
: <!--- Build custom index on query result above --->
: <cfindex action="refresh"
: collection="StateRegs"
: key= "Reg_ID"
: type="custom"
: title="Reg_Name"
: query="GetStateRegs"
: body="Reg_Name,MaxDuration,Eligibility_Text,Reason_Text,Note_Text"
: custom1="Reason_Text" <<<<-- Shall I use State over here
: custom2="Eligibility_Text"
: custom3="Reason_No"
: custom4="Eligibility_No">
: Let me know if you need any more input from my side.
: Thanks,
: --
: <Ajas Mohammed />
: http://ajashadi.blogspot.com
: -------------------------------------------------------------
: To unsubscribe from this list, manage your profile @
: http://www.acfug.org?fa=login.edituserform
: For more info, see http://www.acfug.org/mailinglists
: Archive @ http://www.mail-archive.com/discussion%40acfug.org/
: List hosted by FusionLink
: -------------------------------------------------------------
: ------------------------------------------------------------- To
unsubscribe
: from this list, manage your profile @
: http://www.acfug.org?fa=login.edituserform For more info, see
: http://www.acfug.org/mailinglists Archive @
: http://www.mail-archive.com/discussion%40acfug.org/ List hosted by
: http://www.fusionlink.com
: -------------------------------------------------------------
: --
: <Ajas Mohammed />
: http://ajashadi.blogspot.com
: -------------------------------------------------------------
: To unsubscribe from this list, manage your profile @
: http://www.acfug.org?fa=login.edituserform
:
: For more info, see http://www.acfug.org/mailinglists
: Archive @ http://www.mail-archive.com/discussion%40acfug.org/
: List hosted by FusionLink
: -------------------------------------------------------------
Mischa Uppelschoten
The Banker's Exchange, Inc.
2020 Hills Avenue NW
Atlanta, GA 30318
Phone: (404) 605-0100 ext. 10
Fax: (404) 355-7930
Web: www.BankersX.com
Follow this link for Instant Web Chat:
http://www.bankersx.com/Contact/chat.cfm?Queue=MUPPELSCHOTEN
----------------------- *Original Message* -----------------------
*From:* "Ajas Mohammed" <[EMAIL PROTECTED]> <[EMAIL PROTECTED]>
*To:* [email protected]
*Date:* Mon, 5 Mar 2007 11:29:02 -0500
*Subject: Re: [ACFUG Discuss] CF application using verity*
> Change the search page so that if the user chooses a state other than
> all, the following would be added to your Verity query: custom1 CONTAINS
> [state]
>
By verity query, do you mean the query which is used for building
collection? If it is, then dont you think it will be too much to index the
collection everytime based of the state? Sorry if I am missing something
obvious here...
how do you combine 2 columns in custom field? Like this maybe
custom1="Reason_Text"
contains "Eligibility_Text" custom2="state" custom3="whatever"
custom4="anythying"
Thanks
On 3/5/07, Mischa Uppelschoten ext 10 <[EMAIL PROTECTED]>
wrote:
>
> Yes, what you're suggesting is a good idea, however, I would go a bit
> further and place both Reason_Text and State in that first custom field by
> simply delimiting the data. Change the search page so that if the user
> chooses a state other than all, the following would be added to your Verity
> query:
>
> custom1 CONTAINS [state]
>
> (Depending on the value of Reason_Text, you may get too many hits and
> you can resolve that by including the delimiter char in the search (you may
> have to escape it) or query of query or something).
> /m
>
>
>
> : Hi,
> : I have CF application which I have designed using verity search
> feature.
>
> : Overview :
> : The search application lets user search data in tables based of
> keywords and
> : the user could also filter by state or just say ALL ie. all states.
> I can
> : get results when search is based of ALL STATES since I dont have to
> do
> : anything apart from displaying results from the collection. The issue
> is when
> : user searches by a particular state(only one state at a time is
> allowed at
> : this time in appl). I need to somehow filter the results in the
> collection
> : based of the state selected by theuser. Any suggestions would be
> great. I
> : need some ideas. Look at code below and code marked with my comment
> in red.
> : Is it a good idea ?
>
> : Here is the code just in case :
> : Code for Collection
> : <!--- Retrieve all StateRegs to be searched --->
> : <cfquery name="GetStateRegs" datasource="#client.dbname#">
> : SELECT reg.Reg_ID, elig.Eligibility_No, note.Note_No, reas.Reason_No,
> : reg.Reg_Name ,
> : reg.State,reg.MaxDuration, reg.Concurrent_Policies, reg.Link,
> : elig.Eligibility_Text, reas.Reason_Text, note.Note_Text
> : FROM (( (Regulations reg left outer join Reason reas ON reg.Reg_ID =
> : reas.Reason_Reg_ID )
> : left outer join Eligibility elig on reg.Reg_ID = elig.Elig_Reg_ID)
> : left outer JOIN Notes note on reg.Reg_ID = note.Note_Reg_ID )
> : WHERE reg.Country = US
> : ORDER BY 1,4,2,3
> : </cfquery>
>
> : <!--- Build 'custom' index on query result above --->
> : <cfindex action="refresh"
> : collection="StateRegs"
> : key= "Reg_ID"
> : type="custom"
> : title="Reg_Name"
> : query="GetStateRegs"
> : body="Reg_Name,MaxDuration,Eligibility_Text,Reason_Text,Note_Text"
> : custom1="Reason_Text" <<<<-- Shall I use State over here
> : custom2="Eligibility_Text"
> : custom3="Reason_No"
> : custom4="Eligibility_No">
>
> : Let me know if you need any more input from my side.
>
> : Thanks,
> : --
> : <Ajas Mohammed />
> : http://ajashadi.blogspot.com
> : -------------------------------------------------------------
> : To unsubscribe from this list, manage your profile @
> : http://www.acfug.org?fa=login.edituserform
> :
> : For more info, see http://www.acfug.org/mailinglists
> : Archive @ http://www.mail-archive.com/discussion%40acfug.org/
> : List hosted by FusionLink
> : -------------------------------------------------------------
>
>
>
>
> Mischa Uppelschoten
> The Banker's Exchange, Inc.
> 2020 Hills Avenue NW
> Atlanta, GA 30318
>
> Phone: (404) 605-0100 ext. 10
> Fax: (404) 355-7930
> Web: www.BankersX.com
> Follow this link for Instant Web Chat:
> http://www.bankersx.com/Contact/chat.cfm?Queue=MUPPELSCHOTEN
> ----------------------- *Original Message* -----------------------
>
> *From:* "Ajas Mohammed" <[EMAIL PROTECTED]> <[EMAIL PROTECTED]>
> *To:* [email protected]
> *Date:* Mon, 5 Mar 2007 10:36:34 -0500
> *Subject: [ACFUG Discuss] CF application using verity*
>
> Hi,
> I have CF application which I have designed using verity search
> feature.
>
> Overview :
> The search application lets user search data in tables based of keywords
> and the user could also filter by state or just say ALL ie. all states.
> I can get results when search is based of ALL STATES since I dont have
> to do anything apart from displaying results from the collection. The
> issue is when user searches by a particular state(only one state at a
> time is allowed at this time in appl). I need to somehow filter the results
> in the collection based of the state selected by the user. Any
> suggestions would be great. I need some ideas. Look at code below and code
> marked with my comment in red. Is it a good idea ?
>
> Here is the code just in case :
> Code for Collection
> <!--- Retrieve all StateRegs to be searched --->
> <cfquery name="GetStateRegs" datasource="#client.dbname#">
> SELECT reg.Reg_ID, elig.Eligibility_No, note.Note_No, reas.Reason_No,
> reg.Reg_Name ,
> reg.State,reg.MaxDuration, reg.Concurrent_Policies, reg.Link,
> elig.Eligibility_Text, reas.Reason_Text, note.Note_Text
> FROM (( (Regulations reg left outer join Reason reas ON reg.Reg_ID =
> reas.Reason_Reg_ID )
> left outer join Eligibility elig on reg.Reg_ID = elig.Elig_Reg_ID )
> left outer JOIN Notes note on reg.Reg_ID = note.Note_Reg_ID )
> WHERE reg.Country = 'US'
> ORDER BY 1,4,2,3
> </cfquery>
>
> <!--- Build 'custom' index on query result above --->
> <cfindex action="refresh"
> collection="StateRegs"
> key= "Reg_ID"
> type="custom"
> title="Reg_Name"
> query="GetStateRegs"
> body="Reg_Name,MaxDuration,Eligibility_Text,Reason_Text,Note_Text"
> custom1="Reason_Text" <<<< -- Shall I use State over here
> custom2="Eligibility_Text"
> custom3="Reason_No"
> custom4="Eligibility_No">
>
> Let me know if you need any more input from my side.
>
> Thanks,
> --
> <Ajas Mohammed />
> http://ajashadi.blogspot.com
> -------------------------------------------------------------
> To unsubscribe from this list, manage your profile @
> http://www.acfug.org?fa=login.edituserform
>
> For more info, see http://www.acfug.org/mailinglists
> Archive @ http://www.mail-archive.com/discussion%40acfug.org/
> List hosted by FusionLink <http://www.fusionlink.com>
> -------------------------------------------------------------
> ------------------------------------------------------------- To
> unsubscribe from this list, manage your profile @
> http://www.acfug.org?fa=login.edituserform For more info, see
> http://www.acfug.org/mailinglists Archive @
> http://www.mail-archive.com/discussion%40acfug.org/ List hosted by
>
http://www.fusionlink.com-------------------------------------------------------------
>
--
<Ajas Mohammed />
http://ajashadi.blogspot.com
-------------------------------------------------------------
To unsubscribe from this list, manage your profile @
http://www.acfug.org?fa=login.edituserform
For more info, see http://www.acfug.org/mailinglists
Archive @ http://www.mail-archive.com/discussion%40acfug.org/
List hosted by FusionLink <http://www.fusionlink.com>
-------------------------------------------------------------
------------------------------------------------------------- To
unsubscribe from this list, manage your profile @
http://www.acfug.org?fa=login.edituserform For more info, see
http://www.acfug.org/mailinglists Archive @
http://www.mail-archive.com/discussion%40acfug.org/ List hosted by
http://www.fusionlink.com-------------------------------------------------------------
--
<Ajas Mohammed />
http://ajashadi.blogspot.com
-------------------------------------------------------------
To unsubscribe from this list, manage your profile @
http://www.acfug.org?fa=login.edituserform
For more info, see http://www.acfug.org/mailinglists
Archive @ http://www.mail-archive.com/discussion%40acfug.org/
List hosted by http://www.fusionlink.com
-------------------------------------------------------------
