Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Answered

Is it ok to have index on RecId ?

(0) ShareShare
ReportReport
Posted on by 592

Hi guys,

I have a custom table that currently use as staging table and it is possible at some time most of the field (not all) are same, However I need to have index with eventually will be a primary index since I'm gonna need to create data entity of it. In my understanding, we should have primary index which obviously will need unique value.

May I know what is the solution of this? I am thinking of just use RecId as the field in my index, would it be fine ? or is there better approach ?

Thanks,

  • Verified answer
    André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,965 Super User 2025 Season 1 on at
    RE: Is it ok to have index on RecId ?

    HI Lars,

    Above, you mentioned that the data entity will be used for export only. In this scenario, you don't have to worry about the entity key. There are several tables in the standard application having multiple fields to determine the primary key. In that scenario it is not an issue to list e.g. 5 fields as the entity key.

  • Voltes Profile Picture
    Voltes 592 on at
    RE: Is it ok to have index on RecId ?

    Hi,

    Yes, I'd just remembered about this natural key thing. There is "Natural key" error in Data Entity Wizard if we do not have Primary key with our own field, and it also doesn't accept if RecId is part of the index when create Data entity.

    So is there a better approach how to have a primary index when most of the fields may have same value ?

    Thanks,

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,965 Super User 2025 Season 1 on at
    RE: Is it ok to have index on RecId ?

    Hi Lars,

    Let's take a standard table as example to illustrate the issue for using record IDs as entity key for a data entity.

    The customer groups table has a primary index on the field Customer group. It will not allow for duplicates. When you create a new record, it will generate a unique record ID as well. The record ID value itself does not have any meaning. It can be used for references in other tables.

    When you create the same customer group in another environment, it will have another record ID.

    Now when you import data using a data entity, it will try to find existing records based on the entity key. If this is the customer group, it can find a match and would update a record. If there is no existing record, it will create a new one. When the entity key is the record ID, it will not find the same record (as the record ID is different) and will try to create a second customer group with the same customer group ID.

  • Voltes Profile Picture
    Voltes 592 on at
    RE: Is it ok to have index on RecId ?

    Yes, I think I won't need to publish data. But out of curiosity does it mean by using RecId will make it unable to publish or is there anything else ?

    May I know what about the natural key ? I'm sorry I just do not play around with tables key until recently, so might need further explanation of what I should consider.

    Thanks,

  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 230,817 Most Valuable Professional on at
    RE: Is it ok to have index on RecId ?

    If it's just for export (you won't be able to pub;ish data from Excel), then having RecId as an entity key isn't a big deal.

    Nevertheless I would still think about a natural key of the table.

  • Voltes Profile Picture
    Voltes 592 on at
    RE: Is it ok to have index on RecId ?

    Hi Martin,

    It is for export only. I need to create that data entity because just found out it is mandatory so that I can have the feature "Open In Excel" when open from SysTableBrowser.

  • Martin Dráb Profile Picture
    Martin Dráb 230,817 Most Valuable Professional on at
    RE: Is it ok to have index on RecId ?

    Is your intention to import data through this entity, or will it be a readonly entity used for export only?

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

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,965 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,817 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans