Answered

Patch Lookup column to collection

(0) Share

Report

Posted on by AdaEloka

Hello everyone,

I'm trying to patch multiple rows to a collection and then to a sharepoint list. However, I'm experiencing difficulties with one of the columns that is a LookUp column. I'm not sure what I have is correct and in addition to that, I get the squiggly lines for the part that is supposed to load new entries to the collection.

This is the collection I'm trying to patch:

ClearCollect(
 colUpdates,
 {
 ID: 1,
 Title: "A",
 Department1: {Id: 1, Value: drd_NewBudget_Department.Selected.Value},
 Unit1: {Id: 1, Value: drd_NewBudget_Unit_1.Selected.Value},
 Year: {Value: "A"},
 StartingBudget: 1,
 CurrentBalance: 1,
 LineItem: "A"
 }
);
Clear(colUpdates);

These are the records I'm trying to patch...

// Go through each record and filter those items that have been changed or updated. Update corresponding records in the Budget sharepoint list, then set the varGalleryMode to blank so it can go back to View only mode.
//The save icon has a different behaviour when the gallery is in new mode vs edit mode.

// Create a collection to store updated values
If(
 // Do this when new entries are being created
 varGalleryMode="New",

 // Load all new entries into the collection colUpdates
 ForAll(
 gal_NewBudget_2.AllItems As NewRows,
 Patch(colUpdates,
 LookUp(colUpdates, ID=NewRows.ID), {
 Department1: {Id: NewRows.ID, Value: NewRows.drd_NewBudget_Department.Selected.Value},
 Title: NewRows.txt_NewBudget_Code_1.Text,
 Unit1: {Value: NewRows.drd_NewBudget_Unit.Selected.Value},
 Year: {Value: NewRows.drd_NewBudget_Year.Selected.Value},
 StartingBudget: Value(NewRows.txt_NewBudget_StartingBudget.Text),

 //Make the current balance the same value as the starting budget.
 CurrentBalance: Value(NewRows.txt_NewBudget_StartingBudget.Text),
 LineItem: NewRows.txt_NewBudget_LineItem.Text
 });
 );

 // Clear any ID numbers from colUpdates and delete the last row which holds no info 
 UpdateIf(colUpdates, true, {ID: Blank()});
 Remove(colUpdates, Last(colUpdates)),

 // Do this when entries are being edited
 varGalleryMode="Edit",
 //hold the old starting budget in a collection here. also hold the old current balance.

 // Load changed entries into the collection colUpdates
 ForAll(
 Filter(
 gal_NewBudget_2.AllItems,
 tog_NewBudget_isChanged_1.Value
 ) As ChangedRows,
 Patch(colUpdates,
 Defaults(colUpdates), {
 ID: ChangedRows.ID,
 Department1: {Id: ChangedRows.ID, Value: ChangedRows.drd_NewBudget_Department.Selected.Value},
 Title: ChangedRows.txt_NewBudget_Code_1.Text,
 Unit1: {Id: ChangedRows.ID, Value: ChangedRows.drd_NewBudget_Unit.Selected.Value},
 Year: {Value: ChangedRows.drd_NewBudget_Year.Selected.Value},
 StartingBudget: Value(ChangedRows.txt_NewBudget_StartingBudget.Text),

 //remove this current balance from here and patch it separately?
 CurrentBalance: Value(ChangedRows.txt_NewBudget_CurrentBalance.Text),
 LineItem: ChangedRows.txt_NewBudget_LineItem.Text
 })
 )
);

// Update SharePoint with new values or changed values for existing rows
Patch(Budgets, colUpdates);

//collect the new starting budget in another collection here
//create a context variable that is the difference between the new starting budget and the old starting budget.
//add the variable to the old current balance to get the new current balance.
//then patch the new current balance by doing the whole ForAll thing again?

Clear(colUpdates);

//Collect the collection again to get the new sum after new item has been added.
ClearCollect(colBudgetFilter, If(User().Email = lbl_UnitControls_DeptHead.Text, Filter(Budgets, Department1.Value = lbl_UnitControls_Dept.Text), Filter(Budgets, Unit1.Value = lbl_UnitControls_Unit.Text)));

// check for errors
If(
 IsEmpty(Errors(Budgets)),
 // on success
 Notify(
 "Success: Changes Have Been Updated",
 NotificationType.Success
 ),
 // on failure
 Notify(
 "Errors: Changes Have Not Been Updated",
 NotificationType.Error
 )
);

// Return gallery to view mode
Set(varGalleryMode, Blank());

This is the part that gives me the squiggly lines. Any help will be appreciated. Thanks.

Categories:

Building Power Apps

I have the same question (0)

All responses (2)

Answers (1)

Hassan_SZ_365 542 on at

Like (0)

Report
Copy link

