Answered

UpdateIf with Lookup column

(0) Share

Report

Posted on by INAPowerBI

Hi! I'm trying to Update multiple records in a Sharepoint List, the column I want to update is a record column. And I get different errors. First, I'll share my code:

Set(
 LastSavedGemba,
 Patch(
 Gemba5S,
 Defaults(Gemba5S),
 {
 Title: "GEMBA-5S-00000",
 Nombre: "GEMBA-5S-00000",
 Fecha: Now(),
 MiniCia: DataCardValue3.Selected,
 Participante: DataCardValue4.Text,
 MaterialNecesario: DataCardValue5.Value,
 Layout: DataCardValue6.Value,
 LugarCorrespondiente: DataCardValue7.Value,
 Señalizacion: DataCardValue26.Value,
 AcuerdosLimpieza: DataCardValue9.Value,
 AnalisisLimpieza: DataCardValue10.Value,
 AcuerdosGenerales: DataCardValue39.Value,
 Revisiones: DataCardValue25.Value,
 Realizacion: DataCardValue23.Value,
 Entrenamiento: DataCardValue30.Text,
 Explicacion: DataCardValue31.Text,
 Importancia: DataCardValue32.Text,
 Ventajas: DataCardValue33.Text,
 MantencionLimpieza: DataCardValue34.Text,
 FrecuenciaLimpieza: DataCardValue16.Text,
 EstimulacionGerencia: DataCardValue14.Text,
 ProblemasMejoras: DataCardValue20.Text,
 CalificacionLimpieza: DataCardValue29.Value,
 Mejoras: DataCardValue35.Text,
 PropuestasImplementadas: DataCardValue36.Text,
 Progreso: DataCardValue37.Text,
 Reconocimiento: DataCardValue38.Text
 }
 ).ID
);
Set(
 LastGembaName,
 Concatenate(
 "GEMBA-5S-",
 Text(
 Value(LastSavedGemba),
 "[$-en-US]00000"
 )
 )
);
UpdateIf(
 Gemba5S,
 ID = LastSavedGemba,
 {
 Nombre: LastGembaName,
 Title: LastGembaName
 }
); UpdateIf(Acciones, ID in AccionesIdCollection.IdAccion, { IDGemba: LastSavedGemba.ID });
ResetForm(Gemba5SForm);
Refresh(Gemba5S);
Navigate(
 [@Gemba5SHome],
 ScreenTransition.Fade
);

I'm setting a variable (LastSavedGemba) with the result of a Patch method. I already tried the patch method by it's own and it's working fine. Then I need to update the name of this record, but I have no trouble doing that neither. But, after that, I have a collection of "Actions Identifiers" (this actions where previously saved and the collection does have records) and I want to update the Sharepoint List "Acciones" where the ID is in the collection and set the field GembaID (which references the first Sharepoint List, the one we applied the Patch function). The Sharepoint list currently has two columns referencing that Sharepoint List (name of the list: Gemba5S): IDGemba and IDGemba:Nombre. So, the IDGemba is a Lookup field (so does the IDGemba:Nombre) and I've tried the code above and of course it throws me this error:

Incompatible type. The 'IDGemba' column in the data source you're updating expects a 'Record' type and you're using a 'Number' type

And if I change the code to this (selecting the full saved record instead of just the ID):

Set(
 LastSavedGemba,
 Patch(
 Gemba5S,
 Defaults(Gemba5S),
 {
 Title: "GEMBA-5S-00000",
 Nombre: "GEMBA-5S-00000",
 Fecha: Now(),
 MiniCia: DataCardValue3.Selected,
 Participante: DataCardValue4.Text,
 MaterialNecesario: DataCardValue5.Value,
 Layout: DataCardValue6.Value,
 LugarCorrespondiente: DataCardValue7.Value,
 Señalizacion: DataCardValue26.Value,
 AcuerdosLimpieza: DataCardValue9.Value,
 AnalisisLimpieza: DataCardValue10.Value,
 AcuerdosGenerales: DataCardValue39.Value,
 Revisiones: DataCardValue25.Value,
 Realizacion: DataCardValue23.Value,
 Entrenamiento: DataCardValue30.Text,
 Explicacion: DataCardValue31.Text,
 Importancia: DataCardValue32.Text,
 Ventajas: DataCardValue33.Text,
 MantencionLimpieza: DataCardValue34.Text,
 FrecuenciaLimpieza: DataCardValue16.Text,
 EstimulacionGerencia: DataCardValue14.Text,
 ProblemasMejoras: DataCardValue20.Text,
 CalificacionLimpieza: DataCardValue29.Value,
 Mejoras: DataCardValue35.Text,
 PropuestasImplementadas: DataCardValue36.Text,
 Progreso: DataCardValue37.Text,
 Reconocimiento: DataCardValue38.Text
 }
 )
);
Set(
 LastGembaName,
 Concatenate(
 "GEMBA-5S-",
 Text(
 Value(LastSavedGemba.ID),
 "[$-en-US]00000"
 )
 )
);
UpdateIf(
 Gemba5S,
 ID = LastSavedGemba.ID,
 {
 Nombre: LastGembaName,
 Title: LastGembaName
 }
); UpdateIf(Acciones, ID in AccionesIdCollection.IdAccion, { IDGemba: LastSavedGemba });
ResetForm(Gemba5SForm);
Refresh(Gemba5S);
Navigate(
 [@Gemba5SHome],
 ScreenTransition.Fade
);

