Power Apps working with SharePoint List Relationships (https://www.youtube.com/watch?v=SCYY9ZL48Mw)

stevib1975 · ‎11-11-2023

Hello all,

first thank you for this great forum. I learned a lof in this forum. But need an advice as i maybe missed a feature or possiblity.

The goal would be to submit travel costs as powerapps app. The issue is see/i have is that normally a travel claim contains different positions and type of bills. I have issues to understand how it should work. I tried it with gallery, but this means that each position of the claim is one a gallery entry. But each claim should be have an unique sharepoint claim id. This unique sharepoint id item contains more items and positions and that´s my problem. I would need a kind of gallery in gallery with the goal that the user see in the apps his personal claim id and if he open/edit it or added a new more positionen / rows are containing one claim. Further not each claim has the same amount of items. So maybe would be great to have an + symbol to add a row. Or recycle bin to delete one row... one example row how it should be...Each claim has an unique ID, but the cells beside the Claim ID should be changeable. The challenge is the design of the sharepoint list and the frontend. Further technically in gallery also the user can change anything after added it to the sharepoint list. So maybe after the manager has approved as first step the claim, user could change it and change the values as the data is not write-protected. Maybe anybody knows also here anything about the technical solutions.

Claim ID	Type of Cost Dropdown	Value - Manally added	Travel Goal - Manually added
00001	Hotel	100	Munich
00001	Gas/Fuel	60	Munich

My expextations is not get the complete solution, That´s maybe to diffcult to explain here, But maybe somebody can advice or has an link for technical solution/youtube video how it could work...

Thanks a lot!
Stephan

Ninja_365 · ‎11-11-2023

Hi @stevib1975 ,

For your travel cost submission app in Power Apps, you're on the right track with using a gallery, but you'll need a bit more to handle the complexity of multiple items per claim. Here's a simplified approach:

SharePoint List Structure:

Have one list for Claims (each claim has a unique ID).
Create another list for Claim Details (where each entry links to a claim in the Claims list and contains information like Type of Cost, Value, Travel Goal).
Power Apps Design:

Use a gallery to display Claims. When a user selects a claim, show another gallery within the first one or on another screen to display the related Claim Details.
To add new positions to a claim, include an “Add” button in the Claim Details gallery that lets users input new items.
Managing Data:

For dynamic rows, use the 'Add' button to collect new entries and 'Delete' icon in each row for removal.
Implement logic to update SharePoint lists only when the user submits or finalizes the claim.
Security and Workflow:

To prevent changes after manager approval, include a status field in your Claims list. Once a claim is approved, lock the editing feature in the app based on this status.
Consider using Power Automate to manage the workflow, like sending notifications for approval and locking the claim for editing post-approval.
Hope this gives you a direction to start with! If you find this solution helpful, please feel free to accept it. 😊

View solution in original post

Ninja_365 · ‎11-11-2023

Hi @stevib1975 ,

For your travel cost submission app in Power Apps, you're on the right track with using a gallery, but you'll need a bit more to handle the complexity of multiple items per claim. Here's a simplified approach:

SharePoint List Structure:

Have one list for Claims (each claim has a unique ID).
Create another list for Claim Details (where each entry links to a claim in the Claims list and contains information like Type of Cost, Value, Travel Goal).
Power Apps Design:

Use a gallery to display Claims. When a user selects a claim, show another gallery within the first one or on another screen to display the related Claim Details.
To add new positions to a claim, include an “Add” button in the Claim Details gallery that lets users input new items.
Managing Data:

For dynamic rows, use the 'Add' button to collect new entries and 'Delete' icon in each row for removal.
Implement logic to update SharePoint lists only when the user submits or finalizes the claim.
Security and Workflow:

To prevent changes after manager approval, include a status field in your Claims list. Once a claim is approved, lock the editing feature in the app based on this status.
Consider using Power Automate to manage the workflow, like sending notifications for approval and locking the claim for editing post-approval.
Hope this gives you a direction to start with! If you find this solution helpful, please feel free to accept it. 😊

stevib1975 · ‎11-11-2023

Ninja_365 thanks for your really fast and great input. I think can even accept it as solution, but i want to let the forum open for a few days that other members have a change to give an input. But i got now exactly what you described. Really appreciate!

Ninja_365 · ‎11-11-2023

@stevib1975 I'm really glad to hear that you found my input helpful! Absolutely, it makes sense to keep the forum open for a bit longer to see if others have additional insights or suggestions to offer. It's always great to have multiple perspectives on a solution. Feel free to reach out if you have any more questions or need further clarification as you work on your project.

And hey, thanks for considering my solution! If you eventually find it the most helpful, I'd be delighted if you accept it. 😊

Take care and best of luck with your app!

stevib1975 · ‎11-11-2023

@Ninja_365 sorry to come back with the topic again. Maybe can you make a short notice on which way i can combine two lists and two galleries together. How can i filter based on the first gallery. How would name the tables in the sharepoint list. Where is the dependency to define? I tried it out, but failed with my first try and idea. My brain is still thinking and thinking, but can´t see the "trick". Sorry and Thanks! Futher, if possible you can loose some words about the locking topic (Technical....power flow or with the patch command)...But till i am so far i need a few days, i think. LG Stephan

stevib1975 · ‎11-12-2023

I was looking for the puzzle part and i think this video is the goal for my solution.With this it is possible to assign a list of claims to the indiviual related. I have not known that with lookup it is possible to combine two lists...