Reports and Generic Inquires

Hi,I am still green to creating Generic Inquiries. I am trying to create a GI that displays the total inventory Items including qty and amount for each customer invoice. Then from there I am going to create a dashboard that shows the top selling items for each inventory item by qty. Here are screenshots for my total setup. How can I get the Customers Invoices to consolidate/filter correctly in my GI? Let me know or what conditions/settings I am missing? I have also attached an export of the GI if needed. Thank you,Frances Pantelidakis  Screen 1 Tables Setup: Screen 2 Relations Setup: Screen 3 Sort Order: Screen 4 Results Grid: Screen 5 Output of GI:  

Hi, I am doing a GI to show the GL accounts for NonStock Items.The NS Items are tied to a posting class. The posting class can use the default of Inventory Item (I) or Posting Class (P) I am trying to get the GL account to be in one column. My data field looks like this: =iif ( [INPostClass.InvtAcctDefault]='I', [InventoryItem.InvtAcctID], [INPostClass.InvtAcctID])The GI is returning 2037 instead of the GL#, 11302. Is there a function or a way to get the 11302( The GL from the chart of accounts) to display? Thank you, Bill 

HeyI was wondering if anyone was able to create a GI with more than 213 Rows. The max seems to be 213. Also, has anyone added new rows to a GI  from the XML file? My guess is you need to generate a new RowID; that's something I have not been able to figure out. 

Like my last question, I am receiving the following error, but it is not on a field that can have a aggregate function. I have attached the xml file.Sorry for all the questions. I will get this down eventually.

Still trying to fully understand the workings of the GIs.I have a GI that is working in our sandbox system, but when I create it in our live system, I receive the following error: Any assistance on this would be greatly appreciated.

I am struggling to add a date range to this generic inquiry. I need to add the date range of 1st day of month to last day of month in the default value section of the parameters for a generic inquiry.  Thank you in advance for your help!

Trying to build a GI to show AP Bills approved by a specific employee (or any employee really), and can’t find a way to join APInvoice with EPApproval or EPOwned or any other DAC that can have approval data (approvedByID). Any ideas how to solve this riddle?

hi all, is there an out of the box report that shows totals sales for a customer? this would include open sales and sales history and should also include credits. thanks in advance.

Hi allI need to show VAT per line in our Receivables invoice that prints for customers, but I’m finding that both the ARTran.curyTaxAmt and ARTran.taxAmt fields are showing zero.  Is there a different way to do this?

