Reports and Generic Inquires

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?  

Hello! I am trying to create a GI to see consumption (issues, adjustments, invoices) and receipts by stock ID by week # (ex. the 15th week of 2023 (15 2023)). We are struggling to take the information from “Inventory Transaction History” and put it in a GI. The table name we’re looking at is InventoryTranHistEngResult and its relation to intran and intransplit.Thanks for your help!

Hello Community , I need to make an report of AP and AR invoices which are in same Due date , in generic inquiry , i use left join AP to AR on Data field of Due date , but i’m facing with issue of repetition of data ,For example at 2/15/2024 i have 4 AR invoices and 1 AP invoices , in the Result Grid AP invoices will repeated instead of showing just 1 invoice . also when i want to use group by i have same problem on summing the invoice amount and if i want to use Union Join there is no featuret in Generic inquiry .in a nutshell, how can i avoid repeating data in Generic inquiry

HI,I need to send out email to all existing employees.I have created a GI with all active employees and their Email address.I have also setup a Business event, with Email Notification. Business event works when new record is added, but I need it to work for all records.  Please Help!!!

I want to take a look at the current access rights for all users and roles to determine if they were given proper rights according to their nature of job duties or roles and determine if the access rights needs to be modified.

Hi Guys,Hope you are all having a great day!!!I have been using a GI to calculate our weekly labour costs.I have encountered an issue where if a BOM Cost is calculated, from the BOM, a fair few BOM costs disappear from my GI and i have to roll the costs, we have near 60000 InventoryIDs, and this is quite a lengthy process.My GI is attached and our VARs are unfortunately unable to see anything wrong.Am I using the wrong table? Should I use BOMCostHistory rather than BOMCost?Any help is greatly apreciated,Thanks,Aleks!

How to retain the formatting even after refresh ?

Hello all,I have been working on Budget Details GI and I am looking to add the ref numbers of the PM transactions, only the ones that either have the status as On-Hold or Balanced. I am able to display the correct figures, but the ref numbers are getting duplicated even for the Project Tasks for which no PM transaction has been created. How can I resolve this?  Also attaching the XML file for same.Kindly let me know how to resolve this.Thanks in advance.

Community,I created a new GI from the INPIDetail table.  In my GI, I am attempting to create a date parameter that is required.  I have it set up, but when I run the report, no matter the date entered, it will not return any results.  Looking for some pointers.Parameters tab in the GIConditions tab in the GIReport results.The goal is to have the user be required to enter the date and return results from that date of the cycle count.  Most generally they will use this report the day they do their cycle count. Thank you,Trisha 

I am trying to bring in 2 different qty avail fields in this GI. I have a finished good (kit) sku on the left, and the related kit components on the right. The qty avail is pulling from the InSiteStatus table, but it is only pulling the kit sku levels in. Based on the pictures below, how would I display the kit components qty avail? 

I attended a fascinating summit session given by Steve Hoglum in which he discussed how to optimize GIs by applying conditions in the GI relations table, if I am remembering correctly. I downloaded the presentation PDF but I couldn’t find what I was looking for.Again, if I remember correctly, he suggested adding conditions in the relations table, something like this:Did I remember correctly? If so what is the format for dates?

HI I am unable to login to Acumatica Report Designer. I am using my local host and login credentials but it shows me error “Unable to connect to remote server” . Can anyone help me please  

Hello team, We have a customer that requires us use the report: AP622500.rpx[AP Payment Register] to print payment remittance emails, so we have used the email settings to adjust the subject and the body. Previously they were using an email template which copied files from the email to the Checks and Payments form.  We can see that the payment remittance went out with the attached report. But the actual record does not have the file.  Is there a way to add the file, when processing records from batch payment . 

Hi there, newbie to Acumatica here and I hope someone can help.I have a workflow where my users (field services) will complete an FSAppointment and upload image files to support the work. The customer will then sign that the work was done. There are three files I can see uploaded in Files, two with a prefix “image_” and one with a prefix “signature_”. I want to add the photos and signature to the appointment report with the photos of the work and the customer signature on each report..I have tried to display using =[FSAppointment.NoteFiles] but the first image is displayed.How can I specifiy the signature and/or the uploaded files?ThanksMilan