3 Replies Latest reply on Nov 27, 2013 11:05 AM by erolst

    Relational question

    roryduffy

      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

        • 1. Re: Relational question
          erolst

          roryduffy wrote:

          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...

           

          When using run-of-the-mill relationships, you could only see this number if you had a field for each genre within Advance (or slightly less cumbersome, a table with one record per GenreDaysAdvance combination). In short, there's no place to hold this data for all Genre/Advance combis.

           

          Luckily, there's a workaround: Add a global field to DaysAdvance, say gGenre, and define a relationship from DaysAdvance to CDATA that also uses that global. Define another field where you calculate the desired number as Count ( CDATAForCurrentGenre::aNonEmptyField ).

           

          Now when you a have relationship/portal that looks from Genre through CDATA into DaysAdvance, you can display this calculation field – IF you set the global in DaysAdvance to the ID of the genre you're on, e.g. with a OnLayoutEnter trigger. Of course, you have to keep in mind that the field only shows the correct numbers if that global was set correctly.

           

          If you just need a report, you can prepare a sub-summary layout in CDATA, search for records of a specific genre, sort by daysInAdvance and display only the summary part without the body.

          • 2. Re: Relational question
            roryduffy

            Hi Erolst,

             

            Perfect! Works like a charm. I've never used script triggers before. Thank you so much for your help - Honestly, I don't know how developers manage to hold that level of concentration when looking at other peoples' database structures!

             

            I'll let you know if I have any other queries.

             

            Cheers,

             

            Rory

            • 3. Re: Relational question
              erolst

              roryduffy wrote:

              Honestly, I don't know how developers manage to hold that level of concentration when looking at other peoples' database structures!

               

              Been there, done that …

               

              I'll let you know if I have any other queries.

               

              I'd rather you ask the community … Anyway, glad to hear it's working for you.