R.I., but only for values > 0?

Hi everyone,

We'd like to restrict record inserts/updates in a child table when the
foreign key field value is greater than zero, but only if the FK value
doesn't exist in the corresponding "parent" table.

In other words, this particular field value can be blank (or zero) in the
child table, but if it isn't it needs to be a value that exists in the
"parent" table. We don't want to have to ship the parent table with a zero
PK record to satisfy the R.I.

We have other R.I rules for this table and we're trying to make this type of
logic work. So far we haven't come up with anything that doesn't create
holes in the other defined R.I. rules.

Any suggestions?

Thanks.

--
William Fields
MCSD - Microsoft Visual FoxPro
MCP - Win2k Pro
US Bankruptcy Court
Phoenix, AZ

The secret to creativity is knowing how to hide your sources.

- Albert Einstein

William
Wed Aug 06 15:05:06 CDT 2003

Thanks Gregory, the RecordValidationRule approach is exactly what I need
(should have thought of it myself...).

Regards,

--
William Fields
MCSD - Microsoft Visual FoxPro
MCP - Win2k Pro
US Bankruptcy Court
Phoenix, AZ

The secret to creativity is knowing how to hide your sources.

- Albert Einstein


"Gregory Adam" <GregoryAdam@PleaseReplyViaNewsGroup.com> wrote in message
news:%23ZnnWk%23WDHA.2464@TK2MSFTNGP09.phx.gbl...
> William,
>
> a couple of possibilities
>
> (1)
> The easiest way to solve this is to have a parent record with key=0, desc=
> (none). The RI rules can be applied since the zero parent will exist.
> You can 'hide' the zero parent in the maintenance form.
>
> (2) in the RI, set the Insert/Update to Ignore.
> Then, either
> (a) write a ttrigger to cover that, or
> (b) do the check in the child.recordValidationRule() only if the field has
> changed.
>
>
*--------------------------------------------------------------------------
> function FieldChanged(FieldName)
>
> do case
> case isnull(oldval(FieldName)) or Isnull(eval(FieldName))
> return (isnull(oldval(FieldName)) <> Isnull(eval(FieldName)) )
> otherwise
> return (oldval(FieldName) <> eval(FieldName))
> endcase
> endfunc
> *-------------------------------------------------------------------------
>
> Gregory
>
> "William Fields" <Bill_Fields@azb.uscourts.gov> wrote in message
> news:%23eiYcg5WDHA.608@TK2MSFTNGP12.phx.gbl...
> > Hi everyone,
> >
> > We'd like to restrict record inserts/updates in a child table when the
> > foreign key field value is greater than zero, but only if the FK value
> > doesn't exist in the corresponding "parent" table.
> >
> > In other words, this particular field value can be blank (or zero) in
the
> > child table, but if it isn't it needs to be a value that exists in the
> > "parent" table. We don't want to have to ship the parent table with a
zero
> > PK record to satisfy the R.I.
> >
> > We have other R.I rules for this table and we're trying to make this
type
> of
> > logic work. So far we haven't come up with anything that doesn't create
> > holes in the other defined R.I. rules.
> >
> > Any suggestions?
> >
> > Thanks.
> >
> > --
> > William Fields
> > MCSD - Microsoft Visual FoxPro
> > MCP - Win2k Pro
> > US Bankruptcy Court
> > Phoenix, AZ
> >
> > The secret to creativity is knowing how to hide your sources.
> >
> > - Albert Einstein
> >
> >
> >
>