Joining 1:N related table in form without row duplication using Computed fields in views

Case

A customer wanted to display and filter Customer reference and Customer requisition fields from sales order on Project invoice journal screen. As there is no direct link to do this and the indirect link is a 1:N or even N:N in certain cases this gets problematic when displaying the journals. Resulting in duplicate journal entries in the journal overview. As the customer only have one sales order pr. project invoice  and we are selecting the first only (Top 1) this will not be an issue.

As joining and display methods do not provide filter and sort options we need to generate the data on the server using computed fields.

Explanation

As display methods do not provide filter and sort options and joining 1:N table relations will duplicate the journals in the list, we need to generate the data on server using computed fields.

To do this we need a View with Proposal ID, join the view to the ProjInvoiceJour table on Proposal ID and add the view as a datasource to the form ProjProposalJournal.

1. Create the view

1. Crate view (in example I called it ProjInvoiceJourSalesView_Postfix)
2. Add table ProjInvoiceJour as datasource
3. Drag ProposalId from datasource fields to View fields
4. Add three string computed fields to the fields node of your view
   
   
5. Save your view

2. Add computed methods to use in computed fields

Get Proposal ID from main datasource

public static server str getProposalId()
    {
        str projProposalIdLocal;

        projProposalIdLocal = SysComputedColumn::returnField(   viewstr(ProjInvoiceJourSalesView_Postfix)
                                                            ,   identifierStr(ProjInvoiceJour)
                                                            ,   fieldstr(ProjInvoiceJour,ProposalID));

        return projProposalIdLocal;
    }

Get Sales ID

public static server Str getSalesId()
    {
        str ret;

        ret = strFmt(@"select top 1 ProjProposalItem.SalesId
                        from ProjProposalItem
                        where ProjProposalItem.ProposalId = %1
                        and ProjProposalItem.SalesId <> ''
                        order by ProjProposalItem.SALESID asc"
                        , ProjInvoiceJourSalesView_Postfix::getProposalId());
        
        return ret;

    }

Get customer reference

public static server str getCustomerRef()
    {
        str ret;
        
        ret = strFmt(@"select Top 1 SalesTable.CUSTOMERREF
                            from ProjProposalItem
                            join SalesTable
                                on SalesTable.SalesID = ProjProposalItem.SALESID
                                and ProjProposalItem.DATAAREAID	= SALESTABLE.DATAAREAID
                            where   ProjProposalItem.ProposalId	= %1
                            and     ProjProposalItem.SalesId	<> ''
                            order by SalesTable.SALESID asc"
                            ,   ProjInvoiceJourSalesView_Postfix::getProposalId()
                );
        
        return ret;
    }

Get Customer requisition

public static server str getPrchOrderFormNum()
    {
        str ret;

        ret = strFmt(@"select Top 1 SalesTable.PURCHORDERFORMNUM
                            from ProjProposalItem
                            join SalesTable
                                on SalesTable.SalesID = ProjProposalItem.SALESID
                                and ProjProposalItem.DATAAREAID	= SALESTABLE.DATAAREAID
                            where   ProjProposalItem.ProposalId	= %1
                            and     ProjProposalItem.SalesId	<> ''
                            order by SalesTable.SALESID asc"
                            ,   ProjInvoiceJourSalesView_PostFix::getProposalId()
                    );

        return ret;
    }

Experiences and notations worth mentioning;

• Note that there is no data area Id range and that the Proposal ID is not enclosed in single quotes, this I found that did not work when implemented. The computed fields are strange like this. Did compile and synchronize but did not give any data.
  
  
• The syntax in the strings are MSSQL and not X++.
  
  
• Extra important, if you get lots of synchronization issues when compiling, your MSSQL scripts in your computed methods are probably not compiling. You can try simple queries to root out your culprit eg. ret = "select 123" will return the value 123 in every instance.

4. Reference methods in computed fields in view

1. Select your Extended data type, in this example Sales ID
2. For each computed field in your view select corresponding method in the parameter View Method, here getSalesID is selected

5. Link view to table

1. Create or find the extension for your ProjInvoiceJour table
2. Add relation to your view on the field Proposal ID

6. Add the view and fields to the form

1. Create or locate extension of Form ProjInvoiceJournalV2
2. Add your view as a datasource outer joining the ProjInvoiceJour datasource
3. Drag your computed fields from the view

After build and synchronize you are now able to sort and filter on Sales Id, Customer requisition and Customer reference in the Project invoice journal form.

And as an added bonus the fields are also available in the list pane on the left.

You can also view your view as a table by referencing it in the URL addressfield of your browser

Add this your URL

?cmp=ComapnyName&mi=systablebrowser&tablename=ProjInvoiceJourSalesView_Postfix

Result:

[your D365FO main URL]/?cmp=ComapnyName&mi=systablebrowser&tablename=ProjInvoiceJourSalesView_Postfix