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:




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




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!