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.