How to Use Count Function

Posted by Sudhakar on 11-Jun-2014 12:21

Hi all,
i want  following things into 3 different fields


 1.# Emp created Yesterday

2. # Emp created this week

 3.# Emp created this month

Can any one help me on this.

All Replies

Posted by Laurent on 11-Jun-2014 12:43

By Count function do you mean inside a SQL query? Also, are the Employees records related to the Object where you want to have these fields?

Posted by Sudhakar on 11-Jun-2014 12:51

actually i need to send daily email to admin like following:

HI Admin,

No.of employees created Yesterday:: 20 (count of no.of employees created yesterday ) 

No.of employees created This week :: 50 (count of no.of employees created this week)

No.of employees created this month:: 100 (count of no.of employees created this month)

Thanks,

so i'm planning to get the count values in fields and use the field values in email template.But i'm not getting how to get the count.

If you have better solution,Kindly let me know.

Posted by Laurent on 11-Jun-2014 17:19

If you want to send such numbers in an email template, the best way would be to use the rbv_api.getCount() API. You do it by:

- Creating three views that get the list of employees created respectively yesterday, this week and this month (you can use the tokens TODAY - 1, WEEK, MONTH in the filter section)

- In your email template, insert [tag:EVAL][rbv_api.getCount(106933437);] instead of 20, 50 and 100 in your example. You will need to replace the ID with the View ID.

This way, if someone decide to change the filter (e.g. only the employees of a particular division) you can do so easily by updating the views.

Posted by Sudhakar on 11-Jun-2014 17:37

ty Laurent,

if i want to use display the Count based yesterday to each referral source(Relative field to employee) ,How can i achieve this.

Ex::

No.of employees created Yesterday:: 20 (This number will change for each referral source based on how many employees they created Yesterday)

Posted by Laurent on 12-Jun-2014 08:39

Can you give me more information about your schema here? You want to create an email template for what Object? How is this Object related to the Employee and the Referral?

Posted by Sudhakar on 12-Jun-2014 10:29

emp is a object and Referral is related field to emp.

my main goal is i want to create email template in emp as follows.

HI {!Referral} ,

No.of employees you created Yesterday:: 20 (This number will change for each referral based on how many employees they created Yesterday )

No.of employees you created This week :: 50 (This number will change for each referral based on how many employees they created this week)

No.of employees you created this month:: 100 (This number will change for each referral based on how many employees they created this month)

Thanks.

I hope my requirement is clear.

Posted by Laurent on 12-Jun-2014 17:12

Shouldn't the email template created for the object Referral? If it's created on the object Emp, how would you get the list of employees? Look back up at the referral and then at the related employees?

I don't know if it can be used in your case, but if the template is on object Referral you should be able to use the CALC_COUNT function. To do so, select "Related Emp" in the first Template Helper combo and then COUNT dependent. This will add something like:

   #CALC_COUNT.R87985010( 1 | true )

The true is the condition, where you can add the desired time range.

Posted by Sudhakar on 12-Jun-2014 18:07

can you pls give me one example,so that i can understand completely.

Posted by kiran kumar Angara on 13-Jun-2014 00:29

Hi Sudhakar,

I hope this might be useful to achieve Count Functions

documentation.progress.com/.../index.html

Thanks & Regards,

Kiran Avs

Posted by Sudhakar on 13-Jun-2014 11:08

thanks for your link kangara,

I have gone through your Documentation,

The List if String tokens i can use in Count Functions are as follows

TODAY ,WEEK ,MONTH ,QUARTER,YEAR ,CURR_USER

im getting same value for all string tokens functiions,

can you pls test these string functions and post the right token here(My condition is " #CALC_COUNT.R101162498(1 | CreatedAt = Yesterday) " ) so i'm using #CALC_COUNT.R101162498(1 | CreatedAt <= TODAY-1),But im not getting the number as expected.

This thread is closed