SBX - Search With Button

SBX - Forum Post Title

How to select the newest data in DataEntity?

Dynamics 365 for Finance and Operations Forum

JustZM asked a question on 13 Jun 2019 5:30 PM
My Badges

Question Status

Suggested Answer

Hi Everyone

          I created a DataEntity Extension(A), I also add a Table and a DataEntity to its DataSource. Now, everything is good, but some are repetitive because they have different Date.

          How can I add code to a DataEntity Extension to select the newest record if the records are repetitive?

Best Regards,

Jack

Reply

What is repetitive ? Which entity did you extend ? Is that a valid time state table ?

Reply
Satish Panwar responded on 13 Jun 2019 9:51 PM
Suggested Answer

Hi Jack,

You need to verify the relations for the table you added to make sure records are not repeating. Find what's causing record to duplicate and fix the source (probably through Joins between tables).

Reply

Hi Jack,

You had duplicated your question. For your answer have a look at your other thread: community.dynamics.com/.../357068

The duplicate might be an accident. Please pay attention in future to prevent creating duplicates.

Reply
Pete Alberts responded on 14 Jun 2019 4:15 AM
My Badges
Suggested Answer

I assume the datasource is from a VTS source. The "duplicates" you mentioned are because of a one-to-many relation that occurs because of the VTS fact.

There is an option on the data entity datasource node named "Apply Date Filter". Set it to yes and then pull the ValidFrom and ValidTo fields into the data entity field list (you will get a compile time error if you don't).

Then when you query the data entity you should get only the current valid record from the VTS table. And no more "duplicates".

A good example is CustCustomerV3Entity: click Data Sources -> CustTable -> Data Sources -> DirPartyBaseEntity -> Data Sources -> LogisticsPostalAddressBaseEntity. Then notice the property "Apply Date Filter". Also DirPartyBaseEntity: DirPartyTable -> DirPersonForName -> DirPersonName

Kind regards

Reply
JustZM responded on 14 Jun 2019 12:37 PM
My Badges

Hi All Thanks for the answers.

         The duplicates data is caused from LogisticsPostalAddress because in some case LogisticsLocation.RecId has multiple LogisticsPostalAddress.Location. User creates new address, but system does not delete old address. I believe the relation between all tables should be correct. So I need to some code to do the rest. But this is my first time to code for DataEntity. I hope I can have some reference to learn it. I think I should use "insertEntityDataSource" method. But how can I use normal SQL language such as "Select, decrease, Increase, firstonly" on insertEntityDataSource?

Best Regards,

JustZM

Reply

Hi JustZM,

If you want to have just one record, you can set the relation to 1:1 on the datasource.

Reply
Satish Panwar responded on 13 Jun 2019 9:51 PM
Suggested Answer

Hi Jack,

You need to verify the relations for the table you added to make sure records are not repeating. Find what's causing record to duplicate and fix the source (probably through Joins between tables).

Reply
Pete Alberts responded on 14 Jun 2019 4:15 AM
My Badges
Suggested Answer

I assume the datasource is from a VTS source. The "duplicates" you mentioned are because of a one-to-many relation that occurs because of the VTS fact.

There is an option on the data entity datasource node named "Apply Date Filter". Set it to yes and then pull the ValidFrom and ValidTo fields into the data entity field list (you will get a compile time error if you don't).

Then when you query the data entity you should get only the current valid record from the VTS table. And no more "duplicates".

A good example is CustCustomerV3Entity: click Data Sources -> CustTable -> Data Sources -> DirPartyBaseEntity -> Data Sources -> LogisticsPostalAddressBaseEntity. Then notice the property "Apply Date Filter". Also DirPartyBaseEntity: DirPartyTable -> DirPersonForName -> DirPersonName

Kind regards

Reply

SBX - Two Col Forum

SBX - Migrated JS