Add/Delete/Update table based on the items in collection

[MaryamSh]

Hi,

I started learning Power App and so far am able to collect user data and add them to table 🙂 . Now, I need to learn the Update feature.

In my project I have a search page that user can look for an employee and click on Edit link to get redirected to another page. The collected rows are kept in a collection (temp_items) and connected to a gallery. 

As you can see in above picture, user can add a new record to the temp_items collection (#1), delete an existing record (#2) or modify the existing record inside gallery(#3). Once all or any of the mentioned action is done, user click on update button and all the changes from temp_items collection should goes to the table. I don't know how to implement below tasks on button click:

1) if the row in collection is not in the table then "Add" it as new record

2)if the row was in collection but it's not anymore then "Delete" that record from the table.
3) if the row in collection exist in table then "Update" the record

p.s. I know how o delete the row from collection by using Remove function or update the row using Patch, I don't knw how to make the changes to the table in Dataverse.

That would be great if anyone can help me on this issue.

Thanks.

[RandyHayes]

@MaryamSh 

This is all quite simple (I say that so that you keep it simple...there are a lot of "solutions" out there that overcomplicate this process)!

Here is what you need to focus on...the primary key!  Your records all have them....or not (new records)!

It is highly important that your collection for the gallery includes the primary keys of the records.

You also need to implement either another collection for deletes, or anther column in your collection to indicate a delete.

Now...one question that I have - what is the relationship of the gallery to the form above it?

You are mentioning that the user can click on "Update", but I am not seeing that in your picture.  Is Update where you submit the form?

I ask this because, if there is a relationship between the form record and the gallery records (there usually is a parent to child relationship), then you will need to have submitted any new record in your form prior to submitting the records in the gallery.  Otherwise there will be no valid relationship.

Can you provide more details on the above?

[MaryamSh]

Hi Randy,

Thank you for your reply. Please see my answer to your questions:

Your records all have them....or not (new records)! only existing records have the primary key not the new record. The table has a AutoNumber primary key, which will be generated once record is added to the table (not at collection level).

You also need to implement either another collection for deletes, or anther column in your collection to indicate a delete. So you mean once I am removing the record from this collection, it should get added to another collection (includes all the deleted record from current collection), correct?

Now...one question that I have - what is the relationship of the gallery to the form above it?

You are mentioning that the user can click on "Update", but I am not seeing that in your picture.  Is Update where you submit the form? The form is connected to the table (top section of the picture) only to do the validation for required field, but when user click Add button, I have below code:

Collect(
    tmp_CSDCalculator,
    {
        'From Date': DateValue4.SelectedDate,
        'To Date': DateValue3.SelectedDate,
        Notes: DataCardValue3.Text,
        Percentage: Value(DataCardValue4.Text),
        In: DataCardValue5.Selected.Value,
        NumberOfDays: If(
            !IsBlank(DateValue4.SelectedDate) && !IsBlank(DateValue3.SelectedDate),
            DateDiff(
                DateValue4.SelectedDate,
                DateValue3.SelectedDate
            ) + 1,
            0
        ),
        NumberOfWeeks: If(
            !IsBlank(DateValue4.SelectedDate) && !IsBlank(DateValue3.SelectedDate),
            Round(
                (DateDiff(
                    DateValue4.SelectedDate,
                    DateValue3.SelectedDate
                ) + 1) / 7,
                1
            ),
            0
        ),
        NumberOfDayToInclude:0,
        'Employee ID': varEmployeeID
    }
);

And, the gallery is connected to the tmp_CSDCalculator collection. The Update button is below the gallery and has some code as below which is not correct and need to be change (the code works perfect in my Create page and is not right for Edit page):

ForAll(
    tmp_CSDCalculator,
    Patch(
        'CSD Calculators',
        Defaults('CSD Calculators'),
        {
            'From Date': ThisRecord.'From Date',
            'To Date': ThisRecord.'To Date',
            Notes:ThisRecord.Notes,
            Percentage: ThisRecord.Percentage,
            In: ThisRecord.In,
            NumberOfDays:Value(txt_NumberOfDays.Text),
            NumberOfWeeks:Value(txt_NumberOfWeeks.Text),
            NumberOfDayToInclude:Value(txt_DaysToInclude.Text),
            'Employee ID':varEmployeeID
        }
    )
);

Please let me know if you need more detail.

Thanks 🙏

[RandyHayes]

@MaryamSh 

Okay...going through this a little more detailed...you don't need to do all the work you are doing.  It is a waste of effort.  Your Gallery is already going to have the information you need in it...use it!

What I am understanding from your formulas is that you are NOT using the form to edit a single record.  Instead your use of the form is for input only.

In other words...it appears you do NOT have a parent-child relationship in your data.  Instead you have one list 'CSD Calculators' and you are storing X number of records with repetitive information in each record.

Is this assumption correct or am I missing something?

And if it is correct...can you not have a Parent-Child relationship for your data?  It would make things much simpler! 

[MaryamSh]

As you mentioned I am using the form only for input. There is a Parent/Child relationship in my app, all the X number of records belong to an employee. Basically, user can search for an employee (Parent table) and when they click on Edit they will be able to add/edit/delete records (child table) for that employee.

[RandyHayes]

@MaryamSh 

Sorry for the delay on this - I had it open in a browser window to reply and it got buried under quite a few!

Are you still struggling on this one?

[RJM07]

Gutted that this thread was never finished, it's exactly what I was after... @RandyHayes ?