Re: COLCOUNT not working
Hi, You can make a filter with 2 set field actions: - the 1st one is just setting a dummy field with the result of the query you want to count the results - the 2nd one uses the $LASTCOUNT$ keyword or you use a set field with SQL source, and write a query like SELECT COUNT(Field1) FROM table_Name WHERE query Best regards, ARSmarts support team ARSmarts, your most useful AR System companion. www.ARSmarts.com On 10 May 2010, at 17:41, Raj wrote: > Hi All, > Setting a field with COLCOUNT($COLUMN FIELD ID$) in a Set Fields > action of a Filter on Submit. Seems is it not working. Always setting > the field to a constant value of 15. > Would like to set the field with the number of rows in that particular > table. Is the any other way around to do this? > How can it be done with the help of Direct SQL ? > > Please advise. > Thanks, > Ravi > > ___ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are" ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are"
Re: COLCOUNT not working
Thanks for the reply. so, DB ID of Table Name = Table1 DB ID of Column in the table = Column Field ( Only one column in this table ) then my Set Field with SQL Source should look like : SELECT COUNT(Column FIeld) FROM Table1 WHERE query ? Please let me know. Thanks. On Mon, May 10, 2010 at 9:09 AM, ARSmarts Support wrote: > ** > Hi, > > You can make a filter with 2 set field actions: > - the 1st one is just setting a dummy field with the result of the query > you want to count the results > - the 2nd one uses the $LASTCOUNT$ keyword > > or > you use a set field with SQL source, and write a query like SELECT > COUNT(Field1) FROM table_Name WHERE query > > Best regards, > ARSmarts support team > > > > ARSmarts, your most useful AR System companion. > www.ARSmarts.com > > On 10 May 2010, at 17:41, Raj wrote: > > Hi All, > Setting a field with COLCOUNT($COLUMN FIELD ID$) in a Set Fields > action of a Filter on Submit. Seems is it not working. Always setting > the field to a constant value of 15. > Would like to set the field with the number of rows in that particular > table. Is the any other way around to do this? > How can it be done with the help of Direct SQL ? > > Please advise. > Thanks, > Ravi > > > ___ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are" > > > > > > > > _attend WWRUG10 www.wwrug.com ARSlist: "Where the Answers Are"_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are"
Re: COLCOUNT not working
So, if I use the first way you mentioned: Since, I don't have the dummy field, so do I need to add a temp field to that form. If so then still what should I be using to set the count? As the need is to set count of number of rows in the table to a field.( Table contains only one column but will have multiple rows) and then use $LASTCOUNT$ ( As COLCOUNT is not working) ? Please let me know. Thank you. On Mon, May 10, 2010 at 9:09 AM, ARSmarts Support wrote: > ** > Hi, > > You can make a filter with 2 set field actions: > - the 1st one is just setting a dummy field with the result of the query > you want to count the results > - the 2nd one uses the $LASTCOUNT$ keyword > > or > you use a set field with SQL source, and write a query like SELECT > COUNT(Field1) FROM table_Name WHERE query > > Best regards, > ARSmarts support team > > > > ARSmarts, your most useful AR System companion. > www.ARSmarts.com > > On 10 May 2010, at 17:41, Raj wrote: > > Hi All, > Setting a field with COLCOUNT($COLUMN FIELD ID$) in a Set Fields > action of a Filter on Submit. Seems is it not working. Always setting > the field to a constant value of 15. > Would like to set the field with the number of rows in that particular > table. Is the any other way around to do this? > How can it be done with the help of Direct SQL ? > > Please advise. > Thanks, > Ravi > > > ___ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are" > > > > > > > > _attend WWRUG10 www.wwrug.com ARSlist: "Where the Answers Are"_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are"
Re: COLCOUNT not working
You can make use of the view created by ars. Here is an example: suppose you have a table name HPD:Help Desk, and count the New incidents in it. The sql statement would be SELECT Count(Case_ID) FROM HPD_Help_Desk WHERE Status = 0 Status = 0 because it is the integer corresponding to the New value of the status field Best regards, ARSmarts support team ARSmarts, your most useful AR System companion. www.ARSmarts.com On 10 May 2010, at 18:41, Ravi wrote: > ** Thanks for the reply. > so, > DB ID of Table Name = Table1 > DB ID of Column in the table = Column Field ( Only one column in this table ) > then my Set Field with SQL Source should look like : > SELECT COUNT(Column FIeld) FROM Table1 WHERE query ? > > Please let me know. > > Thanks. > > > On Mon, May 10, 2010 at 9:09 AM, ARSmarts Support > wrote: > ** > Hi, > > You can make a filter with 2 set field actions: > - the 1st one is just setting a dummy field with the result of the query you > want to count the results > - the 2nd one uses the $LASTCOUNT$ keyword > > or > you use a set field with SQL source, and write a query like SELECT > COUNT(Field1) FROM table_Name WHERE query > > Best regards, > ARSmarts support team > > > > ARSmarts, your most useful AR System companion. > www.ARSmarts.com > > On 10 May 2010, at 17:41, Raj wrote: > >> Hi All, >> Setting a field with COLCOUNT($COLUMN FIELD ID$) in a Set Fields >> action of a Filter on Submit. Seems is it not working. Always setting >> the field to a constant value of 15. >> Would like to set the field with the number of rows in that particular >> table. Is the any other way around to do this? >> How can it be done with the help of Direct SQL ? >> >> Please advise. >> Thanks, >> Ravi >> >> ___ >> UNSUBSCRIBE or access ARSlist Archives at www.arslist.org >> attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are" > > > > > > > _attend WWRUG10 www.wwrug.com ARSlist: "Where the Answers Are"_ > > _attend WWRUG10 www.wwrug.com ARSlist: "Where the Answers Are"_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are"
Re: COLCOUNT not working
You have 2 forms: form1 which contains the field where you want to store the number of records you find in form2. Create a temp field in form1: zTmp Create an optional field in form1 to hold the number of records found in form2: RowCount Create a filter attached to form1: 1st action: Set field from: Form1 No match: Set fields to $NULL$ Multiple match: Use 1st matching Set Field if: the query you had in your table field Set Fields: zTmp <-- $1$ 2nd action Set field from: Current Transaction Set Fields: RowCount <-- $LASTCOUNT$ Best regards, ARSmarts support team ARSmarts, your most useful AR System companion. www.ARSmarts.com On 10 May 2010, at 18:48, Ravi wrote: > ** So, if I use the first way you mentioned: > Since, I don't have the dummy field, so do I need to add a temp field to that > form. > If so then still what should I be using to set the count? As the need is to > set count of number of rows in the table to a field.( > Table contains only one column but will have multiple rows) and then use > $LASTCOUNT$ ( As COLCOUNT is not working) ? > Please let me know. > Thank you. > > On Mon, May 10, 2010 at 9:09 AM, ARSmarts Support > wrote: > ** > Hi, > > You can make a filter with 2 set field actions: > - the 1st one is just setting a dummy field with the result of the query you > want to count the results > - the 2nd one uses the $LASTCOUNT$ keyword > > or > you use a set field with SQL source, and write a query like SELECT > COUNT(Field1) FROM table_Name WHERE query > > Best regards, > ARSmarts support team > > > > ARSmarts, your most useful AR System companion. > www.ARSmarts.com > > On 10 May 2010, at 17:41, Raj wrote: > >> Hi All, >> Setting a field with COLCOUNT($COLUMN FIELD ID$) in a Set Fields >> action of a Filter on Submit. Seems is it not working. Always setting >> the field to a constant value of 15. >> Would like to set the field with the number of rows in that particular >> table. Is the any other way around to do this? >> How can it be done with the help of Direct SQL ? >> >> Please advise. >> Thanks, >> Ravi >> >> ___ >> UNSUBSCRIBE or access ARSlist Archives at www.arslist.org >> attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are" > > > > > > > _attend WWRUG10 www.wwrug.com ARSlist: "Where the Answers Are"_ > > _attend WWRUG10 www.wwrug.com ARSlist: "Where the Answers Are"_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are"
Re: COLCOUNT not working
Raj, sum(fieldname) is the function used across most standard databases to do something similar to a colcount() update table tablename set fieldname = sum(field2name) where your conditions... Just curious - what are your actions on your filter that doesn't seem to work? Hope this helps.. Cheers Joe From: Raj To: arslist@ARSLIST.ORG Sent: Mon, May 10, 2010 11:41:19 AM Subject: COLCOUNT not working Hi All, Setting a field with COLCOUNT($COLUMN FIELD ID$) in a Set Fields action of a Filter on Submit. Seems is it not working. Always setting the field to a constant value of 15. Would like to set the field with the number of rows in that particular table. Is the any other way around to do this? How can it be done with the help of Direct SQL ? Please advise. Thanks, Ravi ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are" ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are"
Re: COLCOUNT not working
Joe, Sorry to disagree with you, but 'sum' gives you an adding of all the values in a column.whereas colcount gives you the number of records returned.. From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Joe DeSouza Sent: Monday, May 10, 2010 12:02 PM To: arslist@ARSLIST.ORG Subject: Re: COLCOUNT not working ** Raj, sum(fieldname) is the function used across most standard databases to do something similar to a colcount() update table tablename set fieldname = sum(field2name) where your conditions... Just curious - what are your actions on your filter that doesn't seem to work? Hope this helps.. Cheers Joe _ From: Raj To: arslist@ARSLIST.ORG Sent: Mon, May 10, 2010 11:41:19 AM Subject: COLCOUNT not working Hi All, Setting a field with COLCOUNT($COLUMN FIELD ID$) in a Set Fields action of a Filter on Submit. Seems is it not working. Always setting the field to a constant value of 15. Would like to set the field with the number of rows in that particular table. Is the any other way around to do this? How can it be done with the help of Direct SQL ? Please advise. Thanks, Ravi ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are" _attend WWRUG10 www.wwrug.com ARSlist: "Where the Answers Are"_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are"
Re: COLCOUNT not working
I apologize for my error in my previous email. I just realized I helped you to colsum and not colcount.. I read and posted it in a hurry.. Joe From: Ravi To: arslist@ARSLIST.ORG Sent: Mon, May 10, 2010 12:48:24 PM Subject: Re: COLCOUNT not working ** So, if I use the first way you mentioned: Since, I don't have the dummy field, so do I need to add a temp field to that form. If so then still what should I be using to set the count? As the need is to set count of number of rows in the table to a field.( Table contains only one column but will have multiple rows) and then use $LASTCOUNT$ ( As COLCOUNT is not working) ? Please let me know. Thank you. On Mon, May 10, 2010 at 9:09 AM, ARSmarts Support wrote: ** > >Hi, > > >You can make a filter with 2 set field actions: >- the 1st one is just setting a dummy field with the result of the query you >want to count the results >- the 2nd one uses the $LASTCOUNT$ keyword > > >or >you use a set field with SQL source, and write a query like SELECT >COUNT(Field1) FROM table_Name WHERE query > > >Best regards, >ARSmarts support team > > > > > > >ARSmarts, your most useful AR System companion. >www.ARSmarts.com > > >On 10 May 2010, at 17:41, Raj wrote: > >Hi All, >>Setting a field with COLCOUNT($COLUMN FIELD ID$) in a Set Fields >>action of a Filter on Submit. Seems is it not working. Always setting >>>>the field to a constant value of 15. >>Would like to set the field with the number of rows in that particular >>table. Is the any other way around to do this? >>How can it be done with the help of Direct SQL ? >> >>Please advise. >>>>Thanks, >>Ravi >> >>___ >>UNSUBSCRIBE or access ARSlist Archives at www.arslist.org >>attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are" >> > > > > > > > >_attend WWRUG10 www.wwrug.com ARSlist: "Where the Answers Are"_ > _attend WWRUG10 www.wwrug.com ARSlist: "Where the Answers Are"_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are"
Re: COLCOUNT not working
Yup I posted a correction later when I realized its not a sum that he wants but just the count of records.. I read and posted my mail with an erroneous suggestion in a hurry.. and later realized I made a mistake.. Joe From: LJ LongWing To: arslist@ARSLIST.ORG Sent: Mon, May 10, 2010 2:47:07 PM Subject: Re: COLCOUNT not working ** Joe, Sorry to disagree with you, but ‘sum’ gives you an adding of all the values in a column…whereas colcount gives you the number of records returned…. From:Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Joe DeSouza Sent: Monday, May 10, 2010 12:02 PM To: arslist@ARSLIST.ORG Subject: Re: COLCOUNT not working ** Raj, sum(fieldname) is the function used across most standard databases to do something similar to a colcount() update table tablename set fieldname = sum(field2name) where your conditions... Just curious - what are your actions on your filter that doesn't seem to work? Hope this helps.. Cheers Joe From:Raj To: arslist@ARSLIST.ORG Sent: Mon, May 10, 2010 11:41:19 AM Subject: COLCOUNT not working Hi All, Setting a field with COLCOUNT($COLUMN FIELD ID$) in a Set Fields action of a Filter on Submit. Seems is it not working. Always setting the field to a constant value of 15. Would like to set the field with the number of rows in that particular table. Is the any other way around to do this? How can it be done with the help of Direct SQL ? Please advise. Thanks, Ravi ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are" _attend WWRUG10 www.wwrug.com ARSlist: "Where the Answers Are"__attend WWRUG10 www.wwrug.com ARSlist: "Where the Answers Are"_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are"
Re: COLCOUNT not working
It’s all good….we all make mistakes J From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Joe DeSouza Sent: Tuesday, May 11, 2010 9:17 AM To: arslist@ARSLIST.ORG Subject: Re: COLCOUNT not working ** Yup I posted a correction later when I realized its not a sum that he wants but just the count of records.. I read and posted my mail with an erroneous suggestion in a hurry.. and later realized I made a mistake.. Joe _ From: LJ LongWing To: arslist@ARSLIST.ORG Sent: Mon, May 10, 2010 2:47:07 PM Subject: Re: COLCOUNT not working ** Joe, Sorry to disagree with you, but ‘sum’ gives you an adding of all the values in a column…whereas colcount gives you the number of records returned…. From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Joe DeSouza Sent: Monday, May 10, 2010 12:02 PM To: arslist@ARSLIST.ORG Subject: Re: COLCOUNT not working ** Raj, sum(fieldname) is the function used across most standard databases to do something similar to a colcount() update table tablename set fieldname = sum(field2name) where your conditions... Just curious - what are your actions on your filter that doesn't seem to work? Hope this helps.. Cheers Joe _ From: Raj To: arslist@ARSLIST.ORG Sent: Mon, May 10, 2010 11:41:19 AM Subject: COLCOUNT not working Hi All, Setting a field with COLCOUNT($COLUMN FIELD ID$) in a Set Fields action of a Filter on Submit. Seems is it not working. Always setting the field to a constant value of 15. Would like to set the field with the number of rows in that particular table. Is the any other way around to do this? How can it be done with the help of Direct SQL ? Please advise. Thanks, Ravi ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are" _attend WWRUG10 www.wwrug.com ARSlist: "Where the Answers Are"_ _attend WWRUG10 www.wwrug.com ARSlist: "Where the Answers Are"_ _attend WWRUG10 www.wwrug.com ARSlist: "Where the Answers Are"_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are"
Re: {Remedy ARS} Re: COLCOUNT not working
Hi Joe, Filter is on submit. Only one If Action Set Fields Action (Current Transaction- Current Transaction) FIELD NAME<--COLCOUNT($COLUMN NAME$) (There is only one column in the table, that DB ID of the column field is "COLUMN NAME" and DB ID of the field to be set is "FIELD NAME". I am doing the above action , please see below for more details: Set Fields Action Name FIELD NAME Value COLCOUNT($COLUMN NAME$) This COLCOUNT is not working properly, it is always setting the value "15" to the field FIELD NAME. Although I checked there are 20 entries in that column(Table). So the value should be 20 but it is 15. Thanks, Raj On May 10, 11:01 am, Joe DeSouza wrote: > Raj, > > sum(fieldname) is the function used across most standard databases to do > something similar to a colcount() > > update table tablename set fieldname = sum(field2name) where your > conditions... > > Just curious - what are your actions on your filter that doesn't seem to work? > > Hope this helps.. > > Cheers > > Joe > > > From: Raj > To: arsl...@arslist.org > Sent: Mon, May 10, 2010 11:41:19 AM > Subject: COLCOUNT not working > > Hi All, > Setting a field with COLCOUNT($COLUMN FIELD ID$) in a Set Fields > action of a Filter on Submit. Seems is it not working. Always setting > the field to a constant value of 15. > Would like to set the field with the number of rows in that particular > table. Is the any other way around to do this? > How can it be done with the help of Direct SQL ? > > Please advise. > Thanks, > Ravi > > ___ > > UNSUBSCRIBE or access ARSlist Archives atwww.arslist.org > attend wwrug10www.wwrug.comARSlist: "Where the Answers Are" > > ___ > > UNSUBSCRIBE or access ARSlist Archives atwww.arslist.org > attend wwrug10www.wwrug.comARSlist: "Where the Answers Are" > > -- > You received this message because you are subscribed to the Google Groups > "Remedy ARS" group. > To post to this group, send email to arsl...@googlegroups.com. > To unsubscribe from this group, send email to > arslist+unsubscr...@googlegroups.com. > For more options, visit this group > athttp://groups.google.com/group/arslist?hl=en. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are"
Re: {Remedy ARS} Re: COLCOUNT not working
Raj, Any chance the Table Chunking is set to 15? I know it is a wild thought but it is the only thing I can think of that might explain the behavior. Mark -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Raj Sent: Monday, May 10, 2010 2:21 PM To: arslist@ARSLIST.ORG Subject: Re: {Remedy ARS} Re: COLCOUNT not working Hi Joe, Filter is on submit. Only one If Action Set Fields Action (Current Transaction- Current Transaction) FIELD NAME<--COLCOUNT($COLUMN NAME$) (There is only one column in the table, that DB ID of the column field is "COLUMN NAME" and DB ID of the field to be set is "FIELD NAME". I am doing the above action , please see below for more details: Set Fields Action Name FIELD NAME Value COLCOUNT($COLUMN NAME$) This COLCOUNT is not working properly, it is always setting the value "15" to the field FIELD NAME. Although I checked there are 20 entries in that column(Table). So the value should be 20 but it is 15. Thanks, Raj On May 10, 11:01 am, Joe DeSouza wrote: > Raj, > > sum(fieldname) is the function used across most standard databases to do > something similar to a colcount() > > update table tablename set fieldname = sum(field2name) where your > conditions... > > Just curious - what are your actions on your filter that doesn't seem to work? > > Hope this helps.. > > Cheers > > Joe > > > From: Raj > To: arsl...@arslist.org > Sent: Mon, May 10, 2010 11:41:19 AM > Subject: COLCOUNT not working > > Hi All, > Setting a field with COLCOUNT($COLUMN FIELD ID$) in a Set Fields > action of a Filter on Submit. Seems is it not working. Always setting > the field to a constant value of 15. > Would like to set the field with the number of rows in that particular > table. Is the any other way around to do this? > How can it be done with the help of Direct SQL ? > > Please advise. > Thanks, > Ravi > > ___ > > UNSUBSCRIBE or access ARSlist Archives atwww.arslist.org > attend wwrug10www.wwrug.comARSlist: "Where the Answers Are" > > ___ > > UNSUBSCRIBE or access ARSlist Archives atwww.arslist.org > attend wwrug10www.wwrug.comARSlist: "Where the Answers Are" > > -- > You received this message because you are subscribed to the Google Groups > "Remedy ARS" group. > To post to this group, send email to arsl...@googlegroups.com. > To unsubscribe from this group, send email to > arslist+unsubscr...@googlegroups.com. > For more options, visit this group > athttp://groups.google.com/group/arslist?hl=en. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are" This e-mail is the property of NaviSite, Inc. It is intended only for the person or entity to which it is addressed and may contain information that is privileged, confidential, or otherwise protected from disclosure. Distribution or copying of this e-mail, or the information contained herein, to anyone other than the intended recipient is prohibited. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are"
Re: {Remedy ARS} Re: COLCOUNT not working
Thanks again. So, as per this query: SELECT Count(Column FIeld) FROM HPD_Help_Desk WHERE Status = 0 In my case this table has only one column and the rows will keep on increasing whenever there is a submit to that form. Don't want to query , just need the number of rows from that table to be set to the field. So, my query should look like below: ??? SELECT Count(Case_ID) FROM Table1 Please let me know. thanks, Raj On May 10, 10:53 am, ARSmarts Support wrote: > You can make use of the view created by ars. Here is an example: > suppose you have a table name HPD:Help Desk, and count the New incidents in > it. The sql statement would be > > SELECT Count(Case_ID) FROM HPD_Help_Desk WHERE Status = 0 > > Status = 0 because it is the integer corresponding to the New value of the > status field > > Best regards, > ARSmarts support team > > ARSmarts, your most useful AR System companion.www.ARSmarts.com > > On 10 May 2010, at 18:41, Ravi wrote: > > > > > > > ** Thanks for the reply. > > so, > > DB ID of Table Name = Table1 > > DB ID of Column in the table = Column Field ( Only one column in this table > > ) > > then my Set Field with SQL Source should look like : > > SELECT COUNT(Column FIeld) FROM Table1 WHERE query ? > > > Please let me know. > > > Thanks. > > > On Mon, May 10, 2010 at 9:09 AM, ARSmarts Support > > wrote: > > ** > > Hi, > > > You can make a filter with 2 set field actions: > > - the 1st one is just setting a dummy field with the result of the query > > you want to count the results > > - the 2nd one uses the $LASTCOUNT$ keyword > > > or > > you use a set field with SQL source, and write a query like SELECT > > COUNT(Field1) FROM table_Name WHERE query > > > Best regards, > > ARSmarts support team > > > ARSmarts, your most useful AR System companion. > >www.ARSmarts.com > > > On 10 May 2010, at 17:41, Raj wrote: > > >> Hi All, > >> Setting a field with COLCOUNT($COLUMN FIELD ID$) in a Set Fields > >> action of a Filter on Submit. Seems is it not working. Always setting > >> the field to a constant value of 15. > >> Would like to set the field with the number of rows in that particular > >> table. Is the any other way around to do this? > >> How can it be done with the help of Direct SQL ? > > >> Please advise. > >> Thanks, > >> Ravi > > >> ___ > >> > >> UNSUBSCRIBE or access ARSlist Archives atwww.arslist.org > >> attend wwrug10www.wwrug.comARSlist: "Where the Answers Are" > > > _attend WWRUG10www.wwrug.comARSlist: "Where the Answers Are"_ > > > _attend WWRUG10www.wwrug.comARSlist: "Where the Answers Are"_ > > ___ > > UNSUBSCRIBE or access ARSlist Archives atwww.arslist.org > attend wwrug10www.wwrug.comARSlist: "Where the Answers Are" > > -- > You received this message because you are subscribed to the Google Groups > "Remedy ARS" group. > To post to this group, send email to arsl...@googlegroups.com. > To unsubscribe from this group, send email to > arslist+unsubscr...@googlegroups.com. > For more options, visit this group > athttp://groups.google.com/group/arslist?hl=en. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are"
Re: {Remedy ARS} Re: COLCOUNT not working
Thank you Mark for the reply. Where can I check the Table Chunking value? On May 10, 12:03 pm, "Brittain, Mark" wrote: > Raj, > > Any chance the Table Chunking is set to 15? I know it is a wild thought but > it is the only thing I can think of that might explain the behavior. > > Mark > > > > -Original Message- > From: Action Request System discussion list(ARSList) > [mailto:arsl...@arslist.org] On Behalf Of Raj > Sent: Monday, May 10, 2010 2:21 PM > To: arsl...@arslist.org > Subject: Re: {Remedy ARS} Re: COLCOUNT not working > > Hi Joe, > Filter is on submit. > Only one If Action > > Set Fields Action (Current Transaction- Current Transaction) > > FIELD NAME<--COLCOUNT($COLUMN NAME$) > > (There is only one column in the table, that DB ID of the column field > is "COLUMN NAME" and DB ID of the field to be set is "FIELD NAME". I > am doing the above action , please see below for more details: > > Set Fields Action > > Name FIELD NAME > Value COLCOUNT($COLUMN NAME$) > > This COLCOUNT is not working properly, it is always setting the value > "15" to the field FIELD NAME. Although I checked there are 20 entries > in that column(Table). > So the value should be 20 but it is 15. > > Thanks, > Raj > > On May 10, 11:01 am, Joe DeSouza wrote: > > Raj, > > > sum(fieldname) is the function used across most standard databases to do > > something similar to a colcount() > > > update table tablename set fieldname = sum(field2name) where your > > conditions... > > > Just curious - what are your actions on your filter that doesn't seem to > > work? > > > Hope this helps.. > > > Cheers > > > Joe > > > > > From: Raj > > To: arsl...@arslist.org > > Sent: Mon, May 10, 2010 11:41:19 AM > > Subject: COLCOUNT not working > > > Hi All, > > Setting a field with COLCOUNT($COLUMN FIELD ID$) in a Set Fields > > action of a Filter on Submit. Seems is it not working. Always setting > > the field to a constant value of 15. > > Would like to set the field with the number of rows in that particular > > table. Is the any other way around to do this? > > How can it be done with the help of Direct SQL ? > > > Please advise. > > Thanks, > > Ravi > > > ___ > > > > UNSUBSCRIBE or access ARSlist Archives atwww.arslist.org > > attend wwrug10www.wwrug.comARSlist:"Where the Answers Are" > > > ___ > > > > UNSUBSCRIBE or access ARSlist Archives atwww.arslist.org > > attend wwrug10www.wwrug.comARSlist:"Where the Answers Are" > > > -- > > You received this message because you are subscribed to the Google Groups > > "Remedy ARS" group. > > To post to this group, send email to arsl...@googlegroups.com. > > To unsubscribe from this group, send email to > > arslist+unsubscr...@googlegroups.com. > > For more options, visit this group > > athttp://groups.google.com/group/arslist?hl=en. > > ___ > > UNSUBSCRIBE or access ARSlist Archives atwww.arslist.org > attend wwrug10www.wwrug.comARSlist: "Where the Answers Are" > > This e-mail is the property of NaviSite, Inc. It is intended only for the > person or entity to which it is addressed and may contain information that is > privileged, confidential, or otherwise protected from disclosure. > Distribution or copying of this e-mail, or the information contained herein, > to anyone other than the intended recipient is prohibited. > > ___ > > UNSUBSCRIBE or access ARSlist Archives atwww.arslist.org > attend wwrug10www.wwrug.comARSlist: "Where the Answers Are" > > -- > You received this message because you are subscribed to the Google Groups > "Remedy ARS" group. > To post to this group, send email to arsl...@googlegroups.com. > To unsubscribe from this group, send email to > arslist+unsubscr...@googlegroups.com. > For more options, visit this group > athttp://groups.google.com/group/arslist?hl=en. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are"
Re: {Remedy ARS} Re: COLCOUNT not working
It depends ... What is your table field's qualification statement? The Where clause in SQL should be the same (enclosing strings in single quotes of course). If there is no table field qualification then the statement below is ok. Fred -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Raj Sent: Tuesday, May 11, 2010 2:51 PM To: arslist@ARSLIST.ORG Subject: Re: {Remedy ARS} Re: COLCOUNT not working Thanks again. So, as per this query: SELECT Count(Column FIeld) FROM HPD_Help_Desk WHERE Status = 0 In my case this table has only one column and the rows will keep on increasing whenever there is a submit to that form. Don't want to query , just need the number of rows from that table to be set to the field. So, my query should look like below: ??? SELECT Count(Case_ID) FROM Table1 Please let me know. thanks, Raj On May 10, 10:53 am, ARSmarts Support wrote: > You can make use of the view created by ars. Here is an example: > suppose you have a table name HPD:Help Desk, and count the New incidents in > it. The sql statement would be > > SELECT Count(Case_ID) FROM HPD_Help_Desk WHERE Status = 0 > > Status = 0 because it is the integer corresponding to the New value of the > status field > > Best regards, > ARSmarts support team > > ARSmarts, your most useful AR System companion.www.ARSmarts.com > > On 10 May 2010, at 18:41, Ravi wrote: > > > ** Thanks for the reply. > > so, > > DB ID of Table Name = Table1 > > DB ID of Column in the table = Column Field ( Only one column in this table > > ) > > then my Set Field with SQL Source should look like : > > SELECT COUNT(Column FIeld) FROM Table1 WHERE query ? > > > Please let me know. > > > Thanks. > > > On Mon, May 10, 2010 at 9:09 AM, ARSmarts Support > > wrote: > > ** > > Hi, > > > You can make a filter with 2 set field actions: > > - the 1st one is just setting a dummy field with the result of the query > > you want to count the results > > - the 2nd one uses the $LASTCOUNT$ keyword > > > or > > you use a set field with SQL source, and write a query like SELECT > > COUNT(Field1) FROM table_Name WHERE query > > > Best regards, > > ARSmarts support team > > > ARSmarts, your most useful AR System companion. > >www.ARSmarts.com > > > On 10 May 2010, at 17:41, Raj wrote: > > >> Hi All, > >> Setting a field with COLCOUNT($COLUMN FIELD ID$) in a Set Fields > >> action of a Filter on Submit. Seems is it not working. Always setting > >> the field to a constant value of 15. > >> Would like to set the field with the number of rows in that particular > >> table. Is the any other way around to do this? > >> How can it be done with the help of Direct SQL ? > > >> Please advise. > >> Thanks, > >> Ravi ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are"
Re: {Remedy ARS} Re: COLCOUNT not working
Maybe a silly question but why don't you use an Active Link to do it?? Shafqat Ayaz --- On Tue, 5/11/10, Raj wrote: From: Raj Subject: Re: {Remedy ARS} Re: COLCOUNT not working To: arslist@ARSLIST.ORG Date: Tuesday, May 11, 2010, 2:51 PM Thanks again. So, as per this query: SELECT Count(Column FIeld) FROM HPD_Help_Desk WHERE Status = 0 In my case this table has only one column and the rows will keep on increasing whenever there is a submit to that form. Don't want to query , just need the number of rows from that table to be set to the field. So, my query should look like below: ??? SELECT Count(Case_ID) FROM Table1 Please let me know. thanks, Raj On May 10, 10:53 am, ARSmarts Support wrote: > You can make use of the view created by ars. Here is an example: > suppose you have a table name HPD:Help Desk, and count the New incidents in > it. The sql statement would be > > SELECT Count(Case_ID) FROM HPD_Help_Desk WHERE Status = 0 > > Status = 0 because it is the integer corresponding to the New value of the > status field > > Best regards, > ARSmarts support team > > ARSmarts, your most useful AR System companion.www.ARSmarts.com > > On 10 May 2010, at 18:41, Ravi wrote: > > > > > > > ** Thanks for the reply. > > so, > > DB ID of Table Name = Table1 > > DB ID of Column in the table = Column Field ( Only one column in this table > > ) > > then my Set Field with SQL Source should look like : > > SELECT COUNT(Column FIeld) FROM Table1 WHERE query ? > > > Please let me know. > > > Thanks. > > > On Mon, May 10, 2010 at 9:09 AM, ARSmarts Support > > wrote: > > ** > > Hi, > > > You can make a filter with 2 set field actions: > > - the 1st one is just setting a dummy field with the result of the query > > you want to count the results > > - the 2nd one uses the $LASTCOUNT$ keyword > > > or > > you use a set field with SQL source, and write a query like SELECT > > COUNT(Field1) FROM table_Name WHERE query > > > Best regards, > > ARSmarts support team > > > ARSmarts, your most useful AR System companion. > >www.ARSmarts.com > > > On 10 May 2010, at 17:41, Raj wrote: > > >> Hi All, > >> Setting a field with COLCOUNT($COLUMN FIELD ID$) in a Set Fields > >> action of a Filter on Submit. Seems is it not working. Always setting > >> the field to a constant value of 15. > >> Would like to set the field with the number of rows in that particular > >> table. Is the any other way around to do this? > >> How can it be done with the help of Direct SQL ? > > >> Please advise. > >> Thanks, > >> Ravi > > >> ___ > >> > >> UNSUBSCRIBE or access ARSlist Archives atwww.arslist.org > >> attend wwrug10www.wwrug.comARSlist: "Where the Answers Are" > > > _attend WWRUG10www.wwrug.comARSlist: "Where the Answers Are"_ > > > _attend WWRUG10www.wwrug.comARSlist: "Where the Answers Are"_ > > ___ > > UNSUBSCRIBE or access ARSlist Archives atwww.arslist.org > attend wwrug10www.wwrug.comARSlist: "Where the Answers Are" > > -- > You received this message because you are subscribed to the Google Groups > "Remedy ARS" group. > To post to this group, send email to arsl...@googlegroups.com. > To unsubscribe from this group, send email to > arslist+unsubscr...@googlegroups.com. > For more options, visit this group > athttp://groups.google.com/group/arslist?hl=en. ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are" ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are"
Re: {Remedy ARS} Re: COLCOUNT not working
Hi Raj, In the table properties go to the Advanced Display. There under Data Chunking there is a field named Chunk Size or Size of Chunk depending on your version. The default is 0 which means unlimited number of records are displayed. Not sure if the chunking would affect the column count but figure it is worth checking. FYI Mark -Original Message- From: Action Request System discussion list(ARSList) [mailto:arsl...@arslist.org] On Behalf Of Raj Sent: Tuesday, May 11, 2010 3:52 PM To: arslist@ARSLIST.ORG Subject: Re: {Remedy ARS} Re: COLCOUNT not working Thank you Mark for the reply. Where can I check the Table Chunking value? On May 10, 12:03 pm, "Brittain, Mark" wrote: > Raj, > > Any chance the Table Chunking is set to 15? I know it is a wild thought but > it is the only thing I can think of that might explain the behavior. > > Mark > > > > -Original Message- > From: Action Request System discussion list(ARSList) > [mailto:arsl...@arslist.org] On Behalf Of Raj > Sent: Monday, May 10, 2010 2:21 PM > To: arsl...@arslist.org > Subject: Re: {Remedy ARS} Re: COLCOUNT not working > > Hi Joe, > Filter is on submit. > Only one If Action > > Set Fields Action (Current Transaction- Current Transaction) > > FIELD NAME<--COLCOUNT($COLUMN NAME$) > > (There is only one column in the table, that DB ID of the column field > is "COLUMN NAME" and DB ID of the field to be set is "FIELD NAME". I > am doing the above action , please see below for more details: > > Set Fields Action > > Name FIELD NAME > Value COLCOUNT($COLUMN NAME$) > > This COLCOUNT is not working properly, it is always setting the value > "15" to the field FIELD NAME. Although I checked there are 20 entries > in that column(Table). > So the value should be 20 but it is 15. > > Thanks, > Raj > > On May 10, 11:01 am, Joe DeSouza wrote: > > Raj, > > > sum(fieldname) is the function used across most standard databases to do > > something similar to a colcount() > > > update table tablename set fieldname = sum(field2name) where your > > conditions... > > > Just curious - what are your actions on your filter that doesn't seem to > > work? > > > Hope this helps.. > > > Cheers > > > Joe > > > > > From: Raj > > To: arsl...@arslist.org > > Sent: Mon, May 10, 2010 11:41:19 AM > > Subject: COLCOUNT not working > > > Hi All, > > Setting a field with COLCOUNT($COLUMN FIELD ID$) in a Set Fields > > action of a Filter on Submit. Seems is it not working. Always setting > > the field to a constant value of 15. > > Would like to set the field with the number of rows in that particular > > table. Is the any other way around to do this? > > How can it be done with the help of Direct SQL ? > > > Please advise. > > Thanks, > > Ravi > > > ___ > > > > UNSUBSCRIBE or access ARSlist Archives atwww.arslist.org > > attend wwrug10www.wwrug.comARSlist:"Where the Answers Are" > > > ___ > > > > UNSUBSCRIBE or access ARSlist Archives atwww.arslist.org > > attend wwrug10www.wwrug.comARSlist:"Where the Answers Are" > > > -- > > You received this message because you are subscribed to the Google Groups > > "Remedy ARS" group. > > To post to this group, send email to arsl...@googlegroups.com. > > To unsubscribe from this group, send email to > > arslist+unsubscr...@googlegroups.com. > > For more options, visit this group > > athttp://groups.google.com/group/arslist?hl=en. > > ___ > > UNSUBSCRIBE or access ARSlist Archives atwww.arslist.org > attend wwrug10www.wwrug.comARSlist: "Where the Answers Are" > > This e-mail is the property of NaviSite, Inc. It is intended only for the > person or entity to which it is addressed and may contain information that is > privileged, confidential, or otherwise protected from disclosure. > Distribution or copying of this e-mail, or the information contained herein, > to anyone other than the intended recipient is prohibited. > > ___ > > UNSUBSCRIBE or access ARSlist Archives atwww.arslist.org > attend wwrug10www.wwrug.comARSlist: "Where the Answers Are
Re: {Remedy ARS} Re: COLCOUNT not working
Hi Mark, Just checked, it is set to default value 0. Thank you, Raj On Wed, May 12, 2010 at 7:28 AM, Brittain, Mark wrote: > Hi Raj, > > In the table properties go to the Advanced Display. There under Data > Chunking there is a field named Chunk Size or Size of Chunk depending on > your version. The default is 0 which means unlimited number of records are > displayed. Not sure if the chunking would affect the column count but figure > it is worth checking. > > FYI > Mark > > -Original Message- > From: Action Request System discussion list(ARSList) [mailto: > arsl...@arslist.org] On Behalf Of Raj > Sent: Tuesday, May 11, 2010 3:52 PM > To: arslist@ARSLIST.ORG > Subject: Re: {Remedy ARS} Re: COLCOUNT not working > > Thank you Mark for the reply. > Where can I check the Table Chunking value? > > On May 10, 12:03 pm, "Brittain, Mark" wrote: > > Raj, > > > > Any chance the Table Chunking is set to 15? I know it is a wild thought > but it is the only thing I can think of that might explain the behavior. > > > > Mark > > > > > > > > -Original Message- > > From: Action Request System discussion list(ARSList) [mailto: > arsl...@arslist.org] On Behalf Of Raj > > Sent: Monday, May 10, 2010 2:21 PM > > To: arsl...@arslist.org > > Subject: Re: {Remedy ARS} Re: COLCOUNT not working > > > > Hi Joe, > > Filter is on submit. > > Only one If Action > > > > Set Fields Action (Current Transaction- Current Transaction) > > > > FIELD NAME<--COLCOUNT($COLUMN NAME$) > > > > (There is only one column in the table, that DB ID of the column field > > is "COLUMN NAME" and DB ID of the field to be set is "FIELD NAME". I > > am doing the above action , please see below for more details: > > > > Set Fields Action > > > > Name FIELD NAME > > Value COLCOUNT($COLUMN NAME$) > > > > This COLCOUNT is not working properly, it is always setting the value > > "15" to the field FIELD NAME. Although I checked there are 20 entries > > in that column(Table). > > So the value should be 20 but it is 15. > > > > Thanks, > > Raj > > > > On May 10, 11:01 am, Joe DeSouza wrote: > > > Raj, > > > > > sum(fieldname) is the function used across most standard databases to > do something similar to a colcount() > > > > > update table tablename set fieldname = sum(field2name) where your > conditions... > > > > > Just curious - what are your actions on your filter that doesn't seem > to work? > > > > > Hope this helps.. > > > > > Cheers > > > > > Joe > > > > > > > > From: Raj > > > To: arsl...@arslist.org > > > Sent: Mon, May 10, 2010 11:41:19 AM > > > Subject: COLCOUNT not working > > > > > Hi All, > > > Setting a field with COLCOUNT($COLUMN FIELD ID$) in a Set Fields > > > action of a Filter on Submit. Seems is it not working. Always setting > > > the field to a constant value of 15. > > > Would like to set the field with the number of rows in that particular > > > table. Is the any other way around to do this? > > > How can it be done with the help of Direct SQL ? > > > > > Please advise. > > > Thanks, > > > Ravi > > > > > > ___ > > > > UNSUBSCRIBE or access ARSlist Archives atwww.arslist.org > > > attend wwrug10www.wwrug.comARSlist:"Where the Answers Are" > > > > > > ___ > > > > UNSUBSCRIBE or access ARSlist Archives atwww.arslist.org > > > attend wwrug10www.wwrug.comARSlist:"Where the Answers Are" > > > > > -- > > > You received this message because you are subscribed to the Google > Groups "Remedy ARS" group. > > > To post to this group, send email to arsl...@googlegroups.com. > > > To unsubscribe from this group, send email to > arslist+unsubscr...@googlegroups.com > . > > > For more options, visit this group athttp:// > groups.google.com/group/arslist?hl=en. > > > > > ___ > > > UNSUBSCRIBE or access ARSlist Archives atwww.arslist.org > > attend wwrug10www.wwrug.comARSlist: "Where the Answers Are" > > > > This e-mail is the property of NaviSite