Look up expression for ref column

[Phong_Lam1]

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]

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 · Accepted solution]

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

help.appsheet.com

LOOKUP()

Get column value from row in table or slice

help.appsheet.com

References Between Tables

Learn about references, when to create them, and how to use them so your data makes sense.

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]

Phong_Lam:

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 ?

correct.

[Phong_Lam1]

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

[Aurelien]

You are welcome

[Phong_Lam1]

Aurelien:

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.

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:

help.appsheet.com

LOOKUP()

Get column value from row in table or slice

[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

Phong_Lam:

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…

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]

Phong_Lam:

Slice is a good idea, can I do a different view for a “Client” slice than the table “Potential Client” ?

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”)