Solved: Re: Look up expression for ref column

Phong_Lam1 · 12-07-2021 09:30 AM

Hi all, I have table “Client” ,table “Potential Client” and table “People” , Table “People” referenced to table “Client” ,and table “Potential Client”

Is there a way when I add new “People” in form,when we choose “Potential Client ID” (a reference column), the other column [Client ID] (also ref column with “People” table, will automatic filled (just as dereference) when “Potential Client ID” is [Client ID] (potential client become client ).

I think about to have a look up expression for [Client ID]. But dont know how to do.

Thanks !

Suvrutt_Gurjar

As per understanding, finding a key that is unique in a table from other values through LOOKUP() could be an issue because there is a possibility of other values being repeated in a table.

Anyway, you may wish to try the below.

Please create a column in the "People " table called [Potential Client Name] with the expression [Potential Client ID].[Potemtial Client Name]

Then the lookup for [Client ID] in the “People” table can be

LOOKUP([_THISROW].[Potential Client Name], “Client”, “Client Name” , “Client ID”)

View solution in original post

Suvrutt_Gurjar

If you could better describe your requirement, the community could help you better.

Phong_Lam1

I will describe It
Table Client

table “Potential Client”

Table People

Table “People” referenced to table “Client” ,and table “Potential Client” . in Form of of table People, Is there a way automatic filled the red cell (The value shoud be ClientID001 )
Hope It clear, thanks

Suvrutt_Gurjar

As per understanding, finding a key that is unique in a table from other values through LOOKUP() could be an issue because there is a possibility of other values being repeated in a table.

Anyway, you may wish to try the below.

Please create a column in the "People " table called [Potential Client Name] with the expression [Potential Client ID].[Potemtial Client Name]

Then the lookup for [Client ID] in the “People” table can be

LOOKUP([_THISROW].[Potential Client Name], “Client”, “Client Name” , “Client ID”)

Phong_Lam1

Thanks @Suvrutt_Gurjar and @Aurelien , may I ask
why not LOOKUP([Potential Client ID].[Potential Client Name], “Client”, “Client Name” , “Client ID”) but LOOKUP([_THISROW].[Potential Client Name], “Client”, “Client Name” , “Client ID”) . I always confused about [_THISROW]

Aurelien

You may want to read this

Short explanation:
In a row evaluation:
LOOKUP([_THISROW].[Potential Client Name], “Client”, “Client Name” , “Client ID”)
is equivalent to:
LOOKUP([Potential Client Name], “Client”, “Client Name” , “Client ID”)
if…[Potential Client Name] is in the same table.

This:
LOOKUP([Potential Client ID].[Potential Client Name], “Client”, “Client Name” , “Client ID”)
is looking for [Potential Client Name] value, in the table of Clients, based on the value of [Potential Client ID], as it belongs to it. This is called a dereferenced expression.

Phong_Lam1

Sorry may I ask one more question: if I use this expression
LOOKUP([Potential Client ID].[Potential Client Name], “Client”, “Client Name” , “Client ID”)
then I dont have to create a column in the "People " table called [Potential Client Name] with the expression [Potential Client ID].[Potemtial Client Name]. Right ?

Aurelien

correct.

Phong_Lam1

Great, thank you @Aurelien . Words could not descibe my gratitude for you

Aurelien

You are welcome

Phong_Lam1

Hi @Aurelien,sorry one more question. What if [Potential Client Name] not in the same table, then how to identify [_THISROW].[Potential Client Name] ? Is it the mean the column [Potential Client Name] from the table that referenced to table “People” ? If that is the case, then we can still don’t need to create a column in the "People " table called [Potential Client Name] with the expression [Potential Client ID].[Potemtial Client Name] right ?

Thanks

Aurelien

Hi @Phong_Lam

I suggest you read this in order to get more information about [_THISROW] and LOOKUP() expression:

Aurelien

Hi @Phong_Lam

You question is unclear to me, @Suvrutt_Gurjar is right and he is suggesting a good solution.

May I ask waht’s the difference between Client and Potential Client ?
If it’s just a matter of status, like “potential/real”, you may want to merge the two tables.
So, your table People would use, instead of LOOKUP(), the expression:
IF([Status]="real", [Client_ID],"")

Phong_Lam1

The reason to do this because below table “Potential Client” , and “Client” have inline view of People related to “Potential Client” , and “Client”. So I just dont want to choose twice…
Yes. “Potential Client” have status “active client/met/not approached…”
I am thinking should I merge those 2 tables …Because it have difference references things…

Aurelien

You could use Slices.

Client_Potential, row filter condition [status]="not approached"
Client_Met, row filter condition [status]="Met"
Client_Active, row filter condition [status]="Active"
and so on

I don’t get it, can you explain further ?

Phong_Lam1

I mean below table “Potential Client” , and “Client” have inline view of People related to “Potential Client” , and “Client”

so when you press “add” people there will have a form, already ref to “Potential Client” or “Client”, If that below “Potential Client” , then I dont have to choose for “Client ID” in the dropdown again.
Slice is a good idea, can I do a different view for a “Client” slice than the table “Potential Client” ?

Aurelien

That’s, I would say, one of the main advantage of it

About the Add: if the slice is read-only, you will not have the Add button (you may preferrently set the update mode to “update”)