View synchronization failing

(0) Share

Report

Posted on by Rudi Hansen

4,000

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

All responses (26)

Answers (0)

Rudi Hansen 4,000 on at

Like (0)

Report

RE: View synchronization failing

SELECT FIRSTFAST AccountRelation, Amount, ItemRelation, UnitId, Agreement FROM PriceDiscTable WHERE ((ItemCode = 0)) AND ((AccountCode = 1))
Sohaib Cheema 46,610 User Group Leader on at

Like (0)

Report

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 46,610 User Group Leader on at

Like (0)

Report

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 12,644 on at

Like (0)

Report

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 4,000 on at

Like (0)

Report

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 4,000 on at

Like (0)

Report
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 4,000 on at

Like (0)

Report

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 46,610 User Group Leader on at

Like (0)

Report

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 4,000 on at

Like (0)

Report

RE: View synchronization failing

Well so how do i create the relationship?
Sohaib Cheema 46,610 User Group Leader on at

Like (0)

Report

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)