Update gallery to sql

[jjasper]

Hello, 

I have a sql table that I am using to store data in. I have created an app that pulls that data in from the sql table but now I am struggling with how to submit the data back to the database. I think I have gotten myself all confused on what to do from reading too many articles. I am looking to update fields in the database for a specific indicator. 

I do have one things that I need when it comes to sending the data back to the database. Currently on my OnSelect for each textbox I have that if CollectionLevel is equal to Neighbourhood then navigate to the neighbourhood screen. I need that to stay and I think that is where is I am getting confused. I know I need a collection and I know I need to use to patch but as to how those work together and how I achieve what I am looking to do is where I am getting myself all confused. 

If someone could please explain it to me and give me a good example/tutorial that would be great. 

TIA

[v-yuxima-msft]

Hi @jjasper ,

Could you share more details about your app?

What is CollectionLevel and Neighbourhood?

Do you want to save your gallery’s data to your sql table?

Could you share more about your gallery ?What are the controls in your gallery?

I test as next:

I add the textinput control into the gallery and then test with one button,

Button1.OnSelect: ForAll( Gallery1.AllItems,

          Patch(

                 '[dbo].[Table_20190306test]',

                     Defaults('[dbo].[Table_20190306test]'),

                    {  ID:Value(TextInput3_ID.Text),Name:TextInput4_Name.Text}

          ))                  ('[dbo].[Table_20190306test]' is my sql table name you can change to yours on your side)

Reference case:

https://powerusers.microsoft.com/t5/General-Discussion/Update-gallery-to-sql/m-p/244799#M71817

Hope this can be helpful.

Best Regards.

Yumia

[jjasper]

Hello, 

Please see attached for what my gallery looks like. 

When a user clicks on a text box it will allow them enter a numbe or it will bring them to another screen where they will enter numbers at a more drilled down level. The CollectionLevel will determine if the user can enter a number or if it will bring them to another screen. 

Here is the code that I have for this:

If(CollectionLevel = "Neighbourhood", Navigate('RH Neighbourhood',ScreenTransition.None), "") and I currently have that in the OnSelect. 

I would like that each month will update the database with the numbers they have entered. I was hoping to avoid doing this a button but if is not possible I will add a button to submit the data from the gallery to sql. I would like to save my gallery data to sql (I have already created all the records I am just looking to up date the records via month).  There is a label at the top of the gallery that shows the indicator that they are updating. 

I am just looking for a way to update the database based on the conditions that have been set out by the app with filters and lookups with the numbers that are entered. 

Hope this helps. 

TIA

[v-yuxima-msft]

Hi @jjasper ,

Also the same question,what is CollectionLevel and Neighbourhood?

Are they table name or what?

Could you share more details about this?

What is your database's structure?

Best Regards.

Yumia

[jjasper]

CollectionLevel and Neighbourhood are column in the database that help to filter. The way my table is set up is there is a Village which has indicators which have collection levels. When the collection level is equal to neighbourhood then screen will flip to the neighbourhood screen which is attached to the neighbourhood table. The neighbourhood table has a village which has a neighbourhood which has an indicator. Both tables have Months from Jan - Dec in columns. Because we collect data at two different levels I need to have two tables and I need to have the screen drilldown to neighbourhood when needed. We collect data for each indicator either at a village level or a neighbourhood level across the 12 months of the year. 

I am going to attempt to do what I want with flow but just thought there might another way to send it to sql. 

TIA