Reports and Generic Inquires

Hi Everyone, I’d like to use the Date-Relative Filter Clause: “@WeekStart-1” and “WeekEnd-1” so that I can have this report distributed via email weekly with the previous week’s shipments. The additional filtering condition does not let me enter anything but a hard-coded date. I’ve looked in report designer “build schema” but couldn’t find anything obvious that is casting this field as requiring a hard-coded date value.The documentation only references this pane in context of the “Behavior > ViewerFieldsMode” property. I have it set to MergeWithUsedPerhaps Advanced Filters can be enabled in the Report designer and I missed it? Thank you, 

I have an automation that is kicking off daily, but the automation that defined it has been deleted. How do I diagnose and find the cause of the daily email I am still getting. A few weeks ago, I was testing automated sending of reports via email. I created a template of a canned report: Shipments by Customer. I added a schedule to our Send Reports screen. I set the report to email daily at 12:54 pm. Then I added this template to the schedule from the report screen. After some more testing, I dialed in my settings and created a new template and a new automated schedule. The new schedule sends the report every Sunday and is working fine. I deleted the original template and schedule… but I’m still receiving the daily email.In the history, the only thing I can find at 12:54 is a generic “process all pending emails” schedule, but when I look into that history, there’s no evidence of my report in the pending/completed/failed list.

Hello,We recently had the unpleasant surprise of discovering that the default behavior of the Sales Prices Inquiry screen and web service has changed between 2020 R1 and 2021 R1.We used to query the API for all sales prices with no EffectiveAsOf date specified, and would get back all prices for all Effective Dates, including expired prices.Now, all API requests default to returning only prices that are EffectiveAsOf the current date.We whipped up an emergency GI to serve this purpose, but I would prefer to use the built in Sales Prices Inquiry, if possible.Is there a way we can get the old behavior back? I have tried API requests with no EffectiveAsOf date, a null EffectiveAsOf date, and an empty string, but it always comes back with today’s date in the response, and prices missing.Thanks in advance for any insight. 

The auditors would like a printout of the audit history.  We can see the changes through Audit history, but there is no way to send a report to auditors for their files.

Hi There, Just checking if there is better answer for this.I know that when using “is between” in SQL Server, or GI,  start date is inclusive and end date is exclusive since time default to 12:00AM. But on Case screen, if I do a filter using “is between” on date column,  the end data record is included unless I manually change the time to 12:01AM.Why? Thanks,Woody 

Hi Everyone, I’m trying to understand formulas in GIs. The documentation does not provide guidance on how to use formulas. There is a Report Designer guide that lists each formula and how it’s used (Isnull() on p. 63) but it lacks detail about how data types affect expressions within the formula. If you know of another resource, please share :-) On to Isnull(). Here’s the documentation.I’ve written a simple formula with unexpected results:=Isnull( CStr([ARInvoice.CustomerID]), 'NULL')in row 1, ‘9698’ does not match either of the referenced columns. Why?My ultimate goal is to have this algorithm in a way that Acumatica understands:If CustomerID is null, return ReferenceID, else return CustomerID 

The report exists on my end.  I can see it under Data views. See screenshot.  However, Emily and other users can’t view it anymore.  She used it this morning.  I looked at the security.  I review the GI but nothing pops out on what the issue could be.  I tried moving it to a different module and the moved it back.  Any ideas? 

The MICR font on my check form is displaying with all red zeros when I print the check.  Any ideas why this is happening?

I have created a GI to use as as an overview of all approval maps.  For some reason I can’t select “Use in Quick Search” for the AssignmentMapID_Description.  Is that because it has a navigation target selected?  Also I noticed that for some of these fields, it takes really long to search, is that because of the complexity of the query?  

I need to create a report based on the Current Sales Price and compare it to Last Cost and Avg Cost, so we can see what the Profit Margin/Profit % is.I need to include the following fields in the report:Customer        Inventory ID     Current Sale Price       Last Cost         Avg. Cost         Last Transaction Qty     Last Transaction Date    Profit Margin    Profit%I would like to be able to filter this report based on Customer Sales Price Code/Customer and Start Date (this would be the Start Date for the Customer’s Price Code)I’m not sure how to bring all this data together, so if someone out there can help, I would greatly appreciate it!Sarah

Hi All,I am working on a report using Detail Sales Profitability as the template to include Freight Cost. Everything works well until I am trying to add up the total for Freight Cost. The issue I am having is that the system somehow keep adding with the same freight cost base on the amount of lines it has within the shipment. Please see below for screen shots:From the report:Example 1Example 2 From Report Designer:  How would I be able to fix it so it don’t repeat adding itself within the same transaction and actually add up all the invoices within the customer?Kind regards,Calvin

Customer would like to print the Shipment Confirmation Report with one click, compare to standard 2 click, one to render report to preview and second click to print.Any thoughts?

