Lookup value with format rule?

[ten4info]

I have information entered in one table, I want to lookup a value from this table and see if it is present in another table. If it is NOT present, I want to execute a format rule that color code it for recognition that it is NOT in that table used for the Lookup. How to?

[ten4info]

Would this be a DEREF action?

[ten4info]

I think I got it with:

[Contact]<>LOOKUP([_thisrow].[contact],“CRM”,“contact”,“contact”)

It seems to work when I test it. However, I’m noticing when I go to add a record in tables that have Format Rules, the options available (via Ref) are colored. Why would they be colored by default? Once the record is saved though, the Format Rules appear to settle accordingly.

[Steve]

Try:

ISBLANK(FILTER("CRM", ([Contact] = [_THISROW].[Contact])))

[ten4info]

@Steve, this doesn’t seem to do anything for me, ISBLANK(FILTER(“CRM”, ([Contact] = [_THISROW].[Contact]))). The goal is that when I go to the REF list, it’s black text, not defaulted to RED. Only once it is selected, will it become RED or GREEN based on whether the contact is already existing in the CRM table. Thanks much.

[Steve]

I’m afraid I’m out of ideas.

[ten4info]

@Steve, ISBLANK, is that the right expression to use? The format rule should only take action IF the cell includes a contact…not if it is empty. Right?

[Steve]

The condition can be whatever you need it to be; perhaps I misunderstood in my suggestion. You could try using ISNOTBLANK() instead.

[ten4info]

@Steve, OK, so I deployed:

ISBLANK(FILTER(“CRM”, ([Contact] = [_THISROW].[Contact]))) to mark the record RED if not in the CRM

and

ISNOTBLANK(FILTER(“CRM”, ([Contact] = [_THISROW].[Contact]))) to mark the record GREEN if in the CRM.

These seem to be working. However, the REF values in the drop down list form the form are ALL red, including values that are in the CRM.

Not sure if it is possible to ensure that the REF value list is black text to start with to avoid user confusion as to why it is red. Even REF values that are already in the Activity History Log and the CRM are red.

[ten4info]

ISBLANK(FILTER(“CRM”, ([Contact] = [_THISROW].[Contact]))) to mark the record RED if not in the CRM

and

ISNOTBLANK(FILTER(“CRM”, ([Contact] = [_THISROW].[Contact]))) to mark the record GREEN if in the CRM.

These seem to be working. However, the REF values in the drop down list form the form are ALL red, including values that are in the CRM.

Not sure if it is possible to ensure that the REF value list is black text to start with to avoid user confusion as to why it is red. Even REF values that are already in the Activity History Log and the CRM are red.

Any help is greatly appreciated!