Reports and Generic Inquires

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?

 Hi everyone, I am wondering what is the right way to sort Kit in Report Designer. I have build a report based on below: I have add below sort filter:When I run the report, the KitHeaderID is not sort numerical or Alphabetically and is all over the place:I went and look at Kit Specification and it would show the Kit in numerical order. Does anyone have an idea what I need to do in order to have it sort numerically?Kind regards,Calvin

Hello,     The requirement is as below.      Supposing I am designing a report with report designer, and this report would show all the items ordered in today, and group by itemID.  I would like to show the lines sorting by orderQty of each line item descending. Here, the order qty of each line is a calculated sum value, I did not find a way to sort by the sum value.     Can someone give me a hand on this requirement?     I know there is a workround, if I export this report into excel, and it is very easy to sort by the order qty field. However, I still would like to know is it possible to just display the lines in the sequence of sum value directly.

Hi everybody,I am currently working on a Generic Inquiry that needs to give out the latest location after a transfer.I read something about Self-Joins which doesn’t seem to be working for the INTran object.Basically, I need to evaluate if the ToLocationID is the one from the latest transaction and display this location. Does anybody know a feasible solution?The client is currently operating in 2022 R1.

I’m trying to understand the difference between the ARTran table and the INTran table and when to use either. They seem to have a lot of similar data when it comes to the TranAmt, TranCost, etc., but I do notice some discrepancies between the two.Any help or insight is appreciated.

I am in the process of trying to add the expense subaccount from the AP bill - using the APTran table - onto the AP Aging Period Sensitive report. Has anyone been successful in doing this?I have added the tables APTran  and POReceiptLine - which hold all the information I need. The APTran has the AP bill line detail information and the POReceiptLine has the account number and subaccount if the bill is linked to a PO.I have most of the information pulling on BUT it is pulling each line on the AP bill twice. I realize that something is wrong with my relationships and/or my grouping since the RefNbr is a link between multiple tables (APRegister, APInvoice, APTran,..). I am at a loss on how to correct this since it doesn’t seem like I can remove any of these tables or modify the relationships.Any help would be appreciated! Thank you.

We use prefixes on our Stock Items to indicate the condition of products. I’m trying to use the LEFT function on the ARTran.InventoryID field in a GI to return the first 3 characters of the InventoryID but it is instead returning 3 numbers.I’ve tried using all the conversion functions and they all return a string of numbers from the ARTran.InventoryID field.Any suggestions?

I export the report designate to excel, but i find the columns and rows in unordered file so that are merging.I need a solution, thanks in advance 

Hello,I see a few tables in the DB for GI's.  None of them seem to tell me when the GI was last run and by Whom.   I only see created by and modified by. Does anyone know of how to get this info?We have some legacy GI's that need to get cleaned up and I was hoping to use this as a starting point. Cheers,k2

Hi:Our client is used to filter his legacy Crystal Reports using the condition [CustID] Not Equal 9*.Crystal generates the correct SQL clause as Where [CustID] Not Like ‘9%’.What would be the equivalent syntax in Report Designer’s Additional Filtering Conditions?We don’t have an option like “Does Not Start With” I could generate the correct filter using Parameters, but the client may want to use multiple ‘Not equal’, so it would be ideal to use the Additional Filtering Conditions. Thanks

I am attempting to create our own subcontract form in report designer. I am using the ABC Contractor -Subcontractor Agreement (CSA). Can anyone help me line up the columns so I can have each paragraph have a title and then the next paragraphs title will only display when the next paragraph appears. Title column is on left & Paragraphs are on right (there are blank text boxes between each row)This appears not as expected and instead repeats the Left (title) column in the first paragraph. Instead of 1 title / per paragraph.See left column, repeats 4 title rows per 1 paragraph.Any ideas how to align the title with its corresponding paragraph?? 

Hello,        I have a requirement to send a report to vendor in excel format using Email.I know this can be done by report designer and “report template”. But, right now, this report is simply wrote in GI. So I am asking is there a way to directly prepare this excel report and send by email.      I also know a way to prepare the excel report in automation by using export scenario, however, in this way, It seems I can just upload this prepared file using SFTP or FTP, while our vendor insisted receiving the report by email.      So can anybody help me?

Is this possible? I know there’s an option to attach a report, but we would like the ability to send an email on a schedule with an excel version of a generic inquiry. The inquiry has a fair amount of columns that wouldn’t fit on the report format, so it’s not an option to create a report version of the inquiry. Thanks!

Hi All,We are currently creating a report in Acumatica’s Report designer that is referring to a User-defined Field.The data in this User-defined field is not updating into the report when it is run and printed. Does anybody if there is a certain setting that might be restricting this or any other issue I don’t know of? Thank you!

Is there a way in which one can round the billable time from the CRPMTimeActivity table in a generic inquiry? In the Case Class the billable time is rounded by 0:05. So if the billable time is 83 min, I would need the GI to show 85 min or where the billable time is 57min, it should show 60min.

Hello,for one of our customers we created a very customized docket that can be generated using a run report action in a sales order and gets used to find parts etc. in the warehouse.We now would like to attach this report to the so line files.Here:I know i can add a parameter to the Report to filter for a individual line nbr, but we can’t figure how you would go on and generate all pdfs for all line nbrs where the article has a price > 0 and attach these to the individual corresponding so line.The Report that would be generated would only contain information from line 2 for example and should be attached to line 2, proceed to line 3 where it only contains information from line 3 etc.Is there a method we can use?greetings