cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Yarko
New Member

Portals - Join Custom table with Notes record as Attachments

Hi There,

I'm trying to create a simple form using Portals with attachments. 

What I was able to accomplish:

1. Created custom table (with customer request name and email)

2. Provision Web Page

3. Created a simple Form to  expose my custom table columns

4. Enabled Attachments via Notes

As result : Attachments are saved to Notes new record created in Custom table when a user submits from.

 

What I have a problem with: 

I see all files saved to the Notes table but how I supposed to join this information back to my custom table so I can run report 

and see information about who submitted what files?

I checked all notes columns and can't find anything pointing back to my custom table 

 

Please help

 

 

 

 

 

 

 

2 REPLIES 2
chleverenz
Helper V
Helper V

Hi @Yarko ,

not sure, whether its helpful, but the attachment and annotation entitiestables contain an objectid and an objecttypecode. The joining takes place between your custom entityid and the objectid in the annotation. As guids should be unique, you only have to do this joining.

If you are a kind of paranoid as me, you should also check, whether the objecttypecode matches your custom table which can be daunting, as the typecode may change between systems. I usually omit that check.

Additionally, annotations generated from the web start with "*web*" in the standard (is a setting) when generated from portals. So you can see them with this selection:

<fetch no-lock="true" count="3" >
<entity name="annotation" >
<link-entity name="incident" to="objectid" from="incidentid" />
<filter>
<condition attribute="isdocument" operator="eq" value="1" />
<condition attribute="subject" operator="like" value="*web*%" />
</filter>
</entity>
</fetch>

This is an example for incident, but when you exchange that with your table, it should work, too.

The content of the attachment itself is stored in the documentbody as base64 encoded stuff. The mimetype can be obtained from the mimetype attributecolumn 🙂 .

 

For me its a little bit unclear how you would like to see a kind of report over that.

 

Hope this helps,

  Christian

 

PS there are annotations and attachments in the system. I am not sure, whther the annotations also show the attachments or whether you have to look for the attachments also.

At least in one of my systems i found annotations with the *WEB* starting name. So these should be the right ones.

PPS of course you can omit the linked entity stuff and directly make a query for the annotations which have as an objectid that id, which you care for.

chleverenz
Helper V
Helper V

Hi @Yarko ,

may be reporting can be something like:

<fetch no-lock="true" aggregate="true" >
<entity name="annotation" >
<attribute name="mimetype" groupby="true" alias="mimetype" />
<attribute name="objectid" groupby="true" alias="objectid" />
<attribute name="annotationid" aggregate="count" alias="cnt" />
<filter>
<condition attribute="isdocument" operator="eq" value="1" />
<condition attribute="subject" operator="like" value="*web*%" />
</filter>
<order alias="mimetype" descending="true" />
</entity>
</fetch>

which yields the count of annotation per incident (in my case) grouped by mimetype and indicentid.

Output  could be something like:

chleverenz_0-1637855102325.png

So, may be making use of aggregation in fetchxml could also bring light in your reporting 🙂

Happy portalling,

  Christian

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (2,742)