Hey Harm,

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.

Hope it can inspire you 

Kind regards,
Mark Jongeling

Mark thanks for your suggestions.

I am familiar with both methods, and know you to work that out. But they are not what I am looking for.

In most cases I will use views, but I don't like it to maintain instead of triggers.

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. 

Just an idea, which I now achieved with a view, but there should be an easier way..
 

Table 'activity’ has a lookup to table 'status_log’. 4 different columns are shown in the activities table, done by a view.

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.

Hi Anne,

Thanks for your reply.

I have now created a solution with expression fields and functions. 

In this case I make 5 calls to the status table for each row in the activity table:

col_id                         col_expression        
act_status_date                dbo.last_status_log_start_date(t1.act_id)
act_status                     dbo.last_status_log_status(t1.act_id)
act_status_progress_pct        dbo.last_status_log_progress_pct(t1.act_id)
act_status_icon                dbo.last_status_log_icon(t1.act_id)
act_status_icon_upload_control dbo.last_status_log_status(t1.act_id)

It works, but it is not the best solution for good performance.

The alternative where I use a view, results in best performance, but the disadvantage is the necessity of instead of triggers.

I would be nice if to have an option ‘add to table query’ for lookup references. Which creates t2, t3, etc. in the table query. 

I think someting similare happens already when lookup fields are in the sort order set.

Hi Harm,

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.