Hi Bill
Thanks for the idea. I?ll try it and let you know! 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
