Reports and Generic Inquires

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,

Hello there,We are developing custom report using 21 R2 release, and the requirement is to print a PDF attachment after printing the custom report.The PDF is attached as a file attachment either under STOCK ITEM or KIT Assembly.Any idea or any one has done this kind.

Hi, I have a requirement where one field must be printed with some amount of line spacing between them, if the field length is long and uses many lines. Is there an option in Acumatica Report Designer to increase line spacing or any other workaround to achieve space between according to the layout would be appreciated. Thank you.Line Spacing Must Increase for the Field Value Printed 

I need to display on a custom GI the start and end log TIME(from the Appointment screen FS300200).As date-time fields are stored in UTC format.Therefore I use the next well known approach to calculate hour’s part into local time zone:Hour ( DateAdd ( [FSLog.DateTimeBegin], 'h', DateDiff ( 'h', NowUTC(), Now() ) )) But, the problem.How to manage winter/summer saving time periods?The customer is from Singapore, and Singapore doesn't have saving time period.With this Now/NowUTC approach for records from winter period a time is provided with one extra hour.

Hi everyone!  New to Acumatica here, learning GIs, copying, processing, etc.  I’m on my 2nd cycle through the courses, and I need to know what the following warning means:“The correlation name 'InnerQuery_BAccountR_Customer' is specified multiple times in a FROM clause.”I have removed, to the best of my knowledge, all traces of Relations & Conditions that would trigger this.Any help would be appreciated.Thanks, everyone.

Hello,      I have a need to find out the top 10 items with the most sales qty drops comparing to last month.It could be done by sum up the figures group by item this month---------Result 1And sum up the figures group by item last month-------Result 2Then, I can do caculate in excel by comparing result 1 and result 2.       I am just wondering is there any way that I can get the final result directly in Acumatica with GI or Report?       Thank you in advance.

New to Acumatica. My question is why is my Depreciation calculation not reducing the asset cost for salvage value before calculating depreciation.  The report reads asset cost = depreciable value on an asset with a declared salvage value.