Reports and Generic Inquires

Hello,     I know we are having a table named “InitemsiteHistDay”, using this table is quick to calculate a warehouse daily receipt totals and shipped totals.      However, for some reasons, we would like to count the worker’s work load by receipt documents and shipment documents, Like                            Receipt Total          Shipped Total        DateWorker 1                   10                          20                      2023-5-1Worker 2                   5                             45                     2023-5-2        It would be easy if we count seperately,  like we count  the daily receipt docs in 1 inquiry And all daily shipment docs in the other inquiry.   Then we join them in a new report or excle.      Just curious, is it possible that just use 1 Generic inquiry to fulfill the need?I tried join  Receipt with Shipment  on   Receipt.Site=Shipment. site, receipt.date=shipment.date, receipt.User=Shipment.User, but it would bring duplicated data , not right.

 Hello,    We are still using 2021R2. I added an report run action to Process Orders screen(SO501000) by below method. It is almost fine. Just a little bit issue like below.It actually successfully run all reports. But it still shows “Remaining” and “Pending”, not converted to “Processed”.  Altough the top line states “Processing completed” Is this issue solved in Lastest version?    Or Can I solve it in 2021R2?

There is a custom report created by me. It contains two columns as Invoiced Date and Document date. I want to filter the report by using these date fields. First priority goes to Invoiced date. If there is no Invoiced date filtration should be happened with the Document Date. And again If there is both Invoiced date and document dates filtration should be occurred by the invoiced date. Please check the below images.Start Date, End Date parameters  1st attempt by me1st attempt result2nd attempt methodI tried it in several ways in the Filters section of the report designer. But it did not work. In the first method, filtration occurred, but the priority was on the Document date although there was an invoiced date. In the 2nd method, an error message popped up as no Invoice date field found.So can you please give me guidance to fulfill this requirement? Thank you. 

i have value feild like 1120 but i want to show like $11,250.00    but when i used  =Concat( '$',CDec(Sum([POLine.CuryExtCost])))  but it show like this 

Hi All, I have tried to link table CRActivity with field BAccountID with table BAccountID (as default of system) field  BAccountID but it’s does not work. My problem is when I leave all filter blank, the report just show customers who have task link to it, it only shows the customers with the task associated with it, the rest of the customers don't appear.One more thing I want to show type task not invoice, so I set filter as below Please advice me if you have any idea.

I’m trying to create a GI where I can see my AP Invoices with the State of the shipping address from the PO related to this invoice. However, only the remit state is coming up, I can’t get the ship-to state. I attached the XML file. 

Hi everyone, How to use string contains function in report designer, If I saved string list in the particular field means How can check particular string is there or not My field Name TypeofChangeField Value  = Design; Sub-con's Proposal; My question is ‘Sub-con's Proposal’ string is available or not in the particular string field  

When i use utill functions show this error, what’s the lib missing ?

Good dayI am trying to come up with a report that shows my opening stock balance for a period and the total sales made to a customer (quantity wise) and then the closing balance.Which table can I use to get the opening stock balance for a specific period?I am working in the Report designer , I have used the table “INItemSiteHist” table , it only picks one line item instead of stock from all the fields for the respective stock item.I have attached the report I am 

Hi everyone I need to clear parameter value When Change dependent parameter value, please help me to solve this issueSample ScreenshotIf I Clear first Parameter Cash Account value means I Need to Clear Ref Number Parameter also, can? please give me solution for this 

HiI have been changing a lot of native Acumatica reports and our own in Report Designer from Auto to Manual. Users don’t like to get merged columns, colors or word wrapping because it takes more time to manipulate the data how they want it, etc. Changing this setting to Manual has been great. It takes a bit of practice and work, but after you figure it out, it works! and clients are happy.When I change the setting from Auto to Manual- the grid lines don’t show anymore, any body knows how to fix this? This is what we change in order to make this happen: Example when it is set to AUTO (default):This is a Work in process report, Excel Mode set to Auto- we can see grid lines:  Because I don’t want merges, word wrapping, and unnecessary fields on the export, I change to Manual and place what I want and how I want it to export:   The result below is how I want this to export, except that I don’t have grid lines anymore: Not everyone is an excel expert, so I have told my users to select them

Hi There,I’m trying to create a field using a formula that takes GL Actuals - GL Budget. Here is my formula but its not giving me the right numbers...=IIf( ( [Account.Type] = 'I' and [glhistory.balancetype] = 'A' ), [GLHistory.CuryFinPtdCredit] - [GLHistory.CuryFinPtdDebit],0) - IIf( ( [Account.Type] = 'I' and [glhistory.balancetype] = 'B' ), [GLHistory.CuryFinPtdCredit] - [GLHistory.CuryFinPtdDebit],  0 ) I also want to nest in for account.type = ’E’ when debits and credits are reversed but was trying to get this to work first. All my other numbers are fine...just this variance field that is off.

