Reports and Generic Inquires

Hello All,I have been trying to add a calculation for the following scenario,I have the Total Sales (25000) & Probability (75%) (an attribute). I would like to multiply the values of both the fields and display in the GI  Both these values are from the Project Quotes screen.So I want to display the result as 25000*75% = 18750

Hi Everyone,I have customized the AP641000 report.I've encountered an issue with the customized report. The problem is that it duplicates the bills included in the check, resulting in the respective number of lines being printed in the report. ex. if checks include three bills it displays each bill three times in the report.I have attached a copy of my report and the mapping I've used to fetch bills. I would greatly appreciate any assistance or insights you can provide on where I might be making a mistake.any help will be greatly appreciated,Regards,Sagar Mapping I have used.The output result. 

Hello - I am trying to join GLTran, Batch, ARInvoice and ARTran tables, but my records are duplicating. Is there a way to join these so that only 1 line per GL account appears? Here is what I have currently:  

Good day, I have a client that has a Generic Inquiry and have saved several tabs with different filters on them. The main sort (from the GI), is the Descending sort for Production Order number, will make all the data tabs to sort by the production orders numbers. But we want one of the tabs to sort by End Date (a column on the GI), is there a way to assing a sort to a single tab of the GI?On next image, the Sorting on the GI is by ProdOrdID, but the ALL PRINT WC tab needs to be by a Date colum, instead of Production Order number. is there a way to sort that tab individually? Thank you.

In AR Register Detailed report, my client has report fully modified according to their requirements. in their report they calculate total amount based on CuryOrigDocAmt field in ARInvoice table. when calculate total they add 10% vat to the total. They request to modify it when customer is non taxable , the 10% vat must not add to the total. only taxable customers should billed 10% vat. In their AR Register detailed report, I tried to do the mentioned modification. I consider the ARInvoice.CuryTaxTotal field to identify weather customer is taxable or non-taxable. But with existing relations on their AR Register Detailed report , it doesn’t retrieve data of ARInvoice.CuryTaxTotal field. Beacuse of that i implement two sub reports get values to the main report.The question is two sub reports retrieve data successfully. when i connect it to the main report the main report not display any values. Here i attached two sub reports and main report  

When we had some attributes created 2 of our date fields were created using Text rather than DateTimeI have been through some articles mentioning using CDATE, however I have not been able to get this to work yet. The final aim is to be able to sort by Contract End date DESC in the Customers GI.Is there a way to do this without recreated the fields? 

We noticed recently that we have had a few sales order #’s skip in sequence.  This only happens if someone deleted a Sales Order. While we probably need to tighten up our security to prevent this from a broader set of team members, I’m wondering if we can create a GI that is able to identify deleted sales orders?  We have been able to do so with deleted Stock Items and notice that some records have an “IS DELETED” field.  We cannot locate this on the Sales Order table - PX.Objects.SO.SOOrder?

I’ve been testing 23R2.  In many Generic Inquiry definitions I get the warning shown below.  The message makes no sense, when I run the inquiry it does display these field and my user has sufficient rights and there was no warning like this in 22R2.  This issue introduced in 23R2.  Anyone else seen this or have an idea what cause sthis?  

Hi, I am making an EFT only report of report AP Payment Register. One of the changes includes wanting change the RefNbr parameter to use the APPayment DAC to select a payment rather than from APRegister. Currently, the report parameter is defined as follows: And parameter Doctype is this: When you select a doctype first, the RefNbr parameter selector then displays all AP register records for the doc type you’ve chosen. So I naturally figured I’d change the RefNbr view name definition simply to the following below so that it works like the selector in bills and adjustments: =Report.GetFieldSchema('APPayment.RefNbr,DocType')  However, when I go to select a payment using the new selector, while the payment selector pops up, it’s always empty. There are plenty of EFT payment method records listed with Doctype Check. Going to the bills and adjustments transaction and using the selector is proof of this. Is there something I’m doing wrong or does APPayment selector simply not work properly w

I have 3 sub reports added to my main report and each as a sub total, how can I access the sub totals on my main report - I tried $subReport_TotBills for example and it didn’t work.

I am creating a generic inquiry where i would like to join the ARInvoice table to the FSServiceOrder table. This is so i can refence the docdate of the Invoice. What are the Parent and child fields to link service orders and AR invoices table? 

I have created a new report that I send in addition to my drop ship purchase orders. For each PO I send they need to have their own customer facing pack slip sent along for fulfillment. I need to filter based on the date and the vendor ID. I have gotten everything set up where the report runs but it is not respecting the individual orders and dumping everything into one PDF, grabbing the most recent PO header information. Where would I add something to say I want each order and their respective lines to generate on its own PDF? The PDF file can still be compressed to contain all available order, they just need to print on their own pages based of the PO order number. 

