Reports and Generic Inquires

I am trying to build a GI with the invoice info and the activities that belong to it.  I was trying to link table ARInvoice with CRActivity.  Has anyone linked these two table up before or know if i am targeting the wrong activity table ?  Thank you .

So we are about to try and use an attribute on our Production Orders for the first time. I have associated an attribute with a production order type that we use for all of our production orders. It is a checkbox.  I now need to show a textbox on the production order ticket when this checkbox is checked, however, when I go to find the attribute in the schema builder, it’s not there. Normally when the attributes don’t show up, I’d just hit “Refresh All” to reload the fields and it would show up, but that’s not working for this case.  We just upgraded to 21.106.0024 from 2019R2 and I have no way of telling if the upgrade is the root of my issue, as we just started using attributes on production orders this week.

I have a Generic Inquiry that sums transactions over a date range specified by Required Parameters StartDate and ThruDate with Default Values of @YearStart and @MonthEnd-1 respectively:Generic Inquiry ParametersAs I needed the ThruDate in Excel exports of this GI, I added it to the Results Grid:Generic Inquiry Results GridThe unexpected result is the Parameter (Thru Date) and the Result column (Statement Date) get different values when the Inquiry is executed:ResultsIf the Parameter is manually changed in the results screen, the dates match.The value in the resultant column is the date being applied (not the defaulted date displaying at top) verified by the calculated values and manually entering the dates. 

I am creating a pivot table for shipment line details and want to be able to filter for multiple shipment numbers.However, when you add Shipment Number to the filter section you only have limited options for filtering, as below:Is there any way to make this a multi-select filter so the above would show a list of available shipment numbers that you can select with a checkbox?Many thanks

Hi, I have created a pivot table for shipment data and it has lots of number fields that are presented as text, when you export to excel. Is there a way to format the number columns so, it will show left aligned in the table and will have the correct format when exported to excel?

Hello, How to include the aggregated field or sum fields in the bottom of a generic inquiry when exporting to excel? Thanks, Ed

Hi,Is it possible to pass a Parameter to the GI when the GI is extended via a WebService EndPoint? Setup:I have created a generic inquiry called SubcontractsByProject, it takes in 1 Parameter called ProjectID, I have set the ProjectID with a default value of a project and it filters to that Project, so the GI is working as it should. I have extended a Webservice EndPoint to SubcontractsByProject using instructions from https://www.acumatica.com/blog/contract-based-apis-in-generic-inquiries/ The web service workshttp://localhost/entity/Subcontracts/18.200.001/SubcontractsByProject?$expand=ResultIt is returning data with the default ProjectID in the GI How can I pass a different value of ProjectID to the webservice?I tried adding a parameter in the Body like so in PostMan but it does not work ThanksMark

Hello all,I’m working on a generic inquiry. I need a total inventory amount for each item. The result as below. The item existed in different warehouse.  When I add a grouping for InventoryID. The result become to :  My question is how do I ignore grouping on MSRP column? My GI setting is below.  

Using the APARord view as previously documented works well.  I have a client that needs 3 copies of a form, how do I set this up?  I am trying to print 3 copies of the Sales Order, each copy has a slight variation to what prints.  I used 2 sub-reports to achieve this, but the issue is when it tries to print a multipage sales order, the sub-report prints page 1 correctly, but then continues on with the line details onto page 2, with no logo, heading information which the client does not like.  I can create 3 separate forms, place them in the reports tab of Sales Order screen.  So the client wouldn’t have to click on each 3 separately to print, can there be some type of automation that when form A prints, automatically print form B then form C?

Hi,I have created an User Defined Field in Sales Order form. I want to add the same in Sales Order printed form (SO641010). I am aware the value for User Defined Fields are stored in SOOrderKvExt but I do not see in Report Designer. I have even tried Build Schema > Load schema and Refresh/Refresh All, but still I am not able to get it.Any suggestions?I am using Acumatica version 20.208.0031.

Hello,   I’ve wrote a tabular report to show customer(as row) sales in 4 warehouses (as column).The values are the total sales qty.     The column right now is dynamic, that if 1 warehouse has no sales at all, this column stands for this warehouse would not show on the report.       Is it possible to always show 4 columns, even if that warehouse has no sales.  

Hello,      I’ve wrote a report and I’d like it to run on schedule to show the last week’s data.I was using “[WeekStart]-1” as the datefrom and “[WeekEnd]-1” as the dateto.       And I save a template to schedule run on every Sunday.       However, if the business date changes, When using the template the DateFrom would change accordingly, while the dateto would remain still.       I temporarily work around this issue by assigningDateTo  =DateAdd( ([@WeekStart]-1), 'd', 6)         Then, the Dateto would change according to the business date.        So, I am wondering did someone else meet the same issue and is there anything wrong with the [WeekEnd] parameters.

