Karen I thought I had too, but the next day it didn't work and hasn't worked since. Compare your copy of the rule with what I sent: the messages are different but the rest of the rule is exactly the same
David David Blocker Email: [email protected] Phone: 781-344-1920 Cell: 339-206-0261 -----Original Message----- From: Karen Tellef [mailto:[email protected]] Sent: Tuesday, July 30, 2013 05:13 PM To: 'RBASE-L Mailing List' Subject: [RBASE-L] - Re: An unresolved RULES issue from earlier email I thought you HAD figured this out. I copied your response to my notes document because I thought the rule syntax was clever: RULES + 'Another driver is already assigned to this vehicle' + FOR Drivers + FAILS + WHERE VID IS NOT NULL + AND VID IN + (SELECT VID FROM Drivers d2 + WHERE d2.VID =Drivers.VID AND d2.DCode <> Drivers.DCode) Karen -----Original Message----- From: David Blocker <[email protected]> To: RBASE-L Mailing List <[email protected]> Sent: Tue, Jul 30, 2013 4:02 pm Subject: [RBASE-L] - An unresolved RULES issue from earlier email Hi friends I just realized as I temporarily moved forward on my projectthat an issue I wrote to the list about several weeks ago never wasresolved.I?m trying again to see ifanyone has an idea for a solution. Here?s the Structure:VEH (vehicles) table has primary key column VID (vehicle ID) Table DRIVERS (people who drive the vehicles) has Foreignkey column VID. When a driver is assigned a vehicle, the VID column for thatdriver?s row is set to the VID value. However, there are two wrinkles here: 1)ADriver may NOT currently be assigned a vehicle, so VID could be NULL 2)IfDriver A has a certain vehicle, say VID = 1, Driver B should NOT be allowed toget that vehicle.In other words, NO TWORECORDS in Drivers should have the same value for VID So the rule I want would: 1)PermitNulls 2)Forbidtwo rows in Drivers from having the same value. A rule is needed, because a Unique Key for VID in Driverscannot be set up unless the column has a NOT NULL constraint. Here?s the rule that I tried based on some emails back andforth with Bill Downall: RULES 'Vehicle IDmust be either blank or unique: two drivers cannot + be assigned to the same vehicle' + FOR Drivers FAILS+ WHERE VID IS NOTNULL AND VID IN + (SELECT VID FROMDRIVERS d2 WHERE d2.VID = Drivers.VID AND + d2.DCode <> Drivers.DCode) This does NOT work.R:Base DOES allow null values, but it also allows me to set multiplerows to the same VID value. Any ideas? David Blocker David Blocker Email: [email protected] Phone: 781-344-1920 Cell: 339-206-0261
