12 Replies Latest reply on Jan 15, 2013 12:43 AM by zhaohongbin

    About String Search

    zhaohongbin

      I want to know which string in (a,b,c,d) is not in (b,e,d,f).of course the answer is (a,c).

      but how to use function to reveal it?

      thanks

        • 1. Re: About String Search
          beverly

          Filter()

          http://www.filemaker.com/12help/html/func_ref3.33.52.html#1030109

           

          -- sent from my iPhone4 --

          Beverly Voth

          --

          • 2. Re: About String Search
            zhaohongbin

            thanks a lot.

            can you tell me 1:how to omit the ","?2:how to obtain the number of the result string,just like "cc,dd"?(should be 2)

            赵 洪滨

            simon3c2007@163.com

             

             

             

            在 2013-1-2,下午4:24,Beverly Voth <noreply@filemaker.com> 写道:

             

             

            created by Beverly Voth in Advanced Discussion - View the full discussion

            Filter()

             

            http://www.filemaker.com/12help/html/func_ref3.33.52.html#1030109

             

             

            -- sent from my iPhone4 --

             

            Beverly Voth

             

            --

             

            Reply to this message by replying to this email -or- go to the message on FileMaker Technical Network

            Start a new discussion in Advanced Discussion by email or at FileMaker Technical Network

            Manage your email preferences.

             

            FileMaker Developer Conference 2013 • San Diego, California • August 12-15 • www.filemaker.com/devcon

             

            • 3. Re: About String Search
              beverly

              are the actual VALUES: "a,b,c,d" & "b,e,d,f"? if so, then you can use

              Substitute ( value ; "," ; Char(13) )

              to change the "," to ";" for the Filter function.

               

              Another function that is useful is FilterValues and it is looking for a return between values. You can use Substitute() to change the "," to Char(13) and then use FilterValues(). The added bonus of using FilterValues is the function ValueCount() which would tell the the number of the result string.

               

              If you need the "," in the final result, you can always convert it back with

              Substitute ( value ; Char(13) ; "," )

               

              The link I gave you can also point to the additional functions I have listed above. <http://www.filemaker.com/12help/html/help_func_cat.29.1.html#1030109>

              Beverly

               

              can you tell me 1:how to omit the ","?2:how to obtain the number of the result string,just like "cc,dd"?(should be 2)

               

              Filter()

              http://www.filemaker.com/12help/html/func_ref3.33.52.html#1030109

               

              • 4. Re: About String Search
                zhaohongbin

                thanks a lot .

                below is my own example:

                Let([

                A=

                ExecuteSQL (

                "

                SELECT DISTINCT c.\"__kp_CustomerID\" AS CC

                FROM  Customer AS c INNER JOIN

                          VisitRecord AS v ON c.\"__kp_CustomerID\" = v.\"_kf_CustomerID\" INNER JOIN

                          Employee AS e ON v.\"_kf_EmployeeID\" = e.\"__kp_EmployeeID\"

                WHERE (V.DateVisited<=? AND V.DateVisited>=?) AND e.\"__kp_EmployeeID\"=?

                "

                ;""

                ;""

                ;z_RESOURCES::zg_ReportEndDate

                ;z_RESOURCES::zg_ReportStartDate

                ;z_RESOURCES::_kf_EmployeeID

                )

                ;B=ExecuteSQL (

                "

                (

                SELECT DISTINCT c.\"__kp_CustomerID\" AS CC

                FROM  Customer AS c INNER JOIN

                          VisitRecord AS v ON c.\"__kp_CustomerID\" = v.\"_kf_CustomerID\" INNER JOIN

                          Employee AS e ON v.\"_kf_EmployeeID\" = e.\"__kp_EmployeeID\"

                WHERE (V.DateVisited<?) AND e.\"__kp_EmployeeID\"=?

                )

                "

                ;""

                ;""

                ;z_RESOURCES::zg_ReportStartDate

                ;z_RESOURCES::_kf_EmployeeID

                )

                ;C=Filter(B;A)

                ;D=Substitute (C;",";¶ )

                ];

                ValueCount(D)

                )

                 

                my question is on the red line,because I want get those which in B but NOT in A. the red C in not correct.

                can you tell me how to correct the red line? thanks very much.

                 

                赵 洪滨

                simon3c2007@163.com

                 

                 

                 

                在 2013-1-2,下午8:42,Beverly Voth <noreply@filemaker.com> 写道:

                 

                 

                created by Beverly Voth in Advanced Discussion - View the full discussion

                are the actual VALUES: "a,b,c,d" & "b,e,d,f"? if so, then you can use

                 

                Substitute ( value ; "," ; Char(13) )

                to change the "," to ";" for the Filter function.

                 

                 

                Another function that is useful is FilterValues and it is looking for a return between values. You can use Substitute() to change the "," to Char(13) and then use FilterValues(). The added bonus of using FilterValues is the function ValueCount() which would tell the the number of the result string.

                 

                 

                If you need the "," in the final result, you can always convert it back with

                 

                Substitute ( value ; Char(13) ; "," )

                 

                The link I gave you can also point to the additional functions I have listed above. <http://www.filemaker.com/12help/html/help_func_cat.29.1.html#1030109>

                 

                Beverly

                 

                 

                can you tell me 1:how to omit the ","?2:how to obtain the number of the result string,just like "cc,dd"?(should be 2)

                 

                 

                Filter()

                 

                http://www.filemaker.com/12help/html/func_ref3.33.52.html#1030109

                 

                 

                Reply to this message by replying to this email -or- go to the message on FileMaker Technical Network

                Start a new discussion in Advanced Discussion by email or at FileMaker Technical Network

                Manage your email preferences.

                 

                FileMaker Developer Conference 2013 • San Diego, California • August 12-15 • www.filemaker.com/devcon

                 

                • 5. Re: About String Search
                  beverly

                  Well! you did not specify that you were using ExecuteSQL() function!

                   

                  Have you considered nesting the SELECTs, so that part of the WHERE is the second select and using NOT IN

                   

                  I'm confused on which set is supposed to filter out: A or B. I don't see "the red line"...

                  Beverly

                  • 6. Re: About String Search
                    comment

                    zhaohongbin wrote:

                     

                    I want to know which string in (a,b,c,d) is not in (b,e,d,f).of course the answer is (a,c).

                    but how to use function to reveal it?

                     

                    Knowing what are you really trying to achieve here would be helpful. The FilterValues() function is an easy way to return the values that are common to both lists; returning the difference is not as easy, but if your values are represented by records, you can use a ≠ relationship to show values that are not in the list.

                    • 7. Re: About String Search
                      zhaohongbin

                      Thank U very much.

                      Sorry I haven't describe my question early.

                      the question is:

                      there are some customers,employees,and so have some VisitRecords for Employee to Customer.

                      I want to know the number of Customers and the list of Customers which are the new ones. It means those Customers only found in VisitRecords between ReportStartDate and ReportEndDate, and not be found in VisitRecords from begin to ReportStartDate.

                      for example, the Customers in VisitRecords from begin to ReportStartDate are "a,b,c,d",and the Customers in VisitRecords between ReportStartDate and ReportEndDate are "b,e,e,d,f,a".So the new one of Customers should be "e,f".

                      if use SQL and use SubQuery of SQL,it's very easy to achieve this result. but how to get the same one use FileMaker?

                      Thank U very much and happy new year.

                       

                      赵 洪滨 (Simon from Shanghai,China)

                      simon3c2007@163.com

                       

                       

                       

                      在 2013-1-3,上午8:21,Michael Horak <noreply@filemaker.com> 写道:

                       

                       

                      created by Michael Horak in Advanced Discussion - View the full discussion

                       

                      • 8. Re: About String Search
                        beverly

                        can you post an example of the SQL you'd use to make this query (NOT in FMP)?

                         

                        if use SQL and use SubQuery of SQL,it's very easy to achieve this result. but how to get the same one use FileMaker?

                         

                        Thank you,

                        Beverly

                        • 9. Re: About String Search
                          comment

                          zhaohongbin wrote:

                           

                          if use SQL and use SubQuery of SQL,it's very easy to achieve this result. but how to get the same one use FileMaker?

                           

                          First, I don't see why you shouldn't use Execute SQL() for this. If you prefer a more "native" method, you could do it this way:

                           

                          1. Define a relationship showing all Visits up to report start date. This could be a self-join, but for this example I will use a table named Viewer as the "parent" table, so:

                           

                          Viewer::gStartDate > Visits::VisitDate

                           

                           

                          2. Define a calculation field cPrevCustomers in the Viewer table (result is Text) =

                           

                          List ( Visits::CustomerID )

                           

                           

                          3. Define a relationship between the Viewer table and another occurrence of Visits as =

                           

                          Viewer::gStartDate ≤ Visits 2::VisitDate

                          AND

                          Viewer::gEndDate ≥ Visits 2::VisitDate

                          AND

                          Viewer::cPrevCustomers ≠ Visits 2::CustomerID

                           

                          This relationship will show only visits to "new" customers, i.e. customers that do not have any visits before the report's start date.

                           

                           

                          There may be other ways to achieve the same thing. You still haven't told us what do you intend to do with the result, once you have it.

                          • 10. Re: About String Search
                            zhaohongbin

                            SELECT COUNT(*)

                            FROM

                            (

                            SELECT DISTINCT c.__kp_CustomerID AS CC

                            FROM  Customer AS c INNER JOIN

                                      VisitRecord AS v ON c.__kp_CustomerID = v._kf_CustomerID INNER JOIN

                                      Employee AS e ON v._kf_EmployeeID = e.__kp_EmployeeID

                            WHERE (V.DateVisited<='2012-12-10' AND V.DateVisited>='2012-12-4')

                            ) AS A

                            WHERE CC NOT IN

                            (

                            SELECT DISTINCT c.__kp_CustomerID AS CC

                            FROM  Customer AS c INNER JOIN

                                      VisitRecord AS v ON c.__kp_CustomerID = v._kf_CustomerID INNER JOIN

                                      Employee AS e ON v._kf_EmployeeID = e.__kp_EmployeeID

                            WHERE (V.DateVisited<='2012-12-3')

                            )

                            赵 洪滨

                            simon3c2007@163.com

                             

                             

                             

                            在 2013-1-3,下午12:29,Beverly Voth <noreply@filemaker.com> 写道:

                             

                             

                            created by Beverly Voth in Advanced Discussion - View the full discussion

                            can you post an example of the SQL you'd use to make this query (NOT in FMP)?

                             

                             

                            if use SQL and use SubQuery of SQL,it's very easy to achieve this result. but how to get the same one use FileMaker?

                             

                             

                            Thank you,

                             

                            Beverly

                             

                            Reply to this message by replying to this email -or- go to the message on FileMaker Technical Network

                            Start a new discussion in Advanced Discussion by email or at FileMaker Technical Network

                            Manage your email preferences.

                             

                            FileMaker Developer Conference 2013 • San Diego, California • August 12-15 • www.filemaker.com/devcon

                             

                            • 11. Re: About String Search
                              zhaohongbin

                              Thank U very much.

                              below is my SQL solution:

                              SELECT COUNT(*)

                              FROM

                              (

                              SELECT DISTINCT c.__kp_CustomerID AS CC

                              FROM  Customer AS c INNER JOIN

                                        VisitRecord AS v ON c.__kp_CustomerID = v._kf_CustomerID INNER JOIN

                                        Employee AS e ON v._kf_EmployeeID = e.__kp_EmployeeID

                              WHERE (V.DateVisited<='2012-12-10' AND V.DateVisited>='2012-12-4')

                              ) AS A

                              WHERE CC NOT IN

                              (

                              SELECT DISTINCT c.__kp_CustomerID AS CC

                              FROM  Customer AS c INNER JOIN

                                        VisitRecord AS v ON c.__kp_CustomerID = v._kf_CustomerID INNER JOIN

                                        Employee AS e ON v._kf_EmployeeID = e.__kp_EmployeeID

                              WHERE (V.DateVisited<='2012-12-3')

                              )

                              but I can't find the same manner in FileMaker.

                              thanks and happy new year.

                              赵 洪滨

                              simon3c2007@163.com

                               

                               

                               

                              在 2013-1-3,下午2:46,Michael Horak <noreply@filemaker.com> 写道:

                               

                               

                              created by Michael Horak in Advanced Discussion - View the full discussion

                               

                              • 12. Re: About String Search
                                zhaohongbin

                                Thank U very much. It really work! so great. Thanks a lot.

                                 

                                赵 洪滨

                                simon3c2007@163.com

                                 

                                 

                                 

                                在 2013-1-3,下午2:46,Michael Horak <noreply@filemaker.com> 写道:

                                 

                                 

                                created by Michael Horak in Advanced Discussion - View the full discussion