Relationship for Reporting

[GManNAtl]

I have a report that I need to create and I know that can script what I need. However there has to be a clever way via a self join or something to get what I need without some needless scripting.  Here is a sample of the data and below how I need to report on it.

 

Classes Table

 

ClassID                 ClassName

7RJ                        Rejected

OSU                       Signup

 

Case Table

 

CaseID                 Date                             Class

4000                     2/5/2013                       OSU

4001                     2/5/2013                       OSU

4002                     2/5/2013                       7RJ

4003                     2/6/2013                       OSU

4004                     2/6/2013                       OSU

4005                     2/6/2013                       OSU

4006                     2/6/2013                       7RJ

 

What I need to get to is having a count of each class for a given day.   For example :

 

2/5/2013

 

7RJ       1

OSU      2

 

2/6/2013

 

7RJ       1

OSU      3

 

This example is limited because there are in reality about 20 classes.   What I have done and works is to create a global date field in the classes table and created  a relationship based on both the date and class field.  The problem is that I have to create a relationship for every class.  Not really a problem in itself other than it seems like overkill.  The real problem is that I want to use this in a PHP app on a single page.  The other thing I have tried is using a filtered portal, which also works and is cleaner, but it will not work in PHP.  The fields are just exposed as the same and don't "honor" the filtered portal via the API.   

 

Any help is greatly appreciated!

 

[LaRetta]

Hi GMan,

Go to your Cases table ... that is where the report should be ran. Create summary field in Cases sCountDates ( count of Date; make sure it is date field).

Create report with grouped by Date and place sCountDate in leading part ... be sure to include sort by date or it won't work. Best to let FM create it for you. Place the date field or merge of the date field in the leading part and format it with Inspector to show CUSTOM and as 'February, 2013. Then find the time period for the report and sort by date.