I wonder if there is an option to have column in a GI to enter data on the GI itself and than we can use an import scenario to update data entry forms in Acumatica.Please let me know, Thank you

Hello, Brand new to Acumatica, I am creating a GI for PO vs Avg cost comparison and would like for the field that contains the PO cost to highlight red if its greater than the AVG cost field. I have the entire GI built but am having some trouble getting the format of the formula for this to happen. I was hoping maybe some one here could help me with this ? 

I’m looking to figure out how to make my row styles based on the Days Lagging column, which is a formula that is the following:=DateDiff('d', [SOOrder.OrderDate], Today()) I’d like to color code on the Days Lagging to show Red as greater than 90 days, yellow greater than 60 days, green greater than 30 days.  I’ve tried using a Switch statement that has IIf inside to do the same math, but I’m getting out of bound array issues. What am I doing wrong lol?

I created a custom Attribute for an Inventory Item.  I want to print both the Value ID and the Description on the Report.0001 Ryan HarrisWhen i select the attribute from the Inventory Item.  It only prints the Description.  How can i get both to print on my report? 

Greetings,Newbie Acumatica user here - just 10 days in…When running a Vendor Transaction History report for a specified period, known transactions, and in some cases, entire vendors are missing from the report.Any ideas?

Change Order Commitments report SC645000-Previously Approved CO Total has field listed as =$subReport1_AllLineTotalPrevCommCOAmt What are the steps to open the subreport for modification?    

Hello - We want to add the ability to see who added or removed an attachment from an Bill, and when. I have added the AP Bills Screen ID to the Audit History, as well as all the associated tables, but I cant seem to get it to work correctly. I am getting very minimal results when running the audit history, and they all seem to be centered around the creation of the document. Do you know how to get an audit trail of attachments on a Bill?(In screenshot two I get about 30 results, for all usernames in the prior month. I dont know what is triggering this, but we have hundreds if not thousands of bills across the portfolio across that time frame, so I am not sure what this is triggering. )Thanks for any suggestions!  

I have two tables created but unsure how I can join these since I have different conditions & aggregate functions. Our goal is to see the total # of Service hours for an Installs completed last week. Service hours are defined as estimated duration of inventory IDs on SORs that start with “SER”. Ideally table would look as follows:Service Order Nbr.     Install Appointment #    Install Date   Total Service Here is table 1:The conditions here are FSSODET.InventoryID = Install Grouped by FSServiceOrder.RefNbr The Install Date is the FSAppointmentDet.TranDate and aggregate function set as Max (to give us the latest Install Date. Here is table 2:The condition here is FSSODET.InventoryID starts with “SER”. Grouped by FSServiceOrder.RefNbr The total service is the FSSODet.EstimatedDuration and aggregate function is set to SUM.  Conclusion:I’ve tried to combine these two tables but get inconsistencies - I’m unable to sum the FSSODet.EstimatedDuration while omitting the Install item. Not su

We are creating an automated process for importing BOMs into Acumatica. The data for these BOMs sit in SQL and we are creating an SQL view for all recently uploaded or modified BOMs.Our goal is to create an automated schedule that imports these BOMs daily.I will be creating a business event and schedule, but once I get to create the subscribers I’m seeing that the screen associated with the Business Event becomes the data provider of the import scenario. My data provider for the import scenario needs to be the SQL provider.Is there any way to use an automation schedule to trigger an SQL import using SQL provider? Will I need to create a custom GI for the SQL data which then would flow as the data provider of the import scenario?

​I am currently adding a filter condition to the Trail Balance Summary report, specifying a 'From' and 'To' period as shown in the attached screenshot.When I pass a different period condition (e.g., From period: '01-2023' To period: '07-2023'), and considering that the financial period has been closed, while running the report between these dates still results in the Beginning Balance displaying random values.While running the report with the same period (e.g., From Period: '01-2023' To Period: '01-2023'), we obtain the expected output with the Beginning Balance displaying correctly.However, the issue arises when running the report with different period conditions. The Beginning Balance does not come out as expected in such cases.Any assistance with resolving this issue would be greatly appreciated.Thank you.​

I am currently creating a dashboard for our executive team.  I have created a new inquiry for the fields I need to pull in.  I am using the KPI widget and the labor looks like it is coming through in seconds and the inquiry is doing the same when I use SUM in the total aggregate function. Does anyone know how to make it add minutes/hours?  

I am currently adding a filter condition to the Trail Balance Summary report, specifying a 'From' and 'To' period as shown in the attached screenshot.When I pass a different period condition (e.g., From period: '01-2023' To period: '07-2023'), and considering that the financial period has been closed, running the report between these dates still results in the Beginning Balance displaying random values.Can anyone assist with this issue?Thank you.

Why can’t I use this field in Conditions? I seem to be able to use all other LastModified fields (Ex. Appointment Last Modified) in Condition?