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
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.
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.
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.
I cannot change the primary index of the table. The primary index is correct. A cannot change an index only for the sorting.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 291,134 Super User 2024 Season 2
Martin Dráb 229,928 Most Valuable Professional
nmaenpaa 101,156