Reports and Generic Inquires

Hello, I’ve ran into an issue with a custom report built through the report designer where my totals in the report footer have not been totaling correctly or at all. All of my totals are in variables currently and I’ve got numerous totals to show. I noticed though that if I take the formula from the variable and throw it in just a normal text box, it sums up correctly. This is happening for all variables in my report footer for some reason.For instance, my RptTotEstFee variable here (all created in report footer):With the full value expression:=iif([@InclDeduction]=1, sum([OpenFileReport.OrigEstimate])-sum([OpenFileReport.OrigDiscAmt]),sum([OpenFileReport.OrigEstimate]))I took that same formula and threw it in a TextBox instead of using a variable (tough to show it, but the entire formula is in that text box), also took the CashRcvd variable and did the same thing:Then when running the report, we are shown the first record of the data’s value on the variable and the actual total for th

I am trying to find date variance between SO Request Date and AM prod Item End date. This is the formula I have coded: =DateDiff( d,[SOOrder.RequestDate] , [AMProdItem.EndDate] )I get the following error : Any help is appreciated.

I have built a query in which I have multiple associations with the contact tables to get required information. Contact Table is used to find details for a UDF contact field. Owner Contact table is used to get opportunity owner information.Builder contact table is joined with SO Order to get SO contact information.For Builder contact (actual contact on SO), I am able to get Email, Full Name, ContactID_Description but website is blank. When I check the contact, it has website but not coming up on GI. Another thing I noticed was the Phone 1 is also not coming for most of them, only 2-3 records show up whereas it is present. I am trying to use Contact.Website field. Would really appreciate if some one has an answer to this. -Ameya

I am looking to modify the FA643000 report.In the stock report I see a table called FAProjectedGLTran.What is the source of this data?Maybe I am missing something but I can find no reference to this table other than in the report builder.

Version 22.109.0023, was trying to print Lead from report designer by using CRLead, but seems that OwnerID, OwnerID_Descirption are not loading owner data properly, so where I should read Owner field then?

Hello,       As we know, we are having conditionally style in Genric inquiry, like hightlight color. Is there a way to use this similiar function in report designer.    Like, can I hightlight or show different background color for the below wgt=0 lines?  

Hi All,We recently fixed a data issue for one of our clients and we need to adjust some reporting on the Dashboards.Basically, every other widget on a Dashboard recognizes negative values that are calculated as negative based on below Formula: Yet when using this Data column from the Inquiry in the Pivot Table, it treats it as positive and adds to the total rather to substract from it.What do I need to do that these values are recognized as negative by the Pivot Table?It’s a very basic Table: ​​​​​​​Thank you!

Everything was working fine this morning but we installed additional modules on Acumatica and now when we try to print out a check we just get a blank check remittance document. Do setting change with the installation of other modules?

I want to join ARInvoice to FSBillHistory.  Sometimes I get two returns for one ARInvoice RefNbr, depending on if there are two appointments billed to the one invoice.There must be a way to use relations to grab only one of the two (or possibly more than two) returns.I have tried using First([X]) = [X] , Max([X]) = ([X]) (can’t because of aggregate).  There must be a way to relate the join to provide only one return per match.  Can anyone help?? -David  

Prior to their recent upgrade to 2022R1, our client was able to restict the visibilty of records by department and by user using the @me condition in the PO genreic inquiry. By doing this, only those purchase orders created by the user became visible to the user. Now after their recent upgrade the user is able to see all records for the department.The linking is as follows:And the condition is set as:The @me condition is now not working.

I’m wondering if anyone at Acumatica ( especially @vpanchenko ) has some insight on this.I’ve noticed this behavior before, but this is the first time that I got out my stopwatch to time it and verify.I’m on an Acumatica site, pulling data from a Generic Inquiry over Acumatica OData, using Power BI Desktop. The Generic Inquiry is a Generic Inquiry of AR Invoice Lines, pulling from PX.Objects.AR.ARTran and PX.Objects.AR.ARInvoice. The build is 21.209.0034. I tried two different methods of accessing the data: First MethodConnect to the Acumatica OData URL, then browse to the Generic Inquiry within the Power BI user interface, then remove all columns except DocType, RefNbr, and LineNbr using the Power BI user interface. The end result is three columns and this is the Power BI code that gets generated.let Source = OData.Feed("https://mycompany.acumatica.com/odata/production", null, [Implementation="2.0"]), #"PBI-ARInvoiceLines_table" = Source{[Name="PBI-ARInvoiceLines",Signature="tab

