Problems SYSQUERY findorCreateRange method

(0) Share

Report

Posted on by Namkamura

Hi All,

If you know this,would you please let me know the solution ASAP ?

In fact, I am facing to get 2 queries pattern using FindOrCreateRange of SysQuery class between standard field and custom field now.

1.Add custom field into SalesQuotationTable by extension field.

2.A custom field have many data currently.

3.Made range using SysQuery::findOrCreate method as below,

range = SysQuery::findOrCreateRange(_query.dataSourceName("SalesQuotationTable"), fieldNum(SalesQuotationTable, PrjBasicNo));
range.value(SysQuery::value(sPrjBasicNo));

4.However, As a result, I got an record on the Grid of SalesQuotationListPage form.

The Query is

SELECT
FIRSTFAST FORUPDATE *
FROM
SalesQuotationTable(SalesQuotationTable) ORDER BY SalesQuotationTable.QuotationId ASC

WHERE
((PrjBasicNo = N'12345')) AND
SalesQuotationTable.QuotationId=SalesQuotationTable.QuotationId OUTER JOIN FORUPDATE * FROM smmBusRelTable(smmBusRelTable) ON SalesQuotationTable.BusRelAccount = smmBusRelTable.BusRelAccount OUTER JOIN FORUPDATE * FROM CustTable(CustTable) ON SalesQuotationTable.CustAccount = CustTable.AccountNum OUTER JOIN FORUPDATE * FROM ProjTable(ProjTable_1) ON SalesQuotationTable.ProjIdRef = ProjTable.ProjId OUTER JOIN FORUPDATE * FROM HierarchyLinkTable(HierarchyLinkTable) ON SalesQuotationTable.TableId = HierarchyLinkTable.RefTableId AND
SalesQuotationTable.RecId = HierarchyLinkTable.RefRecId OUTER JOIN FORUPDATE * FROM Hierarchy(Hierarchy) ON HierarchyLinkTable.HierarchyId = Hierarchy.HierarchyId

Table as below,

However, In standard field, there is no problem as below,

ELECT
FIRSTFAST FORUPDATE *
FROM
SalesQuotationTable(SalesQuotationTable) ORDER BY SalesQuotationTable.QuotationId ASC

WHERE
((QuotationStatus = 1)) OUTER JOIN FORUPDATE * FROM smmBusRelTable(smmBusRelTable) ON SalesQuotationTable.BusRelAccount = smmBusRelTable.BusRelAccount OUTER JOIN FORUPDATE * FROM CustTable(CustTable) ON SalesQuotationTable.CustAccount = CustTable.AccountNum OUTER JOIN FORUPDATE * FROM ProjTable(ProjTable_1) ON SalesQuotationTable.ProjIdRef = ProjTable.ProjId OUTER JOIN FORUPDATE * FROM HierarchyLinkTable(HierarchyLinkTable) ON SalesQuotationTable.TableId = HierarchyLinkTable.RefTableId AND
SalesQuotationTable.RecId = HierarchyLinkTable.RefRecId OUTER JOIN FORUPDATE * FROM Hierarchy(Hierarchy) ON HierarchyLinkTable.HierarchyId = Hierarchy.HierarchyId

I think the difference is No.4 red line. Why is added this "AND
SalesQuotationTable.QuotationId=SalesQuotationTable.QuotationId"

I have no idea this issue so , Would you please let me know any hint or solution? Otherwise, Te Custom field can not getting multi data on the grid?

Thanks in advance.

I have the same question (0)

All responses (9)

Answers (1)

Namkamura 25 on at

Like (0)

Report

Below code is same result to get one record.

qbDataSource3 = _query.dataSourceTable(tableNum(SalesQuotationTable));
qFilter7 = _query.findQueryFilter(qbDataSource3, fieldStr(SalesQuotationTable, PrjBasicNo));

if(!qFilter7 || !qFilter7.value()) //To handle the situation like If languageId is added to Range set to empty and If LanguageId is not added to Range
{
range = qbDataSource3.addRange(fieldNum(SalesQuotationTable,PrjBasicNo));
range.value(sPrjBasicNo);

}

Please give to me some advice.!!

Was this reply helpful? Yes No
Namkamura 25 on at

Like (0)

Report

