web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / How to filter based on...
Finance | Project Operations, Human Resources, ...
Suggested answer

How to filter based on legal entities where tables are shared?

(1) ShareShare
ReportReport
Posted on by .. 1,907
Hi,

I have Table1 (save per company = no) which means shared. it has the following three fields:
Id1, Name, legalEntity 
1    Name1,  USMF
2    Name2,  USMF
3    Name2,  ABC
** legal entity relation is with company info
** Id1 is the unique index
 
Another shared table (Table2), which has the following fields:
Id1, Id2 (both of them together are unique index)
1    x
2    x
3    y
**Id1 relation is with the first table
 
Last table (Table3) is not shared (save data per company = yes), and it has the following fields:
CustomerId, Id1, Id2  (the three of them together are unique)
 
**Id1 relation with table1
**Id1, Id2 relation with table2
 
Now Table3 is used in a form alone (no other datasources)

Now when I want to pick Id1 in the form, I should only see in the drop down/Id1s/ where the legal entity in Table1 matches the current user legal entity.
So if the current user legal entity is ABC. When adding record to table3, then he should only be able to see Id1= 3 in the drop down
and if  the current user legal entity is USMF. When adding record to table3, then he should only be able to see Id1= 1 or Id1 =2

how to do that?
I have the same question (0)
  • Martin Dráb Profile Picture
    Martin Dráb 236,570 Most Valuable Professional on at
    How to filter based on legal entities where tables are shared?
    I think it'll be sufficient if you add one more field to the relation from Table3 to Table1. In addition to the link over Id1, you'll link Table3.DataAreaId and Table1.LegalEntity.
  • .. Profile Picture
    .. 1,907 on at
    How to filter based on legal entities where tables are shared?
    Hi Martin,

    It didn't work. i made a relation on Table3 to Table1 (Id1 and legal entity) but still the drop down of Id1 in Table3 is showing everything.

     
     
  • Martin Dráb Profile Picture
    Martin Dráb 236,570 Most Valuable Professional on at
    How to filter based on legal entities where tables are shared?
    The statement that a part of a relation is completely ignored is strange; it shouldn't work like that. Didn't you, for example, try to write a lookup method that is now blocking the standard logic?
  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 299,576 Super User 2025 Season 2 on at
    How to filter based on legal entities where tables are shared?
    Hi IntegrationBeginner,
     
    Can you share what table relations and lookup development you did so far? What is the reason for having the tables 1 and 2 shared?
  • Suggested answer
    Bharani Preetham Peraka Profile Picture
    Bharani Preetham Pe... 3,634 Moderator on at
    How to filter based on legal entities where tables are shared?
    This can be achieved by giving a lookup method on form control or form field and there just give the range in the lookup query.
  • .. Profile Picture
    .. 1,907 on at
    How to filter based on legal entities where tables are shared?
    Hi Martin,

    No, i don't have any custom lookup. So, I deleted the relation now and added code on form data source   field for table3 and it worked.
    However, how can i do this validation if someone tries to fill Table3 using data entity? 
    [Form]
public class Form3 extends FormRun
{
    [DataSource]
    class Table3
    {
        [DataField]
        class Id1 
        {
            /// <summary>
            ///
            /// </summary>
            /// <param name = "_formControl"></param>
            /// <param name = "_filterStr"></param>
            public void lookup(FormControl _formControl, str _filterStr)
            {
                //super(_formControl, _filterStr);

                SysTableLookup          sysTableLookup = SysTableLookup::newParameters(tableNum(Table1), _formControl);
                
                Query                   query = new Query();
                QueryBuildDataSource    queryBuildDataSource;
                QueryBuildRange         queryBuildRange;
 
                queryBuildDataSource = query.addDataSource(tableNum(Table1));
                
                queryBuildRange = queryBuildDataSource.addRange(fieldNum(Table1, LegalEntity));
   
                queryBuildRange.value(queryValue(curExt()));

                sysTableLookup.addLookupfield(fieldNum(Table1, Id1));
                sysTableLookup.addLookupfield(fieldNum(Table1, Name));
                sysTableLookup.addLookupfield(fieldNum(Table1, LegalEntity));
 
                sysTableLookup.parmQuery(query);
                sysTableLookup.performFormLookup();
            }

        }

    }

}


    Hi Andre,
    All relations I have are described in the question. I didn't have any lookup code when i posted the question.
    So at first Table3 had only these two relations:

    First Relation:
    Table3.Id1 = Table1.Id1

    2nd relation
    Table3.Id1 = Table2.Id1
    Table3.Id2 = Table2.Id2
     
    then I  amended first relation to be like this:
    Table3.Id = Table1.Id
    Table3.DataAreaId = Table1.LegalEntity
    but it didn't work, so i removed the legal entity from this relation and added lookup code as i showed above. However, how can i do this validation if someone tries to fill Table3 using data entity?

    Hi Bharani,

    I amended lookup method on form field. However, how can i do this validation if someone tries to fill Table3 using data entity?
  • Suggested answer
    Bharani Preetham Peraka Profile Picture
    Bharani Preetham Pe... 3,634 Moderator on at
    How to filter based on legal entities where tables are shared?
    You can use validatefield on table or data entity for giving the validation.
  • .. Profile Picture
    .. 1,907 on at
    How to filter based on legal entities where tables are shared?
    Hi Bharani,

    it seems when importing through entity, the  "validate field" method on table, only gets called in case of insert. However, not for update.
    is there a method that can be called for both?
  • Suggested answer
    Bharani Preetham Peraka Profile Picture
    Bharani Preetham Pe... 3,634 Moderator on at
    How to filter based on legal entities where tables are shared?
    Table validatefield method gets called on entity update as well. Please refer to this link.
     
    Also you can try validation at entity level where in entity also same methods you can find.
  • .. Profile Picture
    .. 1,907 on at
    How to filter based on legal entities where tables are shared?
    Hi Bharani,

    It seems it doesn't get called, if i import the same record without making any changes.
    So I had an old record in Table3 which shows Id1 in a wrong entity, as it was created before I amended that lookup method.
    If I try to import the same record without making any changes it doesn't error. However, if I make any changes it errors. why is that?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
CA Neeraj Kumar Profile Picture

CA Neeraj Kumar 783

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 715 Super User 2025 Season 2

#3
Sohaib Cheema Profile Picture

Sohaib Cheema 519 User Group Leader

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans