Using dropdown menu to fill different fields in Excel

[Nesh]

Hi there!
I am a newbie for using Power apps,

and I really expect solution of my issue.

So, I have an excel database for my Power Apps application.

In my Excel table there are activity_1, activity_2, and activity_3 field column (actually until 29 activities--> that's why I use drop down),

and I want to use one drop down menu to fill those activity columns.
That means, when I select activity 1 in drop down menu, activity_1 field colum will be filled; when I choose activity 2 the activity_2 field will be filled.

How can I do that? Please help. Thank you!
 

[WarrenBelz]

Hi @Nesh ,

I have posted this article twice earlier today. Please have a read - this covers two levels. Happy to help with the third.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[Nesh]

Hi!

@WarrenBelz Thank you for your help,
I will be back to you as soon as possible after I've tried the reference solution in your article.

Best Regards.

[v-yutliu-msft]

Hi @Nesh ,

Could you tell me whether do you want to update these fields value to one record or multiple records?

1)if you want to update these fields value to one record 

In this situation, I suggest you use several drop downs to select different fields and multiple textinputs to fill in data.

Then combine them in a record to update.

For example: choose "activity_1" in drop down1, enter "data1" in textinput1; choose "activity_2" in drop down1, enter "data2" in textinput2,....

Then the updated record is:

{activity_1:data1,activity_2:data2,.....}

I've made a similar test for your reference:

insert a gallery,insert drop down and textinput in the gallery.  (choose the pen icon in the gallery then insert controls)

If you want to update 29 fields, then you set the gallery's Items: [1,2,3,4,5,6,7....,29]. Then the gallery will have 29 items.

In my test, I just use 3 fields as example, so I set the gallery's Items: 

[1,2,3]

Set the drop down's Items:

["activity_1","activity_2","activity_3",.....,"activity_29"]

insert a submit button outside the gallery.

set the button's OnSelect:

ForAll(Gallery1.AllItems,
       Patch(collection,First(collection),
             If(Dropdown2.Selected.Value="activity_1",{activity_1:TextInput2.Text},
                Dropdown2.Selected.Value="activity_2",{activity_2:TextInput2.Text},
                Dropdown2.Selected.Value="activity_3",{activity_3:TextInput2.Text},
                ...,
                Dropdown2.Selected.Value="activity_29",{activity_29:TextInput2.Text}
                )
            )
      );
Collect(exceltablename,collection)

Set the screen's OnVisible:

ClearCollect(collection,{activity_1:"",activity_2:"",activity_3:"",....,activity_29:""})

Then after you select field names in the drop downs, enter data in the textinputs, click the submit button, your excel file will be updated with a record.

2)if you want to update multiple records

Do you want to make one record has one field's value, others are blank value?

For example: choose "activity_1" in drop down1, enter "data1" in textinput1; choose "activity_2" in drop down1, enter "data2" in textinput2,....

Then the updated record is:

{activity_1:data1},{activity_2:data2},....

One record only has one field value.

I've made a similar test for your reference:

You could only use one drop down, one textinput and one button.

set the drop down's Items:

["activity_1","activity_2","activity_3",.....,"activity_29"]

set the submit button's OnSelect:

If(drop down1.Selected.Value="activity_1",Collect(exceltablename,{activity_1:textinput1.Text}),
   drop down1.Selected.Value="activity_2",Collect(exceltablename,{activity_2:textinput1.Text}),
   drop down1.Selected.Value="activity_3",Collect(exceltablename,{activity_3:textinput1.Text}),
   .....
  drop down1.Selected.Value="activity_29",Collect(exceltablename,{activity_29:textinput1.Text})
  );
Reset(drop down1);Reset(textinput1)

Then after you choose field name in the drop down, enter data in the textinput, click the button, you will update a record in your excel table(only one field value), then drop down and textinput will be reset.

You could choose and enter again , then click the button, you will update another record.

Best regards,

[Nesh]

Hi @v-yutliu-msft!

The second one almost approach my need.

How if in one dropdown I put the title of activity_1....activity_29,

and when I choose title activity_1, it will fill column activity_1 (activity_1 is the header) with the title of activity_1?

Next time when I choose the title of activity_2 in dropdown, it will fill column activity_2 with that title activity_2.

But I want to record data activity_1, activity_2, etc in one row with different column header for one name of person.

Is that possible?

[v-yutliu-msft · Accepted solution]

Hi @Nesh ,

Do you want to use the second way that I posted but update multiple fields to one record?

If so, you just need to make a little change.

