8 Replies Latest reply on Mar 5, 2014 1:46 PM by AnnetteRoman

    Portal Filter by Date Field

    AnnetteRoman

      Title

      Portal Filter by Date Field

      Post

           I have a portal that contains references for projects. We have a rule that once a reference is a year old it needs to be replaced with a new more current reference. We however still need the old reference information. Is there a way to filter a portal to only show records that are under a year in age? 

        • 1. Re: Portal Filter by Date Field
          philmodjunk

               ( Get ( CurrentDate ) - PortalTO::DateField ) < 365

          • 2. Re: Portal Filter by Date Field
            AnnetteRoman

                 Thanks this worked great! 

                 I think I've worked myself into a corner though. These same references are given a point value. 3 if positive, -3 if negative. I have a calculation setup to give each reference one or the other. Case ( Average Score = 0 ; 0 ; Average Score ≥ 3 ; 3 ; Average Score ≤ 3 ;-3 ; 0 ) 

                 I now need a field TC Project References::Reference Score Portal outside the portal that calculates all the 3s & -3s and returns a value. Can I still have it complete this calculation for only those references that are under a year in age?

                 I have a second portal to the same TC Project References table showing the rating of each question answered by a reference. These answer fields give me my TC Project References::Average Score mentioned earlier. 

                 I tried to filter relationships with a self Join using the primary key TC Project References::referenceID# but now the second portal seems to only display the scores of one reference repeatedly not all the references listed in the original portal.

            • 3. Re: Portal Filter by Date Field
              philmodjunk

                   I don't follow all the details of that. What exactly do you mean by "outside the portal that calculates all the 3s & -3s and returns a value.)

                   Since this is outside the portal, you are describing a single value returned. What value will that be? How is it to be calculated from a set of records with 3, -3 or 0 in each field?

                   

                        but now the second portal seems to only display the scores of one reference repeatedly

                   The most common cause of that result is to put a field into the portal row that is not from the correct table occurrence. This often happens when you duplicate a portal and then set it to refer to a different table occurrence in "show related records from" without also updating the fields in the portal row to refer to data from the context of the different table occurrence.

              • 4. Re: Portal Filter by Date Field
                AnnetteRoman

                     Sorry, by "outside" I meant on the layout not within the portal. 

                     The value returned from adding up all the reference weights (-3, 0, 3) within the last year should be a value anywhere between 0 to 18 depending on the number of favorable or negative references contacted (sometimes there are less than 6 references, but there should be no more than 6 of the latest references used in the calculation for a maximum score of 18). 

                     I was able to fix the issue with the portal displaying only one reference repeatedly, thank you. 

                • 5. Re: Portal Filter by Date Field
                  philmodjunk

                       The value returned from adding up all the reference weights (-3, 0, 3) within the last year should be a value anywhere between 0 to 18

                       And how do you calculate that value? What kind of math produces a value in that range? Is this the sum of all the values from the related records in that date range? (If it's the sum, there's no guarantee that the minimum value will be zero, it could be negative if a lot of the records have a value of -3...)

                  • 6. Re: Portal Filter by Date Field
                    AnnetteRoman

                         If the results end up being negative, couldn't I can have another calc field reinterpret the result of that Reference Score Portal to display a zero instead of a negative number?

                         I tried to use a Total Of summary field for my TC Project References::Reference Score Portal (using that to add the TC Project References::Reference Weight Values,  the -3 & 3s), and although it seems to work with some of the records in the TC SOQ Submission layout (on which the portals lay) it doesn't work with all the records. I thought at first the improper calculations were restricted to the records that had more than one negative reference, but I am noticing TC Project References::Reference Score Portal values that do not add up even with all positive TC Project References::Reference Weight Values such as with 4 positive references and returning a TC Project References::Reference Score Portal value of 18. 

                    • 7. Re: Portal Filter by Date Field
                      philmodjunk

                           I'm just trying to figure out how you want that calculation to work. I pointed out that issue with negatives to explain why I couldn't just assume that you are calculating a sum of the portal values. There's no reason why the sum can't be negative unless it creates an issue for how you need to work with this data.

                           A summary field defined in the portal's table can be used to compute the sum of the values shown in the portal, but since the portal is a filtered portal, you have to place the summary field in a portal with the same portal filter to get it to compute the correct value. You can duplicate your current portal. Change the number of portal rows to one and replace the fields in the portal with the summary field and it should show a total based on just the records shown in the portal. You can make the portal borders invisible to make this look like just another field on your layout.

                           But this total is "display only". There's no way to access that value in a calculation or script step. If that's necessary, we have to use other means to get that total.

                      • 8. Re: Portal Filter by Date Field
                        AnnetteRoman

                             Okay this fixed my problem. Thanks for the help.