I need a report or generic inquiry to show customer lead time.Our users need to be able to enter a Starting Date and Ending Date based on Invoice Date.I would like to be able to run this report by selecting a single customer or all customers.The lead time would be calculated by subtracting the Invoice Date - Sales Order Date.I would prefer a report but I’m not familiar enough with the tables/relations yet to make this myself.I see the GI DB Ship On Time, so maybe we could modify this GI…I look forward to seeing what you guys and gals can show me.

Is there a way to add a time stamp to when EDI salesorders enter Acumatica?

How to adding a clickable link for excel export using generic inquiry. It should be same report export. 

Is anyone aware of support for the Maxi / Aztec code barcode standard within Acumatica Report Designer? [ reference : https://www.barcodefaq.com/2d/maxicode/ ]We’re currently working through a phase of our implementation that dives deep into the Shipment Label needs of our larger customers. Some needs seem straight forward but end up at a dead end while others have slightly convoluted solutions. Oh, please note that we’re integrating with Easypost which will likely render the majority of our more typical Shipment Labels but these are not the labels I'm concerned about at this moment.Imho these larger customers' needs could be accommodated easily if we were to take ownership of the design of the Shipment label. I know there’s some decent work involved, which I've done before, but it would be a clean repeatable process.The main road block I now see is that Acumatica Report Designer doesn’t seem to support the Maxi / Aztec code barcode standard.Has anyone had the need to print this barcod

Help me please, how to include image to imagebox for external link, example from google photo

Hello,          In Generic inquiry, we have something fantastic like @weekstart and @weekendBut these does not work for report designer.        Is there a way to set the report designer’s default data range as “Last week”?

Good Afternoon, I am looking for some assistance with creating a GI. I am using the Inventory “Issues” GI as the basis. I am trying to modify it to include information from the INTrans table. I would like to bring in the following Properties:INTrans.SiteIDINTrans.InventoryIDINTrans.QtyINTrans.TranCostThen add an additional condition to only show item that have INTran.ReasonCode of “Damaged” Essentially I am trying to create a GI that shows me the total cost/qty by site and Inventory ID of all “Damages”. Using this to begin tracking manufacturing shrink/waste. Currently I am getting error when trying to view the GI so I am sure I did something wrong with the tables and relations. Attached is the GI as it is now. Thank you for the assistance. 

Howdy,This is my first time attempt in the Community forum  and I am still learning the product. So, correct me if I am wrong please.The report “Sales Order Details by Customer” should not display the total Qty if the UOM (unit of Measure is different). An HOUR is different from EA.The report should only display TOTALS for Line Total and  Open Amount 

Hi, I am trying to do a GI get Month-To-Date, Year-To-Dae & Prior-Year Sales totals for an inventory item. There are multiple warhouses setup. I am using the INTran table, with a condition of the item & the TranType of Invoice. If I sort the results in Site Order (SIteID), the results do show in Site order. However, if I set the grouping to be by SiteID, results are totaled correctly by do not appear in Site Order.Is there a way to sort by the grouping conditions?No grouping:With Grouping:   

Hello,     I need to get the infomation of such items:    We have qty on hand>0  And We didn’t sell for a long time like 90 days.    I can get this done by joining INsitestatus and ARTran table.        The key of my solution is that Acumatica will by default get the maximum of trandate after I group by inventoyID      And I can use a “second filter” on GI results like below to get the information I wanted. It is a solution.However， it is quite time consuming because ARtran table is quite huge.Can I replace ARtran with another table (Where I can still get the last sell date infomation and join much less lines) ? Or is there a better way to fulfill my need. 

The mission with this generic inquiry is to show the payment amount value with the decimal stripped out. I’ve tried a few formulas but Acumatica seems to want to add 2 trailing 0s when I do that. Example:  The goal is, instead of showing $8321.05, to show:  832105In SQL I’d want to return a substring, or trim 2 characters from the right side of the string. Here are a few examples of what I’ve tried: Try to get the field to display as a schema field ‘payment amount’ Try to get the field to display as schema field ‘doc description’ Try to return from the right side of the string, -2 positions: =Right(CStr( Replace(  [APPayment.CuryOrigDocAmt], '.' , '' ) ), -2) Try to cast the field as a decimal =Replace(  CDec([APPayment.CuryOrigDocAmt]), '.' , '' ) Try to cast the field as a string  =Replace(  CStr([APPayment.CuryOrigDocAmt]), '.' , '' ) Any suggestions on this? Acumatica really seems to want to add those zeroes!

Can the report designer run on a Mac book?  If yes, is there any special set up? Thanks,  

What do I need to do in a GI to replicate the functionality I would use in SQL for example to haveSELECT Contact.DisplayName [Contact Name] from Contact where ContactType = ‘LD’I want my GI column name to be Contact Name and not DisplayName.Thanks