AnsweredAssumed Answered

Relational question

Question asked by roryduffy on Nov 27, 2013
Latest reply on Nov 27, 2013 by erolst

Here is a challenge...

 

I have 3 tables linked together in the following formation:

 

Genres <-> CDATA (Transactions) <-> Advance (Days in advance, where each record is a number 0 - 365)

 

See screenshot 1.

 

There are 35 genres, which are displayed in the "genres" layout. Within this layout, I have a portal set up to pull in all transactions that match that genre. e.g., there are 77 CDATA (Transactions) records where Genre = Acoustic ; portal is displaying 77 related records.

 

For each of those related records, I am able to see how many days in advance (See screenshot 2).

 

All fine, so far, until...

 

I want to be able to count within that selection of related records how many transactions are 0 days in advance, how many are 1 days in advance, how many 2, 3, 4 etc... So, I want to see it like this:

 

Acoustic

 

00 days in advance - 39 transactions

01 days in advance - 01 transactions

02 days in advance - 01 transactions

03 days in advance - 01 transactions

04 days in advance - 01 transactions

05 days in advance - 03 transactions

06 days in advance - 02 transactions

07 days in advance - 03 transactions

 

etc

 

How can I get these figures? I have gone around in circles trying many different methods including creating extra occurrences of tables, extra fields, different types of referencing, but it is always returning 11288 transactions for 0 days in advance, which is the number of transactions made 0 days in advance, regardless of genre.

 

I'm able to see which "advance" records are represented from the perspective of the genre (See screenshot 3). So, I can see that there are no Acoustic tickets where days in advance = 11, 12 or 14. But I cannot see how many times each "advance" record is represented for that genre (Acoustic), if that makes sense...

 

Thanks very much in advance!

 

Rory

Outcomes