CREATE VIEW Statement in Acumatica Customization Project

Question

Does anyone know how to push a CREATE VIEW SQL statement into an Acumatica Customization Project?

I know how to do it in SQL Management Studio, but doing it through a Customization Project would be useful for Acumatica SaaS customers.

views are not recommended, however you can just do a SQL Script in a customization project to create the view, then create a DAC for that View — Brendan
Brendan is correct as per standard customization. Though you could still. Once view is created you can add it as a DAC in Customization. It would be best to have a link attributes for drill down functionalities. — xxxAcuGeekxxx

RuslanDev RuslanDev · Accepted Answer · 2017-10-13T03:10:32

You should perform the following steps:

Create your SQL View in Management Studio (for demo purposes let me stick to a simple PositivePay view):

CREATE VIEW [dbo].[PositivePay] AS
SELECT
    APPayment.RefNbr,
    APPayment.ExtRefNbr,
    APRegister.DocDate,
    APRegister.OrigDocAmt,
    CashAccount.ExtRefNbr BankAccountID
FROM APPayment 
JOIN APRegister 
    ON APRegister.CompanyID = APPayment.CompanyID 
        AND APRegister.RefNbr = APPayment.RefNbr 
JOIN CashAccount 
    ON APPayment.CashAccountID = CashAccount.AccountID 
        AND APPayment.CompanyID = CashAccount.CompanyID 
WHERE APPayment.CompanyID = 2 AND APPayment.DocType= 'CHK'
GO

Open your customization project and click on Code, then generate new DAC based on your SQL view as shown in the screenshot below:
Define key fields for your new DAC and save changes. For PositivePay we set IsKey to true for the PXDBString attribute on top of the RefNbr field:
```
[PXDBString(15, IsUnicode = true, InputMask = "", IsKey = true)]
[PXUIField(DisplayName = "Ref Nbr")]
public string RefNbr { get; set; }
```

Click on DB Scripts, select your SQL view name in DBObject Name and put SQL script into the Custom Script control following the pattern below:

IF EXISTS
(
    SELECT * FROM sys.views
    WHERE name = 'PositivePay' AND schema_id = SCHEMA_ID('dbo')
)
DROP VIEW [dbo].[PositivePay]
GO

CREATE VIEW [dbo].[PositivePay] AS
SELECT
    APPayment.RefNbr,
    APPayment.ExtRefNbr,
    APRegister.DocDate,
    APRegister.OrigDocAmt,
    CashAccount.ExtRefNbr BankAccountID
FROM APPayment 
    JOIN APRegister 
        ON APRegister.CompanyID = APPayment.CompanyID 
            AND APRegister.RefNbr = APPayment.RefNbr 
    JOIN CashAccount 
        ON APPayment.CashAccountID = CashAccount.AccountID 
            AND APPayment.CompanyID = CashAccount.CompanyID 
WHERE APPayment.CompanyID = 2 AND APPayment.DocType= 'CHK'
GO

CREATE VIEW Statement in Acumatica Customization Project

1 Answers