Datapage Results Returning Too Many Records

[ShelleyK]

I have setup a datapage report to look at a view and pull a member id from this view. The member id shows in the view tables multiple times. The results is pulling the member id each time they show in the table and I need it to only show once on the results page. What am I missing?

 

Thanks

[svitlanataran]

Hi ShelleyK,

 

Try to exclude from a view fields you don't want to use.  

Step 4 in the article describes how to do it.

Refer to the link:

http://howto.caspio.com/tables-and-views/what-are-views/creating-a-view-to-filter-data/

 

Matilda

[ShelleyK]

Our members compete in events. In the table they are given points from each event. With the formula below it is returning the correct points but showing a record for each time they show in the result table. I need it to return their name only once with totals. 

 

Example

 

Name            Total Points     Points Required  Qualified

 

Joe Smith           11                        12                   No

 

 

It is currently showing the above three times if Joe participated in three events

 

 

This is the formula for points earned

 

select sum(b.CCO_Place_Points)

from Results2015 a

join Lookup_Places b on b.CCO_Place_ID = a.PlaceID

where a.MemberID = [@field:Member2015_MemberName]

 

and this is for Qualification determination

 

select

case

when sum(b.CCO_Place_Points) > [@field:State_Points_Required_Required_Points] then 'Yes'

else 'No'

end

from Results2015 a

join Lookup_Places b on b.CCO_Place_ID = a.PlaceID

where a.MemberID = [@field:Member2015_MemberName]

[svitlanataran]

Hi ShelleyK,

 

 

where a.MemberID = [@field:Member2015_MemberName]

 

 

It looks like you compare member ID with Member name.

 

Matilda.

[ShelleyK]

Yes. In the Results table it is named member ID and the search is done off the Member table by name. Like a details page I want to only return their name once and not for each time it shows in the Results table. I cannot use a details report because I want them to be able to have all results show or only results based off of their search. 

 

All the information shows correctly but it shows on multiple lines due to the duplicate lines with the name.

[svitlanataran]

Hi ShelleyK,

 

Have you tried to use Data Grouping in advanced reporting?

Take a look at this atricle:

http://howto.caspio.com/datapages/reports/advanced-reporting/data-grouping/

 

I hope it works for you. 

 

Matilda

[ShelleyK]

Yes. The data grouping does not give me the report layout I need. I need for the report to return like below with a detail page to show all the activity. Problem is I get this line for every entry that Joe Smith's name shows up for in the table.

 

Name            Total Points     Points Required  Qualified

 

Joe Smith           11                        12                   No                 View Details

[svitlanataran]

ShelleyK,

 

Please find a screenshot attached, is that what you'd like to have?

I've opened the 3rd place to show you the structure.

 

If it's what you need, I'll tell you how to create this report, just let me know.

 

Matilda

 

 

 

 

[ShelleyK]

The grouping does not do what I want. It does not put it all on one line. All the search results need to be on one line. The details page is where the place ID's, Points for each place, Event date, Name, City, State etc. will show.

 

 

Name            Total Points     Points Required  Qualified

 

Joe Smith           11                        12                   No                 View Details

 

If Joe was in several events then this is what I am getting

 

Name            Total Points     Points Required  Qualified

 

Joe Smith           11                        12                   No                 View Details

Joe Smith           11                        12                   No                 View Details

Joe Smith           11                        12                   No                 View Details

 

Is there not a script to tell it if the name or ID shows multiple times in the table to only return it once on result page?

[Walter]

Hi Shelly,

 

I believe, a pivot table report can help. The article about pivots: http://howto.caspio.com/datapages/reports/pivot-table/

Also you can use your Users table as the DataSource, then all users will be displayed only once.

[ShelleyK]

I have created a Pivot table that gives me the name only once but I need to add a column that is a calculated formula.

I need to add if one of the columns in values is greater than the other return yes otherwise no. I cannot get this to work in the Header/Footer

 

select

case

when sum(b.Results2015_Points) > [@field:State_Points_Required_Required_Points] then 'Yes'

else 'No'

[ShelleyK]

Problem with this also being a Pivot table I cannot show a details page to list all the transactions separately