New to Powerapps - Need direction!

[msjs79]

Hello Everyone! Newbie here and  I'm in the process of creating a powerapp and am trying to see the best way to collect the data using forms, collections, etc. I have a SharePoint list with the following fields :

Title, Account, ID, Name, Term (Fall, Spring or Summer), Amount

I have a form with a collection as I need to be able to enter multiple rows for the common fields. not sure if that's the direction to go.

Title: 2020    Account:23800

ID       Name      Fall      Spring       Summer

123     ABC          200        20                0

456     KL               0          0                200

I need the data entered in the above format. I want them as separate entries in the list as they will each need approval.

I tried with a form and collection which had a dropdown for the term, it works, but I want to be able to collect data as entered above where all the terms are entered as shown above.

I tried nested collections but was lost in the process.

Any direction would be appreciated.TIA

[Digital]

Hi @msjs79 

Shane Young's series on repeating tables might be just the ticket.

https://youtu.be/xgznk4XlPCo 

[msjs79]

Yes, I started off with that video. But what I am stuck at is sending the Fall , Spring and Summer amounts entered to the SharePoint list as different terms and separate entries.

[msjs79]

My SharePoint List looks like this:

Title       Account      ID            Name      Term       Amount

2020        23800      123            ABC          Fall            200

2020        23800       123           ABC          Spring        20

2020        23800       456           KL            Summer      200

My app looks like this:

Ttile :2020         Account: 23800

ID       Name      Fall      Spring       Summer

123     ABC          200        20                0

456     KL               0          0                200

[v-yutliu-msft · Accepted solution]

Hi @msjs79 ,

Do you want to use table1 to update table2?

Table1:

Title :2020         Account: 23800

ID       Name      Fall      Spring       Summer

123     ABC          200        20                0

456     KL               0          0                200

Table2:

Title       Account      ID            Name      Term       Amount

2020        23800      123            ABC          Fall            200

2020        23800       123           ABC          Spring        20

2020        23800       456           KL            Summer      200

Could you tell me the fields' data type in table1 and table2?

I assume that:

1)in table1: Title(text) ,Account(text),ID(number),Name(text),Fall(number),Spring(number),Summer(number)

2)in table2:Title(text)       Account(text)      ID(number)            Name(text)      Term(single choice)       Amount(number)

I've made a similar test for your reference:

1)reshape the collection:

I assume that data in table1 is in collection named test, textinput for Title named TextInput1,textinput for Account named TextInput2

ClearCollect(test1,AddColumns(test,"Title",TextInput1.Text,"Account", TextInput2.Text));
//add data of Title and Account to the collection

ClearCollect(test2,
               Filter(
                  AddColumns(
                       Ungroup(
                           AddColumns(
                                      AddColumns(test1,
                                                "choicetest",
                                                "Fall:"&Text(Fall)&";Spring:"&Text(Spring)&
                                                                    ";Summer:"&Text(Summer)
                                                ),
                                      "choicetest2",
                                      Split(choicetest,";")
                                      ),
                           "choicetest2"
                              ),
                       "choicetest3",
                       First(Split(Result,":")).Result,
                       "Amount",
                       Last(Split(Result,":")).Result
                       ),
                 Not(Amount="0")
                )
         )

2)use reshaped collection to update list, my list named survey

ForAll(test2,
       Patch(survey,Defaults(survey),
                    {Title:test2[@Title],
                     Account:test2[@Account],
                     ID:test2[@ID],
                     Name:test2[@Name],
                     Term:{Value:choicetest3},
                     Amount:Value(test2[@Amount])
                    }
              )
        )

Then you could use table1 to update table2.

Best regards,

[msjs79]

Thanks @v-yutliu-msft 

Still very new to Power Apps and trying to figure out how to setup what you mentioned above. Not sure on how to setup the table with collections.

[msjs79]

Hello @v-yutliu-msft ! So I don't think I understand correctly.

I created a collection called test with columns as described by you in table 1.  I added some entries(data).

I added a button to create a collection called test1 but has an error that says column name already exists : ClearCollect(test1,AddColumns(test,"Title",TextInput1.Text, "Account",TextInput2.Text))

[msjs79]

Hello @v-yutliu-msft 

Took some time to read through and understand it. Finally got it to work. Absolutely brilliant !!!Thanks a million!