Data Validity and Formatting for referenced tables

tcanelli · 07-23-2019 05:27 AM

I have two tables: EvacRecord and PetRegistration.

I have been asked to prevent users from checking a person out (EvacRecord) if they have not checked out their pet (PetReg). How can I do this?

I tried using the data validity to say: ISNOTBLANK(dbo.tblPetRegistration[CheckedOutBy]) but this still let me check the person out.

Also, I want to set a format rule so if the person has a pet checked in their is a green paw and if the person has checked their pet out the paw is a diff color. But when I set a format rule every record gets a paw - what is the expression to have it match the RegID between the two tables?

I’d be setting the format rule for the record in the EvacRecord, but I can’ figure out how to get it to only reference the RegID of the row in question.

Thanks

Aleksi

Instead of using ISNOTBLANK(), you could try to use COUNT(…)>0

tcanelli

But what would I be counting? I would need a way to check which pets were checked in but not checked out. if pets are checked and out then no error, if checked in but not out then error.

Aleksi

How about… AND(COUNT(…)>0,MOD(COUNT(…),2)=0)