Searching a grandchild data entry.

[BAP]

I have a tenant table, an assignments table, an apartments table which shows apartment numbers, and a cleaning check table, each with an ID column. The assignment table pairs a tenant with an apartment and references the ID column for both. The label for the tenant table is the computed name. I want the assignment table to have the same label as the computed name found in the tenant table. In the past, I have just set the label for the assignments table as the referenced Tenant ID and it has worked out great. Each assignment is labeled as the tenant that is in that spot. I then reference the assignment table in the cleaning check table. I have set up a dependent dropdown where I can select an apartment and get only the tenants that live in that apartment when selecting the tenant.

The problem I am running into is that I am unable to search for a tenant based on their name in the cleaning check form. I can only search based on the Assignment ID even though it is showing the names of the tenants. I have tried pulling the computed name into the assignments table via a virtual column and then setting that as the label but it doesn't change anything.

Any thoughts on what I could do to get this to work?

Before Search

After Search

[Aurelien]

Hi @BAP 

This behavior is weird, you may want to issue a ticket with the support.

Just in case, would you please check that in the table of the tenants, the Name is set to be searchable?

[BAP]

I checked and it is set as searchable. I will see about submitting a support ticket on this.

[TeeSee1]

I did a quick test of this and got the same behavior (searchable only on Assignments ids). So this is either a bug or a spec. Either way I expect that it would take some time, if not forever, before it gets altered.

One way you can work around this is that in the Cleaning Check table, create a column that references the Tenants and expose this to the user. In its suggested values you set an expression something like

SELECT(
 tenants[id], 
 IN([id], 
  SELECT(
   assignments[ref to tenants], 
   [ref to apartments] = [_THISROW].[ref to apartments]
 )
)

This will enable the user to search a tenant on its label from a dependent list rather than on the assignment id.

If you need the assignment id, create either a VC or another regular column that references Assignments table and retrieve the id using the [ref to apartments] and [ref to tenants].

Hope I understood your situation correctly and this would provide some help in the meantime till it hopefully gets 'fixed'.

[BAP]

Thanks! I will give this a try. I did reach out to support and they are looking at it as well.