Reports, GIs, Dashboards, Pivots
Recently active
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
We have created a GI and we are calculating values in the Results Grid to come up with a Percentage Complete. Formula is (ActualAmount / RevisedAmount)*100. This should give us a Percentage Result.However, The Calculation results are inconsistent.For Example, the Actual Amount is 2400 and the Revised Amount is 4800. The Percentage Result is coming back at 100. It should be 50%.Another Example, The Actual Amount is 52716 and the Revised Amount is 138000. The percentage Result is coming back as 76% it should be 38%.But then in another line example the calculation is correct. The Actual amount is 7992 and the Revised Amount is 14800 and the resulting percentage is 54% and this is correct.We can not figure out why this calculation is incorrect and inconsistent.here is the actual line in the results grid.=CInt(IIF([BudgetRevenue.CuryRevisedAmount]=0,0 , ([BudgetRevenue.CuryActualAmount])/[BudgetRevenue.CuryRevisedAmount]*100))
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!
Hi,I want to create a notification template and use GI as screen but now it doesn’t list out the all email fields in the “To” field. The GI is using customized DAC and I already linked it with Users DAC but still its not showing Since I want to triggered the template using the Schedule so I cannot use others screen unless GI. Can anyone help me. Thanks
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:
Hi,I have recently increased the character limit of the description fields to 4000for the Tasks, Revenue and Cost Budget tabs in the Projects screen, and I am wanting to replicate this in the report that pulls in these description fields.Is it possible to increase the amount of characters that a field can hold in the report designer? Or is it set to a maximum that isn’t able to be changed?Kind regards,Andrew
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
How can I add the ship to State to the Sales Order by Customer Report - or any sales order report? Thanks
