Dynamics Community Forum Thread Details

How to split & insert transactions

Hi Experts,

I have a requirement that in my Table I have some data that needs to be splitted by date & hours. Can anyone help me by providing the best way to achieve this please.

Consider the table - MyTable has fields From date, To date, From time, To Time & hours.

Consider the record - From Date To Date From Time To Time Hours

23/05/2021 25/05/2021 10:00 am 12:00 am 38

I need to split above record into two records 14 hours for 23/05/2021 & 24 hours for 24/05/2021. There are around 10,000 records to be splitted in this way, what will be the best way to get it done ? AOT job, Class or SQL. Can any one help with the best logic also please.

Thanks in advance.

I believe you need to create your own class. And 2 key methods would be

1. Validate if a record needs to be split according to given From Time. Take your data as example, you need to compare the Hours on the record with rest time of a day according to the From time. In our case the rest time of a day would be 24- 10= 14. Since 38 > 14, this record needs to be split. The return outcome would be True. Else return false.

2. if #1 method returns true, then split current record to 2 ones. Parameter would be from date and from time. For the generated 1st record, the new From date would be the parameter value which is May 23. The hours should be rest time of a day based on from time, which would be 14 hours. For the generated 2nd record, The From date would be parameter value +1 which would be May 24. The from time should be 12:00am. The Hours should be rest hours which would be 38- 14 = 24 hours.

Once above 2 methods are ready, you need to continue to validate if the generated second record needs to be split or not. If yes, then go to the second method to continue split this record. So as you can this is a recursive funtion.

Quick Links

Leaderboard