Reports and Generic Inquires

Goal of the GI:Compare the Current Price Class with the New Price class, and filter out any entries that are already the same to reduce the amount of entries sent through an Import ScenarioCode for the New Price Class assigns a type of discount based on the amount sold:=switch(Max([Location.CPriceClassID])='STAFF','STAFF',Sum([SOOrder.CuryOrderTotal])>1200, 'RETAILBULK',Sum([SOOrder.CuryOrderTotal])<1200, 'CUSTOMERS')But as y’all know, when you make a new field, you can pull up it’s internal ‘field’ name, and I can use that for style like this: =iif(SOOrder_Formulad9d511aff428430595f645b5e7225bcf = [Location.CPriceClassID],'','red20') But I can’t use it for comparison in any part of conditions or even within the results grid=[SOOrder_Formulad9d511aff428430595f645b5e7225bcf] doesn’t work@SOOrder_Formulad9d511aff428430595f645b5e7225bcf of course doesn’t workjust SOOrder_Formulad9d511aff428430595f645b5e7225bcf doesn’t workIs this a hard limitation on GI’s, or is there a trick I don’

We are looking for a way to “turn off” items that are discontinued or sold out when gone once inventory is depleted. We’re not necessarily looking for an event to be triggered in Acumatica (purchase or transfer), but a way to identify and track when inventory is low for specific SKUs would be helpful so they can be set as Inactive. 

Hi allIs there a way to create GI with the Inventory balance? We need to create it with inventory transaction details such as receipts, Adjustments, etc and it should include dates and batch numbers, and ref numbers also. I have tried to create it with INregister and INtran tables. But it did not work as the landed cost variance details are also fetched to the GI. Thank you! 

Hi All,I want to add a condition to a generic inquiry to filter timecards created on last week. To do that I think that I would need to compare EPTimeCard.WeekID with current WeekId. Is there a way to populate Value field of GI with current WeekID ? Thanks

Hi all, I’m struggling to get an OData connection from excel and Power BI.Both the below urls gives me what I expect when running them in a browser:https://{site.com}/ERP/odata/{Tenant}/https://{site.com}/ERP/odata/{Tenant}/{GI}But when I try to use the same urls in excel’s OData Feed I get the following error:We encountered an error while trying to connect.Details: "The field 'Branch' already exists in the record." Our version: Acumatica 2023 R2 Build 23.206.0006Any idea on how to solve this issue?

 We are running Acumatica version 23.206.0006. Our last update was a couple of months ago. After the upgrade, we noticed that the reports were taking more time to be printed, but it wasn’t a problem.However, for the past couple of weeks, some reports have been missing images from time to time. As a workaround, users need to reprint the reports. This issue is occurring with multiple reports. Note that all the reports are using sub-reports, but the complexity in some cases is not too high – for example, a report with two sub-reports and not many tables in the schema is having the issue. This is how the images are displayed when the reports fail: We have tested on different PCs and printers, so it is definitely something in Acumatica and we believe that could be related to a performance issue. CPU and memory usage on Acumatica are at normal levels. Has anyone experienced the same issue or have any idea where to look? Any idea is welcome!Thanks!

Hello!I am creating a custom GI that aims to pull the Shipment Details by a given date. I want to show what inventory ID shipped out on what shipment along with the qty shipped and the shipping address. However, I am facing some trouble when I try to pull the shipping address onto the GI.I was able to join the SOOrder and SOShipmentAddress tables by joining the SOOrder.shipAddressID and SOShipmentAddress.AddressID fields (See below).However, it seems to pull the shipping address from the customer rather than the shipping address that is on the Sales Order or Shipment. We typically have to override the shipping address when entering a Sales Order, as we are in a business where a customer has many open job sites / warehouses to ship to.Does anyone know how we can get around the override and have the user-entered Ship-To address to come up in the GI? Thank you! 

I am trying to compress the size of the content showing in the remittance area of the Check Form with Remittance.  I have that task completed.  My employer would like for me to squeeze as many lines into that section with smaller font sizes as possible, ideally between 30 and 40 entries being displayed.  I have the structure of the layout completed on the ap641000.rpx form in Designer.  I've checked all the field values and incremented them according to the pattern in the "canned" items on the report.  However, I am only successful in getting a total of 12 invoice item values to display.  The remaining items do show up on the form output, but "Invoice Date" shows zero, "Invoice Number" shows zero, and Invoice "Invoice Amt" and "Payment Amt" both show "0.00" instead of the appropriate values.  I have adjusted the Payment Method "Lines per Stub" to 33 since I have 33 items ready to be paid with a single check and listed in the remittance area.  Can the form not be manipulated in this way

Not all stock items are showing on the IN2025PL.  They are visible on the IN202500 form.  I cannot figure out why this is happening.  I do not see it as a known item in 2024 R1, the part appears to be set up exactly as the visible parts.  The client is on 2024 R1 Build 24.111.0011. New stock item -Not on GI 

