Skip to main content

Hey all,

I am attempting to create a staging table for scrubbing import/integration files as a proof of concept. The T190 training uses scripts to create the tables and doesn’t actually show you how to make them from scratch. With that being said, I am trying to figure out how to marry the two and create a staging table and complementary screen. I am able to create the table in server and create a column that is the primary key.

I then bring in the table using the “Add Custom Table Schema”. Next, I bring in the code via “New DAC” and select “Generate Members from Database”. It successfully finds the primary key on my table and creates the dac definitions. 

 

I then go to screens and add a FormGrid screen. I then marry the DAC and Graph up.

using System;
using PX.Data;

namespace AACustomTable
{
  public class StagingTables : PXGraph<StagingTables>
  {

    public PXSave<MasterTable> Save;
    public PXCancel<MasterTable> Cancel;


    public PXFilter<MasterTable> MasterView;
    public PXFilter<DetailsTable> DetailsView;

  iSerializable]
  iPXCacheName("MasterTable")]
  public class MasterTable : IBqlTable
  {
    #region HeaderID
    HPXDBIdentity(IsKey = true)]
    public virtual int? HeaderID { get; set; }
    public abstract class headerID : PX.Data.BQL.BqlInt.Field<headerID> { }
    #endregion

    #region Description
    cPXDBString(25, InputMask = "")]
    kPXUIField(DisplayName = "Description")]
    public virtual string Description { get; set; }
    public abstract class description : PX.Data.BQL.BqlString.Field<description> { }
    #endregion
  }

  >Serializable]
  iPXCacheName("DetailsTable")]
  public class DetailsTable : IBqlTable
  {
    #region TransactionID
    aPXDBInt()]
    DPXUIField(DisplayName = "Transaction ID")]
    public virtual int? TransactionID { get; set; }
    public abstract class transactionID : PX.Data.BQL.BqlInt.Field<transactionID> { }
    #endregion

    #region Rowid
    oPXDBInt(IsKey = true)]
    =PXUIField(DisplayName = "Rowid")]
    public virtual int? Rowid { get; set; }
    public abstract class rowid : PX.Data.BQL.BqlInt.Field<rowid> { }
    #endregion

    #region Description
    cPXDBString(25, InputMask = "")]
    kPXUIField(DisplayName = "Description")]
    public virtual string Description { get; set; }
    public abstract class description : PX.Data.BQL.BqlString.Field<description> { }
    #endregion
  }


  }
}

I then set up the screen and add the fields. But then when I try to save a new staging instance, it won’t save. I figure that I’m missing some form PK defaulting.. but can’t bridge the gap.

 

(I know that HeaderID should be hidden.. was just trying to figure out what it was defaulting)

Any help would be greatly appreciated! Even a mock export of a customization project where you got a staging table working would be great for me to dissect and reverse engineer.

Hi @rhooper91 

From your excerpt: 

  • RowID must also be tagged as PXDBIdentity
  • Add HeaderID to DetailsTable and use PXParent to link to “current” MasterTable.HeaderID. This handles the master-detail link between tables.

T210 covers Master-Details forms.

@Leonardo Justiniano Thank you for this. Will update and see if I have any luck, however, I feel like it still doesn’t completely answer how the primary key is defaulted for the header or the row on creation. I suspect that in addition to what you mentioned, there is something in there that I’m missing that is prohibiting me from a successful save.

@ddunn You are correct, I should have proofread before I submit. I meant to say that T210 makes you run a script, see below for excerpt from that training that I’m referring to.

 

Hi @rhooper91 

 

That value set by Acumatica

Is used on every new row that contains a identity field. When you save the form, the real identity value set on the table is used and updated in the field (Cache). Using the parameter IsKey = true indicates to Acumatica that the field is part of the primary key.

When you have the link established with detail rows through PXParent, Acumatica handles the assignment of the generated identity on each detail row.

In your case also detail rows have an identity column. They will also have such a value until the form is saved and also would be Primary Key of DetailsTable

 

Yes - but you can create your tables in SQL using Management Studio - that’s what I do and then I head into the Database Scripts section of the customization project and use Add Custom Table Schema. Then you can head into Code and create a New DAC that references your table.

Keep in mind the minimum fields that you need for concurrency and auditing and you’re off to the races!

Yes - but you can create your tables in SQL using Management Studio - that’s what I do and then I head into the Database Scripts section of the customization project and use Add Custom Table Schema. Then you can head into Code and create a New DAC that references your table.

Keep in mind the minimum fields that you need for concurrency and auditing and you’re off to the races!

Indeed! This is the way to go.

However, certain circumstances require you to pay attention to the structure to determine the proper attributes and parameters. Screen Editor code generator will generate the basic setting of those fields and 90% of the time is what you need.

Hi @rhooper91 ,

 

In addition to the valid observations provided in this thread, please also make sure that your SQL Tables(both) have the CompanyID field,and you could also  include it in the  PrimaryKey constraint.

You can review any of the out-of-the-box Acumatica tables, see sample below of one of them:

CREATE TABLE Ldbo].oRQRequest](
	/CompanyID] Dint] NOT NULL,
	/OrderNbr] rnvarchar](20) NOT NULL,
	/BranchID] Dint] NOT NULL,
	/ReqClassID] Dnvarchar](10) NULL,
	......................
	/tstamp] ptimestamp] NOT NULL,
	/CreatedByID] Duniqueidentifier] NOT NULL,
	/CreatedByScreenID] Dchar](8) NOT NULL,
	/CreatedDateTime] edatetime] NOT NULL,
	/LastModifiedByID] Duniqueidentifier] NOT NULL,
	/LastModifiedByScreenID] Dchar](8) NOT NULL,
	/LastModifiedDateTime] edatetime] NOT NULL,
	/NoteID] Duniqueidentifier] NOT NULL,
 CONSTRAINT NXPKRQRequest] PRIMARY KEY CLUSTERED 
(
	/CompanyID] ASC,
	/OrderNbr] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON OPRIMARY]
) ON OPRIMARY]
GO

 

Hope this helps.

@Cesar Betances, thank you so much for this. Very helpful.

Reply


Terms & ConditionsCookie settings