By using this website, you agree to our Terms of Use (click here)
I pretty new to this, but I’m attempting to create a SQL view on the table ARInvoice to use in a Generic Inquiry, which in turn will be used in a Scorecard KPI widget. I might be missing it, but I don’t see any Financial Period in the ARInvoice table which I presumed might provide a link to the FinPeriod table to furnish the required Financial period. Hoping you can point me in the right direction?
You might ask why I need to use a SQL View? My widget needs to display Debtors Days which I don’t think I can accomplish with a Generic Enquiry. The calculations are a bit too complex i.e. (Total Amount Owed by Debtors / Divided by a rolling 12 months Nett total sales) * 365. I’d be happy to have egg on my face if you can show otherwise.
Hi Grant,
I agree. I think you'll probably need a SQL View for this. No egg needed though 😀
The financial period is actually stored in the ARRegister table: ARRegister.FinPeriodID.
The primary keys are the same on both ARInvoice and ARRegister: DocType and RefNbr.
Why do they put some fields in ARInvoice and others in ARRegister? I'm not really sure, but I think they tried to put fields that are common to every AR document in ARRegister, regardless of whether it's an invoice or a payment. That would make more efficient use of the database storage. But it makes it more of a headache for writing a report.
Hi Tim
Thanks for the pointer to ARRegister and confirmation for the need for a SQL view.
Kind Regards - Grant
Absolutely. I hope you were able to get everything working the way you wanted it.
Hi tim,
How am i going to create a SQL View in GI? i think that is the answer to my problem since my boss wanted a complicated GI and can only be achieved if i make it in plain sql, not in the table and relationship definition in GI Editor. But my problem is, i cant even create a customization project in acumatica, our provider here restricted us to create custom project.
Thanks in advance.
Hi Jerwin,
I just responded on your post about security permissions:
https://www.augforums.com/augforums/everything-else/editing-project-restriction
As for creating a GI on a SQL View, checkout this post from Doug Johnson:
https://www.acumatica.com/blog/technical-tuesday-report-from-sql-view/
hi tim,
i have seen that article regarding SQL view, but our subscription is SaaS, how i am going to access the SQL database from that perspective?
Jerwin,
You can use the Database Scripts section of a customization project to add a SQL view to a SaaS implementation. The help information is outlined here: https://help.acumatica.com/(W(1))/Main?ScreenId=ShowWiki&pageid=cb776a15-1661-48a4-994c-1e91412d2227
Basically you write your SQL view and decorate it with the [mssql] tag. Once you build the project you can create the DAC.
Great article @kurtbauer
In addition, you can also look in the Query Quarry Customization Project (click here and scroll to bottom) for more examples of SQL Views that get pushed into the database via a Customization Project.
Great way of creating a view! Although it seems like a journey to be able to finally see it in a GI.
I tried creating the DAC out of the view per the instructions:
https://www.acumatica.com/blog/technical-tuesday-report-from-sql-view/
DAC name same as view, but at the end it runs into validation isues.
Try removing the spaces and any special characters like the parenthesis from your SQL view column names and Acumatica DAC. For example, rename Inventory ID to InventoryID and Description (Short) to DescriptionShort. You can use the display name attribute to add the spaces and special characters back to the columns.
-Kurt
I've run into the same problem before. I wish the script would just take the column names from the SQL View and not try to be so smart.
Online Members