Hello! I am using the formula below to calculate kit availability: =Floor((IsNull(  [INSiteStatus.QtyOnHand], 0 )  /  IIF( [INKitSpecStkDet.DfltCompQty] >0, [INKitSpecStkDet.DfltCompQty],1 )))I have a parameter for InventoryCD so user can search for the inventory ID if needed. The GI would run fine and there are records that show up, however, every time I click to search for an inventory ID, I receive the following error: I’m not sure I understand the error and what I need to change in the formula. Any help would be appreciated!Another note: originally the formula looks like: =Floor((IsNull(  [INSiteStatus.QtyAvail], 0 )  / IsNull( [INKitSpecStkDet.DfltCompQty], 1 )))This formula does not give me the above error, instead it gives a different error saying “Divide by zero error encountered.” 

Hi,I have an existing Generic Inquiry which provides a breakdown of employee costs by subaccount.  Please see the attached screenshot. The Inquiry currently displays a column with the amount by subaccount and the total for each employee.  I would like the total for each employee to exclude where a subaccount is equal to 1. I still want to retain the rows where the subaccount is to 1 so I don’t think I can use Parameters and Conditions to achieve this.I have been trying to use IIF and SUM in formula in the Results Grid but without success. Any assistance appreciated !

I am trying to make another version of the Multi Level Bom as a SQL View. The multi level bom doesnt have all the information that we need on it and I cant join tables on it. So I am creating a Sql view with a recursive statement to explode the boms. It needs to have a parameter in the anchor for it to work correctly and to keep it from bogging down the system with trying to do it for everything. How would I go about loading it into acumatica with a “Blank” for this field so that it could be filled out using the parameter from a report or a GI parameter? Screen shot below of my sql statement with the area im talking about highlighted. Doing it this way, would give me the ability to have any or all information from the tables in the joined in the below query. The information is currently limited to make it easier for testing. 

Does Acumatica allow us to bulk print the Pro Forma invoice? I would like to use the Created On date to generate a pdf file with all the Pro Forma’s from that date. I added it as a parameter and filter, but the form comes back blank. 

Hi allI need to round down values in report designer. Say a customer owes $1076, we need it to show as $1000 only. Please advise

Has anyone encountered the Substantiated Billing - Consolidated report (PM650050) displaying links instead of images for attached PDFs after switching external file storage to point to Amazon S3 storage? We verified that the attachment files exist in the S3 bucket, but for some reason won’t display on the report.

I am trying to pull the total quantity from the invoice screen (via Report Designer) when it is under the sales order tab (so not released yet), as some customers need the invoice prior to our Financial Director releasing the invoices. Anyone know a way to do this? I have to do the below within report designer, but the system seems to get confused when there is more than one sales orders on the invoice.  Value used in Report DesignerThe value the report gives me (should be 15,234 units for this example)Pulling from this page (Sales orders → Invoices) 

Is there an example GI somewhere that provides me an example of how to list all of the “child” members of the company tree based on the ParentWGID of my Employee Account?

Per our internal Acumatica help wiki, I should be able to download the Acumatica Report Designer from http://acumatica-builds.s3.amazonaws.com/index.html?prefix=builds/<buildNumber>, but upon searching for my build number, I encounter a directory with no items.Has the Builds URL changed and is there an alternate URL to download this directly from Acumatica?

Is there a way to add report logic to pull the vendor type alternate ID based on the vendor on the purchase order? We have items with multiple alternate ID types, and in several cases there are multiple vendor numbers listed. I need some sort of logic to specify the alternate type, then match the Vendor ID with the vendor on the PO.Thanks in advanced!

I’m struggling to get the sales person Id to the the page. All I get is numbers and not an actual name.Can someone help me figure out what I did wrong?  

Example: Total value is 30, Sales is 7. so when i check percentage calculation, Percentage  = (sales/total)*100. but output shown is 0. how to check percentage?

Hello - I am trying to get a prior period depreciation amount added to the FA Balance Projection by Class report but can’t seem to get it to work. Any ideas on how to work this in report designer? 

Is there a way - either as a parameter or GI filter - to filter the view of a generic inquiry by item workgroup that I am a member of?  We are not looking to set up restrictions, just filter a list of items on a few GIs. We’ve setup each item with a workgroup and can assign employees to one or more workgroup. We’d want to then add a filter or T/F parameter to view only items in “MyWorkGroup” in the same way you can on other process screens like Prepare Replenishment or similar to how @me can be used   

All, I’m having trouble validating the syntax of this visible expression… I’ve looked at it so many times now I think I’m going blind. =iif(([AMProdOper.Descr]='Stage 2' and (Right([AMProdItem.InventoryID],4)='-PGC', True, False))) So I want the text box to display if:the production stage description is ‘Stage 2’ andIf the InventoryID ends in ‘-PGC’I’m not sure if it’s the closing parens or if my expression to pull the correct Inventory IDs is at fault… thanks!

Hi, can someone please explain to me why, in a generic inquiry, sometimes reference number or document number shows up as blue and I can drill down to the document, but sometimes I can’t drill down?For example: I can drill into the payment reference number here but nothing gets pulled up when I try to drill into the invoice reference number. And sometimes when I create a GI, the reference number would not even show up as a link.Can anyone explain why this would be?Thanks!!