8 Replies Latest reply: Nov 16, 2013 9:45 AM by Robert Greene RSS

    Filter with two directions

      Hi, all

      I have a data model like this. (with more fields)

      error loading image

      The customers access to QV with Section Access using KEY.

      This model filters well but DETAIL table and CLIENT table have millions of rows, so I want to put other filter from CLIENT table, using COMPANY.

      I tried to do that only adding COMPANY in Section Access but it doesn't work properly.

       


      /*************************** Section Access ****************************/
      Section Access;

      Access:

      Load *
      Inline
      [USERID,ACCESS,PASSWORD,KEY,COMPANY
      ADMIN,ADMIN,ADMIN,1,A
      USER1,USER,PASSWORD,1,A
      USER2,USER,PASSWORD,2,A
      USER3,USER,PASSWORD,3,B
      ];


       

      Is it possible to filter with KEY and COMPANY at the same time?

      (And due to other reasons, I can't change this model.)

       

      Thanks. S.I.

        • Filter with two directions
          Peter Rieper

          Think that you need to separate the steps, in the first step you may just steer the access: ADMIN or USER, USERID, Password (and Group). This would only regulate, who is allowed to open the document, and whether he is USER or ADMIN. In a second table you then may regulate access - either for each user on USERID or on the group for each other field in the application. Chapter 30.8 of the manual may guide you a bit further.
          Please remember that all entries should be done in capitals and resp. tickmarks in the document-properties should be set ("Initial Data Reduction ...")

          Peter

            • Filter with two directions

              Hi, Peter

              Thanks fot your reply.

              I did not explain enough. I had done the Section Access that you explain to filter by KEY and it works well.

              What I want to do now is below:

              A cuetomer entered using a KEY (Section Access). Only having a KEY, it is possible to filter well because a KEY depends on a COMPANY. (eg, KEY: 1A, 2A or 2B and COMPANY: A, B or C)

              Due to a huge number of rows, now I want customers to have another type of filter (COMPANY) in addition to KEY. In fact, COMPANY's filter is not neccesary to work.

              I tried this case with the code that I've post, but it does not work well.

               

              Is it possible to obtain higher performance with additional filter (COMPANY)? and

              Is it possible to filter data from two directions without changing the model like this sketch?

               

              Thanks, S.I.

                • Filter with two directions
                  Peter Rieper

                  I am not sure, whether I really get the point. You may add endless number of restrictions on other fields, the only restriction is that typically they are cumulative, i.e. if you restrict user A to see only Clients 1, 2, 3 and further restrict the user to see only company XYZ than he will only be able to see clients 1,2,3 if they simulteneously belong to compay XYZ.

                  Peter

                    • Filter with two directions

                      Maybe you could add the company key into the KEY field ? In this way you would not have to split the section access fields ?

                      Or you create another table, let's call it GROUP_TABLE where there is 3 fields : KEY, COMPANY and RIGHTS_KEY which is concatenation of KEY and COMPANY. Then you add the Section access field RIGHTS_KEY ?

                       

                      Rgds,

                      Sébastien

                        • Filter with two directions

                          > Hi, Peter and Sébastien

                          Thank you for your reply!

                          The KEY field contains data that filter COMPANY data (eg. "Type1_CompanyX"), so only with a KEY works properly, filtering well also COMPANY data.

                          So I know it is not necessary to add COMPANY field in Section Access, but I want to try to filter not only by KEY but also COMPANY (I've explained above), if it would work better.

                           

                          What I want to try makes sense?

                           

                          Thanks, S.I.

                      • Filter with two directions

                        we are facing a similar situation.

                        Even when you are able to filter data for a particular customer through the KEY field, the performance of the report in not good due to the fact that Detail and Client tables are large tables and joining by client field is taking so long. If the Client table would be previously filtered by COMPANY, this would reduce the size and therefore would increase performance (let me know if I didn't understand you right)

                        Unfortunately I haven't found a valid solution for this problem either, but any feedback will be welcome!!