5 Replies Latest reply on Jun 1, 2016 12:10 AM by nkolios

    Filtering portal records based on a self join.

    nkolios

      I have a table with records of takings called "Date", I want to create a portal on a separate layout grouping the data by year and financial quarter.  I created two global fields that I put on the new layout to select which year and quarter i want to look at. I created a second table occurrence of my Date table called "report_DATE" and also fields to identify which quarter and year a record falls in.  So the relationship looks like:

       

      Date::g_year = report_DATE::Year

                     and

      Date::g_QTR = report_DATE::MGD_QTR

       

      The layout is based on the Date table, the portal with its fields are based on the report_DATE table.  Its not working.  I know it would work if my data was in a separate table as a child table but it doesn't seem to work as a self join.  I'm not sure whether I need a script to do something or use the portal filtering, or set up the relationship differently.

       

      Some input would be much appreciated.  I think it's probably my lack of understanding of "context" that is giving me problems.

        • 1. Re: Filtering portal records based on a self join.
          macwombat

          Hi - not sure how the second condition in your relationship could work - the value in the global QTR field won't equal the value in the Year field will it?

           

          I've attached a simple sample file that works with fields that are setup how I understand QTRs and Years to work.

           

          HTH.  Chris

          1 of 1 people found this helpful
          • 2. Re: Filtering portal records based on a self join.
            nkolios

            Hi Chris,

            sorry, that was a typo, i have corrected the post now.  Thanks for the file, I will take a look.

            1 of 1 people found this helpful
            • 3. Re: Filtering portal records based on a self join.
              ucharles

              Hi Chris

               

              I should first thank nkolios for bringing up the question on Filtering portal records based on a self join. I have a similar problem, where I want a portal based on a self-joint relationship to list records filtered by a calculation field named "classification."

               

              Here is the calculation:

              classification =

              Case ( age ≤ 345 and sex = "Female" ; "Calf-Female" ;

                age ≤ 345 and sex = "Male" ; "Calf-Male";

                age ≤ 720 and sex = "Female" ; "Heifer" ;

                age ≤ 720 and sex = "Male" ; "Steer/Tollie" ;

                age ≥ 720 and sex = "Female" ; "Cow" ;

                age ≥ 720 and sex = "Male"  ; "Ox" ; )

               

              Now I want to be able to determine by portal listing: How many "Calf-Female", "Calf-Male", " Cow" and so on. Do I apply the same formula as in nKolios case, do I have to create a a global field that is the same as the classification for how do I do it?

              1 of 1 people found this helpful
              • 4. Re: Filtering portal records based on a self join.
                macwombat

                Hi - yes if you wanted a self join relationship based on the classification you mention you would create a global field for classification and join the two Table Occurrences (TOs) based on the global field = classification.

                 

                Sample file attached.

                 

                HTH. Chris

                1 of 1 people found this helpful
                • 5. Re: Filtering portal records based on a self join.
                  nkolios

                  Hi,

                  Thanks for all the input, I have managed to get it working..more or less.  It filters the data for the correct period now but the portal gives me a line for each record even though there is no data.  My database is based around a daily record with various bits of data recorded on each day.  On some days for some fields, there is no data at all for the data in the portal I am trying to create at the moment but a record is still created, as there is other data recorded on that day.  So I have a lot of blank records.  Is there a way of stopping the blank records being presented in the portal?