Reports and Generic Inquires

Reports, GIs, Dashboards, Pivots

  • 1,421 Topics
  • 5,560 Replies

1421 Topics

Laura02
Captain II
Laura02Captain II
posted in Reports and Generic Inquires

How to Mass Update Records with Generic Inquiry

Do you have a new Cash Account, and need to add the new Cash Account to a large number of Open AP Bills? Are you adding Sales Tax module, and you need to apply a new Tax Zone to Customers based on their Zip Codes? Are you changing Salespeople to different zones or products, and you need to update existing Default Salesperson for your Customers or existing Product Manager on your Inventory Items?Mass Update Records may help you!In this example, a company opened a new bank account and needs to update the Cash Account on 3000 open AP Bills. Mass Update is one way to update open bills. (Import Scenario is another way that is not covered in this post.)Add the Cash Account field that needs to be changed to the Results tab of Generic Inquiry:Add Cash Account to AP Bills InquiryOn Entry Point tab of Generic Inquiry screen, Enable Mass Record Updates.On Mass Update Fields tab of Generic Inquiry, add the field to be updated; in this case, Cash Account.Return to your newly updated Generic Inquiry

1
Chris Hackett
Community Manager
1 month ago
J
Freshman I
jsherrillFreshman I
asked in Reports and Generic Inquires

Finding contacts with override set but no address

I have a number of customers with a contact that has Override set on the contact’s address, and the address itself is blank.  I want to unset the Override value so they inherit the customer address.  I’m trying to create a generic inquiry that looks for contacts that match this, but Contact.OverrideAddress never returns a value, even on records that I know are set.  Looking at the DAC, it says “nonexistent in DB” so I don’t know how to find it.  Where do I search for that override setting?

3
Laura02
Captain II
5 hours ago
S
Jr Varsity II
suemackeown13Jr Varsity II
asked in Reports and Generic Inquires

How to navigate to a printed form from a report created in Report Designer

Is there a way to navigate from a field on a printed report to another printed form? For example, we have the AR Aged Period Sensitive report and would like to be able to click on the reference number of a document which then would open up the printed invoice. Currently, the invoice screen is displayed when clicking on the reference number, and then one still needs to print the form from this screen. Is it possible to navigate directly to the printed AR641000 form, skipping the invoice screen?Many thanks. 

5
S
Jr Varsity II
14 hours ago
N
Freshman II
nhatnghetinhFreshman II
asked in Reports and Generic Inquires

How to display only the first page when pressing “RUN REPORT”?

Dear Team,Let's say the report has multiple pages. How to display only the first page when pressing “RUN REPORT”?  Best Regards,NNT

7
aaghaei
Captain II
14 hours ago
S
Freshman I
sathish80Freshman I
posted in Reports and Generic Inquires

Report Sorting Not Working With Group

Dear All,            Here I used Sorting But Not working, With Group can sorting or not please help me to solve this issue, I Want to sorting Red Color Highlighted, Grouping yellow color text, I want sorting as following ID Ex:-4465,4466,4467, 4468,4469,4470 and 4471,4472,4473….etc 1.Quality of Product/Service 2.Quotation/Piece 3.Delivery Lead Time 4.Payment Term like this I want to SortSorting I used above Red Color Highlighted ID 

2
Robert Sternberg
Pro III
21 hours ago
estebanperalta54
Pro II
estebanperalta54Pro II
asked in Reports and Generic Inquires

Formula error on generic inquiry

Hi! I’m using the formula editor on generic inquiry for this formula: =IIf( ([POReceipt.Status] = 'R' And [INRegister.Status] = 'R') And  ([POReceipt.OrderQty] = sum([INRegister.TotalQty])), 'True', 'False' )It validates correctly but when I run it, it returns this error: Column 'POReceipt.Status' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.Column 'INRegister.Status' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.Column 'POReceipt.OrderQty' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Any ideas how to troubleshoot? Attached is GI and this field has the formula:  

2
Robert Sternberg
Pro III
21 hours ago
L
Captain I
ltussing03Captain I
asked in Reports and Generic Inquires

Subreport linkage to main report

