3 Replies Latest reply on May 18, 2016 8:10 AM by Mike_Mitchell

    Portal and Data Viewer Not Matching

    BenKingsbury

      Our company has 4 Regions and under each Region there are multiple Districts.  I have a repeating field for Region (in case all 4 are selected) and I have a portal which should display all the Districts in the Region(s) selected.

       

      I am using an ExecuteSQL statement to filter my portal records to show only those specified Districts and in the Data Viewer it shows the correct result of my SQL statement.  When I put this SQL statement in the Filter Portal Records, it only shows me the Districts under Region 1.  It doesn't matter what Region I select or how many I select, it only ever shows me the Districts under Region 1. 

       

      Has anyone come across this situation before?  Below is an example of my SQL statement, the expected results and the portal of the incorrect results.

       

       

      Thank you,

      Ben Kingsbury

        • 1. Re: Portal and Data Viewer Not Matching
          Mike_Mitchell

          You can't reference repeating fields using ExecuteSQL. It doesn't know what you're talking about.

           

          If the values are 1, 2, 3, 4, why not just use:

           

          WHERE u.Region IN ('1','2','3','4')

          • 2. Re: Portal and Data Viewer Not Matching
            BenKingsbury

            I read where you could use repeating fields as long as you reference which repetition you want to use.  My SQL statement is rendering the correct results with using the repeated fields.

             

            I don’t use the IN operation because I want the user to specify which Regions they want to see.  If they select a single Region, this would display all Districts instead of that Region by itself.  I have it where a user clicks on a Region and it stores that value in a Repeating field which is then used by the SQL statement to filter.

            • 3. Re: Portal and Data Viewer Not Matching
              Mike_Mitchell

              BenKingsbury wrote:

               

              I read where you could use repeating fields as long as you reference which repetition you want to use.

               

              Not my understanding, but I've been wrong before.

               

              I don’t use the IN operation because I want the user to specify which Regions they want to see. If they select a single Region, this would display all Districts instead of that Region by itself. I have it where a user clicks on a Region and it stores that value in a Repeating field which is then used by the SQL statement to filter.

               

              They're not mutually exclusive. Even if you want to use a repeating field for temporary storage like this (global, right?), you can get where you want with:

               

              Let ( [

                   selectedValues = "('" & Substitute ( List ( Discount_Temp_Table::RM_Filter ) ; "¶" ; "," ) & "')" ;

                   sqlQuery = "SELECT u.DM FROM Users_2 u WHERE u.Region IN ?"

              ] ;

               

              ExecuteSQL ( sqlQuery ; "" ; " ; ¶" ; selectedValues )

               

              )

               

              P.S. Portal filtering is bad for performance. Portal filtering based on ExecuteSQL is way worse. This may work fine for a small record set, but it's going to crawl with any significant number of records.