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 having problems 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 not assigned a vehicle. It > is filled in if a driver is assigned a vehicle. > > There already is an error trap in Drivers: VID is a Foreign Key to the VEH > table but it can be NULL. This works fine. > > > > I want to set up a rule that says that no TWO drivers can have the same > VID value. I can?t use the UNIQUE KEY option because this requires that > a field be filled in, but it is valid for VID to be NULL. > > > > I tried this rule: > > RULES 'Vehicle ID must be either blank or unique: two drivers cannot be > assigned to the same vehicle' FOR Drivers FAILS WHERE VID IS NOT NULL AND > VID IN (SELECT VID > > FROM DRIVERS WHERE VID IS NOT NULL GROUP BY VID HAVING COUNT(*) > 1) > > > > R:Base takes the rule, but the error trap does not work. I am allowed to > put in duplicate values. > > > > Any ideas? > > > > David Blocker > > David Blocker > Email: [email protected] > Phone: 781-344-1920 > Cell: 339-206-0261 > >
