relationships

[chahineatallah1]

I have 3 tables, one table for employee with employee ID and employee name

Then I have order table with employee ID and order ID

I want to create a new column in sharepoint list orders , called employee reference where it basically will show employee name taken from employee table

So I created a gallery which will populate the employee name from the employee table, this is working fine am just using LookUp(Employees,ID=empdropdown.Selected.EmployeeID).fullname, where it will lookup employee name in each item

Then after that I created a patch function will basically will loop inside each item in gallery and copy the employee name to the new column “employee reference”, but its copying wrong values, don’t know what am writing wrong in my formula

but in my final order table not all employees are populated and what is populated is not correct value

[AmeerJab]

Hello,

i would suggest creating a collection first that saves the data from the gallery, then update employee reference in the forall, also one thing when using forall to have an accurate update i would suggest this syntax

ClearCollect(OutcomeTable,ForAll(UpdateMainCollection,Patch(UpdateMainCollection,LookUp(MainCollection,Col=UpdateMainCollection[@Col]),{Col:UpdateMainCollection[@Col]})))

[WarrenBelz]

Hi @chahineatallah1 ,

What type of columns are employeereference in orders and fullname in Employees ?

[chahineatallah1]

hi Warren

employeereference is lookup type, fullname in employees is just single line text

[chahineatallah1]

thanks for reply AmeerJab, i will check it, seems complicated formula

[chahineatallah1]

is there any video for that ?

[WarrenBelz · Accepted solution]

@chahineatallah1 ,

OK - I am a little confused why you would bother with a lookup column and then not use  Choices(ListName.FieldName) to get the lookup up value choices. I do not (and probably never will) use this column type, but that is another discussion.

The piece missing here is the Items of empdropdown as you refer to empdropdown.Selected.EmployeeID, so you are clearly getting the data from somewhere else. From the Label code

LookUp(
   Employees,
   ID = empdropdown.Selected.EmployeeID
).fullname

You have told me that the fullname column in Employees is a Text column and I assume that that you are displaying the EmployeeID column from Employees in your drop-down. If all of the above is correct, you should need something like this.

Patch(
   orders,
   ForAll(
      ordersgal.A11Items As allorders,
      {
         ID: allorders.ID,
         employeereference:
         {
            Value:
            LookUp(
               Employees,
               ID = allorders.empdropdown.Selected.EmployeeID
            ).fullname,
            Id: 
            LookUp(
               Employees,
               ID = allorders.empdropdown.Selected.EmployeeID
            ).ID
         }
      }
   )
)

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.

MVP (Business Applications)   Visit my blog Practical Power Apps

[chahineatallah1]

hi Warren

Yes it worked, thanks, solution accepted 🙂

But i didnt understand why if i use my formula it didnt work

Let me explain ,in my order table i had lfullname text label which is basically lookup from the employee table as my order table contains only employee ID

so my idea was that in my gallery i will create textlabel ,lfullname which is lookup from employee table to get full name

Then my forall function will go in each item in the gallery and take fullname from the text label and update the column employee reference in my order sharepoint order list with fullname, so that in my order list i will have also a fullname ,its like vlookup in excel if you want

Am just curious what i was doing wrong in my code, as i was using value: allorders.lfullname.text, basically it should read whatever text in lfullname label

[WarrenBelz]

Hi @chahineatallah1 ,

Two things - calculated Labels are not the most reliable route when you have to rely on their recalculation, but more importantly, did the Items of your drop-down (I do not know these presently) actually included the ID you needed. I simply went straight to the source and got both values directly.