adding collection data to sharepoint list from a form

[Mpowis]

Hi,

I'm trying to write the function for the submit button to add a new record to a sharepoint list from a form.

the first part is straight forward the formula grabs all the data from the inputs for each column in the sharepoint list but i have 2 columns that need to be filled by a collection.

this formula works for the first part:

ForAll (Resource, Patch('IT Resource booking',Defaults('IT Resource booking'),{Class_Name: Class_In.Text, Staff_Name: Book_Staff_In, Booked_Out: Value(DateTime_In.Text)}))

 next i have two columns named Resource and Resource_code and i need to get this information from the collection i have created called Resource and with in the collection i need Title to = Resource and Resource to = Resource_Code in the sharepoint list and if more than one item is in the collection it needs to created a new record for each item in the collection using the same input details for the rest.

i have tried to reference the collection using Resource: [@Resource].Title but i get The type of this argument 'item' does not match the expected type 'Table'. Found type 'Error'.

another error message I get is - Name isn't valid. This identifier isn't recognized

I'm really lost now i have been working on this all day so I'm giving my brain a rest and hopefully in the morning one of you wonderful people will point me in the right direction...

thanks

Michael

[Drrickryp]

@Mpowis 

If you have designed your form correctly, the submit button should have only SubmitForm(formname) in it.

[RandyHayes]

@Mpowis 

Out of curiosity, you mention "from a form".  Are you using an EditForm?  If so, then this should all be done with the SubmitForm function.

If not, then can you describe these collections that you mention?  How are they created and what are the columns there and how would you lookup one of the records that you want from it?

And finally, what type of columns do you have in your SharePoint list that you are trying to update?  Are any of them Choice, Lookup or any other complexType?

[Mpowis]

Hi @RandyHayes ,

i created a custom form:

Gallery 1 allows the user to select a class and teacher name, once selected it appears next to the Class: and Teacher: labels.

Then the member of staff booking out the resource can type in their own name and then scan the barcodes on the resources, scanning the barcode validates against another sharepoint list and if valid a button named Select appears next to Scan Result:

When the resource is Selected it the appears in Gallery 2 as a collection and you can continue to book out resources before finally submitting the form.

(Class for example would book out several laptops)

The SharePoint list this information is being put in is called 'IT Resource Booking'

Its Columns are:

ID - Auto Fill

Class_Name - Text

Staff_Name - Text

Resource - Text

Resource_Code - Text

Booked_Out - Yes / No

Booked_In - Yes / No

Ultimately the form would be filled in like:

class = Maple

Staff = Jim Bob

<Collection>

Title = Red Laptop

Description = Laptop

Resource = R124367

Title = Blue Laptop

Description = Laptop

Resource = R287624

Title = Yellow Laptop

Description = Laptop

Resource = R263589

then in the sharepoint list displayed like this:

ID   Class_Name   Staff_Name  Resource             Resource_Code  Booked_Out  Booked_In

1     Maple            Jim Bob        Red Laptop         R124367             Yes                No

2     Maple            Jim Bob        Blue Laptop        R287624             Yes                No

3     Maple            Jim Bob        Yellow Laptop    R263589              Yes                No

The idea is when they are returned they can be scanned back in and update the Booked Out / In Columns           

[Mpowis]

Ok so a quick update this was a column issue on my SharePoint list i had set them up as choices and number rather than Text so once i corrected this the formula no longer errors out, shows what a good nights sleep can do to refresh the brain lol

but i still have an issue that when i submit the form it doesn't input all the information I'm asking for, its filling all the columns in like the example below except the Resource column

ID   Class_Name   Staff_Name  Resource             Resource_Code  Booked_Out  Booked_In

1     Maple            Jim Bob                                   R124367             Yes                No

2     Maple            Jim Bob                                   R287624             Yes                No

3     Maple            Jim Bob                                   R263589              Yes                No

Below is the code for the submit button and you can see the column is referenced Resource: and then the collection information is called Description.