My team is trying to create a report for budget to actuals by salesperson. We have created an attribute associated with the employee class called Sales Budget and entered in the amounts for each of our salespeople. Each of our salespeople are Owners of their respective Opportunities. We are attempting to join the EPEmployee/CREmployee table with the CROpportunity table in order to create a GI that shows Salesperson, Budget and then sums their actual sales for the period.  Has anyone been able to join the employee object with the opportunity object through the Owner field (or otherwise)? Any ideas would be appreciated.   

I’ve tinkered with the groupings and aggregates for a bit and haven’t come up with a way to do this and wanted to check here first before going the SQL view route. I have an inquiry that is showing shipment line detail per order. I only want to see the data for the sales order’s FIRST shipment though. How can I accomplish this without grouping by order nbr and doing a min on the shipment nbr? I still want to see the line detail and the grouping then takes this option away.Thanks!

Hello.We have a use case where we would like to send an automated email for example when a customer has not placed a new order for 90 days. We couldn't find a field in Acumatica with the customer's last order date, so a GI doesn't seem to work. Is there a solution to this without programming it?

If you want to get an updated dashboard, go to the following link: https://portal.acumatica.com/(W(3))/Main?ScreenId=SV000000&PageID=4047ebbb-3f9b-489d-b898-fc3fe9954d6a 

Hi, I modified a report but unable to save it to server. I can preview it but when I try to save to server, I get the following not helpful error.  It does not tell me which field is too long.  The report is AR641000. I was able to save the report but after some changes, it started showing the error.  The error doesn’t tell me which field is causing the error.Anyone know how to narrow down the issue and resolve it?Report designer version is 2021.115Mark  

