Check all fields for each record in gallery

[dungar]

Rough example:

I have a gallery (GalleryMoney) which is populated with a collection (Employees).  Employees has 12 money fields that in the gallery have a text box so you can input a value(one for each possible currency so TextGallery100, TextGallery50, etc).  The collection could have 1 to 9 records.

As a check, each one of these text boxes in the gallery has a short IF(AND(Mod...,IsNumeric...) statement to check that they're entering a number, and that the number is a sane value (can't enter $150 in $100 bills or random letters).  If they enter something incorrect the fill changes to Red.

I'd like to prevent them from submitting the data if any of the textboxes are Red.  The best formula I can come up with is-

ForAll(

    GalleryMoney.AllItems, IF(

        OR(

            [@TextGallery100].Fill = Red,

            [@TextGallery50].Fill = Red,

            [@TextGallery20].Fill = Red,....) = true,

     update error variable fail,

     update error variable pass)

)

I've got some problems here:

1. I can't seem to do an UpdateContext in a ForAll.  What would be the best way to set my fail variable here, or should I just collect the fill states and go from there?

2. Am i really going to need a 12 condition OR/Switch/If statement?  In something like C# i'd just do a for each inside a for each, but there doesn't seem to be anything like ForAll(GalleryMoney.AllItems, ForAll(@Fields...)).  Research i've done indicates this is the case, but as i was finding pretty old topics I wanted to be sure I wasn't missing something.

3. My Employee collection has a "Key" field so I can identify them (1 - 9).  It's not displayed on the gallery, but even if i use a collect in my ForAll, I can't find a way to reference this?  Do i need to make an invisible label in the gallery with the value so it can be referenced?

4. On some testing it seems i've got something wrong?  The failure collection i've created only seems to populate the last incorrect value in the gallery.  All of them could be wrong but it'll only populate with the last one (and it'll populate as many times as there are entries in the gallery, so one wrong box out of 10 records leads to 10 entries of the same data in the Failures collection).  This is "good enough" to stop the submission, but clearly i'm missing something, and it'd be a problem when I want to improve user feedback.

5. The more i mess with this the more errors i find.  I was going to use a similar logic to take all the text boxes in the gallery and Collect them into my Employee Collection (thus updating the values) but it seems that may not work the way I thought it did.  I'm having a very hard time seeing how I should workflow this.

[v-xida-msft · Accepted solution]

Hi @dungar ,

For your first question, currently, we could not set a variable using UpdateContext or Set function within a ForAll function. As an alternative solution, you could consider store the fail/pass value into a collection.

Clear(FailCollection);
Clear(PassCollection);
ForAll(

    GalleryMoney.AllItems, 
    IF(

        OR(

            [@TextGallery100].Fill = Red,

            [@TextGallery50].Fill = Red,

            [@TextGallery20].Fill = Red,....) = true,

     Collect(FailCollection, ....),

     Colelct(PassCollection, ....)
   )

)

For your second question, yes, you are right. If you want to list the 12 TextBox Fill property using ForAll function, I afraid that there is no way to achieve your needs in PowerApps currently.

For your third question, do you want to reference the "Key" field from the Gallery within the Collect function in the Patch formula? If you want to reference the "Key" field from the Gallery within the Collect function in the Patch formula, you could reference the "Key" field value directly.

ForAll(
       Gallery1.AllItems,
       Collect(
                Collectino1, 
                {
                   EmployeeId: Key,   // Reference directly
                   ...
                   ...
                }
       )
)

For your forth question, could you please share a bit more about the formula used to set up the failure collection? Based on the issue that you mentioned, I think this issue is related to If function in your ForAll formula.

If you want to collect 10 entries in your failure collection rather than just the last one, you could consider save a Blank record to the failure collection when the current record Text Boxes have no format issue within the false branch of the If function.

For your fifth question, could you please share more details about the formula you used in your app? If you want to save the Gallery Items value into a collection, the standard formula as below:

Clear(EmployeeCollection);
ForAll(
       Gallery1.AllItems,
       Collect(
               EmployeeCollection, 
               {
                 Column1: TextInput1.Text,
                 Column2: TextInput2.Text,
                 Column3: TextInput3.Text,
                 ...,
                 ...
               }
       )
)

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

Best regards,