in the above picture the collection references are:

Description = Sony Head Phones Red

Type = Other

Resource = R4905524942156

Submit Button Code:

orAll (Resource, Patch('IT Resource booking',Defaults('IT Resource booking'),{Class_Name: Class_In.Text, Staff_Name: Book_Staff_In.Text, Booked_Out: Value(DateTime_In.Text), Resource: Description , Resource_Code: Resource}))

[RandyHayes]

@Mpowis 
Yes, I would say there is an issue with this...you start out with a ForAll(Resource statement (which is not the correct use of a ForAll), but what is Resource?  Because, you then us it in the Patch (which you don't need) as a value for the Resource_Code column.  So, if you are ForAll'ing on a Table called Resource then I am assuming your Resource_Code column would be a table column...which it is not, it is text.

So, can you provide more information on the "Resource" that you show in the formula?

[Mpowis]

Hi Randy,

sorry really new at PowerApps so its being a steep learning curve for me, the Resource is the collection i create when a resource is selected.

I believe i have called to many things resource so its getting confusing but for the fact this maybe incorrect way of doing it its working, but i want to know how to do it correctly if you could advise a better method of doing this?

thanks

Michael

[RandyHayes · Accepted solution]

Collect('IT Resource booking',
    ForAll(Resource, 
        {Class_Name: Class_In.Text, 
         Staff_Name: Book_Staff_In.Text, 
         Booked_Out: Value(DateTime_In.Text), 
         Resource: Description, 
         Resource_Code: Resource
        }
    )
)

@Mpowis 

Well, in general, an EditForm is going to be your friend when it comes to doing these type of things.  But, since you have put the effort into what you have, getting that to work would be good.

As you're trying to learn, avoid using collections and variables as much as you can.  I know all the docs and examples and way too many videos and blogs use them, but they use them either to demonstrate a concept or just plan incorrectly.  Think Excel!  Which is what PowerApps is built around.  You can read more about that from the program manager for PowerApps and the formula builder.

But looking at some of the formulas you did provide, this is a functional equivalent to what you were trying to do, but better performing and easier to maintain.  Note, this is based completely on what you had before.

Collect('IT Resource booking',
    ForAll(Resource, 
        {Class_Name: Class_In.Text, 
         Staff_Name: Book_Staff_In.Text, 
         Booked_Out: Value(DateTime_In.Text), 
         Resource: Description, 
         Resource_Code: Resource
        }
    )
)

NOW, the better approach is to have the correct column names and values in your collection (again, if you are going to use a collection - some cases need it, but don't overuse).

So, when you are collecting the value, make sure the columns names you use are EXACTLY the same as in your datasource.

Collect(Resources,
  {Class_Name: Class_In.Text,  
   Staff_Name: Book_Staff_In.Text, 
   Booked_Out: Value(DateTime_In.Text), 
   Resource: Description.Text, 
   Resource_Code: Resource.Text
  }
)

Above assumes that all of the column names are just as the real names are in your datasource.

Then your submit formula is simply this:

Collect('IT Resource booking',  Resources)

All the rest will be done for you.

Hopefully that is helpful and clear.

[Mpowis]

Hi Randy,

thank you for this information i updated the code to the better practice you listed above, but it doesn't work, the first part you suggested for the collecting of the inputs works and creates the collection but the Submit button isn't submitting the data to IT Resource booking List?

This is the Select button:

This is the Submit button:

This is the SharePoint List the submit should send the data too:

the data in the list is from my earlier testing.

any idea why it won't submit the data? I checked the names were correct and value types (.Text)

The message I'm getting is the specified column 'Class_Name' does not exist. The column with the most similar name is 'Class_Name'.

thanks again Randy as i said i would like to learn the correct way to use formulas in my Apps

[Mpowis]

Hi Randy,

I did some more investigating and it turns out that PowerApps was searching for the Internal Column name and not the name I renamed it too, so Class_Name used to be Title, once I referenced Title instead it submitted it to the booking list.