Error creating collection

[MGC19]

I created a gallery table where the On Change formula in all the fields selects a button (not visible) and will create/update a collection of that row. All rows will patch to my SharePoint list when I hit save.  The formula is below however It points specifically to my List Price saying that "column in the data source you're updating expects a Record type and you are using a Text type". This field in the table is not from a lookup column but rather has a formula in PowerApps that gets pricing from another list and inputs that pricing. How would I change this so it will work?

IsBlank(

    LookUp(

        colWaste,

        ID = ThisItem.ID

    );

    Collect(

        colWaste,

        ThisItem

    )

);

UpdateIf(

    colWaste,

    ID = ThisItem.ID,

    {

        'Product Code': TxtProdCode.Value,

        'Quantity/YR': TxtQtyYr.Value,

        'Category ID (Name)': DrpCatName.Selected,

        Discount: TxtDiscount.Value,

        'Requested Price': TxtReqPrice.Value,

        'List Price': TxtListPrice.Value,

        'Yearly Disc$': TxtYrlyDisc.Value,

        'Yearly List$': TxtYrlyList.Value

    }

)

I tried other formulas from other posts, but none of them seem to work. Any help would be greatly appreciated. Thanks!

[elseb]

have you checked what column type is 'List Price' ? the error clearly states that the column is not of text type which causes the problem.

[MGC19]

@elseb in the Sharepoint list it is currency. Does that matter for the collection? I figured it had something to do with the lookup formula in it since I had to format it to show the currency format:

LookUp(

        'CCMS LP',

        PRODUCT_CODE = TxtProdCode.Value,

        LIST_PRICE

    ),

    "[$-en-US]$#.00"

)

[elseb]

try that:

UpdateIf(
    colWaste,
    ID = ThisItem.ID,
    {
        'Product Code': TxtProdCode.Value,
        'Quantity/YR': TxtQtyYr.Value,
        'Category ID (Name)': DrpCatName.Selected,
        Discount: TxtDiscount.Value,
        'Requested Price': TxtReqPrice.Value,
        'List Price': Value(TxtListPrice.Value),
        'Yearly Disc$': TxtYrlyDisc.Value,
        'Yearly List$': TxtYrlyList.Value
    }
)

[MGC19]

@elseb I tried that, but same error type comes up but with number type instead.

[elseb]

It can't be of the type currency then, go to SharePoint list click on column header > column settings  > edit and tell me what's under type please.

I think you're trying to patch a lookup column... 

If that's the case can you explain what are you trying to achieve? This may help us find a solution that'll work for you.

[james1994]

Could you share the lookup formula that gets you the list price? I'm guessing the formula is returning the entire record rather than just the price value, which is causing the data type conflict error. 

[MGC19]

@elseb my apologies it is a lookup column. I had made two. One Currency and another Lookup thinking that would solve the problem. I am creating an excel like gallery where the user will be entering in a lot of data row by row and all need to be stored in a collection and then patched to a Sharepoint list. This text input control I am trying to patch is looking up to another Sharepoint list to get pricing for product codes entered in another field. That value is what I need to patch to the Sharepoint list.

[MGC19]

@james1994 this is what I have in the Value property

LookUp(

        'CCMS LP',

        PRODUCT_CODE = TxtProdCode.Value,

        LIST_PRICE

    ),

    "[$-en-US]$#.00"

)

[elseb]

right, 

It sounds like you're duplicating your work, that lookup column should already display the correct value from what are you describing so you don't have to patch anything to it.

If however you like to patch it without lookup via the list what you can do is:

create a collection with association on the screen where the user enters the data, you need two columns with data from the excel : column1 - same content as the dropdown from which user selects the item to compare, and  column2 the cost value based of that selection.

When user selects the option you compare their selection with the table (column1) and get the value (column2)

you can display it if you wan the user to see it but when you patch you do:

currency_column: LookUp(colCostTable, column1=dropdown.selected.value).column2