I’m investigating the impact of a customization unintentionally modifying fields that it wasn’t supposed to in a previous version, 2018 R1, and if it is still occurring in our current version, 2019 R2. Luckily I have quite a bit of audit history to refer to. I was able to to use this query from the  Acumatica Developers blog to pull data and then parse it out into something more readable, but I’m unsure of how to validate my results to ensure that I pulled every occurrence of the modified field.I’m specifically looking for every instance where the SOLine unit price was modified by a particular screenID. Would the following query be sufficient in returning all changes to the unit price? Is there a better way to find what I’m looking for?SELECT CompanyID, BatchID, ChangeID, ScreenID, ( SELECT TOP 1 username FROM Users u WHERE PKID = UserID) as Username, ChangeDate, Operation, REPLACE(CAST(CAST(CombinedKey AS VARCHAR(MAX)) AS VARBINARY(MA

Hi everyone,I would like to know what would be the best way to isolate a value that appear in all the lines. I am working on a freight cost report to have SOPackageDetail in its relation. When I add the table, my DocAmt and FreightCost repeat itself based on the number of line in SOPackage,LineNbr. I try to use below formula to filter out but not successful:=IIF([SOPackageDetail.LineNbr]<>first(SOPackageDetail.LineNbr),0,[ARInvoice.OrigDocAmt])=IIF([SOPackageDetail.LineNbr]<>Last(SOPackageDetail.LineNbr),0,[ARInvoice.OrigDocAmt])=IIF([SOPackageDetail.LineNbr]<>MAX(SOPackageDetail.LineNbr),0,[ARInvoice.OrigDocAmt])These formula work with most cases but there are shipments that does not start with line 1 and value could not be shown. See below screen shot for reference:  I have attached the file for reference. Hopefully someone would be provide insights or directions on how this can be done. Kind regards,Calvin

Hello,I want to write reports and/or GIs to show audit trail history.Specifically, I want to show value changes, the field name, what the value was before and the new value and the user who performed the change.I would also like to be able to report lines that were deleted.Specifically, this is for projects, but the question is applicable in general.What table and fields would I use?I see an AuditHistory DAC, but it does not have USER, BEFORE VALUE, AFTER VALUE.And the ModifiedFields entry seems like an unusable jumble of stuff.

I Have joined 2 tables TSNewQCOrder and TSNewQCOrderSpecializedTest.I write the view.public PXSelectJoin<TSNewQCOrder,         InnerJoin<TSNewQCOrderSpecializedTest,             On<TSNewQCOrderSpecializedTest.qCOrderType, Equal<TSNewQCOrder.qCOrderType>,                  And<TSNewQCOrder.qCOrderNbr, Equal<TSNewQCOrderSpecializedTest.qCOrderNbr>>>>,         Where<TSNewQCOrder.qCOrderNbr, IsNotNull>, OrderBy<Desc<TSNewQCOrder.createdDateTime>>> TSSOShipmentQualityOrderView;  protected virtual IEnumerable tSSOShipmentQualityOrderView()        {            SOShipment rec = Base.Document.Current;             PXSelectBase<TSNewQCOrder> cmd = new PXSelectJoin<TSNewQCOrder, InnerJoin<TSNewQCOrderSpecializedTest,                On<TSNewQCOrderSpecializedTest.qCOrderType,              Equal<TSNewQCOrder.qCOrderType>, And<TSNewQCOrder.qCOrderNbr, Equal<TSNewQCOrderSpecializedTest.qCOrderNbr>>>>,         

I've defined a GI, checked the "Show Deleted Records" box and included the "DeletedDatabaseRecord" field in the grid. Within Acumatica, the GI shows me all records, and I can see that 11 of 23 records have been deleted. But when I query the OData feed with no filter, using the same credentials as within the Acumatica UI, it returns only the 12 “live” records (those that have not been deleted). My goal is to keep my EDW in sync with Acumatica, which means I need to capture inserts, updates, and deletes.Is there some configuration I missed?  How can I view deleted records via OData?

HiI have the BigCommerce (BC) connector.I import Sales Orders from BC using an Order Type called EO.I have EO type setup to go ONHOLD when createdI created a Business Event of type Record Inserted on a GI to remove the hold using an Import Scenario.When I create EO on SO301000 screen by hand, the BE triggers fine. Within 3 seconds.When I create EO using BC, nothing happens. The new EO appears on the GI, meaning it pics it up, but the BE never triggers.I have seen this behavior before on other functions where a transaction that’s created programmatically DOES NOT trigger a BE.Why is that?

Client has a valuation financial report where they list out in Rows the (row 1) CY Net Income (ie 2022), (row 2) last year net income (ie 2021), (row 3) year before that (ie 2020) and (row 4) 3rd prior year (ie 2019) in column1, then they have another column, column 2, which starts with the previous year (ie 2021) in row 1, then 2020 in row 2, 2019 in row 3 and 2018 in row 4.  They have a row 5 that then takes the average of those 4 years to show the change from year to year.In the row1 I have defined no offset, In row2 I have defined -1 for year, in row 3 I have defined -2 for the year and in row 4 I have defined -3 for the year offset.  For the current year in column 1 the numbers show correctly for the 4 years.  For column 2 which would start with 2021 the column is defined with -1 in the column set and because the row for the current year has no offset it looks to the column set, so it works for row 1 but for rows 3, 4 and 5 because the offset comes from the row it is showing 2021

My query was working properly until I changed my Acumatica password.  Now I am receiving an error that the credentials provided do not authenticate.  How do I update the credentials within the odata feature?

Hello,Is there functionality to default a sales order (or any other report for that matter) version based on the customer? The base requirement is to be able to dynamically change the format of the report and versioning makes the most amount of sense without creating multiple overlays within the same report.The idea would be that “Print Sales Order” would dynamically spit out the correct version without the user having to go in the report itself and select the version and then run it. Even if this needs to be a customization I would be curious if it’s possible.Thanks in advance,