How to update other fields based on one text field?

(0) Share

Report

Posted on by NewBee117

247

I am not sure how to update the table "WorkingUnit" in SQL database when user input field UnitID changes in Gallery1:

1. Change the value in UnitID text field

2. Lookup data from SQL data table "UnitInfo" for UnitName, AKA, Status, StartDate and EndDate

3. Patch data to table "WorkingUnit".

For OnChange property of UnitID field: Collect(UnitInfo_Collection, LookUp('[dbo].[UnitInfo]', UnitID = UnitID.Text)); Patch('[dbo].[WorkingUnit]', ThisItem, {UnitID: UnitInfo_Collection.UnitName})

I got error saying expected type text, found table. I just need to use the first row in the collection (lookup always returns one row), how do I tell it to use the first row? Thank you in advance for your help!

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (1)

Jeff_Thorpe 6,085 Super User 2024 Season 1 on at

Like (1)

Report

If you are looking to get the first record of UnitInfo_Collection then it would be First(UnitInfo_Collection) and if you wanted just the UnitName value of the first record it would be (UnitInfo_Collection).UnitName

Was this reply helpful? Yes No
Verified answer

v-siky-msft on at

Like (0)

Report
Hi @NewBee117 ,

The cause of your issue is that the UnitID you want to patch should be a value, however what you put (UnitInfo_Collection.UnitName) is a table.
Collect function you use will add records to the existing collection, so it returns a table. And the latest record is in the last column
So you should modify your formulas as below:
Collect(UnitInfo_Collection, LookUp('[dbo].[UnitInfo]', UnitID = UnitID.Text)); Patch('[dbo].[WorkingUnit]', ThisItem, {UnitID: Last(UnitInfo_Collection).UnitName})
NOTE: If you want collection always only saves the current record, please try ClearCollect function.

Best regards,
Sik

Was this reply helpful? Yes No