Reports and Generic Inquires

Hello,We are trying to create a GI that flags if an more than one item has the same customer cross reference/ alternate IDs.  In an ideal situation - the result for the below should be 3. Results prior to groupingWe concatenated Alternate ID and Customer ID and did a count on the concatenationWe grouped the generic inquiry by AlternateID. However, the count only shows up as 1.Results after groupingIs there a way to display all line items, their alternate id, customer and counts. The expected result should look like below with an extra column towards the end called count with values 3 (in this scenario).Expected resultCould you please advise if this is achievable? 

Hello,I’m trying to get the subID (sub account ID) from the APTran connected to APPayments.This GI works fine, but for the majority of records, the bill and associated sub accounts are not displaying even though there is a bill and a sub account associated with the payment. I can’t figure out why these records are not displaying this information correctly. I’ve tried merging this information several ways. Most of the records not showing a bill were payments of the type check or voided check, but not every check or voided check is missing. Most of the debit adjustments are showing, but not every debit adjustment. Below is an example. The payment has one bill connected, and the bill has one payment. Any ideas why this would not show in the GI?one check under billsOne bill in checks and paymentsAttached I have the xml available.Thank you!

All, tearing my hair out over this. GL stuff is way above my head.Basically, we need to print out GL accounts associated with a batch. This is where it goes above my head… sometimes a batch also has an associated PO with an attached GL account (POLine.ExpenseAcctID).You can see in the first screenshot, the amazon GLTran is duplicated numerous times.In the second screen, I added a grouping, it fixed things visually, but it broke things more than fixed. I don’t think the issue is grouping but a relationship/join.Verde Valley Hardware and UPS correct; Linde and Amazon are repeatedTotals for Hardware and UPS are correct but shouldn’t be grouped, and Amazon is incorrectI’ve attached the xml if anyone could take a look… thank you!

Hello - we recently added a new GL and made that our main cash account. This has thrown off the reporting on our cash position dashboard widget. How do I add this new GL to the GI from which this widget is pulling data?  The GL account itself is set up the same as our other cash accounts, which do appear in this GI.Thank you!Some screenshots that I think are relevant:  

HiI am trying to get the last payment date for vendors on a generic inquiry.  Does anyone know where that field is stored or the best way to pull that date into a generic inquiry.Thanks!Coleen.  

Hello all,Whenever I use an equation (apologies if wrong terminology) in report writer, the formatting field doesn’t seem to affect the actual result. In this case, it will always return 4 decimal points when I try to format it to return 2. Take note of ‘Payment Applied’ - you can see it returns 4 decimal pointsWhenever I use an equation (for example, the above), it ignores the formattingI have tried =###,###.00 , =###,###.## , =######.## , =######.00 , all with and without the quotations as seen above. Any insight into this would be great!

Hi Everyone.Once again I need your assistance. I need to show the unit cost by year on the report designer.Each year needs to be on a seperate column and all the unit costs for that particuar period need to show.The structure would be similar to the screenshot attached. Is this possible. So the report will need to adjust based on the number of periods. The max periods should be around 3 to 5.So each column would be a specific period and the data in each column the unit cost of the inventory item. What I need assitsance with is showing the periods input in the parameters as columns.  

Is there a report I could run to discover what countries the materials consumed in a production order came from?  

HiI need to design a QT from scratch, it’s suppose to take the format provided below in an excel sheet.Which tools in report designer should I deploy to achieve this and more specifically draw the tables? Regards  

How can I add the ship to State to the Sales Order by Customer Report - or any sales order report? Thanks

Sub report​​​​Used filters in the sub reportAbove sub report was created to get the bank name, bank code and branch code. All the different data come under a one data field as below. Please check the below images.Sub report viewBelow image shows the DAC related to this.DACI want to assign the Account Value column data corresponding to the 4545,4546,4547,4548 and 4549 ID shown in the above image to separate variables, in order to send them to the main report. If is it possible, can I know how to accomplish my task?

Hello Everyone, I am trying to create a email template to be used when sending out an email from a payables report. I wanted to embed both the vendor’s name and a logo of our company on the report. The problem is that I cannot do both on either the notification templates screen or the report designer mail settings body section. If I use notification templates, the vendor account name does not show up as the screen I am using does not appear in the screen selection. Does anyone know if it is possible to embed an image in the formula editor?   

The Controller DB, has a widget that uses a GI for Actual Revenue vs Budget by Qtr.   It is not correct…Any Suggestions?

