Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Shari Bruno]

Hello!

I have a SmartList Builder report that is looking at Sales Line Items because I need the line details, and combining that with an Extender and Company table. Only sales invoices are on the report even though there are returns that should show up. I have tried linking into SOP SQL tables and various sales tables with no luck. Any thoughts on how to get returns on this report?

Thanks!

——————————
Shari Bruno
Accounting Operations Manager
Bible League International
Crete, IL
——————————

[Sarah Brewster]

Does your report have any restrictions on the SOP Type?  The Sales Line Items SmartList by default will show returns. 

All your SOP transaction line detail (quote/order/invoice/return, etc.) should be in table SOP10200 for unposted/open years and SOP30300 for historical.  The returns will show up as SOPTYPE 4.

——————————
Sarah Brewster
Implementation and Support
ActivReporter by nQativ
Lubbock TX
——————————
——————————————-

[Charles Allen]

Do the transactions that are not invoices have Extender data? I’m wondering if there’s an Inner Join instead of an Outer Join. Try changing the join types to outer joins to see if you get more data.

Charles Allen

Senior Consulting Manager

BKD LLP

713.499.4629 Office

713.494.2104 Cell

****** BKD, LLP Internet Email Confidentiality Footer ******

Privileged/Confidential Information may be contained
in this message. If you are not the addressee indicated in
this message (or responsible for delivery of the message
to such person), you may not copy or deliver this message to
anyone. In such case, you should destroy this message, and
notify us immediately. If you or your employer do not consent
to Internet email messages of this kind, please advise us
immediately. Opinions, conclusions and other information
expressed in this message are not given or endorsed by my
firm or employer unless otherwise indicated by an authorized
representative independent of this message.

Any tax advice contained in the body of this email was not
intended or written to be used, and cannot be used, by the
recipient for the purpose of avoiding penalties that may be
imposed under the Internal Revenue Code or applicable state
or local tax law provisions.

These discussions and conclusions are based on the facts
as stated and existing authorities as of the date of this
email. Our advice could change as a result of changes in the
applicable laws and regulations. We are under no obligation
to update this information if such changes occur. Our advice
is based on your unique facts and circumstances as you
communicated them to us and should not be used or relied
on by anyone else.

——Original Message——

Hello!

I have a SmartList Builder report that is looking at Sales Line Items because I need the line details, and combining that with an Extender and Company table. Only sales invoices are on the report even though there are returns that should show up. I have tried linking into SOP SQL tables and various sales tables with no luck. Any thoughts on how to get returns on this report?

Thanks!

——————————
Shari Bruno
Accounting Operations Manager
Bible League International
Crete, IL
——————————

[Jo deRuiter]

Hi

I second , it sounds like the “joins” are set at Equals?? instead of possible “Left Outer” which means that if it is looking first at the SOP Tables it will not bring in any information that is not “Equal” to a match in the Extender Tables.

If you are using SLB to build it try the Left Outer method.  

If you are not using SLB, then edit the view in SQL to use something other that a clear Left or Right Join, like use an inner join.

——————————

——————————
——————————————-

[Shari Bruno]

   and anyone else who has any ideas on how to fix this.

This is a SLB report, and all my links in SLB are always left outer.

The Extender window I’m linking to is in Item Maintenance, and it doesn’t matter if the item has these fields or not, I have transactions on the report that are blank for those fields.

For the rest it’s a little too much to embed in this reply so I attached a document with screen shots and more explanation.

This report is to come up with a yearly number that previously was done by combining figures from several different people. But all of the information lives in GP so I figured I would try to come up with a way to get it direct. Except for the returns everything else on the report works great.

Thanks!

——————————
Shari Bruno
Accounting Operations Manager
Bible League International
Crete, IL
——————————
——————————————-

[Charles Allen]

Would you display the SQL query that SmartList Builder is generating?

 

 

Charles Allen

Senior Consulting Manager | BKD

2700 Post Oak Blvd. Suite 1500

Houston, TX 77056

713.499.4629 Direct

713.494.2104 Mobile

 

 

****** BKD, LLP Internet Email Confidentiality Footer ******

