Unanswered

How to get values out of nested galleries?

(0) Share

Report

Posted on by martinav

3,347

I have a situation where I have a gallery in a gallery. I need to pull data from the sub-gallery in a ForAll(). It does not seem to be giving me the correct data. I think this is enough to get the idea across. Perhaps there is a better way to do this?

ForAll(Gallery4.AllItems As ThisGall,
 With({ThisGall8:Gallery8.Selected},
 UpdateIf(SN_RMA_Notes,Index=ThisGall.Index,
 If(CheckValid.Text="0",{CheckValue:99},
 If(CheckValid.Text="1",
 {CheckValue:1,
 Line_No:ThisGall8.LineNumber,
 Company:If(First(Gallery8.AllItems).CustomerName<>Trim(ThisGall.Company),ThisGall8.CustomerName),
 Acctivate:1
 }
 
 )
 )
 )
))

Gallery8 is a result of a lookup (one record) from a thisitem.index from Gallery4. I cannot get any of the values out of gallery8 when I do the updateif(). Values do post, but they are wrong.

Edited: I should also add... Visually, the data is correct looking at the galleries on screen. Its only wrong when applying the ForAll() statement.

Categories:

Building Power Apps

All responses (12)

Answers (0)

Sort by

TheRobRush 11,128 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

doing a forall on that will work better if you move it inside the update/patc/collect etc. @RandyHayes is very good at explaining the process.
martinav 3,347 on at

Like
a
(0)

Report
Copy link

Link copied!

@TheRobRush ,

Huh... ok. I'll give it a shot. I can do this quick.
martinav 3,347 on at

Like
a
(0)

Report
Copy link

Link copied!
@TheRobRush , @RandyHayes ,

I'm not having much luck turning this inside out. Brain breaks... This is what I got so far, but its red all over. Checked for "(" and ")" compliance and basic syntax, but its warped my head into a knot.

With({ThisGall4:Gallery4.AllItems}, UpdateIf(SN_RMA_Notes, ForAll(ThisGall4 As varThisGall4, Index=varThisGall4.Index), With({ThisGall8:Gallery8.Selected}, If(CheckValid.Text="0",{CheckValue:99}, If(CheckValid.Text="1",{CheckValue:1, Line_No:ThisGall8.LineNumber, Company:If(First(Gallery8.AllItems).CustomerName<>Trim(ThisGall4.Company),ThisGall8.CustomerName), Acctivate:1 } ) ) ) ))
TheRobRush 11,128 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

If Randy hasn't piped in by tomorrow I will swing back around and help you with the correct formula. I have gotten pretty good at it, just have to write it all out on my side in a test first to be sure of what I give you, whereas Randy seems to have this down so well he can just do it in his head haha
martinav 3,347 on at

Like
a
(0)

Report
Copy link

Link copied!
Well, I got my original syntax to work. However, it does a SQL call for EVERY record. If the other way would do them in a single SQL call, it would work so much faster. I get about one update a second. Quite a long wait to crunch through 2000 records a couple hundred at a time.

The issue was with the first With() statement. I needed to write "With({ThisGall8:ThisGall.Gallery8.Selected},...", in lieu of "With({ThisGall8:Gallery8.Selected},...",

ForAll(Gallery4.AllItems As ThisGall, With({ThisGall8:ThisGall.Gallery8.Selected}, UpdateIf(SN_RMA_Notes,Index=ThisGall.Index, If(CheckValid.Text="0",{CheckValue:9}, If(CheckValid.Text="1",{CheckValue:1, Line_No:ThisGall8.LineNumber, Company:If(ThisGall8.CustomerName<>Trim(ThisGall.Company),ThisGall8.CustomerName), Acctivate:1 } ) ) ) ))
martinav 3,347 on at

Like
a
(0)

Report
Copy link

Link copied!

Ive asked similar questions in the past, @TheRobRush , @RandyHayes , but is there a way to do updates with fewer SQL calls? one a second will be a little unweildly when I start the database update that is 90,000 lines. Thats 25 hours at the going rate. I have never used metaverse data before, would it work there faster? I have never used it. Basically, my data will need to end up in SQL server. However, I could build it anywhere and dump it all to a new table later. This is a one-time project. Or, I could just take the lumps and do things between batches over the next 3 days.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

I've had a lot of challenge with this same thing. A few workarounds I had to do and you can try, not sure if they pertain to your solution. But I bring the value up from the nested gallery to the top gallery, hide the control, then reference that.

Or I use a nested for all with a patch at each condition

ForAll(
gal_Parent.AllItems As _Parent,
ForAll(
_Parent.gal_Child.AllItems,
Patch(
[Data Source],
ThisRecord,
{
field1: val1,
field2: val2
}
)
)
)
martinav 3,347 on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous ,

Yep that is essentially what I did. I was hoping on some refinement that would not require an SQL call for every single item. You can create new records in 'bulk' by doing a Collect(SQL_Table, <stuff stuff stuff>) but I dont know of a way to apply a table of records to a single Update/Updateif() statement.
Community Power Pla... Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

You can do it with a single patch command.

Do the for all and build a local collection of all the changes then patch it as one collection to the data source

Patch(sqltable, collection_you_made)

I've been replicating my initial query into a local collection first, then making changes to that collection, then patch it in one go once confirming.
martinav 3,347 on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous

That works for Patch() and Collect() for NEW records only. Not updating. Unless something has changed. How do you associate each record in the collection to the existing records with Patch() in the sqltable? I'm not sure just placing a primary key in the table will do it. Can you confirm you have done this to update existing records?