Multiple LookUp columns using same table in Model Driven App

[HFG]

Hi all, 

I have a section in a model driven app which is recording deliveries. A delivery can be split up and stored in 3 different silos. I  therefore need to have 3 fields in the deliveries table which use a lookup to the "Silo" table, but Dataverse will only allow me to create one LookUp column using this table. I have tried adding a Many-to-Many relationship to see if that would work, but it hasn't. Does anyone know how I can solve this?

Thanks

[dpoggemann · Accepted solution]

Hi @HFG 

I would do the following from a model perspective to accomplish:

This way you can have a single lookup field on the Delivery Silo to the Delivery table.  

Advantages:

1.  You could have anywhere from 0 to N silos the delivery is split into.

2.  Look at Silo at any time to see deliveries tied to it

3.  You can have fields on the Delivery Silo table that would define metadata about this delivery and silo combination.

Hope this helps.  Please accept if answers your question or Like if helps in any way.

Thanks,

Drew

[HFG · Accepted solution]

Hi @dpoggemann 

You're right, this is best from a modelling persepctive. 

For info, for anyone else wanting to do this, I found out that the problem was not that I can't have mulitple fields looking at the same table, but that the relationship which is created had the same name so I wasn't able to save without changing to the name of the relationship. 

[florianbommariu]

Hey @HFG and @dpoggemann , this comes close to my problem.

Can you describe, how you managed to display the Dilveries, when looking at a Silo? I think that means, you are at a Fom in One Silo and show the user the related Deliveries? How is this solved?

[Sharafaa]

Hi, I'm building new PowerApps for events reservation, this should look up for 3 conditions before submitting the Event (Time/date Periods, Location, and multiple selection Classrooms) to check for time overlapping or (classroom/ location) Conflict.

- multiple selection Classrooms Combo Box converted into Text in order to use it inside lookup function to compare it with multiple selection SPO column, i used Left, Right, and Mid function for the comparison.

comparison is working when the list of selected classrooms is few, otherwise it doesn't work!

Please check the code below:-

ClearCollect(Data_Check_Lesson_AR1,

LookUp('Events Lists',

From in Filter( 'Events Lists',

( ( Start_Time <= From && End_Time >= To )
Or ( Start_Time >= From && End_Time <= To )
Or ( Start_Time < To && End_Time >= To )
)
&& Actv_Location.Value = New_Lesson_Loc_AR.Selected.Value
)

Or
( ( Start_Time <= From && End_Time >= To )
Or ( Start_Time >= From && End_Time <= To )
Or ( Start_Time < To && End_Time >= To )
)

&&
( Left(Label_Class_Less_AR.Text, Find(",",Label_Class_Less_AR.Text)-1) in Concat(Classes_New_EN, Value & ",") Or

Right(Label_Class_Less_AR.Text, Find(",",Label_Class_Less_AR.Text)) in Concat(Classes_New_EN, Value & ",")

Or Mid(Concat(Classes_New_EN, Value & ","), Find(",",Concat(Classes_New_EN,Value & " " ))) in Mid(Label_Class_Less_AR.Text,Find(",",Label_Class_Less_AR.Text))
Or Concat(Classes_New_EN, Value & ",") in Mid(Label_Class_Less_AR.Text,Find(",",Label_Class_Less_AR.Text)+
Len(Left(Label_Class_Less_AR.Text, Find(",",Label_Class_Less_AR.Text))))
)

).From
))

-----

Classes_New_EN is the multiple selection column in SPO

Start_Time & End Time is the combination of date and time

Label_Class_Less_AR.Text is the Concat(Classroom  Combobox)

New_Lesson_Loc_AR.Selected.Value is the DropDown of Location.

Items of Table in the picture is Data_Check_Lesson_AR1

[HFG]

Hi @Sharafaa , 

My problem was related to a mode driven app using 3 Look Up columns in Dataverse. You probably want to start a new thread to get answers to your problem. 

Hope you find the answer. 

[HFG]

Hi @florianbommariu , 

The 3 silo columns were on the delivery form in the model driven app and the user could select up to 3 seperate silos for each delivery. I don't remember if we had a view looking from each silo to the deliveries (I'm no longer working on this project so can't check unfortunately), but if we did I believe we would have had to have a seperate table and a flow to write a line per silo/delivery. 

If I understand, your problem is with showing the relationship from table (house) to a table (person) connected via a middle table (apartment). I don't think this is possible in model driven, but maybe someone can confirm. I remember having a lot of repeated columns in my tables because I couldn't fetch data from tables that weren't immediately related.

Hope that helps.