Reports and Generic Inquires

I created a user defined field [Location.AttributeCHILDACCT] that is of type String ( nvarchar)In my generic inquiry if [Location.AttributeCHILDACCT] is blank then I want it to pull in the Project Customer [PMProject.CustomerID] otherwise I want it to pull the string in [Location.AttributeCHILDACCT]Here is my formula: =iif(([Location.AttributeCHILDACCT]=' '),[PMProject.CustomerID],[Location.AttributeCHILDACCT]) I keep getting the error message below.  The C002452 is the [Location.AttributeCHILDACCT] field. The issue is definitely with my condition in the iif statement. Are you able to set a string to a condition of blank in a GI?  I think I just don’t have the correct syntax for the empty condition.  

Hello,  Does anyone know how to convert a decimal values to inches in report designer?Example: Customer orders 12.50 feet in ACU on the sales order screen but want the value displayed on the report as 12ft 6inches.  Thanks

If you all have any good tried and true examples for Customer Statement forms in Acumatica please post them here. We are a pool company who does Sales, Field Service, Equipment Service Contracts, and Projects (New Pools and Renovations). Our Customers are constantly complaining about our Statement Form. We are currently using a modified version of the “Open Item” Customer Statement. We want to be able to show a clear snapshot of everything that occurred for a Customer on one form. Both for the Month and a specified period of time. Any suggestions?

Is there a report to use to pull vendors not used within a date range?

I’m currently creating a generic inquiry to create a CSV upload report to a third party portal. CHEP (specially treated pallets that are ‘rented’ by our customers). Every week we upload a report to the third party with a count of these pallets with pertinent info - such as which customer and their CHEP account. This also includes quantity. The conundrum I have is that there are 8 item codes. One item code is for a single pallet - so this quantity is 1 to 1. The other 7 item codes are 1 pallet per unit - the unit count is different on them. Example: Customer orders 1 unit of CHEP - qty is 40. They also order RPCs (reusable plastic containers) that are stacked onto 1 CHEP pallet. The customer orders 10 units of the RPCs, so the qty of CHEP pallets is 10. Their total quantity is 50.Where I’m having the issue is coming up with the formula in the results grid to report the quantity of the CHEP for the invoice. I am using ARTran.Qty. I need to translate the unit counts of the 7 item codes to

I have an report in Acumatica that I have customized and when I run the report withing the Acumatica software the formatting is exactly as I designed it but as soon as I print it the formatting is lost. I am not sure if there is a setting that I need to change in order for the formatting to stay when the report is acually printed. Here is what the section looks like in the report writer:  Here is what it looks like when I run the report in the system: But as soon as I print it the red past due box and the yellow highlighting of past due amount is gone as is the formatting of my aging fields as shown below:  Am I missing a setting or something or is the report designer really limited in what can be formatted when the report is actually printed. Any help on this would be greatly appreciated. 

In report designer, is there a way to have a where clause of sorts on a parameter that is searchable?We have a report that sales reps run for their customer by selecting their customer as the parameter. I would like to limit the searchable parameter to only show them their customers instead of every customer.

Can you have the menu which appears on the top right of most screens, appear in the side panel view. These above menu options do not appear when screens like an invoices, purchase orders are viewed in the side panel. 

Hi Acu Community,Is there documentation on creating a Generic Inquiry showing the List of Pending for Approval, specifically for Workgroup Approval?Thank you!

Hi. Is there any Rest API in Acumatica that returns the AP Vendor Balance? This value is not available in the vendor table so not sure how to expose via a GI and can’t find a Rest API that has it.  Thanks!

Hi Everyone,Is it possible to print: 1x pick ticket, 1x packing list, 2x Shipment Confirmation , 2x Label per order ?Thank youJennifer 

We recently upgraded from 23R2 to 24R2. It has been a very smooth process with almost zero issues.However, users have mentioned that on the Lot/Serial Numbers report, when they start typing the warehouse name (SiteCD) in the report parameter, the value no longer auto-completes.To select a value, you have to click an extra time to open the selector, which functions as usual.I’ve tried changing the schema field for the parameter in report designer, but it doesn’t make a difference. Does anyone have any ideas? 

