Bill Thanks for sending me on the right track.I had to make a few minor changes but I gotthe rule to work!
Instead of RULES + 'Another driver is already assigned to thatvehicle' + FOR Drivers + SUCCEEDS + WHERE VID IS NULL + OR NOT EXISTS + (SELECT * FROM Drivers d2 + WHERE d2.VID = Drivers.VID AND d2.DriverID<> Drivers.DriverID) I ended up with (my changes in bold): RULES + 'Another driver is already assigned to this vehicle'+ FOR Drivers + FAILS + WHERE VID IS NOTNULL + ANDVID IN + (SELECT VIDFROM Drivers d2 + WHEREd2.VID = Drivers.VID AND d2.DCode <>Drivers.DCode) I prefer to design rules using FAILS so that I candeliberately create a set of invalid rows, try to find them with a SELECTcommand, then make that into a FAILS rule.The key thing, other than changing to the right Primary Key column forDrivers, was that R:Base did not respond well to the EXISTS or NOT EXISTSsyntax, but worked like a charm with the IN syntax. Thanks again! David David Blocker Email: [email protected] Phone: 781-344-1920 Cell: 339-206-0261 -----Original Message----- From: Bill Downall [mailto:[email protected]] Sent: Tuesday, June 25, 2013 02:51 PM To: 'RBASE-L Mailing List' Subject: [RBASE-L] - Re: Problem creating a RULE Hi, David! Maybe R:BASE is having a hard time doing the count with the existing table and the table as it would be changed by the candidate row. Maybe a simpler, non-GROUP BY rule? RULES +'Another driver is already assigned to that vehicle' + FOR Drivers + SUCCEEDS + WHERE VID IS NULL + OR NOT EXISTS + (SELECT * FROM Drivers d2 + WHERE d2.VID = Drivers.VID AND d2.DriverID <> Drivers.DriverID) Bill On Tue, Jun 25, 2013 at 1:50 PM, David Blocker <[email protected]> wrote: Hi I?m trying to create an error trap with a RULE and havingproblems making it work. The structure: VEH table: one row = one vehicle, Primary Key is VID DRIVERS table: one row = one driver.The VID column is in the DRIVERS table.It is NULL if that driver currently is notassigned a vehicle.It is filled in if adriver is assigned a vehicle. There already is an error trap in Drivers: VID is a Foreign Keyto the VEH table but it can be NULL.Thisworks fine. I want to set up a rule that says that no TWO drivers canhave the same VID value.I can?t use theUNIQUE KEY option because this requires that a field be filled in, but it isvalid for VID to be NULL. I tried this rule: RULES 'Vehicle ID must be either blank or unique: twodrivers cannot be assigned to the same vehicle' FOR Drivers FAILS WHERE VID ISNOT NULL AND VID IN (SELECT VID FROM DRIVERS WHERE VID IS NOT NULL GROUP BY VID HAVINGCOUNT(*) > 1) R:Base takes the rule, but the error trap does notwork.I am allowed to put in duplicatevalues. Any ideas? David Blocker David Blocker Email: [email protected] Phone: 781-344-1920 Cell: 339-206-0261
