Finance | Project Operations, Human Resources, ...

How to build custom lookup based on field value ?

(0) Share

Report

Posted on by Awaxx

229

Hi guys,

I made this custom form, in there i need to make a lookup on red field, based on green field values.

On that form i got a datasource based on a custom table, where i have a field linked to bankaccount table on accountID (Red field) and another linked to data aread id (green field)

The red field will give on each row bankaccount accounts filtered on green field values representing data areas.

I hope my requirement is understoodable.

How could i perform that ?

Thank you all.

I have the same question (0)

All responses (24)

Answers (0)

Suggested answer

GirishS 27,825 Moderator on at

Like (0)

Report

RE: How to build custom lookup based on field value ?

Hi MackNate,

You need to find out the relation between the fields that you mentioned in red and green. After finding the relation you can use that tables in a lookup query to get the desired output.

Thanks,

Girish S.

Was this reply helpful? Yes No
Awaxx 229 on at

Like (0)

Report

RE: How to build custom lookup based on field value ?

Relation might be on Data area Id

But how to write the filter on that ?

Was this reply helpful? Yes No
GirishS 27,825 Moderator on at

Like (0)

Report

RE: How to build custom lookup based on field value ?

You can join those tables in the lookup query and add range to it.

Refer to the below blog.

https://dynamics365musings.com/override-lookup-method-in-d365/

Thanks,

Girish S.

Was this reply helpful? Yes No
Suggested answer

Mohit Rampal 12,563 Moderator on at

Like (0)

Report

RE: How to build custom lookup based on field value ?

Hi, You can refer to below articles to create Field lookup based on another field value

community.dynamics.com/.../applying-range-on-the-lookup-based-on-the-selection-of-value-on-other-field-x

community.dynamics.com/.../858724

Was this reply helpful? Yes No
Awaxx 229 on at

Like (0)

Report

RE: How to build custom lookup based on field value ?

Thank you guys but i didnt understand those examples

Could you explain it please i'm still a newbie ?

Was this reply helpful? Yes No
Bharani Preetham Pe... 3,634 Moderator on at

Like (0)

Report

RE: How to build custom lookup based on field value ?

Please let us know where exactly you are stuck.

Was this reply helpful? Yes No
GirishS 27,825 Moderator on at

Like (0)

Report
RE: How to build custom lookup based on field value ?

You need to override the lookup of the Form DataSource >> Fields >> Method >> Lookup. In your case it will be a red color field.

public void lookup(FormControl _formControl, str _filterStr) { //Specify the name of the table the lookup should show data from. SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(TableName), _formControl); //Create a new query Query query = new Query(); QueryBuildDataSource queryBuildDataSource; QueryBuildRange queryBuildRange; sysTableLookup.addLookupfield(fieldNum(TableName, FieldName)); //join the datasource to the query; queryBuildDataSource = query.addDataSource(tablenum(Table1)); queryBuildDataSource.addRange(fieldnum(TableName,FieldName)).value(queryvalue(DataSourceName.GreenFieldName)); sysTableLookup.parmQuery(query); sysTableLookup.performFormLookup(); }

Thanks,

Girish S.

Was this reply helpful? Yes No
Awaxx 229 on at

Like (0)

Report

RE: How to build custom lookup based on field value ?

I m stuck on how to construct the query

i have field societe representing Data areas values = 523, 535, .... made by a lookup on dataareaid

I have the field where i need the lookp representing Accounts values= SG, BNP, CN ... made by a lookup on bankaccount account id

And one datasource on this form.

I'm wondering how it could be joined ?

Was this reply helpful? Yes No
Mohit Rampal 12,563 Moderator on at

Like (0)

Report

RE: How to build custom lookup based on field value ?

If I understood your requirement correctly, you need to show all data Area Ids in Red column where Bank account field value matches.

You need to override lookup method of data area Id form control (Red) and paste the code that we shared.

BankAccount and DaraArea table can be joined with DataAreaId, as BankAccount is legal entity specific.

Was this reply helpful? Yes No
Suggested answer

Bharani Preetham Pe... 3,634 Moderator on at

Like (0)

Report

RE: How to build custom lookup based on field value ?

For joining tables just go to aot find table and see the relations and make that accordingly. First try in SSMS. Then using ranges and relations execute the query.

Was this reply helpful? Yes No