3 Replies Latest reply on Feb 23, 2012 11:48 AM by RayCologon

    Question about case statement if it exist in FileMaker Pro Advanced and automation

    cdurham

      Hello all,

       

      I need to know if 1. It is possible to code a wildcard statement in FileMaker Pro. 2. Write a case statement that supports 3 or more conditions (for those of you who write sql code you will know what I am talking about) I have inherited code that I am not familiar with. This (Sales) process has been around since 2004 and I joined this company in the summer of 2011. I never heard of Filemaker until 2011 so please bear with me. I have attached the actual table that I am using and needing the case statement for. I also need to know that once a row is updated with the case statement, how do I write a loop for it to automate all of the process. anyones help would be appreciated.

        • 1. Re: Question about case statement if it exist in FileMaker Pro Advanced and automation
          Stephen Huston

          Wildcards are supported in some FileMaker processes, but you din't specify where or why you want them. The term doesn't necessarily mean the same tthing in the FM world as in SQL, so more info...

           

          It is not clear waht the data schema is behind your screenshot, but you should look into both Lookups and the use of the "Case" function within an "Evaluate" function for auto-filling a field based on entries in other fields. Both Lookups and evaluated calculations including Case options are commonly used by FileMaker  developers, and can be triggered at the Field definiition level if FM-native fields, or by Field Triggers on any data-entry layout/screen.

           

          You may need to look into a good third-party FM manual for details. It would take dozens of pages to outline all the possible details involved if these functions are new to you in FileMaker.

          • 2. Re: Question about case statement if it exist in FileMaker Pro Advanced and automation
            ninja

            Howdy cdurham.

             

            Yes, there is a CASE statement...the syntax is just different (I don't know SQL)

             

            Something like:

            Case(RevTypeID = 1 AND RevType = "Comp" AND ProductSku = "Pocoso001435" ; "FTR" ;

            RevTypeID = 1 AND RevType = "RS" AND ProductSku = "Pocoso001435" ; "PPD ACCT" ;

            RevTypeID = 1 AND RevType = "Comp" AND ProductSku = "Pocoso000128" ; "OPT" ;

            et., etc.

             

            Note that the VendorSku field would have to be a Calculation Field resulting in Text.

             

            Is this what you're after?

            • 3. Re: Question about case statement if it exist in FileMaker Pro Advanced and automation
              RayCologon

              Hi cdurham,

               

              The post from ninja (above) gives you an outline of possible syntax for a Case( ) statement that you could use in a calculation field (or an auto-enter calculation expression) in FileMaker to generate the required Vendor SKU result automatically when you enter the RevTypeID etc. However, what that doesn't address is your question about inclusion of 'wildcard' statements.

               

              In FileMaker, it's possible to build several different kinds of expressions to create variants of what you are familiar with as wildcards. For example to incorporate a calculation argument that tests for 'starts with', as per your example, you could use:

               

              Left(ProductSKU; 4) = "AWFE"

               

              ...or to test for AWFE anywhere within the string, you could use:

               

              PatternCount(ProductSKU; "AWFE")

               

              - or any of various other constructions, depending on the precise matching flexibility you need.

               

              Regards,

              Ray

              ------------------------------------------------

              R J Cologon, Ph.D.

              FileMaker Certified Developer

              Author, FileMaker Pro 10 Bible

              NightWing Enterprises, Melbourne, Australia

              http://www.nightwingenterprises.com

              ------------------------------------------------