8 Replies Latest reply on Dec 18, 2012 3:13 AM by nkolios

    Filtering with a relationship

    nkolios

      I have a table containing sales information. About a dozen sales categories, split into months over a period of 3 to 4 years. I am trying to create a table occurance for each year so I can run reports on that data. So I create a self join and join the tables using a global field in the main table and a field containing the year number on the secondary table. I have then created a crosstab report for the resulting data, the crosstab works great but the self join does not work and gives me all the sales data together and not one year at a time. I thought this method of self joining would reduce my data set by only showing data for year 1 or year 2 etc, but it does not seem to be doing this, am I missing something here?

        • 1. Re: Filtering with a relationship
          BruceRobertson

          It is impossible to say. We don't have any detail.

           

          I suggest you upload a copy of your file; or clone; or relationship diagram.

          • 2. Re: Filtering with a relationship
            nkolios

            Hi Bruce,

            Thanks for your reply, I have uploaded my file now.  Hopefully things should be clear now.

            • 3. Re: Filtering with a relationship
              comment

              nkolios wrote:

               

              So I create a self join and join the tables using a global field in the main table and a field containing the year number on the secondary table. 

               

              I think that would look like this:

               

              rel.png

              • 4. Re: Filtering with a relationship
                nkolios

                Hi Michael, thanks for looking at this, I have tried it this way but it makes no difference, for some reason I get year1, 2 and 3 in the table which should only give year 1.

                • 5. Re: Filtering with a relationship
                  nickchapin

                  If I'm understanding your needs, I think what you're trying to get at - as a final result - is your report. To do this you only need one relationship, one "year" layout, and one global field. There's really no need to go multiple years, just have your global not a calc, enter n the year you want to run the report, gtrr to those records on your report layout, and you're done.

                   

                  Of course, there are multiple ways to skin this cat, but this is quick and easy and along the thought process you already have going.

                   

                  See the attached file.

                  1 of 1 people found this helpful
                  • 6. Re: Filtering with a relationship
                    comment

                    nkolios wrote:

                     

                    Hi Michael, thanks for looking at this, I have tried it this way but it makes no difference, for some reason I get year1, 2 and 3 in the table which should only give year 1.

                     

                    Well, I haven't really looked at this, only at the relationships. I suspect you may be confusing the found set with the related set. A relationship only affects the related set. IOW, any TO of a table is capable of showing all records of the table. So either base your report on the related set or perform a find for the year/s you want to include.

                     

                    Note that GTRR [Show related only] is just another way of creating a found set.

                    1 of 1 people found this helpful
                    • 7. Re: Filtering with a relationship
                      nkolios

                      I assumed that if I related the tables in this way then any report based on the second table occurance would automatically be filtered by the join criteria but I see now that if the tables are infact identical then it would make sense that it would show all the data.  So using GTRR would give me what i want.

                      • 8. Re: Filtering with a relationship
                        nkolios

                        Thanks for this Nick. Using GTRR is probably the best way to get the results I want.  The only reason I had decided to use a tabe occurance for each year was that I need to run comparisons between each year, % change of products from one year to the next etc.  I cant see an easy way to do it with having a table occurance for each year, I know it mean I will need to add a table for each new year but I think it is the easiest way to make the comparisons.