For example, consider a table purchase_order with a lookup to an address table.
The link is made to the PK field of the address table.
In the purchase order screen, I want to be able to show fields of the address as separate fields.
I know it is possible to work with expression fields, but multiple lookup queries to the same table is not desirable.
Concatenate address fields is also not what I want. I really want to be able to display the address fields as separate (read-only) fields in the purchase order table.
Ideally, the main table 'purchase_order' will get t1 as an alias and the address table t2 as an alias, etc ..
Is there a trick to get this done yet?
Best answer by Anne Buit
Hi Harm,
Expression fields would be the easiest way to achieve this.
The queries for the expressions would be something like this:
select t2.status_description
from status_log t2
where t2.status_id = t1.status_id
select t2.status_icon
from status_log t2
where t2.status_id = t1.status_id
This will not fire any additional look-up translations and will be included in the initial data loading. The fields will also automatically update without the need for default logic if the status is editable.
Downside is, this needs to be maintained. If this is something that is used a lot, you could consider using dynamic model to automatically update the query based on a tag or a naming convention.
The two other ways I can think of is through the use of a view where you can decide yourself which tables to use and which fields to show. An the other way is through using real fields and filling them by a Default procedure.
I do like the idea of getting multiple fields out of a lookup. These 'borrowed’ fields then could use shown to the user as Read only fields. I do think that making the editable will be very difficult coding wise.
Like you said, having one table with a lookup to Address (probably on table1.address_id = address.address_id). If then it would be possible to also get the street, number, postal_code, maybe even country_id? I think it can be very beneficial.
Expression fields would be the easiest way to achieve this.
The queries for the expressions would be something like this:
select t2.status_description
from status_log t2
where t2.status_id = t1.status_id
select t2.status_icon
from status_log t2
where t2.status_id = t1.status_id
This will not fire any additional look-up translations and will be included in the initial data loading. The fields will also automatically update without the need for default logic if the status is editable.
Downside is, this needs to be maintained. If this is something that is used a lot, you could consider using dynamic model to automatically update the query based on a tag or a naming convention.
Unless you are using SQL 2019 and leverage TSQL_SCALAR_UDF_INLINING, it might be more performant to directly place the query in the expression instead of wrapping the query in a scalar function.
Performance will be comparable to a view this way.
We use 3 different kinds of cookies. You can choose which cookies you want to accept. We need basic cookies to make this site work, therefore these are the minimum you can select. Learn more about our cookies.
