cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mpowis
Helper I
Helper I

adding collection data to sharepoint list from a form

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

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User III
Super User III

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.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

9 REPLIES 9
Drrickryp
Super User II
Super User II

@Mpowis 

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

RandyHayes
Super User III
Super User III

@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?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
Mpowis
Helper I
Helper I

Hi @RandyHayes ,

i created a custom form:

PowerApp Booking 3.png

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
Helper I
Helper I

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.

 

PowerApp Booking 4.png

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
Super User III
Super User III

@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?

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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
Super User III
Super User III

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.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

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:

PowerApp Booking 4 select code.png

 

This is the Submit button:

PowerApp Booking 4 submit code.png

 

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

PowerApp Booking 4 booking list.png

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

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.

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,401)