Populate new list with existing data

[Anonymous]

Hi, I tried to find solutions to my questions all over, but I did not find any clear explanation.

So, here's the case - I'm trying to build a sort of ticketing system where I already have all elements prepared:
-List of available codes to choose from (around 1K records, in excel file, there is a column with code and another with code description, which is mandatory to show up)
-List of countries to choose from

What I have to do, is to assemble:

+Combo box where people can search either code or description (I know how to do this)
+Combo box for a list of countries (also know how to do this)
+Date picker with limitation to only future dates (also know how to do this)
+Checkbox if the ticket is urgent (nothing complicated here, right?)
+ Some kind of hidden fields to gather information of email and name of the submitter

and finally, and most importantly - gather all mentioned above and create a new record on a list or in excel. This I find the most difficult to understand. I saw many videos or posts on how to edit existing lists, but this is not the case. The case is actually to create entirely new records on a list/ in excel file based on all those elements gathered from other lists/excel.

[v-qiaqi-msft]

Hi @Anonymous,

Based on the issue that you mentioned, do you want to create new records that gathers information from another list/table to your main excel table or SP list?

Could you please tell me that if this relates to 2 data sources?

1. If you want to add the creators' email or full name, I think you should use the SharePoint list instead of the Excel table as your data source 
2. Does the data source on your side contains the columns to store the code, description, countries, future dates, urgent-check?
3. If you want to patch the existing record from another list/table to your aimed list/table?

First of all, if your app relates to several data source, you should connect all the data source to your app.

EDIT:

I reviewed it again, I think my previous thinking may be too complicated. Do you just want to patch the data of Combo Box, Checkbox and DatePicker to Excel table?

If so, please check the following formula.

Since you have the excel table as your data source, I think all the columns are General type which is Text type.

The key is that how you set the Items property of your Combo Box:

Patch(Table1,Defaults(Table1),{Code:ComboBox1.Selected.Code,Country:ComboBox2.Selected.Country,Date:Text(DatePicker1.SelectedDate,"dd/mm/yyyy",Check:If(Checkbox1.Value=true,"Yes","No")})

If you just want to patch the existing record from a list/table to another aimed list/table

1). connect your prepared list and aimed list to your app

2). add a Gallery1 and set the Items property as your datasource

3). set the OnSelect property of the Gallery as: 

Navigate(EditForm1)

4). add an Edit Form, set the DataSource as your prepared list name, and set the Item property as:

Gallery1.Selected​

5). add a button outside the Edit Form and set the OnSelect as:

Patch(YourAimedList,Default(YourAimedList),EditForm1.Updates)​

Note that once you click the gallery means that you select a record to patch to your aimed list.

Hope it could help you.

Regards,

Qi

[Anonymous]

Hi @v-qiaqi-msft, thank you for your answer! As I am very new to power apps, I don't really get it. I'm a little (well, maybe even more) confused with how it works. Please see attached screenshots of what I am trying to achieve. Where:

• Green arrows from DATA are source files
• Yellow arrows show what I need to gather
• The red one is where I have to put it after it is combined

Also, boxes with email, first, and last name should not be visible for the submitter, but I think this can be easily by setting up fort color, right?

Also, I don't have to put it on the SharePoint list as it is shown, but I have read that if I have formulas in my excel - this is the only way to gather data on the list first and then to transfer it to the excel file. But if there is a way to put gathered data directly in excel  -I would prefer to do it this way. 

[v-qiaqi-msft]

Hi @Anonymous,

Well, it is so complex for you patch like that.

Let me make it easy for you, we will solve all your problems one by one. Just take a deep breath.💪

Here is the preparation that you should do in your SharePoint list 'List_for_powerautomate'.

1. Make sure that you have correct column types in your 'List_for_powerautomate' to store the values. Since all the column from your excel table is general, I think you'd better create Sigle-line-text column to store code or description. Create a Date and time column to store the date. Create Sigle-line-text column to store Checkbox value
2. Since you want to patch the email and Full name of the submitter, I think you could create a Sigle-line-text or Person column to store them using the User().FullName or User().Email
3. I have mentioned that how you set the Items property of your Combo Box matters how you write the Patch formula, so I assume that you could refer to them as ComboBox.Selected.Value or ComboBox.Selected.Code

After that, you could write your Patch formula for the OnSelect property of the Button as below:

Patch('List_for_powerautomate',
      Defaults('List_for_powerautomate'),
              {Code:ComboBox1.Selected.Code,
               Country:ComboBox2.Selected.Country,
               Date:Text(DatePicker1.SelectedDate,"dd/mm/yyyy"),
               Check:If(Checkbox1.Value=true,"Yes","No"),
               UserFullName:User().FullName,
               UserEmai:User().Email
              }
      )

Note that you should connect the Office365Users to your app.

Check if it could help you.

Regards,

Qi

[Anonymous]

Hey, now I got this error:

Do I have to additionally set up my checkbox to give "Yes"/"No"? I can have True /False - I don't mind that... Or this error apply to something else?

[v-qiaqi-msft]

Hi @Anonymous,

If you follow my formula, there is no need to do this, just add a If statement as below to your formula:

Check:If(Checkbox1.Value=true,"Yes","No")

From the screenshot you provided, it seems that there is not a column named 'Check' in your data source, please check if the column name is matched to how you write in your formula. In my scenario, I have a text column named Check to store the check box value.

Regards,

Qi

[Anonymous]

Well, I do have such column called "Check" on my list_for_powerautomate (output), but not in any other source file... Should I create a separate table (input) and have another data source for that checkbox then? 

[Anonymous · Accepted solution]

Just for the record - I have solved this issue with replacing SharePoint list with excel file. For some reason the connection to list was not working as it should and now all is working properly.