cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JasonBrown2K13
Level: Powered On

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

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! 

6 REPLIES 6
JasonBrown2K13
Level: Powered On

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

Anybody able to help me with this basic query? 🙂

 

Thanks!

Community Support Team
Community Support Team

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

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

Community Support Team _ Mona Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
JasonBrown2K13
Level: Powered On

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

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
Level: Powered On

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

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
Level: Powered On

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

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

Super User
Super User

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

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. 

Shane - Microsoft MVP, YouTube, and PowerApps Consulting for when you are in a bind to get this fixed quickly. And finally we now have PowerApps Training

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,587)