Reports and Generic Inquires

I'm working on a query to join GLTran data to Sales Order (SOOrder or SOLine) and need some pointers if anyone knows.From what little I could find, I need to join GLTran and Sales Order through ARTran. I have the join between GLTran and ARTran producing correct results. However, I'm struggling on the Sales Order to ARTran join as it's not producing the correct results. Below is what I have thus far.Any help or guidance is much appreciated! select top 100 *from acumatica.dbo.SOLine sol left join acumatica.dbo.SOShipLine sl on sol.OrderNbr = sl.OrigOrderNbrand sol.LineNbr = sl.OrigLineNbrand sol.OrderType = sl.OrigOrderTypeleft join acumatica.dbo.SOShipment SS on sl.CompanyID = ss.CompanyIDand sl.ShipmentType = SS.ShipmentType and sl.ShipmentNbr = SS.ShipmentNbrleft Join acumatica.dbo.ARTran AR on sl.CompanyID = ar.CompanyIDand sl.OrigOrderType = ar.SOOrderTypeand sl.OrigOrderNbr = ar.SOOrderNbrand sl.LineNbr = ar.SOOrderLineNbr 

I’m fairly new in working on Generic Inquiries, and would like some guidance on how best proceed. I’m attempting to pull in attribute values from inventory item on a quote. My existing table is below:​​​All of the Join Types are set to Inner:When I view the inquiry, it is essential replicating the lines: The quote itself only has 4 items: 

Hello, Does anyone know the way to sum a field in the detail section into the foot section based on what is retuned from the SO? I currently am using the SO lines in the detail section, but the total is coming off the invoice record. There are times we need to run this report prior to the invoice being posted, so my total in the footer does not populate until we process the invoice. Any thoughts?   

I see many discussions on this topic and I have not seen any successful responses, despite a lot of effort. I would like to tackle this and provide a solution to the community. We simply need remittances to be sent to customers for all payments, whether bill payments or prepayments, sounds simple ... IssueRemittance advice only gets sent when a bill is paid. Prepayments are not sent. For the life of me, I cannot figure out why this is. Notification Template Business EventWhen you view the underlying GI that drives this BE (see below), it includes all payments (bill pmts and prepayments), but only the bill pmts get sent/created. I can’t see how this is possible.In reviewing this configuration above, is there anything that can be identified which would block a prepayment from being created? 

I built a Generic Inquiry with all available Production Orders ready to be stocked/put away.  The hope/expectation is they would print a report out with the From location barcode, To location barcode (physical locations not barcoded currently), and the Inventory ID (some Inventory is not labeled with a barcode).I exported it to a report and copied existing reporting logic to create a report.  GI Data:  Report designer: Acumatica Report: Expected:  I have added new fields to existing reports, but my first new report is giving me some trouble.  I verified my URL is the same, everything ported over from the GI as expected.  All the field names match the table/column targets.  It doesn’t appear to be a barcode issue since the basic data fields are not populating.

What is the best way to find the reason that an inquiry comes up empty for a user?I have an inquiry using the POReceiptLine, POOrder, and SOOrder tables to show all inbound inventory (Things on a PO and things on an SO of type TR).When I (admin) run this, I get data. When my user runs this, he gets the title of the inquiry and no data at all (he has Delete rights to the inquiry).My User’s Screen:My Screen:  He can access PO Receipts, PO Orders, and Sales Orders (all of the tables I’m using). If I give him Admin rights, he gets the data. I don’t have any restriction groups for him. Where would you all suggest checking for this issue?

I am trying to modify the vendor shipments packlist. I do not want it to show the type WIP on the report. Any suggestions?

Hi all,I am trying to build a GI to monitor quantities being sold on our finished good products against the bulk being used in said finished good products. I am using the INSiteStatus table two times to see both QTY Avail. in one row. The issue I am running into is some items (doesn’t matter if it is considered a finished good or BULK item) are appearing multiple times due to being in multiple location IDs. I want to use the total summed QTY Avail. on each item. Is there a way I can do this using the current tables or am I using the incorrect tables? See below for my set up and results.  

all the check mark place have been gray out.how do i get it working 

Hi Everyone,We have a requirement to display the cumulative revenue for a selected period. Currently, in the existing Generic Inquiry DB-DASHBOARD GI 2, there is a field named Revenue that calculates the monthly revenue using the following formula:=IIf(LEFT([Account.AccountCD],1)='4', [GLHistory.FinPtdCredit]-[GLHistory.FinPtdDebit], 0).We have now created a new field called CumRevenue and need to calculate and display the cumulative revenue for the selected period using this field. Could you suggest an approach to achieve the cumulative revenue calculation and display

