How do I start SharePoint List without using Lookup Columns for Power Apps

[Bunly11]

Hi all -

Need help on how to setup a new SharePoint list to use with Power Apps for the user to view.

I was able to build an app using my Access Database connected to SharePoint but there were a lot of work arounds and issues with using lookup columns.

I want to try to start from scratch but am having a hard time figuring out how to set up the lists and not use a lookup column.

I have read many blogs and watched videos (Thanks @WarrenBelz for his blog).

If I have three list

DrugTBL: main list of drugs

SuppliesTBL: supplies needed for each drug

Supplies: list of supplies used

For each drug in my main list would have several supplies.

How do I connect my drug to the corresponding supplies?

Would any one have an example I could follow or demonstrate how to accomplish this?

Thank you!

[StalinPonnusamy]

HI @Bunly11 

You need to have another list that has Drugs with all supplies (1:N). Again we have to use Lookup to relate those tables.

You mentioned, "not use a lookup column". Any reason why you are not interested to use lookup?

[WarrenBelz]

@Bunly11 ,

You simply do the Lookups in Power Apps and write back to Text fields. Also you should if possible not design three-tier (or "look through" structures). You can connect all lists to Power Apps. It is a bit hard to answer in more detail without the code you have attempted.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[Bunly11]

Hi @WarrenBelz 

So I have been trying to work on this and am stuck. I added DrugID to the Supplies table but do not know how to connect the two tables on one form on Sharepoint integration form.

I created a new table for my main data entry

I have a second table called MF Supplies where supplies is a choice field

I tried to create a Share Point Integration form and put two forms the MFRecord and the MFSupplies.

Supplies is a Choice column: 

Choices([@MFSupplies].Supplies)

I tried to create a repeating table like in this video PowerApps Repeating Tables like InfoPath Part 1 - Enter the data - YouTube but I cant even get the gallery to show. 

Would you have any suggestions on how I connect and add data to both lists on one form?

Is it possible to have two forms from two different list on SharePoint Integration form?

Thanks

[WarrenBelz]

Hi @Bunly11 ,

A number of questions there.

Firstly you cannot have two lists on one form but certainly have multiple forms on a SharePoint Integrated app, however you will run into issues with the integrated Edit/Save/View controls as you can only "point" them at one. 

You can solve this by adding buttons as you would on a canvas app, but users have to be aware of this.

To "connect" the other table, simply add it as a data source. You have to add the gallery and probably need a new screen.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[Bunly11]

@WarrenBelz thanks - let me work on this!

[Drrickryp]

@Bunly11 

There may be some help for you in my blog posts here https://powerusers.microsoft.com/t5/Power-Apps-Community-Blog/Relational-Database-Principles-and-Pow...  and the one following it here showing how to implement the one to many relationships in PowerApps.   https://powerusers.microsoft.com/t5/Power-Apps-Community-Blog/Relational-Database-Design-fundamental... .  This can be done using the same principles as in MS Access applied to SharePoint lists. 

[CMOLINAV]

Hello @WarrenBelz 

"Also you should if possible not design three-tier (or "look through" structures)" 

Could you explain this point in more detail? I have a power apps with 3 SP lists (three levels) and I am having problems. Specific with the third list, to retrieve the information into a gallery

[WarrenBelz]

Hi @CMOLINAV ,

What I am referring to is SQL-type relationships in any given Filter/Lookup where you are referring to a third table as a relationship to a second table being queried (which may be what you have). It will never be Delegable and should be avoided - you need to plan your data structure so that at any point your required data has "one degree of separation" (sorry for the movie reference) from where you are presently.