Our system use two GL accounts for incoming payments: 120-Accounts Receivable and 260-Customer Deposits. When I run the AR Balance by GL Account report, some prepayments are reported under the 260 account even though they have posted to our ledger under 120. That means the report doesn’t match our trial balance. Has anyone else experienced this? Is there a cause and is there a correction? Thank you.An example follows.The AR Balance by GL Account report, showing the prepayment listed under GL 260: The AR Register for the highlighted transaction, showing the transaction posted to GL 120: 

I’d like to be able to create a GI that displays product Inventory IDs and their corresponding Shopify Product ID. I know that data is stored somewhere in Acumatica because it is available in the Sync History (BC301000) screen and actually hyperlinks to the product in the Shopify admin.Does anyone know what table stores this External ID data in Acumatica? And how it can be related to the InventoryItem table?

We have a generic inquiry that flags when there is a mismatch or a discrepancy between Customer Billing Information and Sales Order billing information.  We added a conditional formatting whenever SO Billing Email and Customer Billing do not match and similarly for SO Bill To Attention and Customer Bill To Attention do not match as shown below: (this works as expected)Conditional formattingRequirement: Now, we want to add a custom field/ column to this GI called Update/ Action which tells you to update when there is a discrepancy between Customer Billing Information and Sales Order billing information. (i.e. SO Billing Contact, Customer Billing Contact, SO Bill to attention, Customer bill to attention)First implementation: We created a switch statement, which partially works (only when the first condition is not met)Switch statementThere are cases when customer and sales order billing email matches but not the attention, in that case - it returns blank (when it should say update) 2nd i

Hi all,I have created a generic inquiry to show total overdue by customer.  I want to show the trend of change customer like this. I know how to draw when fin-period is one column like this. Because I want to calculate the total overdue of customer base on each invoice due date, etc. So I have no idea how to combine them to one column period. I have also attached a GI I created for this. Pls advice me if you have any idea  Thanks in advance

Hello,We are trying to create a GI that flags if an more than one item has the same customer cross reference/ alternate IDs.  In an ideal situation - the result for the below should be 3. Results prior to groupingWe concatenated Alternate ID and Customer ID and did a count on the concatenationWe grouped the generic inquiry by AlternateID. However, the count only shows up as 1.Results after groupingIs there a way to display all line items, their alternate id, customer and counts. The expected result should look like below with an extra column towards the end called count with values 3 (in this scenario).Expected resultCould you please advise if this is achievable? 

Hello,I’m trying to get the subID (sub account ID) from the APTran connected to APPayments.This GI works fine, but for the majority of records, the bill and associated sub accounts are not displaying even though there is a bill and a sub account associated with the payment. I can’t figure out why these records are not displaying this information correctly. I’ve tried merging this information several ways. Most of the records not showing a bill were payments of the type check or voided check, but not every check or voided check is missing. Most of the debit adjustments are showing, but not every debit adjustment. Below is an example. The payment has one bill connected, and the bill has one payment. Any ideas why this would not show in the GI?one check under billsOne bill in checks and paymentsAttached I have the xml available.Thank you!

All, tearing my hair out over this. GL stuff is way above my head.Basically, we need to print out GL accounts associated with a batch. This is where it goes above my head… sometimes a batch also has an associated PO with an attached GL account (POLine.ExpenseAcctID).You can see in the first screenshot, the amazon GLTran is duplicated numerous times.In the second screen, I added a grouping, it fixed things visually, but it broke things more than fixed. I don’t think the issue is grouping but a relationship/join.Verde Valley Hardware and UPS correct; Linde and Amazon are repeatedTotals for Hardware and UPS are correct but shouldn’t be grouped, and Amazon is incorrectI’ve attached the xml if anyone could take a look… thank you!

Hello - we recently added a new GL and made that our main cash account. This has thrown off the reporting on our cash position dashboard widget. How do I add this new GL to the GI from which this widget is pulling data?  The GL account itself is set up the same as our other cash accounts, which do appear in this GI.Thank you!Some screenshots that I think are relevant:  

HiI am trying to get the last payment date for vendors on a generic inquiry.  Does anyone know where that field is stored or the best way to pull that date into a generic inquiry.Thanks!Coleen.  

Hello all,Whenever I use an equation (apologies if wrong terminology) in report writer, the formatting field doesn’t seem to affect the actual result. In this case, it will always return 4 decimal points when I try to format it to return 2. Take note of ‘Payment Applied’ - you can see it returns 4 decimal pointsWhenever I use an equation (for example, the above), it ignores the formattingI have tried =###,###.00 , =###,###.## , =######.## , =######.00 , all with and without the quotations as seen above. Any insight into this would be great!

Hi Everyone.Once again I need your assistance. I need to show the unit cost by year on the report designer.Each year needs to be on a seperate column and all the unit costs for that particuar period need to show.The structure would be similar to the screenshot attached. Is this possible. So the report will need to adjust based on the number of periods. The max periods should be around 3 to 5.So each column would be a specific period and the data in each column the unit cost of the inventory item. What I need assitsance with is showing the periods input in the parameters as columns.  

Is there a report I could run to discover what countries the materials consumed in a production order came from?  

HiI need to design a QT from scratch, it’s suppose to take the format provided below in an excel sheet.Which tools in report designer should I deploy to achieve this and more specifically draw the tables? Regards