How to Prepare Generic Inquiry to FInd Out Days of Stock & Avg Qty Sold Perday.

I am trying to extract approval information for journals including amounts. Challenge is how to extract more that one approver in one row instead of multiple rows

We found an item that has a phantom quantity stuck in-transit. - Not sure how this has not been caught prior, but this is a very slow mover item.  I have a 2 step transfer that was done from 1 inventory to another.  full quantity was received, but a balance still shows on the inventory allocation as an incoming amount still to receive.  Not sure how to clear this.  This one item has 2 transactions 59190 and 59972 that are stuck like this,  No adjustments have been done on this item.  Lot # matches everywhere also.  you can see on the transfer, it shows zero in-transit qty available. 

Hello All,First time user of this platform!I have a two segment subaccount each with two numerics like this 02-08 and need to create a generic inquiry that contains first two numerics of the first segment and the GL account number without the second segment.For example, current subaccount string with GL account is 02-08-6115, I need the GI to show just 02-6115.Would really appreciate help with this.  Many thanks!

I'm working on creating a generic inquiry for users that displays their screen access rights in Acumatica such as Revoked, Delete, etc.—but I haven't been able to fine any table that stores this data. Has anyone attempted this before?

where would I find this information so I can write a GI?

I have a customer exposing dashboards to the mobile app.   When the flag to expose is set on the dashboard it will appear on the mobile.  But later, without any changes, the dashboard is not on the menu of the ios mobile app.  Resetting the flag will sometimes resolveversion is 2022R118Is it required to expose dashboard via the Mobile Workspace?  Or can they simply use the expose flag in the dashboard settings?Has anyone had issues with dashboard on the ios mobile app?This has been presented to support but I am looking for community feedback - thank you   

Hi All, I am looking to create an actual to budget expense report by sub account for my managers on a monthly basis once close is complete - I have started this, but not complete yet. Does anyone have any implemented solutions you could share with me where managers of departments are seeing their budget to actual reporting? A few things:My managers do not have access to the finance module A PDF generated report would be fine - further details can be directed to Finance Anyone have a process that exports the file by sub monthly (with ease) Any security setup for viewing within the system? Granting access to the Finance module is not what I want to do.Any help and support is appreciated 

I have applied all formulas but its not workinganyone have an idea in 2025 

I want to create a GI that shows the attachment/file Icon like this one on the Purchase Receipts GI. This will actually be the file source I am wanting to duplicate in my GI. 

I need to subtract the sales order of today from yesterdayIn excel as shown below I can put this formula (+C7-C6) and get the difference between the numbers from yesterday and today. how can I do this in Acumaticathis is my Grid 

Hello! I run a ledger report which includes all our all our Companies. However, I would like a column included on the General Ledger report which indicates which company the line was recorded to. Is this a possibility? Does any one know where I can go? 

  Alright, not sure where to begin troubleshooting this so I thought I would bring it to the group.  I have created a pretty simple dashboard to highlight FLM Appointments that are over 1 day old for a certain Service Order Type.  The filter that I have created seems to be working from a number return but the drill down doesn’t match the number return.Screenshots:  The Result of 2 out of the 5 being over 1 Day Old is correct, I have went and manually validated that, but when  I click into the drill down it only gives me one result.  Any Thoughts? Thanks,Chris Lower

Ok, I'm sure I'm missing something super simple. i have a report that I want to summarize open/unpaid balances for a customer where certain Sales Orders are Flagged (with a manual) Checkbox. I have the schema and all data flowing properly in the report EXCEPT when it comes to totaling the report.   I assumed that I could accomplish this with grouping, but that doesn't seem to work.In the report you can see i have 2 Sales Orders.  The report should calculate the total unpaid, but it is circling through and adding up the unpaid balance from ALL the lines on the SO.  I thought if i put the 'totals' in a group and grouped by Customer AcctCD that it would sum at that level but I"m obviously missing something:  

Hi,I have observed in 2024 R2, when we provide the parameters to Run report, it has to provide the parameters by searching it in the list of values. Previously when using report filters, while entering data the system would show a dynamic drop down box with applicable items. Now it shows as if no applicable items are available even though when the search icon for the filter is clicked the pop up table shows the applicable item. As an example, when we run the inventory balance report for a particular item or a particular warehouse, when we type the warehouse name halfway it will not suggest the existing warehouses with the types key letters even though it is available in the system. Instead we have to click on the search icon and select the warehouse from the list. Is there way we can get the warehouse or items suggested like before.  Thanks!