Skip to main content
Solved

DAC created by using SQL view can not be used in GI and Report Designer

  • 13 November 2023
  • 14 replies
  • 296 views

Hi All,

I have created few SQL views and Tried to use those in a GI as well as in Report Designer. 

But when I come to GI or Report Designer, it does not show those DACs as a table in the either in GI or Report Designer.(I have noticed that sometimes it shows some DACs when I recreate those DACs in my customization project editor)

following is one of the DB Scripts of that view and its’ DAC.

CREATE OR REPLACE VIEW MASite As
SELECT I.InventoryCD, I.InventoryID, Site.SiteID, Site.SiteCD, I.CompanyID
FROM InventoryItem I CROSS JOIN InSite Site;
using System;
using PX.Data;

namespace MATest
{
  /Serializable]
  /PXCacheName("masite")]
  public class masite: IBqlTable
  {
    #region InventoryCD
     PXDBString(30, IsUnicode = true, InputMask = "")]
     PXUIField(DisplayName = "Inventory CD")]
    public virtual string InventoryCD { get; set; }
    public abstract class inventoryCD : PX.Data.BQL.BqlString.Field<inventoryCD> { }
    #endregion

    #region InventoryID
     PXDBInt()]
     PXUIField(DisplayName = "Inventory ID")]
    public virtual int? InventoryID { get; set; }
    public abstract class inventoryID : PX.Data.BQL.BqlInt.Field<inventoryID> { }
    #endregion

    #region Siteid
     PXDBInt()]
     PXUIField(DisplayName = "Siteid")]
    public virtual int? Siteid { get; set; }
    public abstract class siteid : PX.Data.BQL.BqlInt.Field<siteid> { }
    #endregion

    #region Sitecd
     PXDBString(30, IsUnicode = true, InputMask = "")]
     PXUIField(DisplayName = "Sitecd")]
    public virtual string Sitecd { get; set; }
    public abstract class sitecd : PX.Data.BQL.BqlString.Field<sitecd> { }
    #endregion
  }
}

Any help would be much appreciated!

 

Thanks

Hi @charithalakshan49 ,

In order to expose your new view in Acumatica, you will need to define the DAC in a customization project.  You can use the Generate Members from Database checkbox wizard to autogenerate the DAC definition, and then adjust it as needed.

 

Hope this helps!

Laura

Are you SaaS hosted by Acumatica (URL will look like x.acumatica.com)

If not, are you using a MS SQL backend or MySQL?

Hi @lauraj46 , I already generated the DAC as you said. I mentioned one of my DACs in the question as well.

 

HI @Robert Sternberg I tried this in my local Acumatica instance. Backend is MYSQL 

It might be worth dropping the view entirely before recreating the view.  You should have this in the customization project under scripts.  

 

DROP VIEW IF EXISTS MASite;

CREATE VIEW MASite AS SELECT I.InventoryCD, I.InventoryID, Site.SiteID, Site.SiteCD, I.CompanyID FROM InventoryItem I  CROSS JOIN InSite Site;
 

@Robert Sternberg I tried that as well. But Still it does not work. My Acumatica Version is 22.218

I noticed some inconsistencies in your naming.  Could you correct them and try again?

VIEW MASite As

vs

namespace MATest

vs

>PXCacheName("masite")]

@Robert Sternberg I tried changing those as well, but no luck

@Robert Sternberg I tried changing those as well, but no luck

I’m having the exact same issue and have a ticket open with support on this.  I noticed that when I deploy multiple custom SQL views and custom DACs, some of them disappear.  My latest test was to put all CREATE VIEW scripts in one customization project and all DAC definitions in another.  This seems to have worked, but now I’m having a strange issue where records are duplicating themselves only in report designer - the exact same table and relationships in a GI are presenting as expected.

 

Let me know if you find a solution to this issue.  

 

Mike

@charithalakshan49  I have verified this and I don’t see issues. I have created a customization package with SQL View, DAC and Generic Inquiry. Please deploy in your instance and check.

Also, please find the screenshots for reference.

 

 

 

@charithalakshan49 If you (re)publish customisation packages that use SQL views/DAC sometimes the DAC may not be visilbe in the GI and seems to have disappeared. The solution is to restart the application and it will reappear so that is worth trying in this case. We always restart the application now if the customer has any SQL views in play after publishing.

@mikegrci if you are seeing duplicated records you need to add the IsKey = true to any string field in the DAC

pPXDBString(4, IsFixed = true, IsKey = true, InputMask = "")]

@dcomerford @Naveen Boga  I tried publishing the same package in another instance and it works. But as @mikegrci said, my GI shows expected results while Report shows some unexpected results. Those extra ones seems to be duplicated records. But GI shows correct records without any issue as I mentioned.

@charithalakshan49 Have you changed the entry on the DAC to add the IsKey = true

@charithalakshan49 If you (re)publish customisation packages that use SQL views/DAC sometimes the DAC may not be visilbe in the GI and seems to have disappeared. The solution is to restart the application and it will reappear so that is worth trying in this case. We always restart the application now if the customer has any SQL views in play after publishing.

@mikegrci if you are seeing duplicated records you need to add the IsKey = true to any string field in the DAC

pPXDBString(4, IsFixed = true, IsKey = true, InputMask = "")]

 

I have had this issue with multiple DACs as well, what has helped in the past is to use the ‘Execute all database scripts’ option when publishing.  This is available if you use the ‘publish to multiple tenants’ function. Be careful of this function if you have other customization projects published that have single-use scripts. I avoid single-use scripts in my projects for this reason. 

 

I like the idea of restarting the application which @dcomerford mentioned, I will have to try that in the future. 

@charithalakshan49 In the customization package, which I provided I already added the KEY fields to avoid the duplicate records.

Have you checked the GI?

Reply


Terms & ConditionsCookie settings