Quick question on "If thens" and counting...

[RyanSpaulding]

Hey all -

I'm working on a small project and am looking to make counting of records easier on our admins. Values are typed in quotes below. What we have is a table that has a column of the type of sales activity ("call", "meeting", "event", "conference") and a column that has all of our team's participants that went to that specific activity in one field ("Tim, Jeff, Dana", "Tim", "Roger, Jim, Jeanne", ect).

What I'm trying to do is create a reports page...a bunch of fields that essentially say

COUNT the number of times an activity cell = "call" AND the participants cell CONTAINS "Jeanne".

This then reports on each activity. I'd like the report's content to read like this:

Jeanne (# of calls, # of meetings, # of conference, # of events)

Tim (# of calls, # of meetings, # of conference, # of events)

Dana (# of calls, # of meetings, # of conference, # of events)

Roger (# of calls, # of meetings, # of conference, # of events)

Am I missing something simple? It's possible to do in Excel...I cant figure out this one...

[Aariel]

Hi Ryan,

I think, its something like:

- group the results by Name(i.e Jeanne, Tim,...)

- Use aggregate function (e.g: SUM) for individual fields (e.g:calls, meetings,...)

Caspio has some tutorials regarding grouping and using aggregate function...

hope I understood your query...

[bahar_vm]

Hi,

This is the way I go to create similar report using Caspio standard features.

Create a Tabular report and include the participant dropdown field in the search form. Comparison type for participant dropdown is "Contains". In the results page you will need to set the report group by the sales activity field and insert an aggregation using function "Count" to count the number of returned records.

Here is the results of such report searching for participant "Jeff", the result is similar but not exactly like what you outlined:

Call Jeff

Total 2

meeting Jeff

Total 4

...

To get the numbers for other participants you can search for that specific participant. Or you can use passing parameters in predefined criteria report instead of search and report and deploying the report four times in your web page to get all four participants reports listed.

I would also like to mention that Caspio's Professional Services team is available to help you build your application. Please feel free to submit your request at http://www.caspio.com/services/custom-a ... opment.asp.

Best,

Bahar M.