Hello,I am creating my first subreport and have a very basic question.  My subreport works.  I have no selection criteria or parameters.  It just spits out all the data in the table.  Only a few records in it.However, I want the subreport to only show records relevent to the main report when I place it on there.Specifically, this is to print relevant discounts in ARInvoiceDiscountDetail when printing an invoice.So I assume I have to create a parameter on my subreport, and pass the invoice ID to it from the main report so I only get the discount records associated with the invoice being printed.Like I said, this is my first one using the reporting tool, so I’d appreciate it if someone could provide detailed instructions on this?  I have viewed videos etc and they seem to skip over this basic linkage.Thanks!

3
M
Freshman I
1 day ago
H
Freshman I
hvanorstrandFreshman I
asked in Reports and Generic Inquires

Purchase Order Report, by Project#

Is there a standard report that would allow me to see all Purchase Orders that have been linked to a particular project number? 

5
alexk95
Freshman II
2 days ago
T
Freshman I
tamiholmes54Freshman I
asked in Reports and Generic Inquires

Email Templates

Does anyone know if there is a specific setting so that the Email Template works the same on invoice screen from Actions > Email vs Actions > Print and then Email from the Invoice. I have set the template on the invoice in Report Writer and have the same invoice template set up on client and project and AR preferences. The Actions > Print and then email template is showing the DAC Labels instead of the DAC values. I have another tenant that works fine and the settings are exactly the same between, but this is a new template I have created and it just seems like it is a bug. I know I can build out the body, email, etc in the Report Designer and I have done that for some of the fields as simpler, but it seems I should be able to manager it in the Email Template.  First screen print is what is received via email (showing DAC Labels). Second screen print is the template itself. When on template I can click on Preview and it looks fine. Note when invoice is sent out via Actions &gt

0
T
Freshman I
3 days ago
T
Freshman I
tveldFreshman I
asked in Reports and Generic Inquires

Adding POOrder to Journal Transactions for Accounts Report

Community,I am attempting to add the table POOrder to the Journal Transactions for Account report in Report Writer.  The field that I have been asked to pull in is the POOrder.OrderDesc field. My struggle is finding the right way to tie the table in with relationships.This is the current relationship set up: I have looked through the DACs and have not been able to find a way to link the POOrder table to any of these other tables.  Does anyone have any ideas on how to make this work?Thank you in advance for any help!  

3
T
Freshman I
3 days ago
L
Freshman II
lwang13Freshman II
asked in Reports and Generic Inquires

Set Sorting by one of Series from Dashboard Chart

Hi everyone, I created a dashboard chart to show top 10 customers we billed this year vs. last year. The underlying GI is simply using the ARInvoice table to pull out all invoices with positive number and credit memos with negative numbers without grouping. I then added a new column using the Year () formula with DocDate to get the Year field. The widget settings are below.However, the result seems to sort by the sum of this year and last year total. So it looks not perfect for our client.Is there a way to setup the sorting by 2023? Please help! 

0
L
Freshman II
3 days ago
L
Freshman II
lwang13Freshman II
asked in Reports and Generic Inquires

Top 10 Customers This Year vs. Last Year

I am working on creating a dashboard chart to show top 10 customers we billed this year vs. last year. The underlying GI is simply using the ARInvoice table to pull out all invoices with positive number and credit memos with negative numbers without grouping. I then added a new column using the Year () formula with DocDate to get the Year field. However, when I put the “year” as series, I got error. Can someone help me with it?I also tried to link the ARInvoice table with MasterFinPeriod table to use FinYear field, but I got the same error for the dashboard chart.

8
L
Freshman II
3 days ago
J
Varsity I
jeffgrammer69Varsity I
asked in Reports and Generic Inquires

How to embed SUM function in an IIF statement of a GI condition

