cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Advocate V
Advocate V

Using Lookup with ForAll returns incorrect record

A few months ago @mdevaney helped me get some code working so that in a gallery, either all of the toggles were forced off, or one was allowed to be on. I though it was working but it isn't. The project got sidelined but now is in testing again and I guess my original test data wasn't sufficient. The original thread is here.

 

 

UpdateContext({varActiveQuoteToggleID: ThisItem.UID});
ClearCollect(
    colQuoteChosenUID,
    ShowColumns(
        galBrowseQuotes.AllItems,
        "UID"
    )
);
If(
    ThisItem.QuoteChosen = true,
    Patch(
        '[dbo].[tblAppItemDevelopmentQuotes]',
        galBrowseQuotes.Selected,
        {QuoteChosen: false}
    ),
    ForAll(
        colQuoteChosenUID,
        Patch(
            '[dbo].[tblAppItemDevelopmentQuotes]',
            LookUp(
                '[dbo].[tblAppItemDevelopmentQuotes]',
                UID = colQuoteChosenUID[@UID]
            ),
            {
                QuoteChosen: If(
                    colQuoteChosenUID[@UID] = varActiveQuoteToggleID,
                    true,
                    false
                )
            }
        )
    )
)

 

  •  So in the sample of data I am using, the first UID in the tblApplicationItemDevelopmentQuotes is 9, then there are a bunch of  others. In this gallery, only UIDs 59 and 61 are chosen.
  • The colChosenQuoteUID correctly has 2 values, 59 and 61.
  • The problem is record 9 is getting updated. It isn't even part the selected data for this gallery. According to the monitor, the LOOKUP() function for the patch is always returning UID#9.

EdHansberry_0-1596501811254.png

and the response from the app shows this:

EdHansberry_1-1596501912573.png

 

I cannot figure out why as FORALL loops through colChosenQuoteUID it would ever return 9, which is the UID of the first record in the main table it would pick 9. I can see the collection and it is definitely 59 and 61 only. Shouldn't it be comparing the table's ID to 59 and 61, not 9?

 

Stumped...

 

1 ACCEPTED SOLUTION

Accepted Solutions

I fixed it - I think. I have to admit I did not understand what the [@UID] was doing in the formula. I was able to rewrite it without that.

The first thing I did is renamed the UID column in my collection to LookupUID so I knew which UID I was working with, then tweaked the original formula a bit to use that. Seems to work with a number of tests.

 

Clearly ForAll is a bit tricky, and it is hard for me to see what is going on inside of it. Need a Debug.Print function. 😁

Thanks @WarrenBelz for your tip on the As function. I didn't use it, but need to look at that as well for future reference.

UpdateContext({varActiveQuoteToggleID: ThisItem.UID});
// Assigns this items UID to this variable;
ClearCollect(
    colQuoteChosenUID,
    RenameColumns(
        ShowColumns(
            galBrowseQuotes.AllItems,
            "UID"
        ),
        "UID",
        "LookupUID"
    )
);
//a collection of all of the quote UIDs visible on this screen and calls them LookupUID to reduce amibiguity later in the ForAll
If(
    ThisItem.QuoteChosen = true,
    Patch(
        '[dbo].[tblAppItemDevelopmentQuotes]',
        galBrowseQuotes.Selected,
        {QuoteChosen: false}
    ),
    ForAll(
        colQuoteChosenUID,
        Patch(
            '[dbo].[tblAppItemDevelopmentQuotes]',
            LookUp(
                '[dbo].[tblAppItemDevelopmentQuotes]',
                UID = LookupUID
            ),
            {
                QuoteChosen: If(
                    LookupUID = varActiveQuoteToggleID,
                    true,
                    false
                )
            }
        )
    )
)

 

View solution in original post

3 REPLIES 3
Super User II
Super User II

You're in luck!

They recently added some new functionality to enhance ForAll functions, and make this easier to use.

 

https://powerapps.microsoft.com/en-us/blog/formulas-thisrecord-as-and-sequence/

 

In your example, try swapping in ThisRecord.UID and also disambiguate the lookup table.

LookUp('[dbo].[tblAppItemDevelopmentQuotes]', '[dbo].[tblAppItemDevelopmentQuotes]'[@UID] = ThisRecord.UID)

Happy coding! Hope this helps.

Hi @EdHansberry ,

You might also try the As function - this totally solves Ambiguity.

UpdateContext({varActiveQuoteToggleID: ThisItem.UID});
ClearCollect(
   colQuoteChosenUID,
   ShowColumns(
      galBrowseQuotes.AllItems,
      "UID"
   )
);
If(
   ThisItem.QuoteChosen = true,
   Patch(
      '[dbo].[tblAppItemDevelopmentQuotes]',
      galBrowseQuotes.Selected,
      {QuoteChosen: false}
   ),
   ForAll(
      colQuoteChosenUID As Quotes,
      Patch(
         '[dbo].[tblAppItemDevelopmentQuotes]',
         {UID:Quotes.UID},
         {
            QuoteChosen: 
            If(
               Quotes.UID = varActiveQuoteToggleID,
               true,
               false
            )
         }
      )
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

I fixed it - I think. I have to admit I did not understand what the [@UID] was doing in the formula. I was able to rewrite it without that.

The first thing I did is renamed the UID column in my collection to LookupUID so I knew which UID I was working with, then tweaked the original formula a bit to use that. Seems to work with a number of tests.

 

Clearly ForAll is a bit tricky, and it is hard for me to see what is going on inside of it. Need a Debug.Print function. 😁

Thanks @WarrenBelz for your tip on the As function. I didn't use it, but need to look at that as well for future reference.

UpdateContext({varActiveQuoteToggleID: ThisItem.UID});
// Assigns this items UID to this variable;
ClearCollect(
    colQuoteChosenUID,
    RenameColumns(
        ShowColumns(
            galBrowseQuotes.AllItems,
            "UID"
        ),
        "UID",
        "LookupUID"
    )
);
//a collection of all of the quote UIDs visible on this screen and calls them LookupUID to reduce amibiguity later in the ForAll
If(
    ThisItem.QuoteChosen = true,
    Patch(
        '[dbo].[tblAppItemDevelopmentQuotes]',
        galBrowseQuotes.Selected,
        {QuoteChosen: false}
    ),
    ForAll(
        colQuoteChosenUID,
        Patch(
            '[dbo].[tblAppItemDevelopmentQuotes]',
            LookUp(
                '[dbo].[tblAppItemDevelopmentQuotes]',
                UID = LookupUID
            ),
            {
                QuoteChosen: If(
                    LookupUID = varActiveQuoteToggleID,
                    true,
                    false
                )
            }
        )
    )
)

 

View solution in original post

Helpful resources

Announcements
News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

Power Apps Community Call

Power Apps Community Call- January

Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST

PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

Top Solution Authors
Top Kudoed Authors
Users online (3,798)