How to connect gallery control items to excel at onedrive and issue of multiplicating collection rows

[freidav1984]

Dear All,

I am new to powerapps, I am currently learning how to create grid-like control screens from the following video .

The video helped a lot in understanding the basics (and more) of building a canvas app. But I am facing the following challenges, I hope that you could help me out. 

Currently, my app looks like the following, I use a gallery control:

I have the following fields in the gallery:

Job_ID - Text input: 5 digit, sequential number

Partner & Location & Room - Dropdowns: These are chosen from the Partner master data screen (If a Partner is chosen, only the Locations connecting to the selected partner are shown in the location dropdown, and so on with the Room)

The partner master data screen looks like the following:

Date of Job - Date Picker field

Price - Text Input: Based on the chosen Partner - Location - Room dropdowns, it is a lookup value, which returns the price of the cleaning job of the selected room

Comment - Text input field: Any comment can be added

Zusatreinigung & Wasche Service Toggle controls: If these are set to yes, the following Hours items become editable

Zusatreinigung Stunden @ Wasche Service Stunden Text input fields, if the toggle controls are set to "Yes", number has to be added (Range: 1-24 (integer))

Zusatreinigung Price @ Wasche Service Price: The above mentioned Hours items multiplied by 20.

Total Price - Text Input: Summarizing the amounts of the price items

Completed toggle: Yes or No

All of the above mentioned data and their changes should be sent and stored to a datatable.

I have 2 issues:

1. 

Because I could not connect and refresh the data from my app to an excel file, which is located on OneDrive, I created a dataverse table and connected it to my App. Could you help me, how to connect the data to an excel file at onedrive? I was not able to generate an unique identifier...

I collect every data change made by the user in a collection, and after hitting the save button the collected values will be sent to the dataverse table. I would like to achieve the same with the excel file at onedrive. 

My excel looks like the following:

At the App's OnStart attribute, I use the formula below

ClearCollect(
    JobUpdates;
    ShowColumns( 
         Table(Defaults(TESZT_Cleaning_Job)); 
        "cr0f6_job_id";
        "cr0f6_partner";
        "cr0f6_location";
        "cr0f6_room";
        "cr0f6_price";
        "cr0f6_datetimeofcleaningjob";
        "cr0f6_completed";
        "cr0f6_teszt_cleaning_jobid";
        "cr0f6_commentoncleaningjob";
        "cr0f6_zusatzreinigung";
        "cr0f6_wascheservice";
        "cr0f6_zusatzreinigungstunden";
        "cr0f6_wascheservicestunden";
        "cr0f6_zusatzreinigungprice";
        "cr0f6_wascheserviceprice"
        ));;
Clear(JobUpdates)

 And the gallery control item's OnChange looks like this

