11 Replies Latest reply on Jun 4, 2015 11:42 AM by philmodjunk

    Case Statements help

    MartinBishop

      Title

      Case Statements help

      Post

      I have some global image container fields: Projects::ImageBoots / Projects::ImageBootLaces which are holding default images. I would like those default images to apply based on their Cat/SubCats...

      Doesn't work =(

      Case (

      //Boots

      Categories::Name= "Boots" and CategoriesSub::Name = "Laces" ; Projects::ImageBootLaces ;
      Categories::Name = "Boots" and CategoriesSub::Name = "" ; Projects::ImageBoots ;

      )

        • 1. Re: Case Statements help
          philmodjunk

          And did you specify "container" as the calculation's result type?

          And note that your field will be empty if Name is not "Boots".

          Instead of a Case Calculation, it would be more flexible to use a relationship. With a relationship, on going changes to what values you use as Categories and SubCategories won't require that you rewrite your Case calculation each time there is a change.

          • 2. Re: Case Statements help
            MartinBishop

            It setup as a Container with a calculated value, not as a Calculation with container results.

            Changing to a Calculation would erase my images and lock in the images. I am just trying to have a default image like "coming soon" that is eventually replaced with the final image. It works perfect when used without the "and" portion.

             

             

             

            • 3. Re: Case Statements help
              philmodjunk

              It works perfect when used without the "and" portion.

              Check the file type and value of the subcategory field. Make sure that it is not of type Number. Make sure that the value is really "laces" and not "Laces " (note trailing space) where some additional text is keeping the values from matching as expected.

              • 4. Re: Case Statements help
                MartinBishop

                I think I tracked down the problem, my conditional drop down is working, but I think it's flawed because its nots transferring the CategoriesSub::Name (or any other field in that table) to Projects... Probably a relationship problem?... The CatSubID should be related to FK (CategorySub_Relation)?  =/

                • 5. Re: Case Statements help
                  philmodjunk

                  Good observation. From what Context does this calculation evaluate? (There's a context drop down at the top of the specify calculation dialog.)

                  • 6. Re: Case Statements help
                    MartinBishop

                    Thanks for the reply... I did not understand your last question though, I think the calculation for the image replacement is working fine.

                    Its just that once I add AND, those fields don’t work properly, which is why I cannot pull correct data from fields within Table::CategoriesSub into Table::Projects.

                    For example I have:

                    Categories::Category = Bags (via Projects::Category_Relation)( This table works perfect, I can pull other fields from this table into Projects)

                    CategoriesSub::CategorySub (via Projects::CategorySub_Relation) ( Back Packs ) ( Travel ) ( Protective )

                    CategoriesSub::SortOrder ( 20 ) ( 30 ) ( 40 ) ( no matter which CategoriesSub::CategorySub is selected, it always goes to ( 20 )

                    CategoriesSub::CategorySub (will always show Back Packs, even though I am on Travel)

                    • 7. Re: Case Statements help
                      philmodjunk

                      ts just that once I add AND, those fields don’t work properly,

                      Then it would seem that your calculation is NOT working. And "context" is an important factor is analyzing how your calculation does or does not work. I need to know what is selected in the drop down circled in Red below:

                      • 8. Re: Case Statements help
                        MartinBishop

                        It's showing the Products Table within that field.

                        • 9. Re: Case Statements help
                          philmodjunk

                          But there is no "products" table occurrence shown in your relationships screen shot. Did you mean "projects"?

                          I will assume yes.

                          It looks like your calculation refers to the wrong occurrence of SubCategories.

                          Try referring to subCategories 2 in your calculation instead of SubCategories.

                          • 10. Re: Case Statements help
                            MartinBishop

                            Yes there is a ProductTable::ProductKF that is related to ProjectsTable:ProjectID

                            Basically Projects is like a folder with (Names, Categories, etc) and Products is files within (ProductID, Part Number, Image (calc for default - the one not working))

                            Changing the CategorySub 2 did fix the problem... Is there a better way to re-work the relationship so that I don't need to use the secondary table occurrence? I feel like maybe I flip flopped something since the non-subcat works without using #2 occurrence... Either way thanks for the help the problem is correct =)

                            • 11. Re: Case Statements help
                              philmodjunk

                              Yes there is a ProductTable::ProductKF that is related to ProjectsTable:ProjectID

                              The names there are confusing. Is ProjectID a unique identifier and ProductKF the non unique match field meaning that for one Project there can be many products?

                              That would be consistent with the results that you report and this makes the "2' table occurrence the correct option as I have suggested.

                              Multiple table occurrences are very common in FileMaker. I don't see any reason not to have the two different occurrences as each is set up for a different purpose.