cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Patch Input Data from Gallery to Excel File

Hey all!  I'm fairly new to PowerApps, but I've watched tons of videos and I've used Excel for years. 

 

I've created a Gallery that pulls the fields from an excel file on OneDrive.  I want users to be able to input the Qty into each text box and then when they hit the save button, it saves the values back to the excel file.  I've tried different iterations of Patch and ForAll and I can't seem to get it to work. 

 

Can anyone guide me in the right direction?  "SpringTbl" is the excel table name.  "2 - Qty" is the column of data I want to update.

 

Screen Shot 2019-08-23 at 4.29.18 PM.png

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Patch Input Data from Gallery to Excel File

Hey @Anonymous, maybe try the following:

 

 

ForAll(
    Gallery1.AllItems,
    Patch(
        SpringTbl,
        LookUp(SpringTbl, Size = SizeLabel.Text && Length = LengthLabel.Text && Color = ColorLabel.Text),
        {'2 - Qty': TextInput1.Text}
    )
)

where Size, Length, and Color are the column names for those items in SpringTbl and SizeLabel, LengthLabel, and ColorLabel are the names of the text labels in Gallery1 for those items.

 

 

Just to attempt to clarify what is happening a bit, that second part of the Patch function needs to be a single record so we need a way to identify each individual row in the table. If you had an ID in that table, we could reduce that statement to something like:

ForAll(
    Gallery1.AllItems,
    Patch(
        SpringTbl,
        LookUp(SpringTbl, ID = Value(IDLabel.Text)),
        {'2 - Qty': TextInput1.Text}
    )
)

where IDLabel is a text label in the gallery, either shown or hidden. 

 

Please let me know if I can explain anything else or attempt to help further!

View solution in original post

2 REPLIES 2
Super User
Super User

Re: Patch Input Data from Gallery to Excel File

Hey @Anonymous, maybe try the following:

 

 

ForAll(
    Gallery1.AllItems,
    Patch(
        SpringTbl,
        LookUp(SpringTbl, Size = SizeLabel.Text && Length = LengthLabel.Text && Color = ColorLabel.Text),
        {'2 - Qty': TextInput1.Text}
    )
)

where Size, Length, and Color are the column names for those items in SpringTbl and SizeLabel, LengthLabel, and ColorLabel are the names of the text labels in Gallery1 for those items.

 

 

Just to attempt to clarify what is happening a bit, that second part of the Patch function needs to be a single record so we need a way to identify each individual row in the table. If you had an ID in that table, we could reduce that statement to something like:

ForAll(
    Gallery1.AllItems,
    Patch(
        SpringTbl,
        LookUp(SpringTbl, ID = Value(IDLabel.Text)),
        {'2 - Qty': TextInput1.Text}
    )
)

where IDLabel is a text label in the gallery, either shown or hidden. 

 

Please let me know if I can explain anything else or attempt to help further!

View solution in original post

Highlighted
Community Support
Community Support

Re: Patch Input Data from Gallery to Excel File

Hi @Anonymous ,

Based on the needs that you mentioned, I think the ForAll function and Patch function could achieve your needs.

 

Based on the formula you provided, I think this is something wrong with it. I have made a test on my side, please take a try with the following workaround:

Set the OnSelect property of the "Save" icon button to following formula:

ForAll(
        Gallery1.AllItems,
Patch(
SpringTbl,
LookUp(SpringTbl, Size = SizeColumn.Text && Length = LengthColumn.Text && Color = ColorColumn.Text),
{
'2 - Qty': TextInput1.Text
}
) )

Note: The Size, Length and Color are all columns in your SpringTbl Excel table.

 

Please take a try with above solution, check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!

Top Solution Authors
Top Kudoed Authors
Users online (5,947)