Reports and Generic Inquires

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.

Howdy all,I’m looking for support on showing some additional customer aging data on the Invoice Form (AR641000).Ideally I’d show these fields:Customer Current Balance Invoice Amount Customer Current Balance - Invoice Amount as “Revised Balance” I *think* what I want to do is add a subreport to the invoice form (AR641000) that references the Customer Documents Total filed from the AR Balance by Customer Report (ar632500). But I’m not clear how to access that exact field value:But I’m not sure what the best way to proceed is in order to recreate the field “$CustomerBal” and add it to the Invoice as a subreport.Or, should I try and do the linking within the current report so I can have “$CustomerBal” interact with the invoice total field? Would be curious to hear what others would do.Thanks for the ideas! 

I am very new to Acumatica, I have inherited a lot of reports in which I want to update the formatting. One thing I’m stuck on is how the coding works on formatting the date. I changed the formula to this (see below). It currently reads as: I want to change the format to include a “/” or a “-” inbetween the month and Year. Another thing I want to do is to just have the last 2 digits of the year if possible so it can look like this “08-22” or at least be able to read as “08-2022”. Any help is greatly appreciated.  

I want to be able to put a GI up on a big screen in the office and not have the menu and headers showing. for example, if i wanted to hide all that i have scribbled on red below, and just keep the main table, is there a way?  

Hi Everyone,Good afternoon,I am having an issue regarding Sales Order Forms in Acumatica. It duplicates all the entries upon running the Sales Order Forms.Please see attached photo for your reference: Sales Order Screen :Upon Clicking of Print Sales Order : It duplicates the line item of the Sales Order Forms; Upon checking of Order Types Maintenance :Here’s the Result :But while searching on the screen of order types it gives me duplicate (Same Order Type) Maybe, this is the cause of the issue in sales order forms.May we ask on how to eliminate the duplicate order types even if it not available in Order Types Maintenance ? or Is there’s a way to correct the Sales Order Form in Report Designer? Thank you !!

Hello,I need to write a report (or GI) that allows me to report if there are more than 1 sales order in Acumatica with the same “Customer Order Nbr.”I have done similar things a million times in other report writers, but I do not know of a method to accomplish this with Acumatica’s report writer.So I need to run through the SO’s and report Acumatica sales orders and their associated “Customer Order Nbr”, but only if multiple sales orders have the same “Customer Order Nbr.”.  I’ve used functions like running totals that reset on the change of a field value etc… using other reporting tools, but I don’t know how to accomplish this here.Any great ideas on Friday afternoon in AcumaticaLand!!!???Thanks!!!!

Hi,We are going to have clients log into to Acumatica. In Report Inventory Valuation - IN615500 for sales team just wanna see their own stock without any information Total Cost of the items.Fyi, Report Inventory Valuation with total cost field will use to others user/user roles such as admin, purchase teams.Is it possible setting restriction total cost on these report by specific user/roles?Note : Client perhaps not any customization report for this implementation phase 1.

We just set up the Twilio SMS Integration.  We are on Acumatica 2021 R2.  If Acumatica sends a SMS via Twilio, and the receipient replies, how / where do I see the reply? 

We want to have a GI that will trigger notification via a Business event if a project is assigned to more than one active Restriction Group.Here are the table Relations:However, when the project is in two or more groups, it’s shown in the GI results with no groups.Please help to get it to work so that if there is more than one group that a project is assigned to, the GI shows all of them in the results. Thank you!

Hi Team,Is there any possibility to show PDF document content on a report.If it is possible can you suggest the best possible way to achieve it please. We are working with 21R2 - Build 21.208.0032. Thank you in advance !

I have a simple GI that pulls inventory IDs, price classes and prices.When I load this GI in Excel (via an OData link), the results sometimes contain serious errors. Many inventory IDs are repeated, and some IDs don’t show up at all. Strangely, these errors don’t occur every time I load the data. If the data contains errors, I can often get the correct data by refreshing 4-5 times. If I run the GI in Acumatica and export to Excel, the data is always correct. Any ideas on what could be causing this?