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?