web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Duplicated Lines displ...
Finance | Project Operations, Human Resources, ...
Answered

Duplicated Lines displayed on form

(0) ShareShare
ReportReport
Posted on by Johnny 6,478

Hi,

We have a bug / strange behavior, that I could not understand.

So we have added one str field to PSAActivityEstimates table.

We just assign the value to it with "On_Inserting" event. Nothing special.

PSAActivityEstimates table is part of the ProjWBSEstimatesView View, which is one of the datasources of ProjForecastListPage form. Main datasource on the form is ProjForecastUnion View 

So, the problem is that, if we have 2 or more records in PSAActivityEstimates for the same ActivityID and if the new Value isn't empty, then we are getting those lines several times.

This new field is only in table, view, and Form's grid.

It is not used anywhere in the query, in any range, group by or similar.

I can see this problem from SQL studio too.

For example, if I open PSAActivityEstimates table with browser and I assign the values manually to new field and run the Select statement like below it displays 4 lines instead of 2.

select * from ProjForecastUnion
join ProjWBSEstimatesView
on ProjForecastUnion.ACTIVITYNUMBER = ProjWBSEstimatesView.ACTIVITYNUMBER
where ProjForecastUnion.ACTIVITYNUMBER = 'xxx'

What can be going wrong?

Thanks.

I have the same question (0)
  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,162 Moderator on at

    I'm not 100% sure if I understand the problem. In general if you join tables in a statement you might get "duplicate" information on some of the results. For example if you select SalesTable and join it with SalesLine, you will get the SalesTable information once for every line of that order.

    I have some questions, could you please take time to answer all of them? Thanks! 

    1) You said you added a field to PSAActivityEstimates. Is this new field the "ActivityId" or "ActivityNumber" that you talk about later?

    2) In your text you talk about ActivityId, but in your select statement I see ActivityNumber. Are they the same thing?

    3) How many records with ActivityNumber = 'xxx" you see if you select only from ProjWBSEstimatesView?

    4) How many records  ActivityNumber = 'xxx" you see if you select only from ProjForecastUnion?

  • Johnny Profile Picture
    Johnny 6,478 on at

    Hi Nikolaos,

    1) No, it is a different str field.

    2) I should have had ActivityNumber in my text, so yes, they are same in this context.

    3) I see only 2 records if I select only ProjWBSEstimatesView with ActivityNumber = 'xxx". If the values of new field is filled.

    If I have new field values empty, then I see only 1 line in ProjWBSEstimatesView with ActivityNumber = 'xxx"

    4) I see 2 records if I select only ProjForecastUnion with ActivityNumber = 'xxx". It does not matter if I have values in new field or not.

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,162 Moderator on at

    So, basically you have two records in table A, having ActivityNumber xxx, and two records in table B, having ActivityNumber xxx.

    Then you select table A, where ActivityNumber = xxx, and join to table B using this same field as the join relation.

    This will result in 4 results and it's working exactly as expected.

    It will return two records from Table A, and for each record in table A it will return both records in Table B. Total 4 records in the resultset.

    Result nr 1: first record of table A, joined to first record of table B

    Result nr 2: first record of table A, joined to second record of table B

    Result nr 3: second record of table A, joined to first record of table B

    Result nr 4: second record of table A, joined to second record of table B

  • Johnny Profile Picture
    Johnny 6,478 on at

    Yes, I understand that and this SQL select statement was just to check it.

    The problem is that only modification we have is that we have added new field to table, view and form grid and we assign value to this new field from On_inserting. So nothing special and just basic modification.

    The question is why this new field is triggering the issue?!

    Why just emptying the new field value makes ProjWBSEstimatesView with ActivityNumber = 'xxx" to be only 1 record?!

    I mean if I remove this field, or not even remove, if I do not assign anything to it and it stays empty then lines on form are not duplicated.

    This is what I do not understand.

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,162 Moderator on at

    I don't believe that simply adding a new field that is not used in the statement would cause that issue. There must be something else. For example, do you happen to have Group By that uses your new field, in the view definition?

    So, if the new field is empty (or has same value in all results) the group by would group and shrink your resultset.

  • Johnny Profile Picture
    Johnny 6,478 on at

    That was also my first thought, but can't see this new field in any group by.

    Also as I see the Group By of View extension can't be modified. Is there any other way that could modify the group by of standard View?

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,162 Moderator on at

    If you see this problem in SQL Server Management Studio as well, it must be explained by something in the view definition. Please check all details of this view one more time. In both AOT and SSMS.

  • Johnny Profile Picture
    Johnny 6,478 on at

    Hi Nikolaos,

    it seems like every field that you add to View, it is automatically added to Group By in SQL. (You can't manually add Group By fields on View extension from VS).

    I see it, when I click "Design" on the View in SQL management Studio.

    What would you suggest? How can I have this field on Form without having it to Group By of View?

    As this Form is a ListPage, I tried to add the datasource to The list page query, but the result is same there as well.

    Should I do it with code? If yes, where exactly should I look to?

  • Verified answer
    nmaenpaa Profile Picture
    nmaenpaa 101,162 Moderator on at

    If adding a new field to existing view via extension would automatically group by this new field, it would definetely break a lot of stuff. So if it really happens to you, it sounds like a bug.

    I suggest you try it one more time with some simple use case and double check it. And if you are sure, you can report it to Microsoft support.

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Martin Dráb Profile Picture

Martin Dráb 451 Most Valuable Professional

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 428 Super User 2025 Season 2

#3
BillurSamdancioglu Profile Picture

BillurSamdancioglu 239 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans