Solved

Dynamic display value for lookup


Userlevel 3
Badge +5

Situation:

I have a list of products with a product name. For each customer I have the option to indicate which products are allowed and give this customer-product an alias. With an alias the customer only knows the alias and not the original product name. So the same product for different customers can have different names.

A screen for the service desk should show all products (also those that are not connected to a/the customer). When no customer is selected all products are shown with their product names. But when selecting a customer, the products should have a mark if it is available for the selected customer, and when an alias is known for this customer, the product name must be extended by this alias (for instance by + ‘ - ‘ + alias).

Question:

What options within the SF do I have to get this dynamic display value without having to resort to a view with a cross product between all customers and products (as this can become very large)?

 

icon

Best answer by Mark Jongeling 1 July 2021, 09:13

View original

4 replies

Userlevel 7
Badge +23

Hi Roy,

Alternatively you can create an Expression field that shows the correct name in every situation. Not sure if it will be very fast but if it isn't, you can limit the amount of rows visible in the screen. Would that help?

Userlevel 3
Badge +5

Hi Mark,

Can I use in an expression field information from outside of the lookup table? As I assume that the lookup table has the display value column that will be an expression field, but the customer information is from outside of this lookup field.

Do I reference the "calling” table t0, with t0.customer_id being the reference to customer and in this way be able to cross this with t1.product_id? And the alias being available (or not) through something like:

SELECT x.alias FROM customer_product x WHERE x.customer_id = t0.customer_id AND x.product_id = t1.product_id

Userlevel 7
Badge +23

You can use other tables in the Expression column. The “calling” table can be accessed by using the alias t1.

Example:

select coalesce(p.alias, p.name)
from product_alias_customer p
where p.product_id = t1.product_id
and p.customer_id = t1.customer_id

The table does not need a reference with the named table in the expression query

Userlevel 7
Badge +23

I looked with Roy at the challenge and we concluded that an expression field would not work sufficiently as a value was needed in a lookup but was not part of the reference to the lookup.

The way of solving it is using a View to combine customers and products (and aliases), then using the customer_id is the lookup reference to filter the View to only show products (and aliases) that the customer may select.

Reply