Relationship for Reporting

February 8, 201312 yr

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!

February 9, 201312 yr

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.