Skip to main content
Solved

Tables


Forum|alt.badge.img+1

Hi All,

Can someone please help me? I can’t get some tables’ field data so I tried removing some tables and relationships that I’m not getting any data from because it seems like they’re causing it. After removing some, I got those tables’ data. But then, my total net sales, still with same filtering, differed. And that shouldn’t happen. Now what do I do? Returning the tables will not display those field again tho. Pls let me know what I need to show so you could help, thanks a lot!

Best answer by Naveen Boga

Hi @paula012  I see some issues with the JOINS it will lead to duplicate/inconsistent results.

 

SOOrderShipment → ARInvoice table we don’t have common fields. Please add the joins like below and hope this helps

SOOrder (OrderTyp, OrderNbr) SOOrderShipment (Order Type, OrderNbr)
SOOrderShipment (ShipmentNbr) SOShipment (ShipmentNbr)
SOOrder (Customer ID) BAccount (BAccountID)
SOShipment (ShipmentNbr) SOShipLine (ShipmentNbr)
SOShipLine (ShipmentType, ShipmentNbr, lineNbr, InventoryID) ARTran (SOShipmentType, SOShipmentNbr, SOShipmentLineNbr, InventoryID)
View original
Did this topic help you find an answer to your question?

12 replies

Naveen Boga
Captain II
Forum|alt.badge.img+19
  • Captain II
  • 3382 replies
  • January 17, 2023

Hi @paula012  Your question is not clear enough.

Can you please let us know what table you have joined, what is not working, and then which tables you have removed?

If you can provide these details along with the screenshots, it would be helpful for us


Forum|alt.badge.img+1
  • Author
  • Semi-Pro II
  • 92 replies
  • January 17, 2023
Naveen Boga wrote:

Hi @paula012  Your question is not clear enough.

Can you please let us know what table you have joined, what is not working, and then which tables you have removed?

If you can provide these details along with the screenshots, it would be helpful for us

This is my original table and relationship, I highlighted what I removed which caused to show me the fields I need. In the next picture is my new set of relationships. And I highlighted the new joins. What’s not showing before the edits are fields from ARInvoice and SOShipment.

Thank you!


Forum|alt.badge.img+1
  • Author
  • Semi-Pro II
  • 92 replies
  • January 17, 2023
This is the output before changing the relationships. Correct total net sales.
And this is after the changes. I got the fields from ARInvoice and SOShipments but total net sales is obviously not equal to total of net sales listed here, no next page

 


Naveen Boga
Captain II
Forum|alt.badge.img+19
  • Captain II
  • 3382 replies
  • Answer
  • January 17, 2023

Hi @paula012  I see some issues with the JOINS it will lead to duplicate/inconsistent results.

 

SOOrderShipment → ARInvoice table we don’t have common fields. Please add the joins like below and hope this helps

SOOrder (OrderTyp, OrderNbr) SOOrderShipment (Order Type, OrderNbr)
SOOrderShipment (ShipmentNbr) SOShipment (ShipmentNbr)
SOOrder (Customer ID) BAccount (BAccountID)
SOShipment (ShipmentNbr) SOShipLine (ShipmentNbr)
SOShipLine (ShipmentType, ShipmentNbr, lineNbr, InventoryID) ARTran (SOShipmentType, SOShipmentNbr, SOShipmentLineNbr, InventoryID)

Forum|alt.badge.img+1
  • Author
  • Semi-Pro II
  • 92 replies
  • January 17, 2023
Maybe this could narrow it down, after I removed this and added what’s in the next picture, the total net sales changed.
B

 


Forum|alt.badge.img+1
  • Author
  • Semi-Pro II
  • 92 replies
  • January 17, 2023
Naveen Boga wrote:

Hi @paula012  I see some issues with the JOINS it will lead to duplicate/inconsistent results.

 

SOOrderShipment → ARInvoice table we don’t have common fields. Please add the joins like below and hope this helps

SOOrder (OrderTyp, OrderNbr) SOOrderShipment (Order Type, OrderNbr)
SOOrderShipment (ShipmentNbr) SOShipment (ShipmentNbr)
SOOrder (Customer ID) BAccount (BAccountID)
SOShipment (ShipmentNbr) SOShipLine (ShipmentNbr)
SOShipLine (ShipmentType, ShipmentNbr, lineNbr, InventoryID) ARTran (SOShipmentType, SOShipmentNbr, SOShipmentLineNbr, InventoryID)

Sir, do you mean these are the only tables I should put. Or from where do I start inserting these? What do I do in my existing tables above? I got error. I added these tables

 


Forum|alt.badge.img+1
  • Author
  • Semi-Pro II
  • 92 replies
  • January 17, 2023

Sir, this is my new set of relationships. It gives me correct total. I need a field from ARInvoice though, how can I relate to that table? Thank you so much!


Forum|alt.badge.img+1
  • Author
  • Semi-Pro II
  • 92 replies
  • January 17, 2023

And sir @Naveen Boga , Sometimes, my total net sales is correct, and sometimes, it doesn’t tally. Here are the screenshots. 1st has wrong, the 2nd has correct.

Can you pls see it? Thanks a lot!


Naveen Boga
Captain II
Forum|alt.badge.img+19
  • Captain II
  • 3382 replies
  • January 17, 2023

@paula012  Can you please the report file here.


Forum|alt.badge.img+1
  • Author
  • Semi-Pro II
  • 92 replies
  • January 17, 2023

Here it is sir @Naveen Boga , Thank you!


Forum|alt.badge.img+1
  • Author
  • Semi-Pro II
  • 92 replies
  • January 18, 2023

sir @Naveen Boga , so this is what I saw, sometimes, total net sales (sum[ARTran.Netsalesamount]) gives me the total of Net Sales ([ARTran.Netsalesamount]) which is also equal to (Total per item/1.12) and sometimes it gives me amount of (Invoice/1.12). Can’t understand why that would happen. Pls help, thanks a lot!


Forum|alt.badge.img+1
  • Author
  • Semi-Pro II
  • 92 replies
  • January 18, 2023

I even found this bycustomer, very far figure.

 


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings