Intercompany orders

(6) Share

Report

Posted on by MacNeg

133

Hello,

I am trying to create a simple list screen that will list intercompany PO-SO orders where their statuses are not in synch. I found tables to use and was able to verify the SQL to use in SQLDB (as inner join). But when I used it in X++, using query or override to executeQuery, I am not getting any information back from the IC sales order and only works as outer join. My main data source is purchTable. I am using fields intercompanycompanyid and intercompanysalesid, to map the record to salesTable. Is there a special setting on the Xpp side that I missed?

Thanks in advance!

Categories:

Dynamics 365 Finance

I have the same question (0)

All responses (23)

Answers (1)

Suggested answer

Martin Dráb 238,143 Most Valuable Professional on at

Like (2)

Report
Copy link

Link copied!

Moved from Integration, Dataverse, and general topics forum.

The problem likely is that you forgot to make the query intercompany and therefore it fetches data from the current company only, while you want two different companies.

Your screenshot shows that you're actually talking about a form; there you can use Cross Company Auto Query property of the form data source. You'll then likely want to filter PurchTable data source by curExt().

Was this reply helpful? Yes No
MacNeg 133 on at

Like (1)

Report
Copy link

Link copied!

Thanks Martin for the response. I updated my forms and set the "Cross company auto query" property to Yes and still did not display IC SO information. I enabled it to both purchtable and salestable ds.

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

Like (2)

Report
Copy link

Link copied!

Try also Set Company property of the form design.

In general, it's benefial to check what query you've generated by your setup and X++ code, not just looking at what the query returns. When you see that there is (say) a wrong filter or a join condition in the SQL statement, you know what to focus on.

Was this reply helpful? Yes No
MacNeg 133 on at

Like (1)

Report
Copy link

Link copied!

Thanks Martin. I checked all the screens I created and that Set Company field is set to Yes (I think by default too).

Was this reply helpful? Yes No
MacNeg 133 on at

Like (1)

Report
Copy link

Link copied!

I also tried creating a simple class to run an sql select statement and did not get the same results vs when running the select statement in MS Azure data studio :(

Am I missing some flags, settings or attributes when using select statement in the code? TIA.

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

Like (2)

Report
Copy link

Link copied!
Your query is not cross-company; it'll try to load all records from the current company. You need to add crossCompany keyword to the select statement.

As I mentioned, when debugging queries, it's useful to check what query you've got, not just what it returns.

In select statements, you can do like this:

select generateOnly forceLiterals purchTable where ...; info(purchTable.getSQLStatement());

Was this reply helpful? Yes No
MacNeg 133 on at

Like (1)

Report
Copy link

Link copied!

Thanks a lot Martin. Still didn't work :( I also added info to display SQL statement and I did not get any information. Does it matter if my visual studio is the community 2022 version?

Thanks!

test class to che...

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

Like (1)

Report
Copy link

Link copied!

Please share you latest code, so we can review it and run it. I doubt that you've found a bug in F&O; more likely there is a bug in your code, but identifying a bug in unknown code is difficult.

Was this reply helpful? Yes No
MacNeg 133 on at

Like (1)

Report
Copy link

Link copied!

Thanks Martin. I have attached my newbie code to my previous post. For some weird reasons, it is not letting me post a reply - I tried it in Edge and Chrome. :(. - This one just worked in Chrome - I am updating it to include my code...

test class to che...

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

Like (2)

Report
Copy link

Link copied!
You missed a few important things in code that I shared:

select generateOnly forceLiterals purchTable where ...; info(purchTable.getSQLStatement());

In your code, you have neither generateOnly keyword (which is crucial) nor forceLiteral (which is optional, but useful), you're using a while select, which is useless when we just generate SQL statement and not actual fetch data from database. Also, don't bother trying to get SQL statement from salesTable. It's just a single query.

while select crosscompany purchTable where purchTable.dataAreaId == "USMF" && purchTable.InterCompanyOrder == 1 outer join salesTable where salesTable.SalesId == purchTable.InterCompanySalesId && salesTable.DataAreaId == purchTable.InterCompanyCompanyId { } info(strFmt("PurchTable SQL: %1", purchTable.getSQLStatement())); info(strFmt("SalesTable SQL: %1", salesTable.getSQLStatement()));

It should look like this:

select generateOnly forceLiterals crossCompany purchTable where purchTable.dataAreaId == "USMF" && purchTable.InterCompanyOrder == NoYes::Yes outer join salesTable where salesTable.SalesId == purchTable.InterCompanySalesId && salesTable.DataAreaId == purchTable.InterCompanyCompanyId; info(purchTable.getSQLStatement());

By the way, I usually use Firefox, but I had to switch to Chrome for Dynamics Community, because quite a few things worked worse there. It's not ideal in Chrome either, but significantly better.

Was this reply helpful? Yes No