Filter dataverse based on sharepoint

[bishnuprasad2]

I am facing a problem in filtering data verse table based on a choice column in share-point document library.

Problem-

when someone uploads project charter into document library then a flow is running in which users are providing approval or rejections.

This gallery is connected to a data verse table - project register and project charter in this gallery has to be connected with share point document library from there it should filter only those records which are newly uploaded i.e pending state.

can some one please help me.

[Devvj]

Hi @bishnuprasad2 
is there any relationship/key which links the the project charter record and the project register?
Or du you want to just show all project charters that are in a specific state/status?

[bishnuprasad2]

both the datasource have project id column

[bishnuprasad2]

@Devvj project charter status is a choice column in sharepoint document library and project register is a dataverse table.

I want to filter the dataverse table where project charter status is pending.

There is a common column in both the tables i.e project ID

[Devvj]

@bishnuprasad2 
Ok, so i guess you could try something like this
Filter(<Project register table>, project ID = <project charter library>.project ID && !IsBlank(LookUp(<project charter library>, status = "pending" &&  project ID = <project register table>.project ID)))

[bishnuprasad2]

@Devvjits not working. Its giving me error

[Devvj]

@bishnuprasad2 
Made a little adjustment to the last post, since it needs to check id in the lookup too.
But from the screenshot it looks like you try to update a textfield within the gallery and not the gallery itself?
The code above is intended for the "items" property in the main gallery, but maybe i misunderstand the intention 🙂

[bishnuprasad2]

@Devvj  I reapplied on items property of gallery but still not working.

[owork138]

To address your problem of filtering data in a Dataverse table based on a choice column in a SharePoint document library, you can follow these steps:

Connect SharePoint with Dataverse: Ensure that your SharePoint document library is properly connected to Microsoft Dataverse. You might already have this set up since you mentioned that a gallery is connected to the Dataverse table. This can be done using Power Automate to create a flow that triggers when a file is added to SharePoint, then checks the approval status and updates Dataverse accordingly.
Filtering Records Based on State: In your case, you want to filter out only those records that are in a 'pending' state, which means these are newly uploaded files yet to be approved or rejected.
Using Power Automate:
Create a Flow: This flow will trigger whenever a document is uploaded or modified in your SharePoint document Genesis library.
Get File Properties: Use the SharePoint action to get properties of the uploaded/modified file, particularly the choice column that indicates the approval status.
Condition: Add a condition to check if the choice column indicates 'Pending'.
Update/Create a Record in Dataverse: If the condition is true, use the appropriate action to either create a new record or update an existing record in the Dataverse table.
Filtering in the App: In your Power Apps app where the gallery is displaying items from the Dataverse table:
Use a filter function on the gallery’s Items property to display only those records where the status is 'Pending'. The formula would look something like:
plaintext
Copy code
Filter('Project Register', StatusColumn = "Pending")
Here, 'Project Register' is your Dataverse table, and StatusColumn is the column in Dataverse that stores the approval status.
Testing: Once set up, test the flow by uploading a document to your SharePoint library and see if it correctly updates the Dataverse table and thus filters the gallery in your app.
Troubleshoot: If you encounter any issues, verify the connections, check for any errors in the flow, and ensure that the SharePoint choice column and the Dataverse status column are correctly mapped.
By setting up such a flow and filtering mechanism, you should be able to achieve the functionality where only those project charters that are newly uploaded and in a pending state are displayed in your gallery. If you need further assistance with specific steps, Microsoft's documentation or forums can be a great resource, or consider reaching out to a professional with expertise in Microsoft Power Platform.