AX – D365FO – Join multiple tables on a Insert_recordset statement

The following X++ code example shows a join of three tables on an insert_recordset statement that has a sub-select.

Also, a while select statement with a similar join is shown.

A variable is used to supply the inserted value for one column. The str variable must be declared with a length that is less than or equal to the maximum length of the corresponding database field.

static void InsertJoin42Job(Args _args)
    {
        GmTabDepartment tabDept2;
        GmTabEmployee tabEmpl3;
        GmTabProject tabProj4;
        GmTabEmployeeProject tabEmplProj5;
        str 64 sDescriptionVariable = "From variable.";
        ;
        DELETE_FROM tabEmplProj5;
    
        INSERT_RECORDSET tabEmplProj5
            (
            Description
            , EmployeeRecId
            , ProjectRecId
            )
        Select
            sDescriptionVariable
            , RecId
        from
            tabEmpl3
            join
                tabDept2
                where tabEmpl3 .DepartmentGuid == tabDept2 .DepartmentGuid
            join RecId
                from tabProj4
                where tabDept2 .DepartmentGuid == tabProj4 .DepartmentGuid
        ;
    
        info(int642str(tabEmplProj5 .rowCount())
            + " ==Number of rows inserted.");
    
        WHILE SELECT *
            from
                tabEmplProj5
                join tabEmpl3
                    where tabEmplProj5 .EmployeeRecId == tabEmpl3 .RecId
                join tabProj4
                    where tabEmplProj5 .ProjectRecId == tabProj4 .RecId
        {
            info(
                tabEmpl3 .EmployeeName
                + "  --works on--  "
                + tabProj4 .ProjectName
                + " (" + tabEmplProj5 .Description + ")."
                );
        }
    /*****************  Actual Infolog output
    Message (01:05:41 pm)
    4 ==Number of rows inserted.
    Alice  --works on--  Project ZZZ (From variable.).
    Alice  --works on--  Project YY (From variable.).
    Beth  --works on--  Project ZZZ (From variable.).
    Beth  --works on--  Project YY (From variable.).
    *****************/
    }