Hello,I am trying to include a quick filter on the PMTran Date for a GI. I would like to have the filter setup up to show everything “Is less than or equal to”, I would like it to also show all blank dates as well. Does anyone know how to do this?I attached two screenshots1). GI Screen results (with quick filter)2). Results grids of that fieldAny insights would be helpful! Thank you,KT

Each of our projects can have different insurance requirements/thresholds and/or warranty duration that need to be included/communicated to our subcontractors/suppliers on purchase orders. I have created the necessary fields using “Attributes” and then I added these attributes to the Projects (PM301000) screen as user-defined fields through the Customization>“Manage User-Defined Fields” prompt.Now, I can also add these attributes as user-defined fields on the Purchase Order screen, but they just appear again as blank on each PO form and it doesn’t copy over from the project.In Report Designer, I can edit the Purchase Order report to print the user-defined fields that I added to the Purchase Order screen, but is there a way to link/relate the Purchase Order back to the Project such that I can print on the the user-defined fields from the Projects screen instead of from the Purchase Order (so the attributes only need to be filled in once and are then copied over to each PO if the PO h

I’ve been playing with OData Version 4 with Excel.  My questions is regarding performance.  Is it faster to link individual tables for example InventoryItem and INSiteStatus or just link the InventoryItem table and expand the INSiteStatusCollection?

If you copy and paste into a Note field it will keep the formatting from the source document.  But when you print the note field in report designer it creates some funky formatting.  Does anyone know if it is possible to keep the original formatting like below right?  

We are trying to download a report of all Available Qty but can only seem to find a report that shows On Hand Quantity. Any suggestions?  I want the available qty as that shows everything that is available to sell where as the on hand quantity will show all inventory (even inventory allocated to a sales order).

Hi,I am editing the SO Invoice report SO643000 and adding in packing info to the invoice so it will show what item is pack to which Skid or Box. After finishing my whole setup, it would work great for invoices. However it now product a blank report when the document type is Credit. Examples below:Credit Memo:Invoices (Boxes):Invoice (Skid)I know the main issue is when I apply SOShipLineSplitPackage.PackedQty filter to IsNotNull, it somehow takes away information from Credit Memo. I have tried to change the filter to apply IsNull for Credit Memo and IsNotNull for INV, but it did not work.   Does anyone have suggestion on what I can do to achieve below logic, either with Filter or another way?If it is Invoice, PackedQTY is not empty If it is Credit Memo, PackedQTY is empty.Thank you,Calvin

Hi,I am working on the invoice report and have modify in a way to show which shipment each item is in on the print out. I have figure out most of the logistic and is currently stuck at grouping the item together instead to have it spill to each line. See below of the current print out of the reports. Screen shot A is when the shipment type is SKID; Screen shot B is when the shipment is ship as Box. A)In this scenario, I would want all Items to show only once with the 2nd detail section to show more than one line per item. As you can see, the skid 1 for TQ002 have 2 boxes and each have their own packed amount. I am not sure how I can link the schema to provide the logic to only show the Item@ only once. B)In this scenario, the shipment is shipped as boxes. For item SM3000T3-Neon, it was shipped as 7 boxes. I would like all the box numbers (7 total) to show under Box column and in only one line of SM3000T3-Neon instead of showing up 7 times, which makes the calculation for QTY and price

Anyone run into the package count column on the Shipments grid not displaying the package count from the field in the Shipment header? Client reported the issue, and it appears to also be an issue in my local demo system. Field is SOShipment PackageCount on GI grid and is located in the header of the Shipment screen. See screenshot from demo system. 207 shown on the shipment. None show in the packages column in the shipments grid. Thanks! 

hello, I need help with this report. When I print the information from the same table but with a condition to separate it, the information is printed in 2 different pages.please help me and thank you.This is how I have declared the fields and the report configuration.  

Our customer has a large amount of projects and would like to have the ability for users to have a My Projects Filter type that is common throughout the system.When I go into the Project manager column, i can enter @me and the column filter works as expected. When I try to make a more permanent filter, it refuses to accept the @me filter. I tried @me, =@me, and =’@me’ What am I doing wrong?

Regular notes change the icon on the primary list to show a record has a note but if the note is a pop-up, it does not change.  The Acumatica suggested work around to open the record does not seem to be a reasonable solution.

We have a SQL script given to us by acumatica  to detect projects that have a problem preventing us to change the AP pay by single project setting.  We then have to make the projects the script provides to activate the projects from a completed state.  Change the AP setting and then mark those same projects complete.  I was hoping to use mass update in a GI to do this so I don’t have to give the AP clerk access to the SQL database to run the script.  The fix for this bug isn’t scheduled for 2023 R1 and we would have to do this process on a weekly basis.  I am open to other ideas also. 

I have two different dates in different screens Ex(stock item and Branch) and i would like to update the minimum value of the stock item date and Branch date into a different screen. Can it be done via generic inquiry and Business events. thank you in advance