4 Replies Latest reply on Oct 12, 2016 3:03 PM by haider2016

    Related records in a specific duration of time

    haider2016

      Hello there,

      The following image:

      Screen Shot 2016-10-12 at 20.16.43.png

      Shows the related records in a portal, the blue coloured numbers shows the sum of the total amount above. It works correctly for all records, but when I tried to find the related records in a specific duration the sum, the blue coloured number, keeps showing the total value for all records which is not correct as shown in the figure above.

       

      I would be greatly appreciated for any help.

       

      kind regards

        • 1. Re: Related records in a specific duration of time
          philmodjunk

          There are multiple ways to compute aggregate values and more than one way to display related data in a portal. I am guessing that your portal is filtered and that you change the filter to control what records appear in the portal. The blue values might be a summary field from the portal's table or a cacluation field in the layout's table using the Sum function. Both will ignore a filter expression set on the portal and simply compute an aggregate value based on all related records.

           

          Three methods come to mind.

          1. Get rid of the portal filter and build the date range criteria into the relationship using additional match fields.

          2. Make a copy of your portal so that it has the same portal filter expression as your first portal. Change it to have just one portal row. Put your summary field from the portal's table into this one row portal and it will now show a total based on just the records that appear in your original filtered portal.

          3. Use the Sum Function inside an SQL query passed to the ExecuteSQL function. Use a JOIN and WHERE clauses to reproduce both the relationship and the logic of your filter so that it sums only the records shown in the portal.
          • 2. Re: Related records in a specific duration of time
            haider2016

            Dear phimodjunk,

             

            Many thanks for your reply. I have tried your second solution but unfortunately it doesn't work. The point is, I have to find all records within two dates, start_date and end_date, which are saved as global fields in the main table. the question is how to make a relation based on two dates ?

             

            kind regards.

            • 3. Re: Related records in a specific duration of time
              philmodjunk

              All three methods do work. I've used them all many times.

               

              To build the dates into the relationship could be done like this:

               

              Say your current relationship used for this portal is:

               

              LayoutTable::_Key = POrtalTable::_key

               

              Change it to:

              LayoutTable::_key = PortalTable::_key AND

              LayoutTable::gDatestart <= POrtalTable::DateField AND

              LayoutTable::gDateEnd >=PortalTable::DateField

               

              To add additional pairs to this relationship and also change the operator from the default = operator to inequalities or other operators, go to Manage | database | relationships and double click the relationship line linking the two table occurrences in order to open up a dialog where you can add/remove pairs of match fields from the relationship.

              • 4. Re: Related records in a specific duration of time
                haider2016

                Hi Philmodjunk,

                 

                you are super star.

                 

                Thanks a lot.