Add records to a table within a table

Imagine a Purchase Request process where by default one product is added to the request when a case is started and some suppliers are added to the product.

Thus, a record of the table Products is created and then a record to the table Suppliers within Products is added.

•There is a one-to-many relationship between the Process Entity PurchaseRequest and the collection entity ProductsRequested.

•There is a one-to-many relationship between the Entity ProductsRequested and the collection entity PossibleSuppliers.

•The the one is called PurchaseRequest.

•The collection entity is called ProductsRequested.

•The second collection is called PossibleSuppliers

An expression is created On Enter of the Purchase Request process to add products and suppliers.

The following is the list of the variables created for the expression.

The first thing to do is to add the Products, that is, the external table:

var parameters = new FilterParameters();

parameters.AddParameter("@Code", "1");

ProdType = CHelper.getEntityAttrib("Producttype","idProducttype","Code = @Code", parameters);

Product = Me.newCollectionItem("PurchaseRequest.ProductsRequested")

Product.setXPath("Producttype",ProdType);

Product.setXPath("Quantity",10);

Once created, go use an Iterate Over XPath module to go through each Product and add the records of the second level table within.

Finally we add the Supplier records to each record of the Products collection.

The Me.newCollectionItem changes in the first variable to the Iterate variable.

Supplier = Me.getXPath("entity-list('Supplier', '')");

for(var i=0;i<Supplier.size();i++)

{

 NewSupplier= NewProducts.newCollectionItem("Possiblesuppliers");

 NewSupplier.setXPath("Offeredunitprice",10);

 NewSupplier.setXPath("Supplier",Supplier[i].getXPath("Id"));

}

Notice that you can also obtain the id with the name of the primary key (surrogate key)

for(var i=0;i<Supplier.size();i++)

{

 NewSupplier= NewProducts.newCollectionItem("Possiblesuppliers");

 NewSupplier.setXPath("Offeredunitprice",10);

 NewSupplier.setXPath("Supplier",Supplier[i].getXPath("idSupplier"));

}

Option 2

Another way to add records to a table within a table is by using an newCollectionItem over a variable that contains another newCollectionItem. This way we do not need to use an iterate over XPath.

An expression is created On Enter of the Purchase Request process to add products and suppliers.

The following is the list of the variables created for the expression.

The first thing to do is to add the Product, that is, the record of the external table. Use the Me.newCollectionItem method.

Now from the Supplier parameter entity obtain the list of suppliers to be added to the second level (internal) table.

Go through the list of suppliers and create a record in the Possible suppliers table for each supplier.

Note Me.newCollectionItem changes in the first variable to the related to the external table. (Product.newCollectionItem)

//Get the product from the parameter entity

var parameters = new FilterParameters();

parameters.AddParameter("@Code", "1");

ProdType = CHelper.getEntityAttrib("Producttype","idProducttype","Code = @Code", parameters);

//Add the product to the first level table

Product = Me.newCollectionItem("PurchaseRequest.ProductsRequested");

Product.setXPath("Producttype",ProdType);

Product.setXPath("Quantity",10);

//Get the suppliers from the parameter entity

Supplier = Me.getXPath("entity-list('Supplier', '')");

//For each supplier

for(var i=0;i<Supplier.size();i++)

{

//Add the supplier to the second level table

NewSupplier= Product.newCollectionItem("Possiblesuppliers");

NewSupplier.setXPath("Supplier",Supplier[i].getXPath("Id");

}

In here, you can also access the id using the primary key.

NewSupplier= Product.newCollectionItem("Possiblesuppliers");

NewSupplier.setXPath("Supplier",Supplier[i].getXPath("idSupplier");

}