If(
    ThisItem.cr0f6_teszt_cleaning_jobid in JobUpdates.cr0f6_teszt_cleaning_jobid;
    Update(
        JobUpdates;
        LookUp(
            JobUpdates;
            cr0f6_teszt_cleaning_jobid = ThisItem.cr0f6_teszt_cleaning_jobid
        );
        {
            cr0f6_teszt_cleaning_jobid: ThisItem.cr0f6_teszt_cleaning_jobid;
            cr0f6_job_id: TextInput1.Text;
            cr0f6_partner: Dropdown2.Selected.Result;
            cr0f6_location: Dropdown2_1.Selected.Result;
            cr0f6_room: Dropdown2_2.Selected.Room;
            cr0f6_price: Value(TextInput1_1.Text);
            cr0f6_datetimeofcleaningjob: DatePicker1.SelectedDate;
            cr0f6_commentoncleaningjob: TextInput1_2.Text;
            cr0f6_zusatzreinigung:If(Toggle1_1.Value; 'Zusatzreinigung (TESZT_Cleaning_Job)'.'1'; 'Zusatzreinigung (TESZT_Cleaning_Job)'.'0');
            cr0f6_wascheservice:If(Toggle1_2.Value; 'Wasche Service (TESZT_Cleaning_Job)'.'1'; 'Wasche Service (TESZT_Cleaning_Job)'.'0');
            cr0f6_zusatzreinigungstunden: Value(TextInput2.Text);
            cr0f6_wascheservicestunden: Value(TextInput2_1.Text);
            cr0f6_zusatzreinigungprice: Value(TextInput1_3.Text);
            cr0f6_wascheserviceprice: Value(TextInput1_4.Text);
            cr0f6_completed:If(Toggle1.Value; 'Completed (TESZT_Cleaning_Job)'.'1'; 'Completed (TESZT_Cleaning_Job)'.'0')
        }
    );
    Collect(
        JobUpdates;
        {
            cr0f6_teszt_cleaning_jobid: ThisItem.cr0f6_teszt_cleaning_jobid;
            cr0f6_job_id: TextInput1.Text;
            cr0f6_partner: Dropdown2.Selected.Result;
            cr0f6_location: Dropdown2_1.Selected.Result;
            cr0f6_room: Dropdown2_2.Selected.Room;
            cr0f6_price: Value(TextInput1_1.Text);
            cr0f6_datetimeofcleaningjob: DatePicker1.SelectedDate;
            cr0f6_commentoncleaningjob: TextInput1_2.Text;
            cr0f6_zusatzreinigung:If(Toggle1_1.Value; 'Zusatzreinigung (TESZT_Cleaning_Job)'.'1'; 'Zusatzreinigung (TESZT_Cleaning_Job)'.'0');
            cr0f6_wascheservice:If(Toggle1_2.Value; 'Wasche Service (TESZT_Cleaning_Job)'.'1'; 'Wasche Service (TESZT_Cleaning_Job)'.'0');
            cr0f6_zusatzreinigungstunden: Value(TextInput2.Text);
            cr0f6_wascheservicestunden: Value(TextInput2_1.Text);
            cr0f6_zusatzreinigungprice: Value(TextInput1_3.Text);
            cr0f6_wascheserviceprice: Value(TextInput1_4.Text);
            cr0f6_completed:If(Toggle1.Value; 'Completed (TESZT_Cleaning_Job)'.'1'; 'Completed (TESZT_Cleaning_Job)'.'0')
        }
    )
)

This works (almost) fine for the dataverse table, but the second issue:

2. After I hit save / or add a new row, the record which have changing values are duplicated in my collection with the same ID. See below the 00174 job_ID row has two records. And if I change the one of the values of this item, only the first record will be changed and this causes problems when running the app.

I have no idea why is this happening. The OnSelect of Addrow and Save button is the following.

//AddRow
Select(Button2);;
Patch(
    TESZT_Cleaning_Job;
    Defaults(TESZT_Cleaning_Job);
    {
        Partner: "";
        Location: "";
        Room: "";
        'Date & Time of cleaning job': Today()
    }
);;
Clear(JobUpdates)

//Save
If(
    CountRows(JobUpdates) > 0;
    Patch(
        TESZT_Cleaning_Job;
        JobUpdates
    );
    Notify(
        "Success";
        NotificationType.Success
    )
);;
Clear(JobUpdates)

Any help would be greatly appreciated!

David

[KBilakanti1]

@freidav1984 - You can achieve the using patch function. I Hope the excel file which you showed in the above screenshot is a table.

Patch(
    Table1,
    Defaults(Table1),
    {
        ID: TextInput4.Text,
        Name: TextInput4_1.Text,
        Address: TextInput4_2.Text
    }
)

[KBilakanti1 · Accepted solution]

@freidav1984 - You can achieve the using patch function. I Hope the excel file which you showed in the above screenshot is a table.

Patch(
    Table1,
    Defaults(Table1),
    {
        ID: TextInput4.Text,
        Name: TextInput4_1.Text,
        Address: TextInput4_2.Text
    }
)