I am trying to use the following statement of a Data Field in the Results of a GI.=iif([PrevPDO.EffectiveDate] >= '2023/06/24',[EmployeePDOHours.PDOHours]-[PrevPDO.HoursUsed],[EmployeePDOHours.PDOHours]-SUM(Iif([PMTask.TaskCD] = '10',[PMTimeActivity.TimeSpent]/60,0.00)) + [EmployeePDOHours.HoursUsed] )When I run the GI it errors out with a GROUP BY clause error.However, when I take out the SUM(Iif([PMTask.TaskCD] = '10',[PMTimeActivity.TimeSpent]/60,0.00)), I can run the GI without an error, but it does not calculate what I want as I need to include the SUM of the time activity based on that Task ID.    I can put that SUM(Iif([PMTask.TaskCD] = '10',[PMTimeActivity.TimeSpent]/60,0.00)) into another GI Results row and it returns the correct value, so it IS a correct forumla.  It simply does not want to work inside of that other IIF statement.  Is there something I can do to make this work?(If there is even a way to create a GI variable with that SUM value and then referenced in the II

8
Robert Sternberg
Pro III
3 days ago
A
Freshman I
abrilromero06Freshman I
asked in Reports and Generic Inquires

Running Balance in Reports Designer

Hi Everyone, Is it possible to create this scenario in Report Designer? Upon reading some articles creating a running balance in GI is not possible.   Where in the Depreciation stop if it identifies a certain value? What reports can I adjust or revise if ever.   

4
Chris Hackett
Community Manager
4 days ago
A
Freshman I
aaronsilberFreshman I
asked in Reports and Generic Inquires

How to use more than four decimals in a formula

I added the following formula to a GI: =[CSAnswers.Value]*[POReceiptLine.ExtCost]The value of CSAnswers.Value for one of the records is 0.204714, and the POReceiptLine.ExtCost for that record is 11,117.76. The value I get in the cell with the formula is 2,275.805472.The product of 0.204714*11,117.76 is 2275.96112064.After closer examination I realized that the formula is only using the first four decimals of CSAnswers.Value. So the value in the cell with the formula is the product of 0.2047*11,117.76 which is indeed 2275.805472.How do I get the formula to calculate the value using the additional decimals? 

4
A
Freshman I
4 days ago
R
Varsity I
raphrfVarsity I
asked in Reports and Generic Inquires

PDF export of Report - Missing information

Hi,For some subreports in my main report : In acumatica preview i have 25 rows , it’s fine If I export it in excel, i have 25 rows, so it’s fine too If I export it in PDF , its seem to be cropted , i have 21 row. What can be my problem?Thanks!

3
lauraj46
Captain II
4 days ago
J
Freshman I
JamieAndersonFreshman I
asked in Reports and Generic Inquires

How to add Ship To Contact “Attention” field to invoice with multiple sales orders?

Hi,We have multiple sales orders from a customer onto a single invoice.  We have the line items for each sales order grouped together on the invoice and subtotaled.  We would like to add the ‘Attention’ field from the Ship-To contact tab to the invoice per each sales order grouping.  Using the report designer, we have been able to add the this field, however it only pulls the information from the first sales order on the invoice, so it’s the same name for all of the sales order subtotals.  How do we get this to pull for each of the additional sales orders that would be on the invoice? Thank you,Jamie.

0
J
Freshman I
4 days ago
F
Jr Varsity II
fpantelidakis65Jr Varsity II
asked in Reports and Generic Inquires

Show current A/R balance on payment and applications screen AR3020PL

Hi, I have a client that wants to show the current A/R balance owed on the Payments and Applications screen. I am trying to modify the GI but keep on getting errors. What table do I need to join  to show the current A/R balance on the Payments and Applications screen. Any help would be appreciated. Thanks,Frances  

4
F
Jr Varsity II
4 days ago
R
Captain II
ray20Captain II
asked in Reports and Generic Inquires

Where is the locales translation be stored, can report use it directly?

Hello,        Does someone know how can I use the translation directly on report?          I mean the user does NOT have to select the run with locales,             Every time, he run the report by default, the item description will display both Chinese and English

3
Chris Hackett
Community Manager
4 days ago
J
jsfink
asked in Reports and Generic Inquires

Expression Operator Other "In"

Hello all.I’m very new to Acumatica. I’m wanting to evaluate the sales order line item’s line type to determine if the line is a stock or non-stock inventory item to perform various sums.How do I best determine if the sales order line item is stock or non-stock? I’m currently evaluating SOLine.LineType. My report displays “Goods for Inventory” or “Non-Inventory Goods”, yet my formula “=IIf( [SOLine.LineType] = 'Goods for Inventory', 'Y', 'N')” displays “N” for every record.In the screenshot, I’m actually wanting to achieve the commented section.EditMy apologies, I submitted and should have changed the subject. Editing doesn’t allow me to change the subject.Thanks in advance for any and all assistance.

1
E
Varsity I
5 days ago
S
Freshman I
sathish80Freshman I
asked in Reports and Generic Inquires

Report Date Parameter Can Set Min Max

Hi Everyone, Report Parameter Can Set Today Date Value as Max Date, Can Disable From Tomorrow date, please help me to solve this issue  

6
Chris Hackett
Community Manager
5 days ago
M
Jr Varsity II
martinmaJr Varsity II
asked in Reports and Generic Inquires

Report Designer Bugs - Formula InStr (Acumatica 2021 R2)

Hi there, I think this is a bug too that the formula of “InStr” return the different value with G.I. In G.I., I use “InStr” formula to find the text position and return 0 if it dosen’t exist.  It is correct.  However when I use “InStr” formula in Report Designer, it return -1 if the value dosen’t exist.  It shouldn’t be correct. Is anyone can give corrective advise or point out what is the problem?   Appreciate & thank you in advance.

3
Chris Hackett
Community Manager
5 days ago
D
Freshman I
DelFreshman I
asked in Reports and Generic Inquires

Pulling base cost for a bundled item

I have a generic inquiry that shows the average cost of each finish good item.Where I am running into an issue is we have some items that are “bundles”. For instance say I have a Red Widget that has an Inventory ID of RWidget, and the average cost of one Red Widget is $5.00, I also sell this item as a bundle of 3 and I an Inventory ID of RWidgetX3 for the bundled item.NOTE: These bundles are not entered as a kit and are not a “stock item”, the Inventory ID of RWidgetX3 simply tells the picker to pull 3 of this item for the order. The issue I am running into is that these bundles don’t have an average cost in the system.In my generic inquiry I created a column called X3 and I am using the following code to flag the bundled items.  =iif(Substring([InventoryItem.InventoryCD],7,2)='X3','True','False')   This is working as is expected, I get a list of all the Inventory IDs, and the ones that end in “X3” show as True in my X3 Column, and the rest show as False.What I would like to do is expa

5
D
Freshman I
5 days ago
K
Jr Varsity II
kanupindiJr Varsity II
asked in Reports and Generic Inquires

Can we reference the same GI as a side panel?

Is it possible to add the same GI as a side side panel? We have a gi that lists kit assembly details and would like to reference the same GI on a side panel but with respect to the selected item as shown below:However, The GI is not getting filtered based on Inventory ID We have it set up as shown below:Desired outcome should be as follows:Kit Inventory ID Revision Qty Side Panel- Inventory ID Revision  Qty Q123 ABC 15 Q123 ABC 15       Q123 CDF 20 How can we achieve this result? Thank you in advance!@lauraj46 , @Robert Sternberg - any ideas? thank you!

2
Robert Sternberg
Pro III
5 days ago
R
Freshman I
rrodicFreshman I
asked in Reports and Generic Inquires

Acumatica Device Hub printing to different locations quality issue

We are using device hub to manage our printing through acumatica. We have customized reports that print to an Epson DFX-9000 dot matrix. Our main location in Canada when we print the report it comes out exactly as intended. However, when we print to another DFX-9000 to our Florida location the printout looks different. We have verified the issue is not with any printer settings, they are configured identically. The main device hub is running on Windows server 2019. We also tried setting up another device hub that is local to the Florida location but the results are the same. If I print in Canada to the local printer from acumatica looks great, send same report to Florida printer from Canada looks bad. Florida user prints report looks like the one I sent, same users prints to Canada looks good. Really not sure where to go from here.

2
Chris Hackett
Community Manager
5 days ago

Badge winners

Show all badges
Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2023  Acumatica, Inc. All rights reserved