Solved

Multi column lookup

  • 17 August 2020
  • 7 replies
  • 256 views

Userlevel 5
Badge +20

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?

 

 

icon

Best answer by Anne Buit 18 August 2020, 08:40

View original

7 replies

Userlevel 7
Badge +5

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.

Userlevel 5
Badge +20

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.

Userlevel 7
Badge +5

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.

Userlevel 5
Badge +20

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.

Userlevel 7
Badge +23

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. 

Userlevel 5
Badge +20

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.

 

Userlevel 7
Badge +23

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 :wink:

Kind regards,
Mark Jongeling

 

Reply