Hi All,I am creating a report that will help me identify amount of defect / return over a period of time. I used SO.61.10.00 Sales Order Detail By Customer as base report. The issue I am having is that the SOLine.ClosedQty is not able to sum based on the different order types. I have multiple order types that I need to subtract each other in order to produce the result I am looking for. Below are what I am trying to do:Sum of Sales Order - Sum of Correction (Custom Order Type)Sum of Credit (Custom Order Type) + Sum of Replacement (Custom Order Type)I used IIF function in on the detail and it work fine, only when it comes to SUM where the number is off. ‘=IIF([SOOrder.OrderType]='SO',[SOLine.ClosedQty],IIF([SOOrder.OrderType]='CO',sum([SOLine.ClosedQty]),'0')‘Are there any suggestion on what I can do in order to achieve what I want?Kind regards,Calvin Law

Hi, We have created a Inventory Transfer with added from Location and To Location. There are Two Items. We have created a report. We changed the Existing inventory transaction report.   Mainly we are using INtran table and if we have two different location system create two lines in same table.  we tried removing the location columns. but we could not find any distinct column option. We need see only two lines in above example. Expected result as follows.   RegardsNethupul    

I'm putting together a GI around Tasks to use for Dashboards and I need to use the Start Date and End Time fields to show the Start Date and Due Date of tasks. However, I've discovered as I was trying to set up filters for the data that the Start Date and End Time fields are displaying as 1 day earlier in the GI results than they actually are when you go inside the Task.For example, in the GI results this Task is showing a Start Date of 6/15/22 and End Date of 6/16/22: However if you go to the actual Task itself you'll see that the Start Date is really 6/16/22 and End Date is really 6/17/22: Anyone know what's going on here and how to fix it so the GI results show the correct dates? The filters appear to be working off of the true dates in the tasks which makes the GI results seem off when using the filters. The only thing I can think of is perhaps there’s a time zone issue between the fields?Thanks!

I’m trying to sort out default locations for vendors using the vendor location generic inquiry. I’m able to add the “IsDefault” from the Location Table to the results grid in the generic inquiry but when I run the query no locations are marked as default, even though obviously one for each vendor should be the default. Am I missing a step or choosing an incorrect field?

We are working on upgrading two clients to 22.106 and this version seems to have removed the ability to use the keyboard copy (Ctrl-C) to copy the value of a cell into the windows clipboard.  This issue does not exist in 22.105.  Can someone confirm if this is a bug because will will have clients that will complain about this loss of functionality?Thanks,Kurt

We are not using the Acumatica branch logo functionality, because we have a header logo that needs to span across a page.We have added this as a picture on our reports, and it renders clearly in PDF as long as the PDF is viewed in an internet browser.  However, as soon as the rendered PDF is downloaded and viewed in Adobe, the logo becomes grainy.  Here is a small portion of the text in the header logo on Adobe vs Chrome (both viewed at 100%):You can’t really see the problem clearly here, but it creates a significant difference in quality when you look at the whole logo.  I’ve tried different sizing options (normal, scale, fit) and none of them make a difference.Does anyone have an idea on how to fix this?

The report is only pulling data utilizing the main report parameters.  As long as there is data within the parameters of the main report it will print the main report and will then print the data from the subreport that is within the same parameters. If nothing prints in the main document due to the parameters nothing prints from the subaccount even though there is data that should print. What am I missing?

I need the date output in a GI to be yyyy/mm/dd for a third party integration but we use mm/dd/yyyy as a default. How can I flip this around?

Dear ALL,I created Generic Inquiry using “InventoryTranHistEnqFilter” table. But when I pressed “VIEW INQUIRY” to see the result it gave error “Invalid object name 'InventoryTranHistEnqFilter'”.Please explain the cause of the error and help how to fix it !Many thanks !NNT 

Is there a way to create a generic inquiry for Dashboard widgets and the linked inquiry screen into the widget. I want to find out and get a summary what generic inquiry we are not using, not linked to a dashboard

Hi all, Do you know how do I add the Description Tab from stock items screen to a generic inquiry? If I add the body field that is how it looks.  Thanks, Patricia 

Hi All,This is a 2 part questions. I used Daily Sales Profitability AR.67.60.00 as a base report for my Freight Rate report. 1 - How to have the report to show SOShipment.CuryFreightCost to show correctly when I use SUM function? The base report used AR Tran for Net Sales and Cost and it works fine as it sum up each line to create a total. But because Freight Cost is in each transaction line, it would multiply itself with the same number of times as the total lines in the order. I somehow manage to have it correct on the individual order, but when I sum them up, they are wrong. See below: 2 - In the same report, I have orders that are being shipped to US. I am trying to show CurrencyRate.CuryRate for all the US orders. When I link Currency and CurrencyRate table, it doesn’t show the exchange rate at all. Hoping the community will give me some ideas to resolve my issues. Kind regards,Calvin Law

Does anyone have a good explanation of the difference between PX.Objects.AP.Vendor and PX.Objects.AP.VendorR?I haven’t encountered a difference between them (yet), but I’ve noticed that the standard Vendors (AP3030PL) Generic Inquiry uses PX.Objects.AP.VendorR and I wonder why.

Hi All,I am looking to count the total line in Sales Order Summary. I do not know what would be the formula to have the text box to count 1 to 10 or more. I used Count but it just don’t add and give me random number.   Ultimately I need a formula that simply count number of lines in the report and is not affected but the order itself.Kind regards,Calvin Law

Background: I received a request to adjust the Bills and Adjustment Generic Inquiry Screen to not allow users to view all Bills. However, the users would need to be able to view some bills that are related to a vendor but not others as they are not relate to their projects. Similarly, the users would need to see certain bills that were related to a GL account but not other transactions. For this reason, I determined that row level security was not the best route. I instead adjusted the Bill and Adjustments Generic Inquiry to have an owner parameter with the default value of @me; corresponding generic inquiry conditions were made for this as well. This has assisted with the issue as now, by default, the users can only see bills that they are listed as an owner on.  Problem: I am unable to prevent the users from editing who is listed as the owner through tools-Access Rights by Screen in the Generic Inquiry Parameter. So while the default is the logged in user they could delete the user a

Hello and thanks for the input to all my reporting & Generic Inquiry people!I'm attempting to create a hierarchical sorting structure for a Report Designer Report. I'm following documentation but can't seem to get the data to sort in the intended order.The goal is to sort the Item list by the first 3 numeric values within a 3-segment Item Class code.I can return the value I want in a separate text field: Left([InventoryItem.ItemClassID],3) But I can’t seem to sort on it. As an example, here are a few item classes in their intended sort order:1. 210FENCE  100BARB   100SHEFF2. 210FENCE  100BARB   200OKSW   (note the first 3 digits of the third segment are 200 (for 200OKSW ) , so this would come after 100SHEFF)3. 210FENCE  100BARB   400BEKHRT 4. 215LVSTOCK100GATES  200METRO  (note the first 3 digits are 215, so this would come after 210FENCE)5. 215LVSTOCK100GATES  200METRO  6. 230GARDN  100GDNSPLY   (note the first 3 digits are 230, so this would come after  215LVSTOCK) The thing that