Hi community,I have an expression that displays the orderDate and percentage data as on following screenshot. However, I want to hide the time part of both the OrderDate and DueDate, only showing the date. How can I achieve this?= 'Zahlbar bis ' + Format(DateAdd(CDate([SOOrder.OrderDate]), 'd', IIf([Terms.DayDisc] Is Null, 0, [Terms.DayDisc])), 'dd.MM.yyyy') + ' (mit ' + Format(CDbl(IIf([Terms.DiscPercent] Is Null, 0, [Terms.DiscPercent])), '0.00') + ' % Skonto = ' + Format(CDbl(IIf($TotWithTax Is Null, 0,     $TotWithTax * IIf([Terms.DiscPercent] Is Null, 0, [Terms.DiscPercent]) / 100)), '#0.00') +' EUR zu zahlen: ' + Format(CDbl(IIf($TotWithTax Is Null, 0,     $TotWithTax - ($TotWithTax * IIf([Terms.DiscPercent] Is Null, 0, [Terms.DiscPercent]) / 100))), '#0.00') + ' EUR) bis zum ' + Format(    DateAdd(        CDate([SOOrder.OrderDate]),        'd',        IIf(            [Terms.DayDue00] Is Null,            IIf([Terms.DayDue01] Is Null, 0, [Terms.DayDue01]),            [Terms.DayDue

I’m using a join between ARInvoice & ARTran as well pulling data from FSAppointment. I need ARTran for credit memos detail lines because it wont always match the appointment detail lines. When activated ARInovice-ARTran join causes a duplicate of how ever many artran lines there are. Any workarounds? I cant group by ARTran ref number for some reason as it removes my billable Labour totals. For context RefNBR 008452 in the last picture should just be the first 3 lines but its doing 3x the return because there is 3 lines of ARTrans in the document. 

Is there anyone can help me to display 5 line items on the first page of the report then sixth or next line item will display on the next page of the report

How does one create a fully new report? I’m making a tweaked version of an existing report, but need to create a new report on the site, and I’m not sure how.Thanks for any help.

Is there a way in a Generic Inquiry to determine the which Quarter of a year a date falls in.I am working on a Generic Inquiry where I would like to display the Quarter of the year the SOInvoice.DoDate falls in.Thank you!

I need help, having difficulty adding multiple line item attributes to the AM production ticket report in report designer. Does anyone have any tips or a report they can share with similiar item attributes added? I truly appreciate any help or guidance you can offer.

I recently had the task of creating a report with a two-column layout and was surprised at the lack of guidance available, so I am posting my results for the next person to find more easily. Hiding alternating rows formula: The key to creating multiple columns pull more than one record of data into a section and then to toggle the visibility of that section by a formula that uses the modulus (% operator) of the row number.First create a variable for the detail section, $RowNbr, with a value of =$RowNbr + 1. If you have groupings for your data, then also set that $RowNbr variable in the parent group to have a value of 0, which will reset the counter in each grouping.Then for the VisibleExpr of that detail section, set the value to =$RowNbr % 2 = 1 to look something like this:Record 1          Record 2    (visible)$Record 2          Record 3   (hidden)Record 3          Record 4    (visible)Record 4         Record 5   (hidden)… Displaying multiple records in one section with NEXT():One ap

hi, All. I am working on export scenario and data provider is GI. I am trying to update sales order fields using data provider GI. But Export by scenario is not showing any records. I am trying to put/update/rewrite tax tab in sales order screen.Can some one please guide me on this 

Hi! Does anyone know if you can use substitution lists with GIs? I’m trying to use them instead of using a long IF formula. This is what it looks like and the error:   

Hello,  Does anyone know of a way to parse data based on a unique identifier in report designer. For example: In one field of a report, Order Description, we want to parse the data using report designer where there is a unique identifier. When the description has a ; we want to add a line break on the report to put the value after the ; on a second row.  Order Description: Ship This Today; UPS GroundWe want the report to display the values as;Line 1: “Ship This Today” (Line Break based on the unique identifier ; )Line 2: “UPS Ground” 

Hi everyone, I think I have thoroughly researched about the topic but cannot seem to find a suitable solution. Anyway, our order confirmation report prints an extra page. The solutions I found are:To make PrintEmpty and PrintForEmptyGroup set to False.  Papersize to LetterTo no avail, I am still getting extra page. Is there a way to “hard set” how many line items are printed before it goes to the next page? As you can see in the screenshot, there are 26 lines items (for the product description field in the line item I changed the properties to CanGrow/ CanShrink/ CanSplit so that the entire description is printed. In this casem line 26 does not have a long description hence in my point of view the footer should print in the first page hence second page should not be printed unnecessarily.I have attached the report in case you would need reference. Thank you so much for your time. (Kindly take note I have removed some of the fields for privacy but the spacing is needed hence no section

Is there any way to do a contains or starts with in an expression? Google gave me some suggestions like using ‘LIKE’ or StartsWith([Field], ‘value’) but acumatica says its not supported. I am trying to do a parameter for IIF the location ID contains or starts with a Q, then Remove the Q, and if not, leave it as is.  Like this:=IIf( ([INLotSerialStatus.LocationID] LIKE 'Q'), ([INLotSerialStatus.LocationID] - 'Q'), [INLotSerialStatus.LocationID] ) Or this:=IIf( (StartsWith([INLotSerialStatus.LocationID], 'Q')), ([INLotSerialStatus.LocationID] - 'Q'), [INLotSerialStatus.LocationID] ) However, I cannot seem to find a way to do a contains expression. Is this possible?

Hello,  I am trying to create a report to return SO lines related to production orders.  The AM Tran table contains a custom field which flags each item that needs to be printed on the report so I need that table in the report.  That table is causing duplications based on production transactions related to the items.  I was able to get rid of duplicates by adding a condition to join on lot/serials and a grouping on AM Batch number, but our client wants the grouping on SOLine.InventoryID, Alt ID, and a custom field on the SO Line.  When I changed the groupings it began duplicating the lines when the Lot/Serial was the same but the items appear on different labor tickets.  Does anyone have any suggestions on what I can add to the join or grouping to get rid of duplicates? I am unable to share the XML due to sensitive data.