cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zstern21
Frequent Visitor

Loading Record to Form for Related Sharepoint Lists Using Lookup Column and Variable

I have a pretty complicated scenario.

 

I have a main Sharepoint List called AHI Projects. It is related to several other Sharepoint Lists (AHI HDA, AHI DEVIDA...). Those sublists are (AHI HDA, AHI DEVIDA...) have a Lookup column that relates them to AHI Projects. Initially, I created a ProjectID column in the AHI Projects Lists, and those sublists used that column as the Lookup. I eventually changed the Lookup in the sublists to Lookup the ID field in the AHI Projects List to see if that would help my issue, but it produced a new issue, which I will discuss.

 

I have a form where the user can create a new record in the AHI Projects table. This form (and the list) has Choice Fields (choices being Yes or No). So there is an HDA field, DEVIDA field etc. If the user selects Yes in the form for one of these fields, a new record is created in the sublist with that ProjectID from the AHI Projects list. This occurs on the OnSuccess within the form and has several steps. A Flow runs to automatically create the ProjectID field in the AHI Projects list (which is autoincremented) and then a series of IF statements with a Patch function move that ProjectID to the sublists for which the user indicated Yes on the form. This all works fine.

Screenshot 2022-03-17 115050.jpg

The user clicks Save Project to SubmitForm. The user can then click the Add Program Information button which takes them to a new screen (which I will show below). The Add Program button Sets a Variable, which should be the ID of the last submitted record and navigates to this new screen using the following code:

 

Set(varID,NewProjectForm.LastSubmit.ID);Navigate(ProgramScreen). 

 

The user is then sent to this Screen. The Buttons on the screen only if appear if the user selected that program on the previous page, using the following code on the Visible property. 

 

If(LookUp('AHI Projects', ID=varID).'Housing Developer Assistance'.Value="Yes",true,false)

Screenshot 2022-03-17 115709.jpg

 

 

 

 

This seems to work. The user then clicks on the button which navigates them to a new screen and form.

 

 

 

 

 

Screenshot 2022-03-17 120039.jpg

Here the Items property is set to: LookUp('AHI HDA', ProjectID.Id=varID) which should return the Last Submitted record from the New Projects screen.

 

Unfortunately, I get the Delegation issue because PowerApps doesn't delegate the Id part of the Lookup field, only Value.

 

 

 

Previously, I had set the variable to be the ProjectID of the AHI Projects list, instead of the ID. Then, I used the code LookUp('AHI HDA', ProjectID.Value=Text(varID)). However, whenever I navigated from the previous screen the Variable was either cleared or the Form on the Program screen wouldn't pull the record.

 

However, I have a Gallery where the user can view existing records and then navigate to that Program screen and see the chosen record.

Screenshot 2022-03-17 120913.jpgThe pencil icon had the following code (from when I was still using ProjectID as the Lookup: Select(Parent); Set(varID,ThisItem.ProjectID); EditForm(EditProjectForm); Navigate(ViewEditProjectScreen).

 

This worked fine even with the code: LookUp('AHI HDA', ProjectID.Value=Text(varID))

 

On that Program screen.

 

 

Any help would be much appreciated. I'm tearing my hair out over this. I was thinking that as a last resort I could have the user create the new record, then navigate to a screen that looks like the AHI - Edit Project Screen above but with only the LastSubmitted ID and then navigate to the Program Screen from there, but that's kind of clunky.

 

Thank you!

1 REPLY 1
zstern21
Frequent Visitor

I think the easiest resolution would be to keep using this code LookUp('AHI HDA', ProjectID.Value=Text(varID)) since it works on that Gallery screen, but I can't seem to maintain the value of the varID variable when navigating from the New Projects Screen

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,270)