Fill textboxes from combobox and save info in a different sheet

[Anonymous]

Hey guys!

Totally newbie here, I was doing a macro in Excel when I thought that maybe I could do the same as an app, still learning thou. So what I want to create is a form whith multiple fields as you see in the attached image. Data is coming from an excel file in onedrive. I have 2 different sheets each one with a table. "Bds" for the names of database and "Registros" will be the one in which the data is gonna be saved.

So the thing is that I want to populate the database name based on the category you chose in the dropdown, which I already populated. So I want to bring the name from the table "Bds" but as you can see in the table, each name is in a specific row different column( base de datos1, base de datos 2, and so on until 8).

Also, I want to bring the latest data(which I think I can make it with vb to always sort from the newest on that table) of that database name from "Registros", that will be for Ticket No, comment and last update.

At the end I want to save and send everything to the Registros Sheet/table, but as you can see there are 8 rows of data that are going to be sent to the table, so with the next record, 8 new rows are going to be added and so on.

Is it possible, is it too much? Sorry for the long post, I'm still learning.

Any advice is appreciated, even if it is to go back to excel haha.

Thank you guys!

[WarrenBelz]

Hi @Anonymous ,

I am not completely clear on your requirements, so I will paste them below with questions/answers

So the thing is that I want to populate the database name based on the category you chose in the dropdown, which I already populated. So I want to bring the name from the table "Bds" but as you can see in the table, each name is in a specific row different column( base de datos1, base de datos 2, and so on until 8).

If what I am seeing on the screenshot is an edit form with the first record selected and all the other columns in fields on the for, then submitting the form SubmitForm(YourFormName) will do this but I have feeling that this is not the case. What is the DataSource and Item of this form and how do you navigate to it (choose from a gallery?)

Also, I want to bring the latest data(which I think I can make it with vb to always sort from the newest on that table) of that database name from "Registros", that will be for Ticket No, comment and last update.

You cannot use VB in PowerApps, but can achieve much the same functionality with the PowerApps language. You can use Lookup to get data from another table, but I need to understand what you want to bring in and when/how

At the end I want to save and send everything to the Registros Sheet/table, but as you can see there are 8 rows of data that are going to be sent to the table, so with the next record, 8 new rows are going to be added and so on.

What is your question here? You can either add or edit records and do you want to add 8 rows at once?

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.

[Anonymous]

Hi @WarrenBelz!

Sure thing, my bad for not clarifying things, I did not use a form as such, I created every piece one by one. In the first screen I created a button that sends you to a second screen which will be the "form" to say so. (I think I'm doing the same as in VBA, that might be a mistake)

After I created every field and input I started thinking about the fill and autofill. So I added the connection, data comes from an Excel sheet on Onedrive. It has 2 tables. "Bds" for the names of the Databases and "Registros" Which will be the one where the data is going to be saved.

On the App Screen screenshot, nothing has a formula, I'm starting, the only one that I managed to populate is the Dropdownlist, which is populated from the table "Bds" Column Categorias. What I need is that, after the user selects a category from the dropdownlist the database name textboxes need to be filled with that row info from "Bds" Then, After the database name is autofilled I need to bring(if any) the ticket value and comment text from the "registros" table, based on the corresponding databse name. And that Also applies for the late update date, I should say that there will be repeated values, so it is important to bring always the latest value based on the date, After the user finishes filling, I need to send all information to that "Registros table" There are 8 database name and info, so 8 new rows of information will be added after the use the save button.

I think that would do it for this part of the project, do you think it can be done? Is it too much trouble?

[WarrenBelz]

Thanks @Anonymous ,

Before I delve further into this, you have said I did not use a form as such - so all your controls on the screen are simply placed there and not connected to any data field in Excel? Are you wanting to populate the excel sheet in your screenshot with one record at a time or some sort of bulk update?

[Anonymous]

Hi @WarrenBelz !

Exactly, I started as I usually start in VB(which might have been wrong) I made the canvas to say so, and then start connecting, I gave it a try at the forms option, but I can only have one row of data or so I see, and I need to update 8 rows of data. That is why I created the layout and then try to figure it out how to connect it. It will be a type of Bulk update in which all 8 rows of data will be sent to the sheet.

Is it possible? Or should I go back and figure it out with forms? I have seen that maybe is possible to have 8 mini forms one for each row of data, I would just need to autopopulate them based on the category and name of the database.

[WarrenBelz · Accepted solution]

Hi @Anonymous ,

I will point you in the right direction with the ForAll function - you can run this through a Collection function which will bulk update rows.

Have a look at these - happy to help with syntax.

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.

[Anonymous]

Hi @WarrenBelz 

Thank you so much! this seems to be the one that should do the trick, I will appreciate the help with the formulas, lets start with the collection and then the autopopulate, can you help me?

I renamed every input so it can be identified, first thing is that the date picker only shows the date, and I need the hour and minutes,  I followed the instructions here: Add time to date picker but I cannot do like, group the three things and do the formula on Update:

DateValue1.SelectedDate + Time(Value(HourDropdown.Selected.Value), Value(MinutesDropdown.Selected.Value), 0)

 Second thing is that I added the save button, but the collect seems to have an error, although every input is written correctly; I do not know why.

Collect(Category.Selected.value,Date.value,Name.value,Bd1.value,Status1.Value,Ticket1.vaue,Coment1.value)

What am I doing wrong?

[WarrenBelz · Accepted solution]

Thanks @Anonymous ,

Firstly the date/time Update code you have provided is valid syntax

DateValue1.SelectedDate + 
Time(
   Value(HourDropdown.Selected.Value), 
   Value(MinutesDropdown.Selected.Value), 0
)

what is it that you are having problems with on the Update of the card?

The second one you need to give the collection a name and fields if collection off the screen controls (below are only examples of the syntax).

ClearCollect(
   colYourCollectionName, 
   {
      YourCategory:YourCategoryDropdown.Selected.Value,
      YourDate:YourDatePicker.SelectedDate,
      YourName: YourNameControl.Text,
      YourNumericValue: Value(YourNumberControl.Text)
   }
)

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.

[Anonymous]

Hi @WarrenBelz !

How can I turn it into a card? I have 3 three separate dropdowns, date, hour and minute, how can I turned them into one so I can change the update value with these and also add it to the collection formula?

I checked in the collections menu, but it seems there is a problem with the category dropdown and the slider. For the dropdown(category) it says the name is not valid, identifier not recognized. And for the slider (Estatus1) I think it is not sending the value.

Everything else is working! Thank you!

ClearCollect(
   Base1, 
   {
      Date : fecha1.SelectedDate,
      Name: Nombre.Text,
      Category: (Category.Selected.value),
      Base1: Bd1.Text,
      Status1: Estatus1,
      Ticket1: Value(Ticket1.Text),
      Comment: Coment1.Text
   }
)

[WarrenBelz]

HI @Anonymous ,

I assumed you have them on a card to update, however you would collect them as below.

ClearCollect(
   Base1, 
   {
      Date : 
      DateValue1.SelectedDate + 
      Time(
         Value(HourDropdown.Selected.Value), 
         Value(MinutesDropdown.Selected.Value), 0
      ),
      Name: Nombre.Text,
      Category: (Category.Selected.value),
      Base1: Bd1.Text,
      Status1: Estatus1,
      Ticket1: Value(Ticket1.Text),
      Comment: Coment1.Text
   }
)

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.