The only custom field has one record. but standard fields of Table is no issue. If they have multiple data, They are displayed on Grid of Listpage form.

Was this reply helpful? Yes No
Martin Dráb 237,874 Most Valuable Professional on at

Like (1)

Report
You said you have a problem with findorCreateRange() but you're testing a form instead. What if you forget the form for a moment and test the query itself? Otherwise you can't be sure whether the problem isn't in some logic of the form.

By the way, your code:

if (!qFilter7 || !qFilter7.value()) { range = qbDataSource3.addRange(fieldNum(SalesQuotationTable, PrjBasicNo)); range.value(sPrjBasicNo); }

means that if the filter exists but it doesn't have any value, you create a second range for the same field. It would make a better sense if you change the value from empty to sPrjBasicNo.

Also, notice how your code snippets lost formatting and mine didn't. I used Insert > Insert Code in the rich formatting view; please use it too next time.

Was this reply helpful? Yes No
Namkamura 25 on at

Like (0)

Report
Hi Martin,

Sorry for the late response.

I show my code to you and all just now.

class SalesQuotationTableLisPagetHandler { ///
/// ///
/// [PreHandlerFor(classStr(SalesQuotationListPageInteraction), methodStr(SalesQuotationListPageInteraction, initializeQuery))] public static void SalesQuotationListPageInteraction_Pre_initializeQuery(XppPrePostArgs args) { QueryBuildRange range,queryBuildRange; str sQuotationType; QueryRun qr; Common common; SalesQuotationTable sq,salesQuotationTable; QueryBuildDataSource queryBuildDataSource; SalesQuotationListPageInteraction ListPageInterAction; QueryBuildDataSource qbDataSource3; str sPrjBasicNo; QueryFilter qFilter7; ; ListPageInterAction = args.getThis(); SalesQuotationTable salesQuotationInfo = ListPageInterAction.listPage().listPageArgs().externalRecord(); //Info(ListPageInterAction.listPage().listPageArgs().menuItemName()); //Info(ListPageInterAction.listPage().listPageArgs().parameters()); //Info(strFmt("%1",salesQuotationInfo.QuotationId)); //Info(strFmt("%1",salesQuotationInfo.PrjBasicNo)); Query _query = args.getArg(identifierStr(_query)); //info(sPrjBasicNo); //str sprjbasicno = "sent"; //sprjbasicno = "12345"; sPrjBasicNo = any2Str(salesQuotationInfo.PrjBasicNo); //info(any2Str(salesQuotationInfo.PrjBasicNo)); range = SysQuery::findOrCreateRange(_query.dataSourceName("SalesQuotationTable"), fieldNum(SalesQuotationTable, PrjBasicNo)); range.value(SysQuery::value(sPrjBasicNo)); //queryBuildDataSource = _query.addDataSource(TableNum(SalesQuotationTable)); //queryBuildRange = queryBuildDataSource.addRange(FieldNum(SalesQuotationTable,PrjBasicNo)); //queryBuildRange.value(sPrjBasicNo); //qbDataSource3 = _query.dataSourceTable(tableNum(SalesQuotationTable)); //qFilter7 = _query.findQueryFilter(qbDataSource3, fieldStr(SalesQuotationTable, PrjBasicNo)); //if(!qFilter7 || !qFilter7.value()) //To handle the situation like If languageId is added to Range set to empty and If LanguageId is not added to Range //{ // range = qbDataSource3.addRange(fieldNum(SalesQuotationTable,PrjBasicNo)); // range.value(sPrjBasicNo); //} //range = SysQuery::findOrCreateRange(_query.dataSourceName("SalesQuotationTable"), fieldNum(SalesQuotationTable, QuotationStatus )); //range.value(SysQuery::value(sPrjBasicNo)); //////_queryにFilterを追加したので、そのまま、先に進める。 /// ///info(_query.toString()); qr = new QueryRun(_query); while (qr.next()) { salesQuotationTable = qr.GetNo(1); info(strFmt("%1",salesQuotationTable.QuotationId)); } } }

I explain current status to you as below,

1. As you seee my code, if sPrjBasicNo = any2Str(salesQuotationInfo.PrjBasicNo);, while debugging, I can see sPrjBasicNo has the value of "12345", but result is below grid

2. if sprjbasicno = "12345"; while debugging, of course, I can see sprjbasicno has the value of "12345" and result is good as below

Currently I have reviewed and tried my code continuously but i have no idea,,,,

Would you please let me know any hint or the solution ASAP?

Thank you in advance.

Was this reply helpful? Yes No
Martin Dráb 237,874 Most Valuable Professional on at

Like (1)

Report
Your code is confusing because most of it isn't used at all. You should have cleaned it up before sharing. Let me do it for you:

class SalesQuotationTableLisPagetHandler { [PreHandlerFor(classStr(SalesQuotationListPageInteraction), methodStr(SalesQuotationListPageInteraction, initializeQuery))] public static void SalesQuotationListPageInteraction_Pre_initializeQuery(XppPrePostArgs _args) { SalesQuotationListPageInteraction listPageInterAction = args.getThis(); SalesQuotationTable salesQuotationInfo = listPageInterAction.listPage().listPageArgs().externalRecord(); Query _query = args.getArg(identifierStr(_query)); str sPrjBasicNo = any2Str(salesQuotationInfo.PrjBasicNo); QueryBuildRange range = SysQuery::findOrCreateRange(_query.dataSourceName("SalesQuotationTable"), fieldNum(SalesQuotationTable, PrjBasicNo)); range.value(SysQuery::value(sPrjBasicNo)); QueryRun qr = new QueryRun(_query); while (qr.next()) { SalesQuotationTable salesQuotationTable = qr.GetNo(1); info(strFmt("%1",salesQuotationTable.QuotationId)); } } }

Now I see that it's also made complicated and hard-to-maintain because it uses the PreHandlerFor and XppPrePostArgs instead of CoC. Let's fix it too:

public void initializeQuery(Query _query) { SalesQuotationTable salesQuotationInfo = this.listPage().listPageArgs().externalRecord(); QueryBuildRange range = SysQuery::findOrCreateRange(_query.dataSourceTable(tableNum(SalesQuotationTable)), fieldNum(SalesQuotationTable, PrjBasicNo)); range.value(queryValue(salesQuotationInfo.PrjBasicNo)); QueryRun qr = new QueryRun(_query); while (qr.next()) { SalesQuotationTable salesQuotationTable = qr.getNo(1); info(strFmt("%1",salesQuotationTable.QuotationId)); } next initializeQuery(_query); }

Wow! That makes things much simpler.

The code looks fine to me - I think you're looking for the problem at a wrong place. I suspect it might be caused by a dynamic link, but unfortunately you didn't give us enough details to allow me to test this theory.

What would be a good starting point is explaining what you mean by "However, In standard field, there is no problem".

Was this reply helpful? Yes No
Namkamura 25 on at

Like (0)

Report

Hi Martin,

Thank you so much for your kind advice.

Yes, you're right. The standard filed or custom field of table is no problem. I was wrong. so sorry,

By the way, I got your code, and understand it, but,where was it made ? SalesQuotationListPageInterAction extension class? if so, I couldn't see next keyword in your code.

Even if there is no next keyword in extension class, I wonder if it works or not.

Would you please let me know that?

Thanks

Was this reply helpful? Yes No
Martin Dráb 237,874 Most Valuable Professional on at

Like (0)

Report

Ah, sorry, I used super() instead of next. This website doesn't have an X++ compiler... :-) If you doubt it'll work, try it!

If you're saying that you gave us a wrong description of the problem, can you please give us a new one? It's not clear when you have the problem and when you don't.

Was this reply helpful? Yes No
Namkamura 25 on at

Like (0)

Report

Yes , I surely send you and all my new code. Please wait... My memory of laptop is not enough to work...tonight. Thanks a lot... I am happy due to being many experts like you. As a Dynamics People.haha.

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,874 Most Valuable Professional on at

Like (0)

Report

It's not a problem, I didn't ask for all your code anyway. What you should do is explain the observed behavior in details. For instance, does it happen all the time or not? You suggested that it's somehow related to the single field only, but then you said you were wrong. So what's the reality? If it happens every time, don't you think it's caused by a dynalink (as I already mentioned) and your code is irrelevant?

Just giving us a piece of code doesn't tell us what problem you have. And if we don't understand your problem, we can't help you. Therefore if you want help, it's clear that you must give us as good description of your situation as possible.

Was this reply helpful? Yes No