Patching lookup field

[Kadd]

Hello,

Hope you are all doing well.

I will ask a question that was asked several times. I read the other posts, tested and it doesn't work. So I guess there is something that i do wrong.

I'm trying to patch a lookup field.

I have a SharePoint list Process with fields Department ( lookup from List Department) and Process (Text field). These fields are part of a gallery (GalleryProcess). So I have GalDepartment and GalProcess.

And i have a SharePoint list called Department containing the name of the department in text fields.

My form is on list Process, I try to update my department. So I worte:

Patch(

    Process,ThisItem, {

        Process:GalProcess.Text,

        Department: {

               '@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",

                Id:GalleryProcess.Selected.ID,

                Value:GalDepartment.Selected.Result 

         }

     }

)

It patches the Process but doesn't patch the department.

Would someone be able to tell me what I'm doing wrong ?

Thanks a lot !!

[Kadd]

@RandyHayes ,

I can unfortunately not make a screenshot of it cause it's on another computer where everything is restricted 😞

I try to do my best to explain but it's not easy.

My GalleryProcess is linked to my Process list. in this Process list i have 2 fields Peocess (text field) and Department (which is a lookup to Department list).

My GalleryProcess shows the content of the Process list (mainly my Process and Department fields).

My form is on the Process list, everything is done on the Process list. 

I open my form, I create a new process setting the process name and department.. i save, ok . because this is part of my new form, i just use a "SubmitForm".

After the form is submited, my form remains open (i do this because all this is in fact a bigger form with tabs) and i see the GalleryProcess with the items from my Process list.

I want to update one of the processes, in the GalleryProcess, on the far right, i have a menu button that shows a cancel, edit, save and remove icon. I hit Edit...that allows my fields in the GalleryProcess to be edited...I do the modification... then i hit button save (which will set the current item back to disabled and save the new values to the Process list. This saves all my values but the department...(which is a lookup field link to the Department list)... doesn't get updated, it's just blank....

is it clearer ? I'm really sorry i can't explain it better 😞

[RandyHayes]

@Kadd 

No problem - you're doing fine explaining, it just takes a little longer to get a clear picture.

So, what is missing from your explanation though is another Gallery or some control called GalDepartment.

This is the source of your issue.  

Trying to read back through the messages to put those pieces together - you referred to this as a dropdown in message #3.  Is GalDepartment a dropdown control then?  

You mention that the Items property is Distinct(Department, Department).  Is there a reason you need to use distinct on that list?  Are there duplicate departments?

[Kadd]

@RandyHayes ,

aaaaahhh....

Yes, GalDepartment is the name of the fields Department (from Process list) in GalleryProcess for which i used a Dropdown control in GalleryProcess.

and yes there are duplicate departments (again, i try to make it simple but there are other fields in my lists. in Department list, there are department information fields as well as an entity and a division)

So if i correctly understand what you mean, I should add another gallery where the items come from Department list, hide this gallery and try to reference my GalDepartment field to the department field in that hidden gallery ? 🙂

[RandyHayes]

@Kadd 

Actually, I am more focused on your use of Distinct.  If you are trying to have your lookup field in SharePoint lookup to a record in your Department list...then which one??  If there are, for example, 10 records that have "Sales" as the department and you use Distinct to filter out duplicates, then when you set your Lookup column in your list to a Department record...which one will be the one referenced in the lookup? - there are 10 of them!!

You don't need a hidden gallery or anything else special for this, but the above point on distinct is the one that is most concerning.

[Kadd]

@RandyHayes ,

ok, i understand what you mean.

ok my bad, then just talking about Department and Process was not a good idea. 

In my Department list , i also have fields like Entity, Division, Department comment

In my Process list, I have fields Entity, Division, Department, Process, Process comment

Entity and Division come from my first screen which holds basic information Entity, Divison, status, etc...

and i use global variables when swithing to the process screen in order to set the value or Entity and Division.

From that point on my Process form, I have those 2 values that are automatically populated and Department, which is a lookup field is a cascading drop down depending on the value of Entity and Division. So If i load a specific Entity/Division, I get the right Department.

so yes, there might be 10 of them but since that "couple" Entity-Division is unique, that gives me the correct Department.

[RandyHayes]

@Kadd 

So this is where you need to be...you need to have the Id of the Department record that corresponds to the correct record you want.  Then, the Value of that record needs to be based on whatever you have defined it in your SharePoint list.

Once you derive that, then your formula is:

UpdateIf(
    Process,
    ID=ThisItem.ID, 
    {
        Process:GalProcess.Text,
        Department: {
                Id: theExactDepartmentRecordID,
                Value: theCorrespondingColumnValueAsDefinedInYourLookupColumn 
         }
     }
)

[Kadd]

@RandyHayes ,

ok....so.... UpdatIf can replace Patch (from my formula in the first post) but... ID and Value come from Department list , right ? or... no...ID is from Department and Value from Process?

my problem is still... how do i get these values ?

I add the Department list as a data source (which is already done) and ...? 😄

I'm so sorry i don't see the thing ..hmm

[Aaron3Sixty5]

If the gallery has this selected you may not need the 'expanded' syntax.

Patch(

Process,ThisItem, {

Process:GalProcess.Text,

Department: GalDepartment.Selected

}

)

[Kadd]

Hi @Aaron3Sixty5 

Thanks for you answer.

That's something i tried at the very beginning but it was not working. however it was not showing errors in my formula.... while now that i test it again... it shows errors saying it expects a Record value of different schema.

hmm...

[RandyHayes]

@Kadd 

Yes, UpdateIf is a better choice as it does not require two data actions and is more accurate.  In your original formula, you were using ThisItem.  This sometimes gives unpredictable results depending on the Items property of the Gallery, controls in the gallery, etc.

A lookup column needs to have the ID of the record that it is related to from the foreign list.  In your case, it needs to be an ID from the Department list.  The Value is determined by the configuration of the lookup column in SharePoint.  Usually, the related lookup column is Title, but it can be anything.  So, you need to provide the Value as the related column value in your foreign list.

So then - Yes - ID and Value come from Department list.

For example, if you have a record in Department that has "Sales" and is record ID 20, then your formula would be such as this:

UpdateIf(
    Process,
    ID=ThisItem.ID, 
    {
        Process:GalProcess.Text,
        Department: {
                Id: 20,
                Value: "Sales" 
         }
     }
)

This just gives a "hard coded" example of what is needed.