3 Replies Latest reply on Jun 3, 2015 4:33 PM by philmodjunk

    Calculated field: = x WHEN y IS z

    BenT

      Title

      Calculated field: = x WHEN y IS z

      Post

      All,

      I've connected to a MySQL server am I'm trying to map the SKU variable I have in Filemaker, to the one online. The online server stores the SKU, and other variables like stock in a weird way:

      meta_id ______ post_id _______ meta_key ________ meta_value

      121917 _______ 7756 __________ _sku ___________ 43060

      121918 _______ 7756 _________ _stock ___________ 10

       

      I made a field that is a calculation and used "If ( meta_key = "_sku" ; meta_value ), however, no matter what I do, it will always display the meta_value independent of the key.

       

      I need Filemaker to know that the meta_key is related to the meta_value. And I need to be able to do a calculation that gives meta_value WHEN meta_key EQUALS a given value. 

       

      It's worth noting that the meta_id is a unique field, and I believe I could use that to relate them all, but I don't what that value is, hence why I need a calculation to find the value when meta_key equals a certain value.

       

      I figured there would be a "WHEN" function in the Filemaker Calculation, but I haven't seen it... Any recommendations?

        • 1. Re: Calculated field: = x WHEN y IS z
          philmodjunk

          Are you doing a query or a calculation? WHEN would be part of a Query. Your If expression would be a calcualtion. They have two different purposes and implementation details.

          In a calculation field, what you have posted should display either the value of Meta_value if meta_key = "_sku" or be empty if it is not.

          But your reference to a WHERE clause suggests that you are actually trying to define a query--which isn't going to work the way you'd expect in an SQL based RDBMS instead of FileMaker.

          There is, by the way, an ExecuteSQL function in FileMaker in which you can use SQL for Select queries.

          I'd guess that you are trying to set up a relationship that only matches to records where the meta_key is "_sku". That would not be done with a an If function in a calculation.

          Here's one way to do it, but I'm making a lot of guesses here and there are alternative options as well:

          Define a calculation field in a table you want to link to the above table of data with this expression:

          "_sku" and Text specified as the result type.

          in Manage | Database | Relationships, drag from this field to the Meta_key field in your shadow table or table of imported ODBC data.

          Now, a portal to the table of ODBC data placed on a layout based on the table where you defined the calculation field to use as a match field will list only records from the ODBC table that have _sku as the meta_key.

          • 2. Re: Calculated field: = x WHEN y IS z
            BenT

            Phil,

            Thanks for the recommendation. The table I'm looking to connect to is my "Products" table. I created a new field called "WebsiteSKU", as a calculation, with simply "_sku", and stored it as text. I then related that to my imported ODBC data. (Products::WebsiteSKU <--> ODBCdata::meta_key).

            Back on my products screen, I tried it in two ways:

            First, I added a simple box and asked it to display the data of Products::WebsiteSKU ... which was always "_sku"

            Secondly, I created a portal from the ODBCdata ... In this instance, I'm shown a huge list off all of the instances in which meta_key = _sku ... which is good, but I only want it for the one product I'm looking at. Not only that, but I'd love to be able to manipulate said value.

            Forgive my lack of understanding, but perhaps a query is a better solution as my end-goal is to be able to have a field be equal to be SKU on each product. Basically I just need to map all of the ODBC data to my Filemaker tables, and that may involve a median via relationships.

            Thanks for the help Phil. You seem to be the true Filemaker pro around here.

            • 3. Re: Calculated field: = x WHEN y IS z
              philmodjunk

              I had a response posted, but it's not here. I must have not submitted it or the "gremlins" got it or something.

              You aren't limited to a single match field in a FileMaker relationship (Join).

              You can match by the calculation field and by matching the value of your SKU field in Products to the Metavalue field in the ODBC data.