Update collection when it already exists within the sharepoint list

[Daniel20]

A very good afternoon for everyone. I am using 3 ready to use in my application:

ListaEmpresaProyecto = total list of companies

TotalHorasHombre 

TareoTrab=

The objective of this application is to save the number of workers per project and per date. This must be unique per project and per date.

This code has been placed in the Onchange property of CBProy:

ClearCollect(
    colContratas;
    ShowColumns(
        Filter(
            ListaEmpresasProyecto;
            Proyecto = CBProy.Selected.Proyectos
        );
        Contratistas
    )
);;
ForAll(
    colContratas As Cont;
    Collect(
        colTareoTrab;
        {
            ContrataPartida: Cont.Title;
            Proyecto: CBProy.Selected.Proyectos;
            Fecha: DatePicker2.SelectedDate;
            NTrabajadores: ""
        }
    )
)

 with which I generate a collection where I have to save the number of workers.

Once I set the number of workers with a button, I save the collection with the following code:

Set(
    varCarga;
    true
);;
Set(
    varidPatch;
    Patch(
        TotalHorasHombre;
        Defaults(TotalHorasHombre);
        {
            Proyecto: CBProy.Selected.Proyectos;
            Fecha: DatePicker2.SelectedDate;
            NTrabajadores: LblNTrab.Text;
            TrabajadoresTotal: LblNTrab.Text * If(
                Text(
                    DatePicker2.SelectedDate;
                    "dddd"
                ) = "sábado";
                5,5;
                8,5
            )
        }
    ).ID
);;
ForAll(
    Filter(
        colTareoTrab;
        !(IsBlank(NTrabajadores))
    ) As Tareo;
    Patch(
        TareoTrab;
        Defaults(TareoTrab);
        {
            ContrataPartida: Tareo.ContrataPartida;
            Proyecto: Tareo.Proyecto;
            Fecha: Tareo.Fecha;
            NTrabajadores: Tareo.NTrabajadores;
            IDTHH: varidPatch
        }
    )
);;
Clear(colTareoTrab)

 So far the application works as I need it to.

My problem is that when I select a project and date that are already created and I press the save button, it is duplicated. How can I do so that, if the project and date already exist, the collection is created with the existing data and how would I then save any modifications to the hours?

Thank you very much for your help, I have been trying to find a solution for hours with various methods but I can't.

[FLMike · Accepted solution]

Hi @Daniel20 

To do that, one way is to use a Patch with a Lookup to the record to patch, versus a Defaults(), which will always create another row.

You could do something like this around your Patch

LookUp or a Filter 
If you user Filter, use CountRows to validate if it was found
If you use LookUp, check ift the result is Blank.

I am using filter

If(CountRows(Filter(MyList, Criteria here)) = 0,
//Is New  
   Patch(MyList, Defaults(Mylist)
all like you have already
,
//Exists Already
  Patch(Mylist, LookUp(MyList, Criteria My List),
     {
       Field1: Etc
     }
  )
)

If I have helped you, I would really appreciate if you please Mark my answer as Resolved/Answered, and give it a thumbs up, so it can help others

Cheers

Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

[Daniel20]

Very kind and thank you for your support.

Set(varCarga; true );;
Set(varidPatch;Patch(TotalHorasHombre;If(IsBlank(varIDD1);Defaults(TotalHorasHombre);LookUp(TotalHorasHombre;varIDD1=ThisRecord.ID));{Proyecto:CBProy_1.Selected.Proyectos;Fecha:DatePicker2_1.SelectedDate;NTrabajadores:LblNTrab_1.Text;TrabajadoresTotal:LblNTrab_1.Text*If(Text(DatePicker2_1.SelectedDate;"dddd")="sábado";5,5;8,5)}).ID);;If(IsBlank(varIDD1);ForAll(Filter(colTareoTrab1;!(IsBlank(NTrabajadores))) As Tareo;Patch(TareoTrab;Defaults(TareoTrab);{ContrataPartida:Tareo.ContrataPartida;Proyecto:Tareo.Proyecto;Fecha:Tareo.Fecha;NTrabajadores:Tareo.NTrabajadores;IDTHH:varidPatch}));Patch(TareoTrab;colTareoTrab1));;Clear(colTareoTrab1);;Reset(CBProy_1);;Set(varCarga; false );;Navigate(ConsultaTrabajador)

this would be the final code. Can anything be improved?

Apologies for all the questions. One last:

To reduce the code, is it possible to replicate in the second part what was done in the first part?

[FLMike]

Hi @Daniel20 

There is another way and its closer to what you are asking.

In your Patch Question. What the below does, is (assuming you have a solid criteria for finding the right row), it essentially does an upsert.

if the RowId is NOT find in my lookup it passes in Blank and creates a new record, if it is found it passes in that value and does an update

Patch(myCollection/List,
  {
    RowId: LookUp(myCollection, something = something).RowId
    MyColumn: Value
  }
)

To be honest and wow brain explosion.. I've never seen or been asked can you add an If to decide to do a Patch Update or Patch Create

So not sure if it works or not, but the last way I just showed does.

[Daniel20]

A good night to you. I'm having a problem with the last formula I sent you. It happens that it does not save all the rows of the collection, it always stops saving one or two rows, what could be the reason for this?

[FLMike]

@Daniel20 

If you put the code in exactly as we talked, that means that the data does not match the criteria. 

We would need to review all the rows you expect it to create and don't just say it should, let's verify because it would if it was supposed too.

Let's pick this up tomorrow, but if you can give me an example of a Full Row of Data you thought should have been saved but wasn't.

[Daniel20]

an excellent night for you.

Set(varCarga; true );;Set(varidPatch;Patch(TotalHorasHombre;If(IsBlank(varIDD1);Defaults(TotalHorasHombre);LookUp(TotalHorasHombre;varIDD1=ThisRecord.ID));{Proyecto:CBProy_1.Selected.Proyectos;Fecha:DatePicker2_1.SelectedDate;NTrabajadores:LblNTrab_1.Text;TrabajadoresTotal:LblNTrab_1.Text*If(Text(DatePicker2_1.SelectedDate;"dddd")="sábado";5,5;8,5)}).ID);;UpdateIf(colTareoTrab1;Fecha=DatePicker2_1.SelectedDate&&Proyecto=CBProy_1.Selected.Proyectos;{IDTHH:varidPatch});;Collect(colPrueba;Patch(TareoTrab;Filter(colTareoTrab1;!(IsBlank(NTrabajadores)))));;Patch(TareoTrab;colPrueba);;Set(varCarga; false );;

Doing it in parts if it can be stored properly.

First I get an ID with a variable. I then update the collection by saving this ID in a column. Next, I create a new collection by filtering the blank rows and finally I make a patch to the created collection.

It works but I don't think it's the right way to do it.

Como lo estaba haciendo anteriormente era asi:

ForAll(Filter(colTareoTrab1;!(IsBlank(NTrabajadores))) As Tareo;Patch(TareoTrab;Defaults(TareoTrab);{ContrataPartida:Tareo.ContrataPartida;Contratista:Tareo.Contratista;Proyecto:Tareo.Proyecto;Fecha:Tareo.Fecha;NTrabajadores:Tareo.NTrabajadores;IDTHH:varidPatch}))

But in this way, for example, if I should save 29 rows, it only saves or 27, two rows. I don't understand why it doesn't save them. I realized that the numbers that are repeated are only saved once.

Estas son las filas que deberia guardar:

Thank you very much for the importance and support you give me.