Skip to main content
Solved

Dynamic display value for lookup

R
Captain
Forum|alt.badge.img+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)?

 

Best answer by Mark Jongeling

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.

View original
Did this topic help you find an answer to your question?
This topic has been closed for replies.

4 replies

Mark Jongeling
Administrator
Forum|alt.badge.img+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?

"Wake me up before you low-code" | On holiday 06-02 till 27-06
R
Captain
Forum|alt.badge.img+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

Mark Jongeling
Administrator
Forum|alt.badge.img+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

"Wake me up before you low-code" | On holiday 06-02 till 27-06
Mark Jongeling
Administrator
Forum|alt.badge.img+23
  • Mark Jongeling
  • Administrator
  • 3945 replies
  • Answer
  • July 1, 2021

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.

"Wake me up before you low-code" | On holiday 06-02 till 27-06
Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings