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 II
Super User II

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 II
Super User II

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.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

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.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (2,044)