Skip to main content

Notifications

Microsoft Dynamics AX (Archived)

DIXF - Staging table sorting

Posted on by 1,470

Hi to all.

I've imported a file that contains "hierachy" data. The lines in the file are in the correct order:

Line 1 (root):   ParentPartyNumber = empty,    PartyNumber, = A,    ProductNumber = 123

Line 2:             ParentPartyNumber = A,            PartyNumber, = B,    ProductNumber = 123

Line 3:             ParentPartyNumber = B,            PartyNumber, = C,    ProductNumber = 123

Line 4:             ParentPartyNumber = B,            PartyNumber, = D,    ProductNumber = 123

...

The index (IndexOfMyTableIdx) of the table is composed with the following fields: ExceutionId, DefinitionGroup, ProductNumber, PartyNumber.

The "View staging data" shows me the data ordered by the index IndexOfMyTableIdx. 

Line 1:             ParentPartyNumber = A,            PartyNumber, = B,    ProductNumber = 123

Line 2:             ParentPartyNumber = B,            PartyNumber, = C,    ProductNumber = 123

Line 3:             ParentPartyNumber = B,            PartyNumber, = D,    ProductNumber = 123

Line 4 (root):   ParentPartyNumber = empty,    PartyNumber, = A,    ProductNumber = 123

The PrimaryIndex of the DMF table is SurrogateKey; the ClusterIndex is SurrogateKey, the ReplacementKey is empty.

When I copy the staging table to the target table, the DIXF uses the sorting by IndexOfMyTableIdx.

I need to import the records ordered by RecId.

(The DMF table is new, not a standard entity)

Any ideas?

Thanks

*This post is locked for comments

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,134 Super User 2024 Season 2 on at
    RE: DIXF - Staging table sorting

    Hi Martina,

    The cluster index is only used in what order the records will be physically stored in the SQL server. This has nothing to do with the result set which the AOS retrieved from the SQL server.

    Did you try to have the index IndexOfMyTableIdx as Primary index and add the field RecordId as part of this index? Then use the next order of fields: ExecutionId, DefinitionGroup, RecId, ProductNumber, PartyNumber. Or probably you can try to add the field ParentPartyNumber instead of the RecId.

  • Martina Bergamo Profile Picture
    Martina Bergamo 1,470 on at
    RE: DIXF - Staging table sorting

    Hi André.

    Thank you for the answer.

    The index of the base table is: ProductNumber, PartyNumber. The staging table has the same fields plus ExceutionId, DefinitionGroup (as you explained).

    The PrimaryIndex of the source table is the SurrogateKey, and the ReplacementKey is the index ( = ProductNumber, PartyNumber).

    For test, I've set the SurrogetKey as ClusterIndex of the DMF table. But the sorting has not changed.

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,134 Super User 2024 Season 2 on at
    RE: DIXF - Staging table sorting

    Hi Martina,

    How did you create the new DMF entity? Usually when you use the wizard the DMF table is not created with the SurragateKey.

    The index of the base table will be used and the fields ExceutionId, DefinitionGroup will be added for the staging table. If the source table has a record-ID based primary key, the replacement index should be used as source.

    When the table property has the correct primary index, you would be able to change the primary key to include e.g. the record ID for sorting. When you make changes, test the entity carefully.

  • Martina Bergamo Profile Picture
    Martina Bergamo 1,470 on at
    RE: DIXF - Staging table sorting

    I cannot change the primary index of the table. The primary index is correct. A cannot change an index only for the sorting.

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!

Community AMA December 12th

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

New! Quick response templatesâš¡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 229,928 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans