Skip to main content
M
martab0003
June 30, 2021
Question

Query a nested CDT

  • June 30, 2021
  • 3 replies
  • 3 views

Hello everyone,

I kindly ask for a piece of advice doing this query.

I have a CDT called PurchaseOrderDetails and another one called ProductOrderedDetails. In this case, my PurchaseOrderDetails is pointing to an array of ProductOrderedDetails, like in the image below.

I was wondering what can be done in case I want to get all the information regarding the ProductOrderedDetails for a specific Purchase Order.

I already tried a few variations pointed in this page (https://docs.appian.com/suite/help/21.2/cdt_design_guidance.html) but I'm still unable to retrieve that information.

In the MySQL database, the PurchaseOrderDetails doesn't have a specific column called "ProductOrdered", but my ProductOrderedDetails column has one called:

On this case, what it's the best way to build queries around nested CDTs like this example?

Kind regards

3 replies

acaciob0002
acaciob0002
July 1, 2021

Hi Marta, 

Im not sure if  fully understood your scenario but seems to me that what you described is correct. In your PurchaseOrderDetails you will have and column called productOrdered that should have the PK from the ProductOrderedDetails.

As you can see in the link that you attached in your post, you can create a normal queryEntity using the Pk of the PurchaseOrderDetails and in the return of this query you will receive all the ProductOrderedDetails related.

To access the data in there you can use index or dot notation in the productOrdered.

Please correct me if I did not understood your scenario.

Regards,

Acacio B.

M
martab0003Author
July 1, 2021

Hi Acacio,

Thanks for the information.

Our PurchaseOrder (Parent) has the possibility to have many (child array) ProductOrdered.

I'm able to get the information regarding both, but whenever I query the child via expression rule, I never get a reference to the parent.

For example, using this code:

if(
  rule!P2P_isNullOrEmpty(ri!productOrderedId),
  null,
  cast(
    'type!{urn:com:appian:types:P2P}P2P_ProductOrderedDetails?list',
    a!queryEntity(
      entity: cons!P2P_POINTER_ENT_PRODUCT_ORDERED_DETAILS,
      query: a!query(
        logicalExpression: a!queryLogicalExpression(
          operator: "AND",
          filters: {
            a!queryFilter(
              field: "productOrderedId",
              operator: "=",
              value: ri!productOrderedId
            )
          }
        ),
        pagingInfo: a!pagingInfo(startIndex: 1, batchSize: - 1)
      )
    ).data
  )
)

I'm able to retrieve the information regarding a specific ProductOrdered, but I never get a reference to the parent.

My goal is to be able to retrieve the PurchaseOrder number via a ProductOrdered.

Nevertheless, I do have a reference to the parent in the database:

I just can't seem to retrieve the info via expression rule.

acaciob0002
acaciob0002
July 1, 2021

Hi Marta,

What would be the result of this query:

a!queryEntity(
      entity: cons!P2P_POINTER_ENT_PRODUCT_ORDERED_DETAILS,
      query: a!query(
        logicalExpression: a!queryLogicalExpression(
          operator: "AND",
          filters: {
            a!queryFilter(
              field: "productOrdered.productOrderedId",
              operator: "=",
              value: ri!productOrderedId
            )
          }
        ),
        pagingInfo: a!pagingInfo(startIndex: 1, batchSize: - 1)
      )
    ).data

Terms & ConditionsAccessibility statement