Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Suggested answer

How do you populate a temp table using dexterity?

Posted on by 1,480

Ultimatel, I want to populate a lookup window from a table with the dealerid.  The source table is a transaction table and has thousands of records.

The field dealerid is not unique in this table. 

If I were to do this in sql, I would write the select statement 

select distinct dealerid from sourcetable.

However, there is no equivalent in Dexterity.

Essentially I want to fill the lookup window the unique dealerid in my source table.

regards

Categories:
  • Suggested answer
    David Musgrave MVP GPUG All Star Legend Moderator Profile Picture
    David Musgrave MVP ... 13,915 Most Valuable Professional on at
    RE: How do you populate a temp table using dexterity?

    Hi Mike

    I would create a Dexterity Table definition that has the fields and indexes that you want for the final data.

    Then in the Form Pre script, you can populate the table. using a SQL Select into statement using SQL_Execute.  Use the Table_GetOSName() command to get the physical SQL path for the temp table.

    This method works really well and does not need a view.

    Regards

    David

  • Suggested answer
    Almas Mahfooz Profile Picture
    Almas Mahfooz 11,003 User Group Leader on at
    RE: How do you populate a temp table using dexterity?

    create view for your sql query.

    like below

    create view
    DealerLookup
    as
    select distinct CUSTNMBR as dealerid,ROW_NUMBER() OVER(ORDER BY CUSTNMBR ASC) AS DEX_ROW_ID
    from SOP10100
    GO

    P.S : change your table name.

    then in dexterity create a table like below.

    lookup-table.png

    now attached your table to lookup scroll window and on window pre event just write fill window, it will fill records from the view.

    almas

    your dexterity developer ;-)

  • Suggested answer
    RE: How do you populate a temp table using dexterity?

    I know the Dexterity manuals have information on lookups and lookup windows, though I imagine you've been through these already?

    I know there are members of the Community that do much work with Dexterity that may have more information to suggest.....

    Thanks

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,240 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,149 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans