Submit data from controls to connected Excel or CDS form/database

[JasonBrown2K13]

Hi all,

Probably a really basic question but I can't seem to find a solution!

In my (new blank) app I have three controls:

- a Text Input Box called 'NameInput'

- a Drop Down Box called 'JobSelect'

- a Star Rating called 'JobSatisfaction'

And I also have a button that the user presses to submit the data.

I want the data in these fields to be submitted as a new record to a connected Excel table or a CDS database. 

My guess was the formula syntax would probably be something like: 

Submit(NameInput, JobSelect, JobSatisfaction(ConnectedFormNameHere)) 

However this isn't the case.

This is just a basic app I am making to help me prepare for a much bigger project I have planned. Any help would be really gratefully appreciated! 

[JasonBrown2K13]

Anybody able to help me with this basic query? 🙂

Thanks!

[v-monli-msft]

Hi @JasonBrown2K13,

To do this, I suggest that you put the fields in data cards in an EditForm, then use the SubmitForm function to update the record to your data source.

You could try to first creat your Excel file with your required columns and let PowerApps create the app using New > OneDrive etc. > Choose the excel table, and see how this app works with the data.

Regards,

Mona

[JasonBrown2K13]

Hi @v-monli-msft, thanks so much for your reply!

This works well if you want to edit the first record in a table, but I was wondering if it's possible to be able to:

- Edit another record in the table from the app (not just the first - maybe by using the datasheet view the user could click on a record they want to edit and then edit options would appear? Could I just make another screen with an edit form on it that hyperlinks to the individual record in the datasheet view perhaps?)

- Be able to select what data to add to the record from a drop-down list. I know you can bind drop-down lists to CDS or Excel tables and the records in the table appear as items in the drop-down list, but could I allow users to select data from a drop-down list to add to a record? It's just so that incorrect data doesn't accidentally get added.

- Add another record to the end of the table - must be a way of doing this from the app?

As you might be able to tell, I'm trying to migrate from Access Web Apps which are being deprecated as we speak. 😉

Thanks!

Jason

[JasonBrown2K13]

Hi @v-monli-msft, thanks so much for your response!

This works great for alterting the first record in the table, but I wonder if it's possible to:

- Edit other records in the table - not just the first

- Add additional records to the end of the table

- Be able to use drop-downs and suchlike to add information to records so that users have pre-set options to choose from to prevent accidentally entering incorrect data. I know it's possible to make drop-downs and bind the data in them to an Excel or CDS table meaning that the records in the table appear as the items in the drop-down list, it would be great to be able to select one of these options to input into the table. 

I'm trying to migrate from Access Web Apps which are being deprecated now, so any help is gratefully received!

Thanks so much!

Jason 

[MickThornton]

I can't take credit for the answer but I can share this video link that solved a simliar problem for me.  I am connecting to MS SQL.

https://www.youtube.com/watch?v=fqH1U0_TanI

[Shanescows]

Thanks @MickThornton

Happy to help if the video doesn't clear things up. 

@JasonBrown2K13 what I would recommend is starting with a fresh app from template and then look at how it is wired up. The challenge you have with the Form control is you have to set the form. So NewForm(YourForm) would let you create a new record. EditForm(YourForm) would let you edit the record that you have specified in the Item property of the form. So in the template app they have a gallery. You click on an item and are taken to the form. The form Item property is Gallery1.Selected.

If you want to do things with Controls and not forms (which is how I like to roll also) then your Submit function doesn't work. To create a new record you would change that line to be more like Patch(DataSource, Defaults(DataSource), {Name: NameInput.Text, Job: JobSelect.Selected.Value, Satisfaction: JobSatisifaction.Rating})

Either way is fine. Just what fits your solution better.