LOOKUP with Show_if

[Stephen_Monagha]

Hello,

I’m having an issue with a LOOKUP in Show_If.

I have 2 tables called Names and Information. Names has just 1 column with a column called Name and there is a list of names in this column. The information table is empty. Then I have an information form with multiple fields e.g. Name, Address, Phone etc. However the visibility of all fields, except the Name field, is based on the value entered in the Name field. When the Name field is populated a lookup is done to see if that name exists in a Names table. If the name exists then the other fields should be shown, if it doesn’t then they should remain hidden.

The formula I am using in the Show_if is IF((LOOKUP([Name], Names, Name, Name))=[Name], TRUE, FALSE). I have set the Name field in the Names table to be the key. However the only value for Name that works is the name of the first entry in the Names table. No other values work.

I have tried using [_THISROW] but this doesn’t work as the entry has not being saved and so is not in the database yet.

I have also tried hard coding some values into the formula above in place of the [Name] that is passed to lookup and then it works for that name. So the problem does seem to be with [Name].

So my question is, why does it only work for the first entry and what am I doing wrong? Or is what I am trying even possible?

Thanks for any help.
Stephen

[WillowMobileSys]

The IN() expression would be better utilized for your purpose.

IN([_THISROW].[Name], Names[Name]) — I hope I have this right.

You can use [_THISROW] even if the row is not yet saved.

[Stephen_Monagha]

WillowMobileSystems:

[_THISROW]

Thank you very much for this answer. This worked, although I had to remove [_THISROW]

[WillowMobileSys]

Yeah, I still am not clear as to when you need to use [_THISROW].

[Suvrutt_Gurjar · Accepted solution]

Hi @Stephen_Monaghan,
May I request you, if feasible to test once with a bit of different name of the [Name] columns in two tables. For example Names table as [NameN] and the other tabke one say [NameD]

I mention that I have not evaluated your expression for any posdible error in detail. I have also not tested my requested suggestion . This is just out of a feeling that in the same expression , columns with same name in two tables could be creating an issue.

[Stephen_Monagha]

Thank you for this answer, this also worked. Not sure why I went for the IN answer above though as it’s more succinct.

[Suvrutt_Gurjar]

Hi @Stephen_Monaghan,

Good to know both suggestions worked. The one suggested by @WillowMobileSystems is of course a compact one.
On a side note, if I may share, I have learned while working with such expressions that as a good practice it is better to rename columns bit differently. It helps in faster debugging as well. We can take care to show the same name or user friendly column name thru’ display name property.