Larry You are VERY very clever - I've been puzzling over this one ever since JM posted it - well done. I tested it as well on the database I'd built here and it works like a charm!
David Blocker ----- Original Message ----- From: "Lawrence Lustig" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, February 25, 2002 12:03 PM Subject: Re: Looking for a rules > Hi JM. See answer at bottom. . . > > > I have a table that contains the components for a receipt. > > Each row contains 3 columns : > > > > Receipt TEXT (8) > > Component TEXT (8) > > Weight_Percentage DOU > > > > I am looking for a rule able to prevent user to enter new rows or change > > existing rows such as the sum of Weight-Percentage stay less or equal to > > 100 % > > > > I tryed : > > RULES 'Receipt incorrect' FOR composition SUCCEEDS + > > WHERE (100 -Weight_Percentage) >= (SELECT SUM(Weight_Percentage) > FROM > > composition T1 + > > WHERE T1.Receipt = composition.Receipt) > > This is almost the answer. The problem is that you need to have a primary > key on this table, so you can exclude the current row from the SUM if the > row has already been added. Assuming the key is CompID, try the rule like > this: > > RULES 'Receipt incorrect' FOR composition SUCCEEDS + > WHERE (100 -Weight_Percentage) >= + > (SELECT SUM(Weight_Percentage) FROM composition T1 + > WHERE T1.Receipt = composition.Receipt AND + > T1.CompID <> Composition.CompID) > > I did a quick test with this rule using both insert and update (from the > browser) and it appears to give the results you want. > -- > Larry > > > > _________________________________________________________ > Do You Yahoo!? > Get your free @yahoo.com address at http://mail.yahoo.com > > ================================================ > TO SEE MESSAGE POSTING GUIDELINES: > Send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: INTRO rbase-l > ================================================ > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: UNSUBSCRIBE rbase-l > ================================================ > TO SEARCH ARCHIVES: > http://www.mail-archive.com/rbase-l%40sonetmail.com/ ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l ================================================ TO SEARCH ARCHIVES: http://www.mail-archive.com/rbase-l%40sonetmail.com/
