Lookup value with format rule?

ten4info
Participant V

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?

0 9 207
  • UX
9 REPLIES 9

ten4info
Participant V

Would this be a DEREF action?

ten4info
Participant V

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.

Try:

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

ten4info
Participant V

@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.

Iโ€™m afraid Iโ€™m out of ideas.

ten4info
Participant V

@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?

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

ten4info
Participant V

@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.

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!

Top Labels in this Space