You’re offline. This is a read only version of the page.
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
SELECT FIRSTFAST AccountRelation, Amount, ItemRelation, UnitId, Agreement FROM PriceDiscTable WHERE ((ItemCode = 0)) AND ((AccountCode = 1))
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
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.
Yes. There probably where some bad references.
When this happens to me, I normally just recreate the view with a new name.
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???????
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.
Share
Report
All responses (
Answers (
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?
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.
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
Well so how do i create the relationship?
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)
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,228
Super User 2024 Season 2
#2
Martin Dráb
230,056
Most Valuable Professional
#3
nmaenpaa
101,156