Privileged/Confidential Information may be contained
in this message. If you are not the addressee indicated in
this message (or responsible for delivery of the message
to such person), you may not copy or deliver this message to
anyone. In such case, you should destroy this message, and
notify us immediately. If you or your employer do not consent
to Internet email messages of this kind, please advise us
immediately. Opinions, conclusions and other information
expressed in this message are not given or endorsed by my
firm or employer unless otherwise indicated by an authorized
representative independent of this message.

Any tax advice contained in the body of this email was not
intended or written to be used, and cannot be used, by the
recipient for the purpose of avoiding penalties that may be
imposed under the Internal Revenue Code or applicable state
or local tax law provisions.

These discussions and conclusions are based on the facts
as stated and existing authorities as of the date of this
email. Our advice could change as a result of changes in the
applicable laws and regulations. We are under no obligation
to update this information if such changes occur. Our advice
is based on your unique facts and circumstances as you
communicated them to us and should not be used or relied
on by anyone else.

——Original Message——

   and anyone else who has any ideas on how to fix this.

This is a SLB report, and all my links in SLB are always left outer.

The Extender window I’m linking to is in Item Maintenance, and it doesn’t matter if the item has these fields or not, I have transactions on the report that are blank for those fields.

For the rest it’s a little too much to embed in this reply so I attached a document with screen shots and more explanation.

This report is to come up with a yearly number that previously was done by combining figures from several different people. But all of the information lives in GP so I figured I would try to come up with a way to get it direct. Except for the returns everything else on the report works great.

Thanks!

——————————
Shari Bruno
Accounting Operations Manager
Bible League International
Crete, IL
——————————

[Shari Bruno]

Hi Charles,

Here’s the

SQL query:
select
* from (select * from [<<COMPANY_ID>>]..slbSalesOrderLines with (NOLOCK)) T1
 LEFT JOIN (select C.Extender_Record_ID, C.[_1], isnull(B204.[BLI Reporting Type_204],0) as [BLI Reporting Type_204], isnull(B205.[BLI Product Type_205],0) as [BLI Product Type_205] from (select EXT01100.Extender_Record_ID, Extender_Key_Values_1 as [_1] from BL..EXT01100 where EXT01100.Extender_Window_ID = ‘P2I INFO’) C
 left join
(select Extender_Record_ID, TOTAL as [BLI Reporting Type_204] from [BL]..EXT01103
 where Field_ID = 204) B204
 on C.Extender_Record_ID = B204.Extender_Record_ID
 and C.Extender_Record_ID = B204.Extender_Record_ID
 left join
(select Extender_Record_ID, TOTAL as [BLI Product Type_205] from [BL]..EXT01103
 where Field_ID = 205) B205
 on C.Extender_Record_ID = B205.Extender_Record_ID
 and C.Extender_Record_ID = B205.Extender_Record_ID ) T2
 on
 T2.[_1] = T1.ITEMNMBR
 LEFT JOIN [BL]..SY04200 T3 with (nolock)
 on
 T3.[COMMNTID] = T1.COMMNTID?

——————————
Shari Bruno
Accounting Operations Manager
Bible League International
Crete, IL
——————————
——————————————-

[Charles Allen]

There’s a Where Clause. Can you take out the Where Clause to see if it makes a difference?

Charles Allen

Senior Consulting Manager

BKD LLP

713.499.4629 Office

713.494.2104 Cell

****** BKD, LLP Internet Email Confidentiality Footer ******

Privileged/Confidential Information may be contained
in this message. If you are not the addressee indicated in
this message (or responsible for delivery of the message
to such person), you may not copy or deliver this message to
anyone. In such case, you should destroy this message, and
notify us immediately. If you or your employer do not consent
to Internet email messages of this kind, please advise us
immediately. Opinions, conclusions and other information
expressed in this message are not given or endorsed by my
firm or employer unless otherwise indicated by an authorized
representative independent of this message.

Any tax advice contained in the body of this email was not
intended or written to be used, and cannot be used, by the
recipient for the purpose of avoiding penalties that may be
imposed under the Internal Revenue Code or applicable state
or local tax law provisions.

These discussions and conclusions are based on the facts
as stated and existing authorities as of the date of this
email. Our advice could change as a result of changes in the
applicable laws and regulations. We are under no obligation
to update this information if such changes occur. Our advice
is based on your unique facts and circumstances as you
communicated them to us and should not be used or relied
on by anyone else.

——Original Message——

Hi Charles,

Here’s the

SQL query:
select
* from (select * from [<<COMPANY_ID>>]..slbSalesOrderLines with (NOLOCK)) T1
 LEFT JOIN (select C.Extender_Record_ID, C.[_1], isnull(B204.[BLI Reporting Type_204],0) as [BLI Reporting Type_204], isnull(B205.[BLI Product Type_205],0) as [BLI Product Type_205] from (select EXT01100.Extender_Record_ID, Extender_Key_Values_1 as [_1] from BL..EXT01100 where EXT01100.Extender_Window_ID = ‘P2I INFO’) C
 left join
(select Extender_Record_ID, TOTAL as [BLI Reporting Type_204] from [BL]..EXT01103
 where Field_ID = 204) B204
 on C.Extender_Record_ID = B204.Extender_Record_ID
 and C.Extender_Record_ID = B204.Extender_Record_ID
 left join
(select Extender_Record_ID, TOTAL as [BLI Product Type_205] from [BL]..EXT01103
 where Field_ID = 205) B205
 on C.Extender_Record_ID = B205.Extender_Record_ID
 and C.Extender_Record_ID = B205.Extender_Record_ID ) T2
 on
 T2.[_1] = T1.ITEMNMBR
 LEFT JOIN [BL]..SY04200 T3 with (nolock)
 on
 T3.[COMMNTID] = T1.COMMNTID?

——————————
Shari Bruno
Accounting Operations Manager
Bible League International
Crete, IL
——————————

[Shari Bruno]

Hi

There are three Where clauses – I underlined and bold typed them. Should I take all of them out??

Thanks!

select
* from (select * from [<<COMPANY_ID>>]..slbSalesOrderLines with (NOLOCK)) T1
 LEFT JOIN (select C.Extender_Record_ID, C.[_1], isnull(B204.[BLI Reporting Type_204],0) as [BLI Reporting Type_204], isnull(B205.[BLI Product Type_205],0) as [BLI Product Type_205] from (select EXT01100.Extender_Record_ID, Extender_Key_Values_1 as [_1] from BL..EXT01100 where EXT01100.Extender_Window_ID = ‘P2I INFO’) C
 left join
(select Extender_Record_ID, TOTAL as [BLI Reporting Type_204] from [BL]..EXT01103
 where Field_ID = 204) B204
 on C.Extender_Record_ID = B204.Extender_Record_ID
 and C.Extender_Record_ID = B204.Extender_Record_ID
 left join
(select Extender_Record_ID, TOTAL as [BLI Product Type_205] from [BL]..EXT01103
 where Field_ID = 205) B205
 on C.Extender_Record_ID = B205.Extender_Record_ID
 and C.Extender_Record_ID = B205.Extender_Record_ID ) T2
 on
 T2.[_1] = T1.ITEMNMBR
 LEFT JOIN [BL]..SY04200 T3 with (nolock)
 on
 T3.[COMMNTID] = T1.COMMNTID???

——————————
Shari Bruno
Accounting Operations Manager
Bible League International
Crete, IL
——————————
——————————————-

[Jim Harris]

If you remove the extender source entirely and just have the part for comments, does it then include the returns?

Do you have any restrictions in place in SLB?

——————————
Jim Harris
Director of Information Systems
ExamWorks, Inc.
Atlanta GA
——————————
——————————————-

[Shari Bruno]

Hi

There are no restrictions in place on the SLB report.

When I remove the Extender window I get this message when I open the report in SmartView:

??I am also unable to add the SOP Type back onto the SmartView report, the wheel just turns.

——————————
Shari Bruno
Accounting Operations Manager
Bible League International
Crete, IL
——————————
——————————————-

[Shari Bruno]

I got a resolution for this from when I emailed her and asked when the next Builder Monday was happening so I could turn in this issue. Because Nicole is SUCH a rock star, and the next Builder Monday is not tackling problems, she looked into it and found the problem – the restriction by Invoice Date. Some returns don’t have invoice dates and when I removed that restriction the returns showed up. To get the data we need from this report I added the Posted Date and restricted by that and all is well.

Thanks to all of you who took the time to help me figure this out, I do appreciate it.
??

——————————
Shari Bruno
Accounting Operations Manager
Bible League International
Crete, IL
——————————
——————————————-