Edit form- dynamic input

[Anonymous]

I created an app that is used to input employees' details, and it's using online excel. I want to add a field of industry and years of experience of my employees. So my problem is that what input option can I use? As some of them worked in 1 industry only and some of them worked in multiples industries, and I have to input the years of experience they have in their particular industry as well.

I'm currently using a combo box where it contains a list of industry name, however, I'm struggling to find a way to include years of experience for a particular industry. 

For the data structure, currently, I have 'industry' and 'year of experience' column to store the value, it can be altered according to needs. 

Does anyone have any suggestions that I can use to fulfill this need? Thank you.

[Drrickryp]

Hi @Anonymous 

You are describing a many to many relationship between Employees and Industries. For that you will need a junction table. Please review my post https://powerusers.microsoft.com/t5/News-Announcements/Relational-Database-Principles-and-PowerApps-Step-3-Keys-and/ba-p/188640 about how to set this up.  The article after that one gives an illustration of how to set up the screens in PowerApps.  The junction table will need lookups to the ID of the Employee table and the ID of the Industry table.  In the PowerApps edit form, these would be dropdown controls that display the text of the Employee and Industry respectively but store the value of the ID in the junction table. The yearsOfExperience would be a numeric column in the junction table as well.  

If you have the time, the entire series starts with the overview https://powerusers.microsoft.com/t5/News-Announcements/Database-Design-Fundamentals-and-PowerApps-An-Overview/ba-p/184485 

[v-yutliu-msft · Accepted solution]

Hi @Anonymous ,

Do you want to have a table with 'industry' and 'year of experience' column and one user may have multiple industries?

If so, I've made a similar test for your reference:

1)The result is like this:

2)my data source table(named test) has these fields: industry,'year of experience',name

3)insert a textinput for entering user name(TextInput8)

  insert a combo box for choosing industries(ComboBox2)

  insert a gallery for displaying the selected industries(Gallery4)

  insert a textinput inside gallery4 for entering year of experience(TextInput7)

 insert a button to update the data

The app looks like this:

4)the controls' settings:

TextInput8's HintText: "username"

ComboBox2's Items: ["HR","IT","Marketing","teacher","business"]

Gallery4's Items: ComboBox2.SelectedItems

the submit button's OnSelect:

ForAll(Gallery4.AllItems,Collect(userdata,{industry:Value,'year of experience':Value(TextInput7.Text),name:TextInput8.Text}));
Collect(test,userdata);
Clear(userdata)

Then you will update multiple records of industries information based one the industries that you select.

Best regards,

[Anonymous]

Hi, 

This looks exactly what I need, however, how can I update it to my excel file? Im currently using edit form to update my excel file (as I have other information as well).

Thank you.

[v-yutliu-msft · Accepted solution]

Hi @Anonymous ,

Same principle.

In my test, my data source name test. You just need to replace test with your Excel table name.

Do you want to update all the data in an edit form?

I suggest you not do this.

Because the fields in edit form could not change dynamically based on the selection of industries. You need to manually change it when you design the app.

Here's my advice:

Do not use edit form to update data.

Use some controls to update. The gallery's Item could change dynamically based on the selection that you select, just like what I posted above.

You just need to :

1)add more textinputs to fill in other fields data, like email,phone,....

2)make a little change on the submit formula:

ForAll(Gallery4.AllItems,Collect(userdata,{
                                           industry:Value,
                                           'year of experience':Value(TextInput7.Text),
                                            name:TextInput8.Text,
                                            email:TextInput9.Text
                                            phone:TextInput10.Text,...

                                             }));
Collect(yourexceltablename,userdata);
//make sure that the field names in excel are same as those in userdata collection
Clear(userdata)

Best regards,