Our client wants to have a report that can be split into two columns in one paper page where each column has different details from different barcodesThis is from Production Order Details screen and when there is two lines that is checked for print barcode, the report must show 2 columns with different details from it.Example:Question:Is it possible to achieve this one? If yes, how?I am very new to Acumatica so it will be kind of you to answer my newbie question :)Note: Every column has different barcode so it has different details too.

Vendor 1 and Vendor 2 need to show page 1, then vendor 3 and 4 need to show page 2.but other details show same format

Hi, I’ve added supplier bank details in the supplier master GI, but bank account number is not displaying in the same format as per the supplier master payment instruction. so how we can solve this.I would really appreciate, if someone can help me on this.         

I’m trying to build two ‘alternate’ sections:Limited space that only shows “Continued” (if the details extend beyond one page) Visible expr: =([PageIndex]<[PageCount]-1) Larger space that shows the full subtotals (intentionally the second-to-last page for terms & conditions at the end) =([PageIndex]=[PageCount]-1) If I apply the visible expressions to the group, it does not appear on any page.If I apply the visible expressions to the individual fields, it appears as-anticipated (though this defeats the purpose). Additional information:The ProcessOrder is Always in both scenarios The PageIndex and PageCount are definitely calculating in both scenarios Both are GroupFooters that are setup to PrintOnEveryPage (with the above exceptions, if I can get them to work)Any ideas? Suggestions?

Doing some editing work on stock Dunning Letter Form, but can’t seem to figure out how to manipulate the ARDunningLetter.Deadline field that is showing up on my Dunning Letter. Looks like it is adding 3 Days to ARDunningLetter.DunningLetterDate, but not sure where that additional 3 day setting would be coming from? 

Trying to modify the Sales Order GI so it calculates partial price (ignoring or subtracting one of the line items on the Sales Orders). Is this possible? Example:SO100038 - Total Order Price of $10,000Item A $2000 Item B $5000 Item C $3000 In my GI, I want to display an adjusted price column that is calculated by taking total Order Price - Item C Price resulting in an adjusted price of $7,000 per this example. I can perform joins to bring in the SOLine detail but not sure how I would be able to subtract the correct line item?  Any thoughts?  

How can we combine all the sales order report (so641010) within a specific date range. I want to merge so641010 report based on a date range on order date. If five order are placed on a particular date I want all 5 report for individual sales order (so641010) to be combined in a single one. Any ideas how it can be achieved ?Thanks

Hi, is it possible to show attached line level record notes and files in the generic inquiry? We have a custom form with a simple form to record data. Even though a note is already attached it’s not shown in the generic inquiry end. Please let me know how I can show the attached note in the generic inquiry as well. 

I am trying to create a comparative balance sheet report and the columns currently have the year (2023, 2022) for the headers.  The report is ran as of 5/31/23 so I would like the column headers to be 5/31/23 and 5/31/22.  How do I change the column set format so the date pulls as of the last day of the period?  Thank you!   

Hello,I have made a new generic inquiry to help with warehouse staff, the last screen they wanted was to show shipments that have been sitting for more than 24 hours. Easy enough since I added a a calculated column to see how long a shipment has been waiting. See image bellow.I then made a new filter and added the new condition: However the result I get is this: And there is 1 shipment that is over 24 hours old, and in the bottom left hand corner we can see that Acumatica is finding it, just not showing it.What is also weird is that if I change the filter query to “Less than” 24 hours, it does show every shipment that is less than 24 hours old! So I dont know why “Greater than” and “Greater than or equal to” is broken. Any advice would be greatly appreciated,Thank you. 

Hi,If someone is having trouble using the attributes values in the inventoryitem table (inventoryitem.AttributeCreated), you could use the CSanswers and CSattibutes tables to get them. Greetings! :)

Hello all,I am working on a report in which I want to display receivables based on the week ending + one week.I am using this formula for the column heading=Concat( 'Week Ending ', DateAdd( [@WeekEnd], 'w', 1 ) )It does exactly what I need, however it displays the time stamp at the end of the date and I would like to remove this. What is the best way to do this? I tried using Left String to cut off after the date, but depending on whether the day and month have one digit or two this would be either 8, 9, or 10 total characters so it’s not a viable solution.=Concat( 'Week Ending ', Left( DateAdd( [@WeekEnd], 'w', 1 ) ,8)) How can I format the date to only include dd/mm/yyyy format without the time stamp?Thanks in advance,Helen