Reports and Generic Inquires

We have a report in Report Designer where the results are not matching up with the GI. The GI is called CRRelation. There is a LEFT JOIN with SOOrder.NoteID and CRRelation.RefNoteID. The GI in the attached document shows that the LEFT join works as all the data across the board matches. Using the same type of LEFT join in the Report Designer and following it through various stages (also shown in the attachment) shows that everything appears to be matched up. The issue is that in the Notes field, section listed at the bottom of the attachment, the CRRelation.TargetNoteID  and the CRRelation.Description are suppose to match the top picture in the attachment. I’ve even tried using, in the Report Designer filter, a filter for the TargetType to be Sales Order, and again, no joy. I have not linked the CROpportunity to any of this as I didn’t think it would be necessary in this case.

I have been hunting for the access right that would hide the attached image totals on purchase orders in the header for a view only user that we do not want to allow to see cost of material. I am having a hard time locating the correct access right for this to revoke. Can anyone point me in the right direction? 

Hi , I Have Attached generic enquiry for three table i have created.Notification source, Notification Receipient , Customer But the customer table am not able to join with other two to display the customer id and name Anyone can suggest and help me to resolve this issue 

How can I disable or hide the Export to Excel button in the report launcher window?

Hi All, does anyone have an example how to add the Step numbers in a Production ticket from the BOMThanks

I’m trying to create a report attached below, and use custom data as a filter field. However, whenever I try to search via the filter box, no data appears within the search function. I’ve tried my best to keep the report as close to the standard ones as possible, but I’m not sure what is causing this issue. 

How do I join the NotificationSource table to the Customer table in a GI? I’m trying to output all mailing settings for each customer so we can see who has overrides enabled for specific functions (like invoices etc). I’ve tried using the DAC browser for this, but I’m not finding a way to get from A to B. Anyone feeling super knowledgeable today? I’d love an assist on this one, thanks! 

how to access relation table in generic inquiry for get information restriction group by user?

I currently have a report that looks like this:How can I get the negative dollar values to appear in red? I have searched other solutions. The closest option I have found is overlapping fields and using VisibleExpr. The issue comes in the fact that, the dollar value column uses an if-statement to calculate its value as a negative. So, I’m not able to tie the VisibleExpr field to a specific parameter like [ARInvoice.DocType] etc. Here is a screenshot of the if-statement used:Is there a way to have the VisibleExpr calculate based on the end result of the “Value” field? Thank you,

Dear ALL,Which DAC/Table can get the Beginning Balance and Ending Balance of the Accounts by each day ?Many thanks !NNT

My controller relies heavily on the Find function (search) in the report viewer. The find function used to find the first instance on the first click (or enter key) of the find button on a report, then would find the second on a secondo click/return & so on through the list. This functionality is now lost, it will only find the first instance every time you use the find, its almost as if the report is re-loading every time you click Find. I have tested this in new instances of the same build and have the same results.Has anyone else noticed this, and is there a fix for it?Acumatica 2023 R2 Build 23.212.0024  

I am trying to design a report using INReceiptStatus which is hidden and cannot be selected in report designer or Generic Enquiry. How can i make the DAC available for report designing 

Hi all, I’m trying to write a GI that lists Sales Orders from 13 months ago. For example, if I run the GI successfully with a business date of today, it will show me all Sales Orders in September 2023. I think this is best achieved by setting some conditions in the Conditions tab of the GI, but I am not sure of the formula to use. Does anyone have any suggestions or can point me in the right direction?  

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.