Reports and Generic Inquires
Reports, GIs, Dashboards, Pivots
- 1,421 Topics
- 5,560 Replies
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:
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 >
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!
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.
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.
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?
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.
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!
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
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?
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
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
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.
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!
Trying to build a GI that looks at total sales price on SOs minus a specific inventory ID. Ex. SO# Sales Price Adjusted Price SO000012 $45,000 $33,000 In this scenario, Sales Price is made up of two items (item A priced $33,000 and item B priced $12,000). I’d like to build a GI that creates the table above. Does anyone know what I can enter on the GI to perform this math?
Is there a way for Report Designer do a proper validation for sub report variables?The Expression Editor always returns this error when validating a Sub report variable, even when the syntax is correct:The variable returns the expected value without any issues when you run the report.Knowing that this is a false check, we are just very careful with the variable syntax in both the main report and the sub-report.Thanks
Community,I need a little direction. On the Order Register it did not contain SOShipment table in it. I want to add that so that I can pull in the field SOShipment.CuryFreightCost to the report. I have it pulling, but when I run the report, it messes up the Total Lines Amount column. I haven’t made changes to it, so I feel that it has to do with the relationship that I have when I added the SOShipment table. I’ve tried multiple relations and haven’t found the right one. This is the report with the relationships that I have in the report. The original report had the top 3 lines already there and I added the last line. In this screenshot the highlighted item is the one that I’ve been using to test for accuracy. The number 7475.00 is supposed to be the Total Lines Amt. However, the correct amount that it should be is 575.00. Any ideas or direction is much appreciated!!Thank you!
We have a number of International customers that utilized Customs Brokers. That data is currently being imported from Macola/Synergy as a textfield. What table(s) associated with the customer would that data be imported into so that it populates a modified Invoice. I’ve seen other references to Brokers, Customs, but so far nothing about where that data may reside.
Our generic inquiry has 2 filters with a default value of NULL. We have them set this way in order to show all records. This works on the desktop but not on mobile. Ive noticed that the Filters auto fill to “=NULL” on the mobile screen, but not on the web version. This is 2023R1 Any ideas?
Hi, I have this existing Generic Inquiry and I added in the Selling Price Sales Price which comes from the ARSalesPrice Data Class and the Data Field is SalesPrice in as I need to see the Sales Price on each Stock Item.The Table joins & relations seem to be ok as I am seeing the correct Sales Price on the Stock Items and now it is creating a double line items for each Stock Item and I am not sure how to Group or create a group on the Field and then create a aggregate function such as MIN to fix if that is the solution I read on some other posts if that is even the solution. Any help would be great. I have attached some screen shots. Thanks.
We are using a report named Monthly Inventory Balance - that will display the customer’s monthly inventory balance. Currently, it is displaying incorrect values. We are using a formula to calculate beginning and end qty: (Base Item Volume * Qty * Inventory Multiplier). In this scenario, the expected result should be Inventory ID Beginning Qty End Qty CBAL 1.968 1.968 CBAL -1.968 -1.968 However, the numbers showing up on the report are not accurate. There are variables that referencing the formulas above in addition to grouping. We tried ungrouping and removing the variables but the values still don’t seem right. Variables The joins below are pretty straight forward too I am attaching a copy of the report below. Can you please suggest what I am missing here?@lauraj46, do you have any recommendations? thank you again!Thank you!
Login to the community
Social LoginLogin with your Acumatica account
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.