Trying to create a format rule for one table ...

(Tammi Canelli) #1

Trying to create a format rule for one table based on info from another.

I tried the following but the format is being applied to all records not just the ones that have a blank check in date:

ISNOTBLANK(dbo.tblEvacuationRecord[CheckedInDate])

What am I missing?

(Grant Stead) #2

So, this table that you’re trying to apply the formatting to… Does it have a column in it that references this evacuation record? If so, it would need to be something like this… ISNOTBLANK([ref_column_name].[CheckedInDate])

(Tammi Canelli) #3

Two tables:

Registration and Evacuation.

The evacuation table has the ref field back to Registration.

I’m trying to put the format rule on the reg table.

With your expression how does it know to check the evac table?

(Tammi Canelli) #4

@Grant_Stead just FYI CheckedInDate isn’t a column in the reg table.

but RegID is in both

(Grant Stead) #5

@Tammi_Canelli it’s a standard derefference. You point to the column that references the other table, then “.” Look at the desired column…

(Tammi Canelli) #6

Since you’re on a roll this morning.

I’m still having trouble with this.

Since my reference is in the evac table back to registration and it’s the registration table I’m trying to format based on a field in the evac table (that isn’t in the reg table), so I need to add a ref in reg to evac?

(Grant Stead) #7

@Tammi_Canelli I wouldn’t add more columns than you need. You can run a lookup to the evac table…

ISNOTBLANK( LOOKUP([reg_key_column], evactable, evactable column, CheckedInDate) )

this only works if there is only one evac record per reg.