I've made a similar test for your reference:

1)insert a drop down, a textinput and two buttons (one for next, one for submit).

If you want to continue choose fieldname and enter in filed value, you click "next" button.

If you want to submit the record with the data that you entered in, you click the "submit" button.

2)Set the screen's OnVisible:

ClearCollect(collection,{activity_1:"",activity_2:"",activity_3:"",activity_4:"",activity_5:"",activity_6:"",activity_7:"",activity_8:"",activity_9:"",activity_10:"",activity_11:"",activity_12:"",activity_13:"",activity_14:"",activity_15:"",activity_16:"",activity_17:"",activity_18:"",activity_19:"",activity_20:"",activity_21:"",activity_22:"",activity_23:"",activity_24:"",activity_25:"",activity_26:"",activity_27:"",activity_28:"",activity_29:""})

2)set the drop down's Items:

["activity_1","activity_2","activity_3","activity_4","activity_5","activity_6","activity_7","activity_8","activity_9","activity_10","activity_11","activity_12","activity_13","activity_14","activity_15","activity_16","activity_17","activity_18","activity_19","activity_20","activity_21","activity_22","activity_23","activity_24","activity_25","activity_26","activity_27","activity_28","activity_29"]

3)set the next button's OnSelect:

Patch(collection,First(collection),
             If(Dropdown1.Selected.Value="activity_1",{activity_1:TextInput1.Text},
                Dropdown1.Selected.Value="activity_2",{activity_2:TextInput1.Text},
                Dropdown1.Selected.Value="activity_3",{activity_3:TextInput1.Text},
                Dropdown1.Selected.Value="activity_4",{activity_4:TextInput1.Text},
                Dropdown1.Selected.Value="activity_5",{activity_5:TextInput1.Text},
                Dropdown1.Selected.Value="activity_6",{activity_6:TextInput1.Text},
                Dropdown1.Selected.Value="activity_7",{activity_7:TextInput1.Text},
                Dropdown1.Selected.Value="activity_8",{activity_8:TextInput1.Text},
                Dropdown1.Selected.Value="activity_9",{activity_9:TextInput1.Text},
                Dropdown1.Selected.Value="activity_10",{activity_10:TextInput1.Text},
                Dropdown1.Selected.Value="activity_11",{activity_11:TextInput1.Text},
                Dropdown1.Selected.Value="activity_12",{activity_12:TextInput1.Text},
                Dropdown1.Selected.Value="activity_13",{activity_13:TextInput1.Text},
                Dropdown1.Selected.Value="activity_14",{activity_14:TextInput1.Text},
                Dropdown1.Selected.Value="activity_15",{activity_15:TextInput1.Text},
                Dropdown1.Selected.Value="activity_16",{activity_16:TextInput1.Text},
                Dropdown1.Selected.Value="activity_17",{activity_17:TextInput1.Text},
                Dropdown1.Selected.Value="activity_18",{activity_18:TextInput1.Text},
                Dropdown1.Selected.Value="activity_19",{activity_19:TextInput1.Text},
                Dropdown1.Selected.Value="activity_20",{activity_20:TextInput1.Text},
                Dropdown1.Selected.Value="activity_21",{activity_21:TextInput1.Text},
                Dropdown1.Selected.Value="activity_22",{activity_22:TextInput1.Text},
                Dropdown1.Selected.Value="activity_23",{activity_23:TextInput1.Text},                
                Dropdown1.Selected.Value="activity_24",{activity_24:TextInput1.Text},
                Dropdown1.Selected.Value="activity_25",{activity_25:TextInput1.Text},                
                Dropdown1.Selected.Value="activity_26",{activity_26:TextInput1.Text},
                Dropdown1.Selected.Value="activity_27",{activity_27:TextInput1.Text},
                Dropdown1.Selected.Value="activity_28",{activity_28:TextInput1.Text},
                Dropdown1.Selected.Value="activity_29",{activity_29:TextInput1.Text}
                );
Reset(drop down1);Reset(textinput1)

4)set the submit button's OnSelect:

Collect(exceltable,collection);Reset(drop down1);Reset(textinput1)
//replace with your excel table name

 Then, firstly you choose field name in the drop down and enter data in the textinput. If you want to submit data click "submit"button.If you want to choose another field to enter, choose "Next" button.

Remember, every time you click the submit button, you will create a new record.

Best regards,

[Nesh]

@v-yutliu-msftOkay, thank you for your suggestion and solution! It helped me a lot to solve my issue.=)