Microsoft Dynamics AX (Archived)

DIXF - Staging table sorting

(0) Share

Report

Posted on by Martina Bergamo

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

All responses (4)

Answers (0)

André Arnaud de Cal... 291,391 Super User 2024 Season 2 on at

Like (0)

Report

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 1,470 on at

Like (0)

Report

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 Cal... 291,391 Super User 2024 Season 2 on at

Like (0)

Report

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 1,470 on at

Like (0)

Report

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.