Power Platform Community Forum Thread Details

How to Retrieve a Row from a Virtual Table by External Primary Key

Hi All,

I'm building a custom component that uses a few virtual tables built from SharePoint lists. Due to the nature of the data in the tables, I can't use linked entities. For each row in table A, I would process a string from a specific column to retrieve an external primary key (aka ID in SharePoint lists). Then, I want to retrieve the row in table B that has this external primary key and get data from the other columns of that row and display it. However, I'm having some trouble getting the other columns as they show up as blank. This is the code that I have so far:

{% fetchxml queryA%}
<fetch mapping='logical' distinct='true'>
  <entity name='msdyn_tablea'>
    <attribute name='msdyn_stringcolumnwithid' />
  </entity>
</fetch>
{% endfetchxml %}

{% fetchxml queryB %}
<fetch mapping="logical">
  <entity name="msdyn_tableb">
    <attribute name="msdyn_externalprimarykey" />
    <attribute name="msdyn_column1" />
    <attribute name="msdyn_column2" />
  </entity>
</fetch>
{% endfetchxml %}

{% for row in queryA.results.entities %}
    {% assign updatedId = row.msdyn_stringcolumnwithid| split: ": " | last | replace: ";", "" | strip %}
    {% assign formattedId = updatedId | decimal %}
    {% assign filterResults = queryB.results.entities | where: "msdyn_externalprimarykey", formattedId %}
    {% assign matchedRow = filterResults [0] %}

    <p>Number of entries found: {{ filterResults.size }}</p>
    <p>Column 1: {{ matchedRow.msdyn_column1 }}</p>
    <p>Column 2: {{ matchedRow.msdyn_column2 }}</p>
{% endfor %}

The filterResults.size will return 1. I have to format updatedId to a decimal as the external primary key is set to decimal by default and I could not change that upon the virtual table creation. However, when I test with a number directly, it is able to filter and return the data from the other 2 columns. I have searched through many articles and solutions within this forum itself and could not find a good solution to my problem so any help would be highly appreciated. Thanks in advance.

Categories:

Design & build

Quick Links

Subscribe to this forum!