Hi, I really hope that somebody may be able to help me with something that is driving me mad!
I am very new to powerapps and I'm trying to build an app that shows a list of project information taken from Dynamics 365. The first part has been easy enough and I have a gallery that links to an entity in Dynamics and shows information from that enity filtered on project number and date information.
The problem comes from needing to also show information of the project team who is linked with that project. In Dynamics, the projects table is linked to a resource allocations entity which them links to the Contacts enitity. If the projectid matches that of the id in resource allocations, the resource allocation record has a contactid that then links to the contacts entity.
I basically want to show infomation where:
Projects/projectid is equal to resource allocations/projectid and resource allocations/role is equal to a particular role id and resource allocations/contactid is equal to Contacts/contactid. Then show Contacts/fullname - this should be the record of the person who is a particular role (such as site manager) on a linked project.
Apologies if this doesn't make sense, but I just wondered if anyone knew of the correct syntax I should be using when tryint to write the formula as everything I have tried so far doesn't work. I have also wondered about using collections but I'm also drawing a blank on how to get the correct related columns in the correct place.
Any help would be greatly appreciated!
Hi @TracyG ,
Could you please share more details about your Filter formula?
Do you want to display your Project Info in your Gallery based on a specific contact? or do you want to display a specific contact within your Projects Gallery?
If you want to display a specific contact within your Projects Gallery, I think the LookUp function could achieve your needs.
I assume that the projectid (display name may be 'Project') in resource allocations Entity is a LookUp column to reference records from Projects Entity, and the contactid (display name may be "Contact") in resource allocations Entity is a LookUp column to reference records from Contact Entity.
Please consider add a Label in your Projects Galery, set the Text property to following:
Contact = LookUp('Resource Allocations Entity', Project.Project = ThisItem.Project, Contact.Contact),
Contact = LookUp('Resource Allocations Entity', projectid.projectid = ThisItem.projectid, contactid.contactid),
@v-xida-msft , Thank you so much for your reply - I am try to show both project and contact information in a gallery. The gallery is linked to the Project entity in Dynamics and basically shows a list of current live projects. From the projects entity it gets the job number, name, address, value and dates but I also want to show who is on the project team.
So, to get the information of who is the site manager for a project, the projects entity looks up the projectid in the resource allocations entity which then looks up the contactid to get the Contact name. I also use the role id from resource allocations to determine that the contact is the site manager for that record (which i didn't mention in my previous post)
I have given your formula a try but get errors, however I have got a lot closer with the following formula:
LookUp(Contacts,contactid=LookUp('Resource Allocations',_kms_projectid_value = ThisItem.Project && kms_role = Value(172100000),_kms_contactid_value),fullname)
This appears to work but it mostly returns values to the lable of 'false' apart from three records where it does actually find and display the name of the associated site manager! I have no idea as to why this is, other than that it could be the size of the entity that is causing problems - all three are pretty big and Contacts is massive - however results from Projects and Resource Allocations are returned instantly.
I was thinking of maybe somehow applying a filter first to narrow down the contact results to only those that are marked as internal but have no idea of how to write this so any further ideas would be greatly appreciated!!
Check out new user group experience and if you are a leader please create your group
Did you know that you can visit the Power Query Forum in Power BI and now Power Apps
Participate in the Power Virtual Agents Community Challenge
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.