cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Preachman85
Helper I
Helper I

Defeated by SharePoint lists

Hi folks,

 

I'm quite new to the world of PowerApps but making some progress and would appreciate a pointer or two! I have been defeated for 2 days on the following scenario;

 

I have 2 SharePoint lists; List1 & List2

List1 contains a look up column of List2 

List1 is for users to book a seat in an office, List2 contains a list of offices + max capacity number

 

Within my app I have an edit form(form 1), and a view form (form 2). 

 

When a drop down on form 1 is selected, I would like form 2 to show me the remaining seats available. I would require form 2 to either perform or trigger a calculation, something like "count all rows in DB where date = today; compare answer to max capacity number and display the result". Result could be displayed on a label or within the view form2 field, however I intended to make it invisible and use a label.

 

I feel this should be straightforward however after spending hours on google and youtube, I just can't seem to crack it!

 

thanks in advance

1 ACCEPTED SOLUTION

Accepted Solutions
Drrickryp
Super User
Super User

Hi @Preachman85 

Get rid of the Lookup field in SharePoint and use a Number type instead, call it list2id.  Do the lookup in PowerApps. See my post https://powerusers.microsoft.com/t5/News-Announcements/Relational-Database-Principles-and-PowerApps-...  for why not to use complex data types in SharePoint.  Then see https://powerusers.microsoft.com/t5/News-Announcements/Relational-Database-Design-fundamentals-Imple... for how to implement it in PowerApps.

If you are a visual learner, Shane Young @Shanescows  has a video that shows you the same process. https://www.youtube.com/watch?v=43ekj5MlNJU 

View solution in original post

5 REPLIES 5
Drrickryp
Super User
Super User

Hi @Preachman85 

Get rid of the Lookup field in SharePoint and use a Number type instead, call it list2id.  Do the lookup in PowerApps. See my post https://powerusers.microsoft.com/t5/News-Announcements/Relational-Database-Principles-and-PowerApps-...  for why not to use complex data types in SharePoint.  Then see https://powerusers.microsoft.com/t5/News-Announcements/Relational-Database-Design-fundamentals-Imple... for how to implement it in PowerApps.

If you are a visual learner, Shane Young @Shanescows  has a video that shows you the same process. https://www.youtube.com/watch?v=43ekj5MlNJU 

View solution in original post

v-yutliu-msft
Community Support
Community Support

Hi @Preachman85 ,

Actually, you not only need to let us know the feather that you want, but also need to show us the details of your data source and app. Or else, I could not provide you detailed solution.

Could you tell me:

1)the structure of the two lists (fieldname, data type), the fields' usage?

2)form1's data source? form2's data source?

3)the calculation rule?

 

Based on your description, I could only suggest you know more about filter and lookup function:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks 

 

@v-yutliu-msft Thank you for responding, I have an issue which is with the support team at the moment so unable to provide screenshots within PowerApps however please see below.

 

Disclaimer: I haven't written actual script below, please consider is pseudo script to demonstrate the logic only

 

List1LookupLookup-List1 
PrinciplePersonNAmeBookingBuildingNameBookingBuildingName:BuildingNameDateBooked
DaveBuilding 1Building 101/06/2020
SamBuilding 2Building 203/06/2020
SarahBuilding 3Building 303/06/2020
    
    
List2   
BuildingIDBuildingNameBuildingReducedCapacityDateBuildingModified
1Building 15006/04/2020
2Building 23006/04/2020
3Building 310006/04/2020
   06/04/2020
    
    
    
Form1 source = List 1   
    
Form2 source = Form1.Selected 
 Form 2 Text1Form1.Dropdown.Selected.BuildingName 
 Form 2 Text2Building ReducedCapacity 
    
    

Calculation = (Count.Form1.Items where date = today()) store as var_NumberUsersbooked;

 

Get Form2.BuildingReducedCapacity where Form2.Text1.Selected store as var_MaxAllowed;

 

Calculate (var_MaxAllowed - var_NumberUsersbooked) = var_AvailableSpaces

 

Label1 = var_AvailableSpaces  

Hi @Preachman85 

Did you know that PowerApps has a template called Book a Room?  It may be helpful in designing your app. I haven't tried it yet but it looks like what you want. ...FYI  I just looked at it but since I don't use Exchange, I couldn't figure it out.  Apparently, it uses a room mailbox available in Exchange.  Perhaps someone else here has experience with it.

Annotation 2020-06-04 082049.jpg

@DrrickrypMany thanks for the tips, pointers and information.

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (1,089)