Solved

Tables

P
Rank
Userlevel 3
Badge +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!

icon

Best answer by Naveen Boga 17 January 2023, 08:40

View original

12 replies

Naveen Boga
Rank
Userlevel 7
Badge +17

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

Naveen B
P
Rank
Userlevel 3
Badge +1

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!

P
Rank
Userlevel 3
Badge +1
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
Rank
Userlevel 7
Badge +17

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)
Naveen B
P
Rank
Userlevel 3
Badge +1
Maybe this could narrow it down, after I removed this and added what’s in the next picture, the total net sales changed.
B

 

P
Rank
Userlevel 3
Badge +1

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

 

P
Rank
Userlevel 3
Badge +1

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!

P
Rank
Userlevel 3
Badge +1

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
Rank
Userlevel 7
Badge +17

@paula012  Can you please the report file here.

Naveen B
P
Rank
Userlevel 3
Badge +1

Here it is sir @Naveen Boga , Thank you!

1 Attachment
P
Rank
Userlevel 3
Badge +1

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!

P
Rank
Userlevel 3
Badge +1

I even found this bycustomer, very far figure.

 

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved