Master Details Table with multiple Ids

mishanian · Post by **mishanian** » Mon Jan 30, 2023 10:18 am

Hi,
I have a tenants table,
the table is as follows:

tenant_id, name // 1, John ... 2, Eve ...

I have another building table (with multiple tenants) that is as follows:

building_id, building_name, tenant_ids // 10, "DavidBld", "1,2"

when I generate this table, I would like to add a column for building from the building table to the tenant's table:
I know that I can not connect these 2 tables as master/details because, in the building table, the tenant_ids is like "1,2"
that's why I added a custom field by the name of building_id with a blank SQL to the tenants table and try to fill it correctly

in the tenants table Row_Rendering() I have

function Row_Rendering()
{
  $tenant_id=$this->tenant_id->CurrentValue;
  $building_id=ExecuteScalar ("select building_id from buildings WHERE FIND_IN_SET( $tenant_id, tenant_ids ) ");
  $this->building_id->CurrentValue=$building_id;
}

Unfortunately, I can not change these 2 tables structures, (but I can add a view in case needed)
is this the best approach to get the buidling_id on the tenant's table?

arbei · Post by **arbei** » Mon Jan 30, 2023 11:54 am

mishanian wrote:

I can add a view in case needed

You better create a database view instead of using server event. You should also improve your SQL to make sure only one building is returned. (Your SQL "select building_id from buildings WHERE FIND_IN_SET( $tenant_id, tenant_ids )" may return more than one records.)

mishanian · Post by **mishanian** » Mon Jan 30, 2023 12:34 pm

Thanks for your answer,
creating view has this issue that
for updating and inserting, it should refer it to the main tenant table and I can not use the view for the CRUD function, that's why I tried to use the server events.

mishanian · Post by **mishanian** » Mon Jan 30, 2023 12:40 pm

Also, I need your advice on this issue.
If I have to use server events for this case,
the Row_Rendering function tries to add building_id to the tenant's records (that is as expected)

if I use a drop-down select with UseLookupTable of tenants table, Row_Rendering function again add building_id first and then drop-down select is generating (still is the correct behaviour)
is it possible only for Lookup the Row_Rendering function, does not populate building_id?