LookUp inside Patch inside ForAll

[JK2020]

Hi,

I am trying to update each record in my SP list with a dynamic value that I need to look up in another data source. My code:

ForAll(RenameColumns(SPList, "ID", "tempID"), 
    Patch(SPList, 
        LookUp(SPList, ID = tempID), 
        {
            Status: 
            {
                Value: LookUp(myDyn365DataSource, 'Purchase Order ID'=ThisRecord.'Purchase Order ID', 'Status'), 
                '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference"
            }
        }
    )
)

However, I am getting an error saying that "the right side of the 'Equal' operator must be a constant value".

Replacing "ThisRecord.'Purchase Order ID'" with a constant value allows the function to run, but that doesn't achieve my goal.

Also tried UpdateIf but with same results.

Any idea how to work around this, please?

Thanks,

[yashag2255]

Hi @JK2020 

Can you try to update the expression to below mentioned expression?

ForAll(RenameColumns(SPList, "ID", "tempID"), 
    Patch(SPList, 
        LookUp(SPList, ID = tempID), 
        {
            Status: LookUp(Choices(SPList.Status),Id = "Value to Match")
        }
    )
)

Here, the "Value to Match" needs to be replaced with the value that will be matched equal to the specified column value. If you can share more details about the schema of the datasource and how you are getting value for "Value to Match", we might be able to help you better.

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[JK2020]

Thank you. Could you clarify two things, please:

1. What's "Id", I don't have in neither of my data sources

2. In the statement below, where should my 'myDyn365DataSource' go?

Status: LookUp(Choices(SPList.Status),Id = "Value to Match")

[yashag2255]

Hi @JK2020 

1. What's "Id", I don't have in neither of my data sources -> When you use Choices function, it returns two columns Id and Value for lookup column and Value for Choice type column. If Status is a choice type field, then you can replace "Id" with "Value".

2. myDyn365DataSource expression will be placed in place of "Value to Match". For that purpose, I asked for the schema of the datasource and how you are getting value for "Value to Match".

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[JK2020]

OK, so yes, Status is a Choice field so I'm changing the "Id" to "Value"

As for the schema of my data source (myDyn365DataSource):

my company's CRM instance / Purchase Orders (records) / Status (option set, i.e. users select set values from drop down in the UI)

Does that clarify?

[yashag2255]

Hi @JK2020 

Based on my understanding, you have a myDyn365DataSource and you want to update the status of the records inside it, if that is the case, you need to replace SharePoint List with the name of your dynamics 365 source. Since you mentioned that the dropdown has all the status values. Can you try to update the configurations as:

Dropdown/Combobox -> Items -> Choices(myDyn365DataSource.Status)

Button -> OnSelect -> 

ForAll(RenameColumns(myDyn365DataSource, "ID", "tempID"), 
    Patch(myDyn365DataSource, 
        LookUp(myDyn365DataSource, ID = tempID), 
        {
            Status: ComboboxName.Selected
        }
    )
)

If this doesn't help, can you share a screenshot of the configuration, this way we will be able to help you better.

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[JK2020]

Thank you for trying to help. As stated in my very first post, I am trying to update records in my SP list, not Dyn365 data source. The SP list field I need updated is a choice field. For each record in my SP list, i need to perform a LookUp to my Dyn365 data source to first find the matching Purchase Order and get its current Status (which is also a choice field/option set in our CRM). And that Status value is what i want to Update (Patch) to my SP list.

Think of it this way - we use Dyn365 to manage orders, and i use a SP list to keep track of some of them. The sales team updates the order status as they go and at the end of the shift, i want to run my function to get the latest status of the bunch of orders i keep track of into my SP list. Does that make it clear?

[JK2020 · Accepted solution]

OK, I figured it out - all that's needed is to remove "ThisRecord." from my initial solution and it works as expected. Thank you yashag2255 for trying to help.