In order to allow me to focus on Consulting (click here) and Courses (click here), I continue to review all posts here in the Forums, but, in general, I'm only personally responding to posts that I mark as #AcumaticaTnT (click here). For Questions (click here), others may respond, or you can post over at Community.Acumatica.com (click here).
Questions Dashboard to display Sales for current month and current year for each salesperson
For Sales Comparison by Item Class what should be the relation between ARInvoice and InventoryItem table?
What version of Acumatica are you running? I get an error trying to upload the xml file.
I was able to upload the xml file into build 17.204.0019. But I've had trouble sometimes with these xml files. Not completely sure why though.
For the join, you want to join on ARTran, not ARInvoice because the Inventory Item lives at the line level of the invoice.
Try this, making sure to set the Join Type to Left because there may or may not be an Inventory Item on the line.
Joining tables together is by far the trickiest thing about building generic inquiries. I used to try to teach people how to do it, but eventually settled on, "hey, you can do everything else, but call your partner when it gets to the Relations tab."
The reason is that you really need an understanding of normalized databases, primary keys on tables, and how joins work. It's also helpful to know how ERP tables are typically structured in general so you know where to expect to find certain information.
For your particular example, what I would do is open the database and find the primary key on the CustomerClass table. The primary key could be made up of more than one column. Now, even though Data Access Classes which you are working with in the Generic Inquiry and the Tables in the database are not the same thing, I would say that about 90% of the time, they happen to have the same names. So, this isn't a 100% of the time method, but usually it works.
Checking the CustomerClass table in the database shows me that CustomerClassID is the only column needed to form a primary key.
But you won't find CustomerClassID in any of your existing tables. You'll need to join to Customer on ARInvoice.CustomerID = Customer.BAccountID, then from Customer you can join to CustomerClass on Customer.CustomerClassID = CustomerClass.CustomerClassID.
I have been following Tim’s video to do a report that will show sales by item class and by period. I managed to make it work as a GI but since I need I to grouped it by period as well, I had to make it into a report. However, the sum formula doesn’t work in this case, it is somehow combining numbers from the group instead of summing. Is it the iif formula that is throwing it off?
This is the formula I used to sum MTD:
=Sum(IIf(Year([SOOrder.OrderDate])=Year(@AsofDate) And Month([SOOrder.OrderDate])=Month(@AsofDate),[ARTran.NetSalesAmount],0))
and this is the GI which is working:
It would be great if there was a simpler way in pulling sales by period ([ARTran.FinPeriodID]) instead of pulling them by order nbr and grouping them. My end goal would be to display those columns by period and then broken down by item class.