Validate using progress 9.1d

[rodvitel]

I need to validate data-entry on field IDProy just like:

alter table USUARIO
add constraint CK_IDProy
check (IDProy LIKE 'IC[A-I][0-9][0-9][0-9][0-9]')

using Data Dictionary (Progress 9.1D). I tried Validation button with LIKE but failed . . . I don't know using format field is enough . . . The problem is validating 3rd character to accept range A-I . . . there isn't any application involved . . .

Be possible that this validation is more complex than sql???
Maybe this can't be done with data dictionary . . . data dictionary isn't enough

 

[mrobles]

Hi.

1.- May be you have a normalization problem.
For this validation I use a table (proyect in this case and containing proyects) and the validation is
CAN-FIND(proyect OF usuario)

2.- The format of your field reduces your validation code.
You can use in the format field XXX-999999

If you want to use hard code then you can use in the validation field
substring(idproy,3,1) >= 'A' and substring(idproy,3,1) <= 'I'

 

[rodvitel]

Hi mrobles.

I agree with you, this validation must be on idproy on Proyecto table (principal table) and the validation on idproy on Usuario table (secondary table) has to be validated with CAN-FIND(proyect OF usuario) (referential itegrity), but the problem is that using the validation button on the field properties, Progresss don't send any error if i execute an "insert into table values()" code on procedure editor, procedure editor allow invalid entry of data. I think all on validation button take effect doing insert on RESULTS tool or other progress tool but not on procedure editor. Also I tried with your substring code. WHY ???

Thanks for your time !!!

 

[mrobles]

Ok, Ok.

This is due the validation code acts when you are typing data.
May be a create trigger in the table lets you control it.

 

[mrobles]

Hi

This trigger in the usuario table controls the 3rd character.

TRIGGER PROCEDURE FOR CREATE OF usuario.
IF NOT(SUBSTRING(idproy,3,1) >= 'A' and SUBSTRING(idproy,3,1) <= 'I) THEN
RETURN ERROR.

 

[rodvitel]

Hi mrobles, each day you learn new things . . .

It's rare, trigger above work fine on idproy field trigger (assign), but it doesn't on table's trigger (create) . . .
Thanks for help