Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX forum
Answered

Can we join View with temp table?

Posted on by 922

Hi everyone,

I have a lookup form where I want to add range to the the querybuilddatasource. 

this.queryBuildDataSource().addRange(fieldNum(ABCHRMEmployeeLookup,PositionId)).
value(HcmPosition::find(hcmPositionHierarchy.Position).PositionId);

this line is working fine with few Records but with 996 records it give this error.

4645.Capture11.JPG

Then I decided to keep the 996 records in the tempDB table and try to create join with my data source

which is a view of the query.

I wrote this code to join with my current datasource.

0841.123.JPG

Then I got this Error

321.JPG

Please help me how I can solve the first and second problem.

P1: How to set multiple ranges without buffer overflow?

P2: How to Join Temp table with this data source? (datasource is AOT View of AOT query)

For Problem 2 I tried this suggestion. But I can't be able to use  'this. query().'

community.dynamics.com/.../the-data-source-is-not-embedded-within-a-parent-data-source

  • Suggested answer
    Khurshid Wali Profile Picture
    Khurshid Wali 922 on at
    RE: Can we join View with temp table?

    I can't do that as the query is working on multiple lookups. I think I should use a regular table and add a field to keep track of the current user. delete table element for new lookup for each user.

    It will be slow but it will work as I am dealing with a few thousand rows.

    Thank you ievgen Miroshnikov..

  • Suggested answer
    Khurshid Wali Profile Picture
    Khurshid Wali 922 on at
    RE: Can we join View with temp table?

    I can't do that as query is working on multiple lookup. I think I should use regular table and add a field to keep track of current user. delete table element for new lookup for each user.

    It will be slow but it will work as i am dealing with few thousand rows.

    Thank you ievgen Miroshnikov..

  • Verified answer
    Mea_ Profile Picture
    Mea_ 60,274 on at
    RE: Can we join View with temp table?

    You cannot set temp table that is view data source, it;s impossible, you have to forget about view and work directly with query.

  • Khurshid Wali Profile Picture
    Khurshid Wali 922 on at
    RE: Can we join View with temp table?

    Yes.

    Please help me understands why it is not working in my case.

    Normally I have used query directly on the form and I can do like this.query(), ... etc

    As now "data source is an AOT View which contains an AOT query." Now I can't use 'this.query()'

    Or any good reading material in this regard.

  • Mea_ Profile Picture
    Mea_ 60,274 on at
    RE: Can we join View with temp table?

    Do you want to explain what you are  doing ?

  • Khurshid Wali Profile Picture
    Khurshid Wali 922 on at
    RE: Can we join View with temp table?

    Hello @ievgen Miroshnikov,

    this.setREcord is not working in my case. because my datasource are associated with View which contain an AOT query.

    e.g

    this.queryBuildDataSource() is working

    but this.query() is not working so as this.setRecord()

    please help?

  • Khurshid Wali Profile Picture
    Khurshid Wali 922 on at
    RE: Can we join View with temp table?

    Using SetRecord is not showing any Result

    qbds=this.querybuilddatasource().addDataSource(tableNum(AbcPositionHierarchy));
    qbds.joinMode(JoinMode::ExistsJoin);
    qbds.addLink(fieldNum(AbcHRMEmployeeLookup,HcmWorker),fieldNum(AbcPositionHierarchy,workerRecId),this.queryBuildDataSource().name());
    this.setRecord(abcPositionHierarchy);

    what is wrong with this code? especially this.setRecord line

  • Mea_ Profile Picture
    Mea_ 60,274 on at
    RE: Can we join View with temp table?

    yes, it's just an example, there should be your table, please look at blog post provided by  faiz7049

  • faiz7049 Profile Picture
    faiz7049 1,641 on at
    RE: Can we join View with temp table?

    Hi Khurshid,

    Please go through below link.

    mafsarkhan.blogspot.com/.../example-for-using-tempdb-table-in-query.html

  • Khurshid Wali Profile Picture
    Khurshid Wali 922 on at
    RE: Can we join View with temp table?

    but there is no tmpTableClass in my AX version?

    AX 2012 R3 6.3.3000

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,986 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,588 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans