Power Pages (portal) Issue with showing related table column in the list

[JacoMathew]

Hi All,

I am trying to create an app with Power pages. I have a set up an azure sql database with table Projects and Phases. I create forms that allow me to create new projects and new phases that are linked to a project. However, when I want to show the Project Name from the related table Name(Project) in the list of Phases, the whole column is blank. The project number which is the Primary column of the Project table shows up correctly but when i create a view and put the project name, it table goes blank. 

Any suggestion on how to tackle this is appreciated.

Thanks in advance

Jaco

[OliverRodrigues]

Hi, I think my first question here would be why did you create a separate Azure SQL for your solution? 

Power Pages is built on top of Dataverse, so that should be the main Database to use. You can use Virtual Tables pointing to your Azure SQL but that will probably have limited capabilities.

[GWham1]

Same question as the above, why are you using a separate azure SQL Database? Surely you're better to use Dataverse? 😕

[JacoMathew]

Hi,

The business is already using a SQL database as their database for other application. I would like to leverage the data that's already in it. The above example is a simplified version of what I intent to do. But the logic is same in the sense that I will be working with tables that are in SQL database and the tables will be connected using Foreign keys and Primary keys. 

[GWham1]

I'm still a little unclear if you are using fetch/liquid to display the result on the page, but if you're getting an ID and not the label - you could try one of the following.

  {% for e in result %}
    {{ e['alias.projectname'] }}          
    {{ e['alias.projectname'].id }}
    {{ e['alias.projectname'].value }}
    {{ e['alias.projectname'].label }}
    {{ e['alias.projectname'].text }}
    {{ e['alias.projectname'].name }}
  {% endfor %}

[OliverRodrigues]

@JacoMathew a few more things:

• I am assuming you are using Virtual Tables, am I right?
• is the data showing up correctly in the Model Driven App? 
• may I ask what was the rationale to chose Power Pages?
• is Dataverse or Power Platform being used for other areas of the solution?

[JacoMathew]

@OliverRodrigues  Thanks..

• I am only using the virtual table that gets created using table from external data. I am able to create the lookup column between the tables. I did this by deleting the fk column that comes with the table and then create a relationship with the main table. This is working correctly and I am able to read and write data from / to the database.
• The data shows up correctly in the lookup column that gets created and is visible when I view the table in the power apps. The issue I am facing is when in create a view in power apps portal, and when I add the name and few other details of the  lookup value from the related section, those columns are showing only blanks. 
• @GWham1I am using a list to show the view in the UI. I didn't make any change to the liquid code that got created in the page. 
• The pages are created to be used by specific departments to use the existing data in the Azure SQL database and to store additional data in it.  Since they already have the primary data, we want to create pages that would allow us to store information and link it to a record from the main table. There is also plans to open it up for external user to register and input data. 
• Currently, the database is used mostly with Canvas apps and Web apps (created not with Power pages).
• We are not storing any production data into Dataverse, only Azure SQL database. We have used dataverse to create table to that would be used in prototype apps. 

Thanks again for looking into this. 

[GWham1]

@JacoMathewAre you using any other features of the list e.g. filtering etc or simply displaying data? If its only displaying have you tried using FetchXML and rendering the data yourself from the virtual table? Does that display the information you require?

{% fetchxml query %}
  // put fetch here
{% endfetchxml %}

{% if query.results.entities.size > 0 %}
  {% for result in query .results.entities %}
    {{ result.fieldname }}
  {% endfor %} 
{% endif %}

[JacoMathew]

To be honest, I am new to Power pages dev. Hence, I am using the default option in the power pages to display the view as a list. I will be adding filters, from the 'Add filter' option of the list.  Displaying the data is currently the first priority. I am not familiar with writing fetchxml and liquid, but I hope to start learning them soon.