Dynamics Community Forum Thread Details

Hi All,

I am trying to add a field to the Cart Line entity in MPOS using the Extensibility Framework described in following links

http://blogs.msdn.com/b/axsa/archive/2015/02/17/extensibility-in-dynamics-ax-2012-r3-cu8-crt-retailserver-mpos-part-1.aspx

http://blogs.msdn.com/b/axsa/archive/2015/05/20/extensibility-in-dynamics-ax-2012-r3-cu8-crt-retailserver-mpos-part-2-new-data-entity.aspx

however the above links talk about Extending the customer entity in which we have a corresponding SQL view.

But AFAIK for the transaction lines there is no corresponding SQL View as the data is stored as a binary field on the CartView. Any ideas how we can achieve the addition of the field?

Regards

Hitesh

Sounds like you need to be able to access those custom properties in Sales Order as well. Sales Order consists of number of SQL tables, the table which corresponds to a Sales Line (which is originating from a Cart Line) is RetailTransactionSalesTrans so you need to extend that table by adding a column, let's say CustomString (for this example I will specify its type as NVARCHAR(50) but you can have any other type. Then you need to customize CRT (see below) to store data corresponding to the Extension Property in that table. Eventually, when CRT reads an order's sales lines it is using SQL View crt.RetailTransactionSalesTransView, therefore that view should also be updated by including into the SELECT statement just added column. Once the View is modified the CRT will automatically include that column into a collection of ExtensionProperties of the SalesLine and finally you will be able to use its value in Retail Server client (POS in your case) the way you want.

Below are more specific details:

1. In the Data.Services project find a class SalesTransactionDataService and modify its method

private static NullResponse SaveSalesTransaction(SaveSalesTransactionDataRequest request)

by adding one more DataTable around line #1412 (here we are instantiating a DataTable which will hold ExtensionProperties values):

using (DataTable linesExtensionPropertiesTable = new DataTable("RETAILTRANSACTIONPROPERTIESTABLETYPE"))

While in the same method, a little bit further you will see several calls to such functions like PopulateXSchema, so, add there one more line (this is a call to the method which will fill the table with the schema):

PopulateExtensionPropertiesSchema(linesExtensionPropertiesTable);

While in the same method locate a call to FillOrderLines and add there an additional parameter which corresponds to just created DataTable, so the call would like something like this (note the new parameter linesExtensionPropertiesTable):

FillOrderLines(
  request.SalesTransaction,
  linesTable,
  incomeExpenseTable,
  markupTable,
  taxTable,
  addressTable,
  discountTable,
  reasonCodeTable,
  propertiesTable,
  affiliationsTable,
  invoiceTable,
  customerAccountDepositTable,
  linesExtensionPropertiesTable,
  request.RequestContext);

Last change in this method, almost at the end of it, will be to insert the following line (this way we will be able to pass newly created DataTable to the layer which works with Sql Server):

insertTablesRequest.SetProperty("extensionProperties", linesExtensionPropertiesTable);

right before this one:

request.RequestContext.Runtime.Execute<NullResponse>(insertTablesRequest, request.RequestContext);

2. While in the same file locate the function

private static void FillOrderLines

and add an additional argument to it:

DataTable linesExtensionPropertiesTable

Then, in the same method, find a call to FillItemLine and pass there new parameter linesExtensionPropertiesTable which was just introduced.

3. Modify the function FillItemLine by adding a new parameter

DataTable linesExtensionPropertiesTable

Then, at the very end of the method add the following call:

FillExtensionProperties(transaction, salesLine, linesExtensionPropertiesTable, context);

4. While in the same file add couple of new function which were referenced by the changes above:

            private static void PopulateExtensionPropertiesSchema(DataTable table)
            {
                ThrowIf.Null(table, nameof(table));
                table.Columns.Add(DataAreaIdColumn, typeof(string)).MaxLength = 4;
                table.Columns.Add(StoreColumn, typeof(string)).MaxLength = 10;
                table.Columns.Add(TerminalIdColumn, typeof(string)).MaxLength = 10;
                table.Columns.Add(TransactionIdColumn, typeof(string)).MaxLength = 44;
                table.Columns.Add(SaleLineNumColumn, typeof(decimal));
                table.Columns.Add(NameColumn, typeof(string)).MaxLength = 32;
                table.Columns.Add(ValueColumn, typeof(string));
            }


            private static void FillExtensionProperties(SalesTransaction transaction, SalesLine salesLine, DataTable propertiesTable, RequestContext context)
            {
                ThrowIf.Null(transaction, nameof(transaction));
                ThrowIf.Null(salesLine, nameof(salesLine));
                ThrowIf.Null(propertiesTable, nameof(propertiesTable));

                ChannelConfiguration channelConfiguration = context.GetChannelConfiguration();

                foreach (CommerceProperty commerceProperty in salesLine.ExtensionProperties)
                {
                    DataRow row = propertiesTable.NewRow();

                    SetField(row, DataAreaIdColumn, channelConfiguration.InventLocationDataAreaId);
                    SetField(row, StoreColumn, transaction.StoreId ?? string.Empty);
                    SetField(row, TerminalIdColumn, transaction.TerminalId ?? string.Empty);
                    SetField(row, TransactionIdColumn, transaction.Id);
                    SetField(row, SaleLineNumColumn, salesLine.LineNumber);
                    SetField(row, NameColumn, commerceProperty.Key);
                    SetField(row, ValueColumn, commerceProperty.Value.GetPropertyValue());
                    propertiesTable.Rows.Add(row);
                }
            }

This completes changes to the file SalesTransactionDataService.cs.

5. Find the file SalesTransactionSqlServerDataService.cs in the project Data.Services.SqlServer, it cntains the function

private static NullResponse InsertSalesTransactionTables(InsertSalesTransactionTablesDataRequest request)

which contains, at the very beginning, several similar lines where parameters are set, add there one more line:

parameters["@tvp_LinesExtensionProperties"] = request.GetProperty("extensionProperties");

This completes C# changes, what we have so far: we have modified a code so it now provides Extension Properties's values to the DB layer so in next steps we will need to modify couple of Stored Procs to leverage the new parameter added above.

6. Modify Stored Procedure crt.InsertSalesOrder by adding one more parameter:

@TVP_LINESEXTENSIONPROPERTIES       [crt].[RETAILTRANSACTIONPROPERTIESTABLETYPE] READONLY

While in the same Stored Procedure, find a call to the Stored Procedure crt.InsertSalesTrans and add one more parameter to that call - @TVP_LINESEXTENSIONPROPERTIES

7. Modify Stored Procedure crt.InsertSalesTrans by adding new parameter there (the same parameter you added to the Stored Procedure above):

@TVP_LINESEXTENSIONPROPERTIES [crt].[RETAILTRANSACTIONPROPERTIESTABLETYPE] READONLY

This is basically it - we have passed the extension properties all the way down to the stored proc where it should be saved and now you need to update the SQL in the way which is the best for your specific needs (depends whether you have one custom property or more of them and whether al o them of the same type or not and so on), as a sample I am providing the following code which assumes that you have just 1 (per sales line) Extension Property which will be stored I the column CustomString NVARCHAR, but, gain, you should modify the SQL the way it fits your needs:

UPDATE [ax].RETAILTRANSACTIONSALESTRANS 
	SET CustomString = properties.VALUE
	FROM [ax].RETAILTRANSACTIONSALESTRANS lines
	JOIN @TVP_LINESEXTENSIONPROPERTIES AS properties ON properties.TRANSACTIONID = lines.TRANSACTIONID
	 AND properties.SALELINENUM = lines.LINENUM
	 AND properties.DATAAREAID = lines.DATAAREAID
	 AND lines.CHANNEL = @bi_ChannelId

	SELECT @i_Error = @@ERROR;
    IF @i_Error <> 0
    BEGIN
        SET @i_ReturnCode = @i_Error;
        GOTO exit_label;
    END;

Community site session details

Extending the Cart Line in MPOS

Helpful resources

Quick Links

Daivat Vartak – Community Spotlight

Announcing Our 2025 Season 1 Super Users!

Tip: Become a User Group leader!

Leaderboard

Featured topics

Product updates