Razzak Arethere known isssues with RULES using sub-selects in their WHERE clause inR:Base 7.6? (Yes, yes, I know, the usershould be on 9.5 but you get what you can get at any given time).If you’ve been following the thread onanother rule, you’ve seen one problem I’ve had.Here’s another I had today. TableVEH has VID (primary key) and a field VLOCAT which indicates the vehicle’scurrent location. This is a foreign key to a VLOCAT table with three values:
Driver Garage Other Table Drivers has VID as a foreign key to VEH.Nulls are allowed. Therule I want says, for the VEH table: IFthe vehicle is assigned to a driver (its VID is found in the Drivers table),then VLOCAT must be Driver IFthe vehicle is NOT assigned to a driver (its VID is NOT found the Driverstable), then VLOCAT must be either “Garage” or “Other”. Hereare two different RULES syntaxes I created: RULES'Vehicle Location must be "Driver" if vehicle IS assigned to adriver. It must be "Garage" or "Other" if the vehicle isNOT assigned to a driver.'+ FOR VEH + SUCCEEDS + WHERE (VLocat IN (Garage,Other) AND VID NOT IN (SELECT VID FROM Drivers)) OR(VLocat = 'Driver' AND VID IN (SELECT VID FROM Drivers)) RULES 'Vehicle Location must be"Driver" if vehicle IS assigned to a driver. It must be"Garage" or "Other" if the vehicle is NOT assigned to a driver.'+ FOR VEH + FAILS + WHERE (VLocat IN (Garage,Other) ANDVID IN (SELECT VID FROM Drivers)) OR + (VLocat = 'Driver' AND VID NOT IN(SELECT VID FROM Drivers)) R:Baseaccepts both versions of the rule .WhenI type: EDITVID, VLOCAT FROM VEH WHERE VID IN (SELECT VID FROM DRIVERS) R:Basecorrectly allows me ONLY to enter DRIVER in the VLOCAT column: nothing else isaccepted. Butif I type: EDITVID, VLOCAT FROM VEH WHERE VID NOT IN (SELECT VID FROM DRIVERS) NOvalue I type in the VLOCAT field is accepted by R:Base!!EVERYTHING I enter, including the correctvalues “Garage” or “Other”brings up therule’s error message. (Yes, I also tried using “VLocat = ‘Garage’ OR VLocat = ‘Other’ “ instead of “VLocatIN (Garage, Other) and got the same result.) Ideas? DavidBlocker David Blocker Email: [email protected] Phone: 781-344-1920 Cell: 339-206-0261
