Skip to main content

Notifications

Microsoft Dynamics AX (Archived)

View synchronization failing

Posted on by 3,987

I have made a new Query, Under Data Sources i put the PriceDiscTable, and under PriceDiscTable´s Data Sources i put InventTable.

Now i can add this Query to a view and add some of the fields from PriceDiscTable and InventTable, all works fine.

So not i add the table SalesLine, under PriceDiscTable´s Data Sources, and add lets say the TaxItemGroup field to the view.

Now when i run the View i get this error:

Cannot select a record in GJSOPriceDiscTable (GJSOPriceDiscTable).
The SQL database has issued an error.

SQL error description: [Microsoft][SQL Native Client][SQL Server]Invalid column name 'TAXITEMGROUP'.

SQL statement: SELECT A.ITEMRELATION,A.ACCOUNTRELATION,A.ITEMNAME,A.TXT,A.UNITID,A.AMOUNT,A.TAXITEMGROUP,A.RECID FROM GJSOPRICEDISCTABLE A WHERE ((((DATAAREAID=?) AND (DATAAREAID#2=?)) AND (DATAAREAID#3=?)) AND (DATAAREAID#4=?)) ORDER BY A.DATAAREAID,A.RECID OPTION(FAST 1)

*This post is locked for comments

  • Rudi Hansen Profile Picture
    Rudi Hansen 3,987 on at
    RE: View synchronization failing

    SELECT FIRSTFAST AccountRelation, Amount, ItemRelation, UnitId, Agreement FROM PriceDiscTable WHERE ((ItemCode = 0)) AND ((AccountCode = 1))

  • Sohaib Cheema Profile Picture
    Sohaib Cheema 46,610 User Group Leader on at
    RE: View synchronization failing

    I would be interested to see query of DataSource PriceDiscTable as of now, while view is working, can you please get it using query.tostring() for PriceDiscTable

  • Sohaib Cheema Profile Picture
    Sohaib Cheema 46,610 User Group Leader on at
    RE: View synchronization failing

    Hi Rudi,

    I was busy because of weekend.

    Yes the behavior is wired as you diagnosed it. so we must say its kind of cache issue, and its difficult to reproduce such issues.

    by the way, as I imported the XPO, it showed erroneous behavior i.e. view was not being opened and was showing synchronization error for where clause on event viewer.

    Matter of pleasure you fixed it.

  • Fredrik Sætre Profile Picture
    Fredrik Sætre 12,644 on at
    RE: View synchronization failing

    Yes. There probably where some bad references.

    When this happens to me, I normally just recreate the view with a new name.

  • Rudi Hansen Profile Picture
    Rudi Hansen 3,987 on at
    RE: View synchronization failing

    Ok and this is totally weird, I just tried to remove all the fields from salesLine on the query save query run view. (View worked)

    Then i added the field PriceUnit to salesLine on the query, save query run view. (View still worked)

    Then i added the field TaxItemGroup to salesLine on the query, save query run view. (View still worked)

    So now everything is working fine???????

  • Rudi Hansen Profile Picture
    Rudi Hansen 3,987 on at
    RE: View synchronization failing

    Had a little time now to create a job to test the Query, and the job returns data from the Query in all the tables, so the Query should work.

    static void RSH_TestQuery2(Args _args)
    {
        PriceDiscTable  priceDiscTable;
        InventTable     inventTable;
        InventTxt       inventTxt;
        SalesLine       salesLine;
        QueryRun        queryRun;
        ;
        // Executing our query.
        queryRun = new QueryRun(queryStr(GJSOPriceDiscTable));//add any name of query here
    
        info(queryRun.query().dataSourceNo(1).toString());
        info(queryRun.query().dataSourceNo(2).toString());
        info(queryRun.query().dataSourceNo(3).toString());
        info(queryRun.query().dataSourceNo(4).toString());
    
        // Looping through query results.
        while (queryRun.next())
        {
            // Assinging query results to table buffer.
            priceDiscTable  = queryRun.get(tableName2Id('PriceDiscTable'));
            inventTable     = queryRun.get(tableName2Id('InventTable'));
            inventTxt       = queryRun.get(tableName2Id('InventTxt'));
            salesLine       = queryRun.get(tableName2Id('SalesLine'));
    
            // Showing results.
            info( strFmt ('%1 - %2 - %3 - %4' , priceDiscTable.AccountRelation,
                                                priceDiscTable.Amount,
                                                priceDiscTable.ItemRelation,
                                                priceDiscTable.UnitId));
            info( strFmt ('%1 - %2 - %3 - %4' , inventTable.itemName));
            info( strFmt ('%1 - %2 - %3 - %4' , inventTxt.Txt));
            info( strFmt ('%1 - %2 - %3 - %4' , salesLine.TaxItemGroup, salesLine.PriceUnit));
        }
    }


    So why is the view not working?

  • Rudi Hansen Profile Picture
    Rudi Hansen 3,987 on at
    RE: View synchronization failing

    Yes but should that not have been solved by putting the relationships into the query.

    The funny thing is that its not all fields on SalesLine that does not work.

    Right now i am on a plane to London, so i can't see witch fields work, but try it out in the copy of the query i sent you and see if it works.

  • Sohaib Cheema Profile Picture
    Sohaib Cheema 46,610 User Group Leader on at
    RE: View synchronization failing

    On table level Microsoft has not created relationships because PriceDiscTable is used for multi-purpose. The field named as relationship defines which kind of data its is going  too save in PriceDiscTable

    in case of sales or purchase, maps are being used to identify records from PriceDiscTable

    The path for this map is

    \Data Dictionary\Maps\SalesPurchLine\Methods\setPriceAgreement

    Also note that a class named as PriceDisc comes into action here. At runtime data is being collected from PriceDiscTable , as its not directly linked with salesLine.

    you should change the way/approach to resolve issue, depending upon what  you are trying to do, in terms of business needs

  • Rudi Hansen Profile Picture
    Rudi Hansen 3,987 on at
    RE: View synchronization failing

    Well so how do i create the relationship?

  • Sohaib Cheema Profile Picture
    Sohaib Cheema 46,610 User Group Leader on at
    RE: View synchronization failing

    it is given on Query level. But there is no relationship on Table level. Also no join is being created between these two tables (in query)

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templatesâš¡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,228 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,056 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans