autofill a lookup field based on a related table

(0) Share

Report

Posted on by Community Member

Hi,

I want users of a Oppty form to complete a lookup field ("Country") then have another field "Region" autofill to save them time, based on a value in a field (also called "region") on a custom table "Country" which has a list of countries. In this table I have effectively 'mapped' each country to a region e.g. EMEA, APAC etc, and I want the mapepd value to appear in the Oppty form.

What is the best way to go about doing this, so that the user when they put a value agains the lookup field for Country, it autofils 'region' (based on my mapping).

Thanks all,

Andrew

I have the same question (0)

All responses (18)

Answers (1)

Suggested answer

moko33 1,788 on at

Like (0)

Report

If that field Oppty.Region already exists I would suggest a Workflow triggering on create and change of field Country. If it doesn't exist I would suggest a Calculated Field where you simply display the Region of the Country.

Was this reply helpful? Yes No
Suggested answer

moko33 1,788 on at

Like (0)

Report

If that field Oppty.Region already exists I would suggest a Workflow triggering on create and change of field Country. If it doesn't exist I would suggest a Calculated Field where you simply display the Region of the Country.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi moko33,

Thanks for the reply!

The Oppty.Region field doesnt exist (but can easily be created) but Im interested in your second option - how can you create a calculated field with the region of the country? What might that formula look like?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Sorry moko33, I'm something of a novice - would you mind also stepping me through the workflow logic (your first recommendation) as well in a bit of detail? Also, just to be clear, the Country field is a lookup to a custom table.

Was this reply helpful? Yes No
Suggested answer

ba365guy 2,950 on at

Like (0)

Report

Hi

I have a similar situation going on. Please see the below solution:

1. Create a workflow on Opportunity entity.

2. Trigger for workflow on change of "Country" (lookup)

3. Workflow snapshot

4. Update region:

5. Save and Publish

6. Make sure the users have appropriate privileges for country and region entities

Let me know if this works for you.

Was this reply helpful? Yes No
LuHao 40,892 on at

Like (0)

Report

Hi partner,

So there is a lookup field "Country" and a text field "region" on the Opportunity form, you would like to populate the field "region" based on the value of the selected field "Country", right?

If so, I would recommend you use calculated field to achieve it.

Here is a demo.

I have 6 countries records.

There is a lookup field "Country" and a text calculated field "region" on the Opportunity form.

The logic of the calculated field is shown below.

(Note that the text in the action of the calculated field requires double quotes.)

The effect is that when I select a country and save the Opportunity record, the field "region" will be automatically filled with the corresponding value.

Was this reply helpful? Yes No
LuHao 40,892 on at

Like (0)

Report

Hi partner,

If "region" is also a lookup field and the entity region and country have a 1: N relationship, then you have to use a workflow.

In addition, if the field "region" is only used to display the selected country's attribute, it does not require user input value, then from a certain point of view, it is actually a redundant field.

Instead of creating a field "region" for the Opportunity entity, we can use the quick view form to display the value of the field "region" for the country which is selected on the Opportunity form.

The quick view form can display the field values of related records without creating any additional fields within this entity.

In this way, you can reduce the storage occupied by the new field and avoid the need to set up calculated fields or workflows.

Quick view form introduction: https://carldesouza.com/dynamics-crm-quick-view-forms/

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

That's much appreciated - If the Region field was a lookup, would this solution change?

Was this reply helpful? Yes No
ba365guy 2,950 on at

Like (0)

Report

In the solution i provided, region is a lookup.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks Lu Hao. I should have mentioned the custom table (i.e the country list) contains around 80 values so i sense this would be a good soln for a smaller list. Knowing this, does that perhaps put the workflow solution as preferred?

Was this reply helpful? Yes No