cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Relations not working

Hi All,
I can't figure this out. In the past I worked with Access and relations where handled by Access so it was easy with autonumber and record save. Like a Parent Autonumber in table 1 and a numeric field as relation on table two.
With PowerApps I have two SharePoint lists. List 1 contains an ID arranged by SharePoint. In this list I have for instance columns like Company Name, Address, ZIP, and place. List 2 contains a lookup to the ID of list 1 and columns like Contactperson, Telephonenumber and E-mail.
Now on Powerapps I have a form (form1) containing the columns of Sharepoint List 1 as fields like Company Name, Address, ZIP and Place. When I do a submitform I want to start a Newform (form2) on second screen to fill in de contact person, Telephonenumber and E-mail related to the information on form1. I want this contact to be connected to de company based on ID in the first SharePoint list and the lookup in de second SharePoint list. So the ID on list 1 is passed over to the lookup field in list 2. As I said, with Access this is easy. With Powerapps, no matter what I do it doesn't work. Lookup not working. Filter not working. Variable not working.
I don't know what I'm doing wrong. I see information on this in de forum but so far not the magic answer. I also see a lot of complex code but how can this be? Should I use a lookup, should I use a filter or should I use a variable and how? Pleas help with simple solution.
Thanks
Chris

4 REPLIES 4
eka24
Super User III
Super User III

Hi @Anonymous

From your question, you would be using Variable and LookUp. Details of what you have tried so far can help.

However assume on form1 linked to List1 on screen1, your form1 contains DataCardValue1 or  TextBox1 with the ID.

Do the following:
1. Create a variable OnVisible of the Screen1: Set(MystoredID,DataCardValue1.Text) This will store whatever ID is entered into DataCardValue1.Text

2. Then on form2, you can do a LookUp on List2 based on the MystoredID

    So Assume you have a DataCardValue2 on form2 which is on Screen2, you can put the following formula to pull     the Contactperson. In the Default of the DataCardValue2 put:

      LookUp(List2,ID=MystoredID,Contactperson)

In another DataCardValue3 Defaults for Telephonenumber:  LookUp(List2,ID=MystoredID,Telephonenumber)

 

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

 

Anonymous
Not applicable

Hi Enka24,

 

Thanks for the answer but this is not exactly what I want:

1. Create a variable OnVisible of the Screen1: Set(MystoredID,DataCardValue1.Text) This will store whatever ID is entered into DataCardValue1.Text

 

I don't want to fill in an ID. Like Access primary ID is automatically generated. All ID's generated by the SharePoint ID is automatically generated. I want to use this ID to create relations.

 

At this moment I solved this by using my own autogenerated ID field besides the ID field standard form SharePoint. Not the perfect way but it works by using:

 

If(frmXYZ.Mode = FormMode.New, Last(Dataconnection).Column + 1)

 

But now I'm landed in the next problem where I think, how hard can this be. Simple storing of (all) updated records in a gallery to SharePoint list. A lot of Patch, and other complex commands that might work but not working. Simple question is, how to store updated records in a gallery. Pffffff.

 

In the past I change a record in an Access form and the update was automatically arranged. How to do this in PowerApps without using complex things. For instance, I used First command now and new records are created. Why, this was Defaults command?

 

I understand, it's also my lack of knowledge but it is frustrating to see that a lot of answers for simple question creating an error mark in my PowerApps development. Again, how hard can this be 🙂

Chris

Drrickryp
Super User II
Super User II

Hi @Anonymous 

I was in a similar place as you 2 years ago.  I was moving my Access web app to PowerApps so I went through similar issues.  I wrote a series that may help you get some perspective and the third part talks about setting up your tables.  https://powerusers.microsoft.com/t5/News-Announcements/Database-Design-Fundamentals-and-PowerApps-An-Overview/ba-p/184485 .  BTW, another issue you will run into is delegation in SharePoint lists.  I suggest First(Sort(List,ID,Descending)).ID+1 instead of Last() or Max() as these functions aren't delegatable and will cause problems if your list grows beyond the 2000 item limit. 

Anonymous
Not applicable

Hi Drrickryp,

 

Thanks for the suggestion to use First and not last.

 

Chris

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 (1,957)