6 Replies Latest reply on Dec 19, 2012 1:25 PM by Terri

    Trouble with Case Script



      Trouble with Case Script


           I have a script that seems pretty straight forward. 
           I have two tables, related by style number. 
           Table 1: Workhorse Data
           Table 2: WorkhorseImport_Review
           This field is on the Workhorse Data table.
           My formula says the following:
           If ( Colorway_Status = "adopted" and WorkhorseImport_Review::Colorway_Status = "Adopted"  or WorkhorseImport_Review::Colorway_Status = "" and Color Correctability = "" ; ""; "Complete")
           Right now, if a status is adopted and the color correctability is not blank, it says complete (which is right). Also, if the status is adopted and the color correctability is blank, it is blank (which is right). But if the color status is dropped (on the Workhorse Data table) it is coming back as blank. I believe this should be "Complete" because it basically doesn't match any of the other criteria. 
           I should mention that there is not always a matching record in the WorhorseImport_Review table. But if I take out the part of the formula that states that is blank (ie. there is no record) then the formula doesn't work at all.
           Any help is appreciated. I feel like I've done hundreds of If/Case formulas and they are easy to comprehend, but this one has me baffled as to why it won't work.

        • 1. Re: Trouble with Case Script

               In the specify calculation dialog, clear the "do not evaluate if all referenced fields are empty" check box.

          • 2. Re: Trouble with Case Script

                 That didn't work, dropped styles are styll blank and they should say complete.

                 Back to my original calculation, it also appears that if the colorway status field in the WorkhorseImport_Review table is populated with something, it is also blank. If it is adopted and the color correctability field is populated, it should also say complete.


            • 3. Re: Trouble with Case Script

                   You may need some parenthesis to be sure you get an oder of operations that evaluates correctly for what you want such as:

                   If ( ( Colorway_Status = "adopted" and WorkhorseImport_Review::Colorway_Status = "Adopted" )  or
                        ( WorkhorseImport_Review::Colorway_Status = "" and Color Correctability = "" ); ""; "Complete")

                   But please note that I am only guessing at what may work. You are testing values in 3 different fields so it is not fully clear to me what result should occur for the possible different combinations of values in these three fields.

                   You may also want to experiment with the case function instead of If to get easier boolean expressions to work with.

              • 4. Re: Trouble with Case Script

                     I'm pretty close (or close as I'm going to get). I did try your suggestions and different combinations resulted in different results, but I appreciate the effort considering the fact that I didn't want to explain every field and every table that I have.

                     The end result is every field works right except if if the WorkhorseImport_Review::Colorway_Status is dropped it still says complete. I think this is fine because that is the least likely scenario anyway.

                     Now, one more hiccup. Phil, you had helped me create a calculation field that would indicate if all the color correctability fields were populated. Because of the way other scripts perform this wasn't working and that is why I developed this process. Now I need to modify that calculation. It currently says: Count ( WorkhorseData::Style_Color ) = Count ( WorkhorseData::Color Correctability )

                     I'm assuming it should change to Count Count ( WorkhorseData::Style_Color ) = Count ( WorkhorseData::Color Correctability Complete )

                     *Note Color Correctability Complete is my new field I just created.

                     But this doesn't seem to be working right. The only reason I can think of is that this new field is a calculation field rather than just a text edit field. Does this need to change?

                • 5. Re: Trouble with Case Script

                       I see no reason why the type of field (Calculation) would be a factor as long as it is set to return "text" not "number". Please note, however, that I have made way too many posts to way too many threads for me to accurately remember the details of your earlier issue.

                  • 6. Re: Trouble with Case Script

                         Understand....if I continue to have trouble I'll create a new post detailing the issue. Thanks for your help.