Link copied!
Hi,
Match Collection Structure: Confirm that the data structure of colUpdates mirrors your SharePoint list's structure, with attention to lookup columns.
Correct Lookup Syntax: For lookup columns, use the correct syntax. For instance:
{ Id: YourLookupColumnControl.Selected.Id, Value: YourLookupColumnControl.Selected.Value }
Verify Control Names: Ensure that all control names referenced in your formula are accurate and consistent.
Use Defaults for New Records: When adding new records to SharePoint, use:
Defaults(YourSharePointList)
and combine this with your data structure for the Patch function.
Refine the Patch Logic: Your Patch function should be structured correctly for updates, like so:
Patch( SharePointList, LookUp(SharePointList, ID = YourRecordID), { /* your updated fields here */ } )
Convert Data Types: If necessary, use Value() to convert text inputs to numbers before patching them to numeric fields in SharePoint.
Iterate with ForAll: When updating multiple items, use ForAll() to iterate over the collection and apply Patch() to each item.
Clear and Reset: After the operation, clear the collection and reset any variables or toggles.
If you follow these steps, you should be able to resolve the issues with the squiggly lines in the formula editor, which usually signify a problem with the syntax or logic in your Power Apps formula. Remember to always test your changes in a controlled environment before rolling them out to your live application.
Best Regards,
Hassan Raza

Was this reply helpful? Yes No
Verified answer

AdaEloka on at

Like (0)

Report
Copy link

Link copied!
This is what worked in the end:
// Go through each record and filter those items that have been changed or updated. Update corresponding records in the Budget sharepoint list, then set the varGalleryMode to blank so it can go back to View only mode. //The save icon has a different behaviour when the gallery is in new mode vs edit mode. //Use context variable to hold the value of the Id from the LookUp table. This way, you're able to avoid the delegation warning. Then use the variable in the ForAll... UpdateContext({varLookUpDept: LookUp(Units, Department = drd_NewBudget_Department.Selected.Value).ID}); UpdateContext({varLookUpUnit: LookUp(Units, Unit = drd_NewBudget_Unit.Selected.Value).ID}); // Create a collection to store updated values If( // Do this when new entries are being created varGalleryMode="New", // Load all new entries into the collection colUpdates ForAll( gal_NewBudget_2.AllItems As NewRows, Patch(colUpdates, LookUp(colUpdates, ID=NewRows.ID), { Department1: {Id: varLookUpDept, Value: NewRows.drd_NewBudget_Department.Selected.Value}, Title: NewRows.txt_NewBudget_Code_1.Text, Unit1: {Id: varLookUpUnit, Value: NewRows.drd_NewBudget_Unit.Selected.Value}, Year: {Value: NewRows.drd_NewBudget_Year.Selected.Value}, StartingBudget: Value(NewRows.txt_NewBudget_StartingBudget.Text), //Make the current balance the same value as the starting budget. CurrentBalance: Value(NewRows.txt_NewBudget_StartingBudget.Text), LineItem: NewRows.txt_NewBudget_LineItem.Text }); ); // Clear any ID numbers from colUpdates and delete the last row which holds no info UpdateIf(colUpdates, true, {ID: Blank()}); Remove(colUpdates, Last(colUpdates)), // Do this when entries are being edited varGalleryMode="Edit", //hold the old starting budget in a collection here. also hold the old current balance. // Load changed entries into the collection colUpdates ForAll( Filter( gal_NewBudget_2.AllItems, tog_NewBudget_isChanged_1.Value ) As ChangedRows, Patch(colUpdates, Defaults(colUpdates), { ID: ChangedRows.ID, Department1: {Id: varLookUpDept, Value: ChangedRows.drd_NewBudget_Department.Selected.Value}, Title: ChangedRows.txt_NewBudget_Code_1.Text, Unit1: {Id: varLookUpUnit, Value: ChangedRows.drd_NewBudget_Unit.Selected.Value}, Year: {Value: ChangedRows.drd_NewBudget_Year.Selected.Value}, StartingBudget: Value(ChangedRows.txt_NewBudget_StartingBudget.Text), //remove this current balance from here and patch it separately? CurrentBalance: Value(ChangedRows.txt_NewBudget_CurrentBalance.Text), LineItem: ChangedRows.txt_NewBudget_LineItem.Text }) ) ); // Update SharePoint with new values or changed values for existing rows Patch(Budgets, colUpdates); //collect the new starting budget in another collection here //create a context variable that is the difference between the new starting budget and the old starting budget. //add the variable to the old current balance to get the new current balance. //then patch the new current balance by doing the whole ForAll thing again? Clear(colUpdates); //Collect the collection again to get the new sum after new item has been added. ClearCollect(colBudgetFilter, If(User().Email = lbl_UnitControls_DeptHead.Text, Filter(Budgets, Department.Value = lbl_UnitControls_Dept.Text), Filter(Budgets, Unit.Value = lbl_UnitControls_Unit.Text))); // check for errors If( IsEmpty(Errors(Budgets)), // on success Notify( "Success: Changes Have Been Updated", NotificationType.Success ), // on failure Notify( "Errors: Changes Have Not Been Updated", NotificationType.Error ) ); // Return gallery to view mode Set(varGalleryMode, Blank());
This is the collection...It's in the OnVisible property of my Start Screen
ClearCollect( colUpdates, { ID: 1, Title: "A", Department1: {Id: 1, Value: "A"}, Unit1: {Id: 1, Value: "A"}, Year: {Value: "A"}, StartingBudget: 1, CurrentBalance: 1, LineItem: "A" } ); Clear(colUpdates);

Was this reply helpful? Yes No