Fields not resolving when patching multiple records at once

[Cooper01]

I have a collection of items I'd like to patch to a Dataverse table. I am trying to patch them all at once instead of using ForEach to increase performance. This is what the statement looks like:

Collect(
    'Order Products',
    AddColumns(
        colRequestedItems,
        "cra00_requestedby",
        gblCurrentUserRecord,
        "cra00_order",
        locSelectedOrder.OrderID
    )
)

The collection already includes a column with the schema name of another column in the Order Products table, cra00_productname which is a text field. cra00_requestedby is a lookup field to User and cra00_order is text field. Requested By and Order columns are supposed to be the same for all items being patched, which is why I'm using AddColumns instead of adding it to the collection.

The warning I am getting is in the AddColumns section. It says "The specified column 'cra00_requestedby' does not exist. The column with the most similar name is 'Requested By'". cra00_requestedby is the schema/internal name of the table in CDS while Requested By is the display name. I also tried to change it to use the display names in AddColumns, as referenced in the warning message. This just changes the error message to "The specified column 'Requested By' does not exist. The column with the most similar name is 'Requested By'". I can get similar message to surface if I delete the Requested By portion on the Order column.

The strange thing is that since this is just a warning, I tried it anyway. The Order will patch as expected but the Requested By will not, that column is just blank for the new records. There are no errors when clicking the button through the studio.

Anyone know what the actual issue is here?

[v-jefferni · Accepted solution]

Hi @Cooper01 ,

Do you mean that lookup field in the collection did not patch to a table successfully?

If so, please first check if the collection have included that lookup field values. By default, in collections creating you would not be able to retrieve a LookUp field with your formula. Since the requested by field looks up to Users table, you will need to:

1\ Add Users table to Power Apps.

2\ Modify your collection formula to:

Collect(
    'Order Products',
    AddColumns(
        colRequestedItems,
        "cra00_order",
        locSelectedOrder.OrderID,
        DropColumns(
                    AddColumns(
                              ShowColumns(Table, "cra00_requestedby"),
                              "tempUserColumn",
                              First(Users).User
                    ),
                   "tempUserColumn"
        )
     )
)

Hope this helps.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

[mdevaney]

@Cooper01 
If you're not able to figure it out using the post by @v-jefferni  please let me know.  I've had this issue before and solved it.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[Joo]

I have a similar issue.

I try to collect a collection to my list. I removed a few columns before sending items. Then I added the '_ImageJson' column to the list and removed the dropcolumns on the '_ImageJson' column (same name) in my collection. But I get the following error:

The specified column '_ImageJson' does not exist. The column with the most similar name is '_ImageJson'.

I tried to add an element of the list to the collection to set the structure to the list.  I also tried to remove and add the list (in data view). I always get the same error.

The column '_ImageJson' has the same internal name.

What can I do?

[v-jefferni]

Hi @Cooper01 ,

Have you resolved this problem?

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

[Cooper01]

@v-jefferni thanks for the reply and sorry for the delayed response.

Can you please clarify what Table is referring to and what the First(Users).User is doing? I am having some trouble getting this to resolve. I think the main issue is that the outer Add Columns doesn't have an odd number of params and I'm not really understanding this enough to know what my issue is/if there is a syntax error

[Cooper01]

Hi @mdevaney , still struggling with this so would be very grateful for any additional thoughts you could add to the original proposed solution. Thanks in advance!!

[v-jefferni]

Hi @Cooper01 ,

Sorry for my late reply.

For your 2 questions:

1\  Table in my post referred to the Table in CDS, which is "Order Products" I suppose.

2\  First(Users).User seemed meaningless, right? Collect LookUp column values in CDS is quite different. In this case, since the LookUp column looks up to the User table, First(Users).User is more likely to declare the internal structure of the LookUp column.

Hope this helps.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

[Cooper01]

Thanks @v-jefferni but I am really struggling to understand what you are trying to do and still even get the example to resolve on my side. I still think the most significant issue is the number of arguments in the outer add columns but again really not following.

Are you aware of any blogs or references you could share on this topic? 

[v-jefferni]

Hi @Cooper01 

Maybe below blog would help:

http://linnzawwin.blogspot.com/2019/12/power-apps-using-common-data-services.html#collection

In this section says:

This behaviour is by design and the app will only load the value of the lookup fields which are being used in the app. (this is to optimise the data loading of the app, avoiding to retrieve unused data in the app). So if you are having empty columns in your collection, don't panic! - just use them in the other parts of the app and trigger the ClearCollect event again to see the data in the lookup of the collection.

So you could also set a variable in your app as described in below blog:

https://sachinbansal.blog/2020/01/21/collect-does-not-collect-lookup-value-from-cds-data-source-by-d...

Hope this helps.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.