Solved

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

  • 13 November 2023
  • 14 replies
  • 168 views
C
Rank
Userlevel 4
Badge +1

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

icon

Best answer by Naveen Boga 14 November 2023, 06:35

View original

14 replies

lauraj46
Rank
Userlevel 7
Badge +7

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

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
Robert Sternberg
Rank
Userlevel 7
Badge +8

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

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

Robert Sternberg, Crestwood Associates -- ** Summit 2024 - Visit Us at Booth #600 ** -- If a community member helped you, please *Like* their post/reply. If they provided an answer to your question, please mark it as *Best Answer*.
C
Rank
Userlevel 4
Badge +1

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 

Robert Sternberg
Rank
Userlevel 7
Badge +8

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, Crestwood Associates -- ** Summit 2024 - Visit Us at Booth #600 ** -- If a community member helped you, please *Like* their post/reply. If they provided an answer to your question, please mark it as *Best Answer*.
C
Rank
Userlevel 4
Badge +1

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

Robert Sternberg
Rank
Userlevel 7
Badge +8

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, Crestwood Associates -- ** Summit 2024 - Visit Us at Booth #600 ** -- If a community member helped you, please *Like* their post/reply. If they provided an answer to your question, please mark it as *Best Answer*.
C
Rank
Userlevel 4
Badge +1

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

M
Rank
Userlevel 1

@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

Naveen Boga
Rank
Userlevel 7
Badge +17

@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.

 

 

 

1 Attachment
Naveen B
dcomerford
Rank
Userlevel 7
Badge +12

@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

[PXDBString(4, IsFixed = true, IsKey = true, InputMask = "")]

Dermot Comerford
C
Rank
Userlevel 4
Badge +1

@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.

dcomerford
Rank
Userlevel 7
Badge +12

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

Dermot Comerford
Robert Sternberg
Rank
Userlevel 7
Badge +8

@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

[PXDBString(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. 

Robert Sternberg, Crestwood Associates -- ** Summit 2024 - Visit Us at Booth #600 ** -- If a community member helped you, please *Like* their post/reply. If they provided an answer to your question, please mark it as *Best Answer*.
Naveen Boga
Rank
Userlevel 7
Badge +17

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

Have you checked the GI?

Naveen B

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved