cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gmanuki
Frequent Visitor

Web API or fetchXML to get count of records

Hi I want to get a count of contact records displayed on screen as information and was wondering should i use fetchXML or the WEB API to achieve this. Any examples would be great 🙂

 

Thanks Gary

1 ACCEPTED SOLUTION

Accepted Solutions
dgoode
MPP
MPP

Hi Gary,

 

 You could leverage the Web API or use a regular liquid Fetch XML statement. If you need perform create, update or delete actions then leverage the web API. If you are only doing read, it may be easier to just create a web template that uses some liquid fetch XML. Below is the documentation of the web api and an example tutroial from docs.

 

https://docs.microsoft.com/en-us/power-apps/maker/portals/read-operations

https://docs.microsoft.com/en-us/power-apps/maker/portals/webapi-tutorial

 

 

Below is an example I put together just using liquid fetch xml. I think this may suffice for your needs.

Web Template:

 

{% if user %}
  Hello, {{ user.fullname }}!
{% endif %}
<p>This is an example of using fetchXml and Liquid to query data in CRM from the Portal:</p>
{% fetchxml my_query %}
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="contact">
    <attribute name="fullname" />
    <attribute name="telephone1" />
    <attribute name="contactid" />
    <order attribute="fullname" descending="false" />
    <filter type="or">
      <condition attribute="fullname" operator="like" value="%Tom%" />
      <condition attribute="fullname" operator="like" value="%Kevin%" />
      <condition attribute="fullname" operator="like" value="%Meg%" />
    </filter>
  </entity>
</fetch>
{% endfetchxml %}
 
<p><b>The FetchXml used in the query is:</b> {{ my_query.xml | escape }}</p><br/>
<p><b>The total number of records returned is:</b>{{ my_query.results.record_count }}</p><br/>
<p><b>The property that indicates if there are more records than the maximum (50) returned is:</b> {{ my_query.results.more_records }}</p><br/>
<p><b>The paging cookie that you use to page through a list of records is:</b> {{ my_query.results.paging_cookie | escape }}</p>
<p><b>This is where you loop through the records returned and build your table or list or anything you would like to do with the collection of records returned</b></p>
<table>
  <tr>
     <th>Contact Id</th>
     <th></th>
     <th>Contact Name</th>
  </tr>
{% for result in my_query.results.entities %}
  <tr>
    <td>{{ result.id | escape }}</td>
    <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td>
    <td>{{ result['fullname'] | escape }}</td>
    <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td>
    <td>{{ result['telephone1'] | escape }}</td>
  </tr>
{% endfor %}
<table>

 

 

Then just link it to a Page Template, then to a web page and you’re done.

 

Here’s how it will look:

 

dgoode_1-1654712787388.png

 

The total number of records returned is: The property that indicates if there are more records than the maximum (50) returned is: false The paging cookie that you use to page through a list of records is:

View solution in original post

9 REPLIES 9
dgoode
MPP
MPP

Hi Gary,

 

 You could leverage the Web API or use a regular liquid Fetch XML statement. If you need perform create, update or delete actions then leverage the web API. If you are only doing read, it may be easier to just create a web template that uses some liquid fetch XML. Below is the documentation of the web api and an example tutroial from docs.

 

https://docs.microsoft.com/en-us/power-apps/maker/portals/read-operations

https://docs.microsoft.com/en-us/power-apps/maker/portals/webapi-tutorial

 

 

Below is an example I put together just using liquid fetch xml. I think this may suffice for your needs.

Web Template:

 

{% if user %}
  Hello, {{ user.fullname }}!
{% endif %}
<p>This is an example of using fetchXml and Liquid to query data in CRM from the Portal:</p>
{% fetchxml my_query %}
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="contact">
    <attribute name="fullname" />
    <attribute name="telephone1" />
    <attribute name="contactid" />
    <order attribute="fullname" descending="false" />
    <filter type="or">
      <condition attribute="fullname" operator="like" value="%Tom%" />
      <condition attribute="fullname" operator="like" value="%Kevin%" />
      <condition attribute="fullname" operator="like" value="%Meg%" />
    </filter>
  </entity>
</fetch>
{% endfetchxml %}
 
<p><b>The FetchXml used in the query is:</b> {{ my_query.xml | escape }}</p><br/>
<p><b>The total number of records returned is:</b>{{ my_query.results.record_count }}</p><br/>
<p><b>The property that indicates if there are more records than the maximum (50) returned is:</b> {{ my_query.results.more_records }}</p><br/>
<p><b>The paging cookie that you use to page through a list of records is:</b> {{ my_query.results.paging_cookie | escape }}</p>
<p><b>This is where you loop through the records returned and build your table or list or anything you would like to do with the collection of records returned</b></p>
<table>
  <tr>
     <th>Contact Id</th>
     <th></th>
     <th>Contact Name</th>
  </tr>
{% for result in my_query.results.entities %}
  <tr>
    <td>{{ result.id | escape }}</td>
    <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td>
    <td>{{ result['fullname'] | escape }}</td>
    <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td>
    <td>{{ result['telephone1'] | escape }}</td>
  </tr>
{% endfor %}
<table>

 

 

Then just link it to a Page Template, then to a web page and you’re done.

 

Here’s how it will look:

 

dgoode_1-1654712787388.png

 

The total number of records returned is: The property that indicates if there are more records than the maximum (50) returned is: false The paging cookie that you use to page through a list of records is:

gmanuki
Frequent Visitor

Thanks for your response i was thinking this would be the best for simple counts

 

Thanks Garry

gmanuki
Frequent Visitor

@dgoode 

So what happens if i have more than fifty records. How do i return the exact count ?

Fubar
Solution Sage
Solution Sage

If all you are after is a count of records then you need to adjust your FetchXML to be an aggregate and return 1 row (rather than try to return all results and count the returned results). see: https://docs.microsoft.com/en-us/power-apps/developer/data-platform/use-fetchxml-aggregation

 

Edit: also, the decision to use Liquid or Web API should be driven by how your Web Page needs the data.  Liquid is server-side code and will run as part of the process runs before sending the Web Page to your Browser, Web API is client-side and pulls the data after the Web Page has loaded.   There are scenarios where either is valid, but often Liquid is the best choice.

adedapo
Helper I
Helper I

@Fubar Please can you elaborate on how to get the result an maybe an example of the fetchxml query. I've been trying to figure is out for some time now.

 

Thanks

adedapo
Helper I
Helper I

@Fubar yeah I was able to eventually solve it after much trial and errors 😅

Hi i am using the fetch query it does not returns any records, I tried adding those extra configuration to fetch page metadata you mentioned above for some reason i noticed the query it returns is partial and not full 

 

Fetch query 

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="contact">
    <attribute name="fullname" />
    <attribute name="emailaddress1" />
    <attribute name="parentcustomerid" />
    <attribute name="telephone1" />
    <attribute name="statecode" />
    <attribute name="contactid" />
    <attribute name="firstname" />
  </entity>
</fetch>

 

Result on page. Not sure why the query is coming partially only and no results on page. 

 

image.png

 

 

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Top Solution Authors
Users online (1,575)