Reports and Generic Inquires

Hello,Working on a GI to replicate some Excel reporting we are doing outside the system. I am receiving an error on the Formula for “Margin in Backlog (%)” , the error is Divide by zero error encountered. Can anyone notice anything wrong with the below formula or another way to avoid the divide by zero error? Thanks,KT Margin in Backlog (%) Formula=100*((sum(IIf( [PMBudget.Type]=[Rev] And [PMBudget.CuryRevisedAmount] <> 0, [PMBudget.CuryRevisedAmount], Null))-sum(IIf( [PMBudget.Type]=[Rev] And [PMBudget.CuryActualAmount] <> 0, [PMBudget.CuryActualAmount], Null))-(sum(IIf( [PMBudget.Type]=[Cost] And [PMBudget.CuryRevisedAmount] <> 0, [PMBudget.CuryRevisedAmount], Null))-sum(IIf( [PMBudget.Type]=[Cost] And [PMBudget.CuryActualAmount] <> 0, [PMBudget.CuryActualAmount], Null))))/(sum(IIf( [PMBudget.Type]=[Rev] And [PMBudget.CuryRevisedAmount] <> 0, [PMBudget.CuryRevisedAmount], Null))-sum(IIf( [PMBudget.Type]=[Rev] And [PMBudget.CuryActualAmount] <> 0, [

Hi everyone, Is there a way to capture all the rates given when you click “Shop for Rates” on the Shipping screen? I would like a way to show what our freight cost is, but also how much we save by choosing one carrier over the other for each shipment. If we shop for rates and click the lowest one, do the other rates just go away or are they stored somewhere? Thanks!  

Hi Everyone,We have a nightly automation that recalculates the customer balance via the “Recalculate Customer Balance” screen. We recently created a data warehouse to pull our invoice and other data together to join with our other financial systems to create a consolidated view. We are pulling data from the ARInvoice table via the REST API and loading into the database. That all works great except for the last modified date. The biggest issue is that the “Recalculate Customer Balance” process that runs nightly is putting the date and time of that automation in the last modified fields in our ARInvoice table. Therefore I can’t pull that table and only capture new activity.  Any ideas on how to get the “Recalculate Customer Balance” nightly job to stop changing the last modified date or another creative way to get last modified for ARInvoices? We’re running Acumatica Cloud ERP 2021 R2 Build 21.223.0012. Thanks,Tom

Hi, I am working on a Report to print Time Cards base on the entry done in Employee Time Card Entry, Screen ID = EP305000 . I am prompting for the beginning & ending weeks using the view name: =Report.GetFieldSchema('EPTimeCard.WeekID')When I use the search, I get columns, Week, Start, End, Description & WeekID. I cannot seem to find the table that these are stored in or defined. I need to print the description, or the start date of the beginning week that is selected. So, I need to find a way to link from the parameter to a table to get the description Any ideas? Thank you in advance! 

Hi, I meet a problem when creating a GI, not sure why the result will not display decimal number result.Here is the calculation.=SUM(DateDiff('d',[POReceipt.UsrReceiptDate],[POOrder.ExpectedDate]))/(COUNT([POOrder.OrderNbr])) The attached with the xml file.

I am working on a GI to pull Customer information. This information will be used to update another system. I have the GI pulling all the information with the exception of the Customer’s SHIP-TO ADDRESS (found on the AR303000 screen under the SHIPPING TAB). I have tried the Inspect Element function and it showsI can not figure out the corresponding table and Address ID field to use to pull the Ship-To information.Thank you for any assistance. 

Hi All, I am currently working through a Requirement for the proForma Invoice where we would like to split Time and Material Project Entries with their separate Headers and after each other:Basically Project Task 1 with the Code 00 will always be Labor and needs to flow into the Time Header Section.The Rest will always be Material.So I need to associate the Line/Detail Section to the Grouped Header of Time.I cannot figure out how to associate a certain Detail section with the appropriate Header Section.Visual Expressions didn’t bring me very far yet as it is not determining where the specific Lines shows up  Any thoughts on how to associate the Detail Section with the Header Section?

I am trying to get the terms & conditions to print on every other page so that they can print on the back of each invoice.  Has anyone had/or been able to do this?  Thank you! 

Hello, I’m working on trying to recreate a report generated by our old ERP system called Job Cost Billing Summary. The main thing that I’m having trouble recreating is separating out the “Actual Costs” based on the section “Account Group” in Acumatica for each job within a set period of time. Our Account Groups are represented by letters L for Labor S for Subcontractor and so on.  I am basing the report off of existing Acumatica report 6540002 and have not added anything to the Schema from the base file. If anyone can help me figure out what I would need to enter and if I need any additional relationships so that I can have total actual costs for each group, I would be most appreciative.

I am creating a quote form for a customer who has quotes / sales order and invoices with many line items.  They have requested that alternate rows on the detail section are highlighted in order for the customer to be able to read these better.

Good day Team, I am trying to add the AP Bills Branch ID to the AP642000 Additional Remittance Form, I used the APAdjust table with the APPayment, doing Doctype equal to AdjgDocType and RefNbr equal ADJgRefNbr, also used the adjd ref and doctypes to test but did not worked either. When I used the APAdjust.AdjgBranchID, as I tested on a GI, it worked, but when doing in the report designer does not work. I have used other tables as well, but no luck. I hope, there is a way. 

When working with Generic Inquiries in Acumatica, it's common to use multiple columns to validate different pieces of information based on specific business requirements. However, this can sometimes lead to a cluttered interface that is hard to understand and interpret. In this post, I'll show you a technique to consolidate multiple validation columns into a single 'Message' column, which will make it easier to communicate concise information about various source columns through one unified message all without developer customization.  This post will include Screenshots, Examples, and Generic Inquiries that use the SalesDemo dataset. Our ExampleConsider a Generic Inquiry where you create validation columns using formulas for one or more data elements. Let's use an example request as our starting point:Add a column to validate each vendor with 30D terms is within the US. Add an indicator to columns where a subcontractor is outside the US. Solving: Conventional vs. Streamlined Approach C

I have created a Date Parameter for a custom report.  I am trying to filter the report by this parameter, the result is coming back with no data (a blank report).  I am selecting a date that i am 100% sure is in the dataset because i have a raw dump of the data.  What am I missing, why is my filter not working?  Does this have something to do with the values being seen as text instead of date? 

Hello,The initial brief was as follows:Show all items where itemclass in (spareparts, lvspareparts) and use date range. Fields to show:Inventory ID, Inventory DescriptionTotal Sales for date range Total qtyonhand all warehouses (just the total holding like on stock items page)Total transferred over date rangeLast invoiced dateLast Cost On the results we are getting some larger numbers, and not all the QOH look right, any idea on how to improve on the results here?  

I have a client asking if the search result color can be changed within Report Designer. They find the light green difficult to see. I haven’t found a way of changing this. Does anyone know?Example below:  

Hello,We want to create a GI that lists all sub assemblies of a kit specification. Below is an example:CJ60 has kit components : CJ30, CJPC, CJLP, CJCB and CBLUE. Similarly, CJ30 has kit components - CJ12,CJTC, CJTD and TH10.  Kit Specifications and Kit AssemblyWe would like this information along with their lot serial numbers into a GI. Currently, I am using INTran table and self joined the same tables multiple times. But it is not working as expected. Table Joins on the GIWill really appreciate if you could share any ideas/ suggestions to get this to work. thank you! I can share the xml file of this GI if needed.  @lauraj46  - could you please advise if you have any ideas as well? Thank you!

We have created a custom GI and we need to build a custom report based on this GI with Report Designer.  When we load the schema in report designer how do we get the custom GI to appear in the report designer list.  

I created my first report. But I’m unable to deploy it. This is a new GL report: GL6333100. It is based upon another GL report. Following deployment steps in the training video, I come to opening the Site Map then adding the new reports. However, no GL entries are displayed in the Site Map. I see several AM type listings. I assume this is permission problem but I’m not sure which permission. Does this make sense?Do you know what I am doing wrong? Thanks,Tom

Hello, I’m looking for a way to count the color with the Generic Inquery, for example here, I want to count all the green case per line. Line 1 : 2 , Line 2 : 3 …  This is the query for the green and orange color : I try a COUNT() but it didn’t work.  Do you have an idea ? 

Hi, I want to add the APPayment Ref No into Bill and adjustment GI, and the Data field is ‘displayRefNbr’ from Table APAdjust.But why the data field is empty? is there any another way to fulfill my request? I have checked the Dac Schema review source data, the whole role of DisplayRefNbr is empty.attachment with the XML file.

I am looking to create a GI with Kit Specification that will show all the item within the KIT as well as Sub Items that will explode for the same specific KIT. Example P# has A123, B123, C123 and S123 (S123 is a KIT), S123 has S234, S345, S456. The goal is to have everything P# required both components and Sub Components under the same P#P#A123B123C123S123S234S345S456At the moment I can’t get the S# to show under the P# in the same GI.  Also, we do not use BOM.  Currently the Sales Order explodes that KITS when the Placeholder for that kit is used. 

Hi everyone.  Is there a way to deduct the applied credit memos of an invoice on the “Net Sales” field of Sales Profitability by Customer and Order? Thank you. 

 when we run the inventory balance report, we find that the numbers are not accurate to what the inventory allocation screen shows.  The Inventory allocation screen is correct, and the inventory balance report is wrong. - what do we need to change or adjust to make the report correct?  I do have rights to the report designer so I can adjust the report if needed. . 

Hello - I am trying to produce a report that shows AP Bill transaction detail (have this already) that also shows if that bill has been reclassed and if so, which accounts they were reclassed to. I’ve tried linking several different tables but none of them seem to work. I thought I was close by linking the GLTranForReclassification but the NewAccountID does not seem to work, keep receiving the below error. Does anyone know of a way to achieve this?  

I need a GI to show the Purchase Receipt History info, and the Element Properties show the name POReceiptPOOriginal. But this error message prompts ‘Invalid object name 'POReceiptPOOriginal'.’. Any other way to get the relation table?