Filter dataverse based on sharepoint

(1) Share

Report

Posted on by 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.

Categories:

Microsoft Dataverse with Power Apps

Screenshot 2024-0...

I have the same question (0)

All responses (10)

Answers (0)

Devvj 1,132 Super User 2024 Season 1 on at

Like (1)

Report

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?

Was this reply helpful? Yes No
bishnuprasad2 10 on at

Like (1)

Report

both the datasource have project id column

Was this reply helpful? Yes No
bishnuprasad2 10 on at

Like (1)

Report

@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

Was this reply helpful? Yes No
Devvj 1,132 Super User 2024 Season 1 on at

Like (1)

Report

@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)))

Was this reply helpful? Yes No
bishnuprasad2 10 on at

Like (1)

Report

@Devvjits not working. Its giving me error

Was this reply helpful? Yes No
Devvj 1,132 Super User 2024 Season 1 on at

Like (1)

Report

@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 🙂

Was this reply helpful? Yes No
bishnuprasad2 10 on at

Like (1)

Report

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

Was this reply helpful? Yes No
owork138 20 on at

Like (1)

Report

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.

Was this reply helpful? Yes No
owork138 20 on at

Like (1)

Report

To filter records in a Dataverse table ("Project Register") based on the status of documents uploaded to a SharePoint library, ensure your flow updates the table with a "pending" status for new uploads. Verify that the Dataverse table has a "Status" column and that the flow is correctly configured. In your gallery, use the formula Filter('Project Register', Status = "Pending") to display only those records. Confirm proper connections between SharePoint and Dataverse, test with a new document upload, and troubleshoot any issues with the flow or data if records do not appear as expected. Visit ups employee login now

Was this reply helpful? Yes No
owork138 20 on at

Like (1)

Report
To filter a Dataverse table based on a choice column in a SharePoint document library and display only newly uploaded records with a "pending" state, follow these steps:

Steps to Resolve:

Ensure Flow Updates Dataverse Table:

Check Flow Configuration: Confirm that your flow updates the Dataverse table with the status of "pending" when a project charter is uploaded to SharePoint. The flow should also link the document to the corresponding record in the Dataverse table.

Verify Dataverse Table Columns:

Status Column: Ensure that your Dataverse table ("Project Register") has a column for status (e.g., "Status") that reflects the approval or rejection state.

Set Up Filtering in the Gallery:

In Power Apps (or your app): Set the Items property of your gallery to filter records based on the "pending" status. Use the following formula:

Filter('Project Register', Status = "Pending")

Ensure Proper Linking: Confirm that the records in the Dataverse table are correctly linked to the corresponding documents in the SharePoint library.

Check SharePoint and Dataverse Integration:

Verify Connections: Make sure that the connection between SharePoint and Dataverse is set up correctly so that new uploads in SharePoint are reflected accurately in the Dataverse table.

Test the Setup:

Upload Test Document: Upload a test project charter to the SharePoint document library and check if it appears with the "pending" status in your gallery.

Troubleshoot If Necessary:

Check Data Flow: If records aren’t appearing as expected, review the flow and verify that it correctly updates the Dataverse table.

Inspect Filtering: Ensure the filter formula in your gallery is correct and that the data is being updated in real-time.

By following these steps, you should be able to filter the gallery to display only the records with a "pending" status, ensuring that newly uploaded documents are correctly reflected.
https://www.streameastguide.com

Was this reply helpful? Yes No