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

Lookup Column/SP List & Data Entry

Hi all,


I am in the process of building a visitor app.


I have two SharePoint lists;


Visitors
Visits

I have added a lookup column to the UserID that is populated for the Visitor when there record is created (standard text column, not calculated), I have manually inserted some data in both lists and can see them linked successfully, however the problems start when I go to add new data via powerapps, both data sources are added to the app and I can see the lookup working as should when I query it via a Gallery control.


I have a gallery that lists all visitors and a button to log a new visit, this takes you to a form that is linked to "Visits", however I cannot get the lookup column to auto populate with the ID selected from the gallery, so when I submit new visits they're not actually linked, is there a way to automate this so users don't have to select manually from the drop down on the lookup?


Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@StevenP 

So if you have a UID004 in your Visitors list and it is the column linked to the Lookup column in your list, then in the Update property of the datacard for the Lookup column you need the following:

With({_visitor: LookUp(Visitors, linkedColumnName = "UID004")},  //substitute with passed value
   {Id: _visitor.ID,
    Value: _visitor.linkedColumnName 
   }
)
_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

6 REPLIES 6
vribeiro
Frequent Visitor

Hi Steven,

 

Try to use Patch on the Form OnSuccess event, it should do the work.

 

I'm not sure if i get your relation between the 2 tables but take just as an example.. :

 

Patch(
Visitors,
Form.LastSubmit.ID,
{VisitId: Gallery.Selected.ID}
);

 

 

RandyHayes
Super User
Super User

@StevenP 

Firstly, you might be better off to not use a Lookup column in this scenario.  It is tremendously easier to simply have a numeric column in your Visits list that would simply have the ID of the visitor.

 

Be that as it may, if you stick with the Lookup column, then you need to supply a record to the Lookup column that contains an Id and a Value.  The Id will be the ID of the record you are referencing and the Value will be the value in the column that you are connecting to (as defined in the Lookup column definition in SharePoint).

 

I am not clear from your description if you are trying to do this manually or through a Form.  If it is part of a Form, then you need to ensure that your Update property for the Lookup column contains a result that produces a record as mentioned above.

 

I hope this is helpful for you.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

I did try this but it tells me Patch has invalid arguments;

 

Patch(
'DA Visits',
Form8_1.LastSubmit.ID,
{UserID: Gallery5.Selected.UserID};)

 

@RandyHayes - if I was to go that route then I am guessing on the data input form, I could add a view only input that = the gallery.selected.userid and then that would keep both in Sync? Then on submit it would append the same ID to that column and down the line if I ever need to report on it then it's just a case of simply filtering by said ID?

RandyHayes
Super User
Super User

@StevenP 

To clarify - you are using an EditForm??

 

If so, then your Lookup column should be in the form.  It does not need to be visible, but it needs to be in the form.  There is no need to do a Patch after you submit your form.  This can all be done in the form submit.

The key is to then supply the proper record information to the Update Property of that lookup datacard.

 

Can you expand more on what you mean by "append the same ID"?

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Hi,

 

I am using new form mode, for a fresh visit entry. I have attached a screenshot of the form

 

I have the UID in a text label (checking if it was passing through) and UserIDLookup is the lookup column, the end user will get to the new data entry form for visits by selecting the visitor and then selecting "Create new visit" so I need somehow to get the lookup column to realize it needs to be pointing at the selected UserID.

RandyHayes
Super User
Super User

@StevenP 

So if you have a UID004 in your Visitors list and it is the column linked to the Lookup column in your list, then in the Update property of the datacard for the Lookup column you need the following:

With({_visitor: LookUp(Visitors, linkedColumnName = "UID004")},  //substitute with passed value
   {Id: _visitor.ID,
    Value: _visitor.linkedColumnName 
   }
)
_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,900)