It now gives me this error:

Missing column. Your formula is missing a column 'Id' with a type of 'Number'.

Any ideas regarding how can I fix this? Please, help me 😞

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

@INAPowerBI

My first observation and comment would be that it appears (since I see datacardvalues) that you are using an EditForm - why are you not just using the built-in SubmitForm function of the form to do all of this for you? Your UpdateIf will become much easier.

Was this reply helpful? Yes No
INAPowerBI 8 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi, I'm not using Submit because I have fields that the user does not choose. Like the name, this field is not filled by the user. Also, so far that is working just as I want, the list I'm trying to apply the "update if" is another list 😮

Was this reply helpful? Yes No
Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(2)

Report
Copy link

Link copied!
@INAPowerBI

Yes, understood, but it creates a tremendous amount of work for you. You can still submit columns that the user does not fill in like your Title and Name columns. To do so, just make sure those fields or in your EditForm and set the update property of the datacard to the value you want, then set the visible property of the datacard to false. Now you can use the SubmitForm and get exactly what you want without the hassle.

That all said, I would recommend the following:

1) Replace the formula you have in the (most likely) OnSubmit of your button with : SubmitForm(yourFormName)

2) Make sure your form has the Nombre and Title fields in it.

a) Set the Update property on the datacard for Nombre and Title to "GEMBA-55-00000"

b) Set the Visible property on the datacard for Nombre and Title to false

c) Also seems like Fecha is perhaps a none displayed field - you can do the same as above for that except set its datacard Update property to Now() (by the way, you might even consider removing this as your record will already have a Created date and time when you submit it.)

3) Add the following formula to your OnSuccess action of the form:

With({_nameValue: "GEMBA-SS-" & Text(Gemba5SForm.LastSubmit.ID, "00000")}, UpdateIf( Gemba5S, ID = Gemba5SForm.LastSubmit.ID, {Nombre: _nameValue, Title: _nameValue } ); UpdateIf(Acciones, ID in AccionesIdCollection.IdAccion, { IDGemba: { Value: _nameValue, Id: Gemba5SForm.LastSubmit.ID, '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference" } } ) ) ResetForm(Gemba5SForm); Navigate(Gemba5SHome, ScreenTransition.Fade)

This is all that you need. It will Submit the information in the form without the patch and the redundant formula. If the Submit is successful, then it will alter the Nombre and Title columns to the proper value. Then it will update the Acciones list with the proper lookup record.

Finally, it will reset the form and then navigate. Note: there is no need to refresh the datasource after a submit or Update or Patch. That should save you some performance.

Was this reply helpful? Yes No
INAPowerBI 8 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks! I'll try it and then I'll come back with the results!

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

@INAPowerBI

Sounds good. I'd say keep things simple and let PowerApps do the heavy lifting as much as you can. It is easier to maintain and design.

Let me know how it turns out.

Was this reply helpful? Yes No
Lynn626 20 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi There,
I have a similar issue where I'm trying to use the updateif function to only update existing records. However, using the code below, the existing records get updated, but new records get created as well. Its like it saving the last record (new record created) prior to updating the existing record.
Any idea how I need to update the code below to only get the existing record updated and not create new records?
Thank you in advance.

UpdateIf(
colGridData,
ThisRecord in colSelected,
{
'Value for This Period': ValueUpdate.Selected,
'Event Response': EventResponseUpdate.Text,
'P1/P2 Impacted Products': P1Update.Text,
Status: StatusUpdate.Selected
}
);
Clear(colSelected);
Reset(checkboxAll)

UpdateIf(
colGridData,
ThisRecord in colSelected,
{
'Value for This Period': ValueUpdate.Selected,
'Event Response': EventResponseUpdate.Text,
'P1/P2 Impacted Products': P1Update.Text,
Status: StatusUpdate.Selected
}
);
Clear(colSelected);
Reset(checkboxAll)

Was this reply helpful? Yes No