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?
Would this be a DEREF action?
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])))
@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.
@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.
@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!
User | Count |
---|---|
32 | |
31 | |
30 | |
19 | |
17 |