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

Help ForAll Nested Gallery Textbox value

Hi,

 

I having problems trying to access the values of an Inner Gallery, and specifically the value of a textbox. Here is the an example of what I'm trying to achieve:

 

 

// Iterate over all line records and save each one.
ForAll(
    OuterGallery.AllItems, 
    ForAll(
        InnerGallery.AllItems, 
        'MS Flow to save line records (on-premise Stored Procedure)'.Run(
            Id,
            ParentId,
            PartCode,
            Description,
            QtyOrdered,
            Value(Textbox_QtyReturned.Text, "0")
        )
    )
);

 

 

The InnerGallery ForAll() never seems to be executed.

 

I am doing something similar saving the OuterGallery items as header records using MS Flow, and these seem to be created OK.

 

I realise that sometimes the approach I take doesn't always match the philosophy behind PowerApps, so I'm happy to listen to alternative suggestions.

 

I have searched for a solution and would be grateful for any help.

 

Thanks,

 

Kaine

7 REPLIES 7
v-bofeng-msft
Community Support
Community Support

HI @ Kaine:

Do you want to call all records in all InnerGallery galleries?

Could you show me more detail about the InnerGallery and the OuterGallery?(Such as the definition of their Items properties)

Firstly, let me explain why this code does not work.

The point is the “ForAll” function manipulates data instead of gallery. The InnerrGallery that in the OuterGallery cannot be referenced by a record from OuterGallery.AllItems

In addition, in this code (Value(Textbox_QtyReturned.Text,"0")), “0 is not supported. I suggest you use this code:

Value(Textbox_QtyReturned.Text)

Value function in Power Apps

Secondly, I suggest you readjust the formula from the perspective of data relations. For example:

BackGround

I assume Textbox_QtyReturned is a textinput control that outside the gallery.

OuterGallery:

Items:

Table(
    {
        Title: "qq",
        Field1: Table(
            {Id: "11",ParentId:"11",PartCode:"11",Description:"11",QtyOrdered:"11"},
            {Id: "22",ParentId:"22",PartCode:"22",Description:"22",QtyOrdered:"22"}
        )
    },
    {
        Title: "bb",
        Field1: Table(
            {Id: "33",ParentId:"33",PartCode:"33",Description:"33",QtyOrdered:"33"},
            {Id: "44",ParentId:"144",PartCode:"44",Description:"44",QtyOrdered:"44"}
        )
    }
)

InnerGallery:

Items:

ThisItem.Field1

In this case, if I want to call all subfield1 in all InnerGallerys ,I need to add a button and set its OnSelected property to:

ForAll(
    Ungroup(
        ForAll(
            OuterGallery.AllItems,
            Field1
        ),
        "Value"
    ),
    'MS Flow to save line records (on-premise Stored Procedure)'.Run(
            Id,
            ParentId,
            PartCode,
            Description,
            QtyOrdered,
            Value(Textbox_QtyReturned.Text)
)
)

qqq.png

Best Regards,

Bof

Hi @v-bofeng-msft,

Thanks ever so much for spending the time to provide such a detailed response.

With regards to the Value() problem, that was my bad while putting together the pseudo code, but thanks for spotting it.

I'm not sure whether you saw it or not, but I included an annotated screenshot with my original post, showing the screen layout which may make things easier to understand. 

 

Test Data

Here is some contrived test data that is very similar in shape and content to that which I'm using:

  {
    Customer: "Blossom Entertainment",
    Supplier: "MICROSOFT - Select Orders",
    Team: "LGOV",
    SalesOrderNumber: "SO280366",
    PurchaseOrderNumber: "PORD2080049",
    OrderDetailId: "146321",
    PartCode: "3373406",
    Description: "SQL Svr Standard Edtn 2005 Win32 Sngl MVL",
    QtyOrdered: 2,
    QtyReturned: 0
  },
  {
    Customer: "Blossom Entertainment",
    Supplier: "MICROSOFT - Select Orders",
    Team: "LGOV",
    SalesOrderNumber: "SO280366",
    PurchaseOrderNumber: "PORD2080049",
    OrderDetailId: "146323",
    PartCode: "3579660",
    Description: "Windows Svr Ent 2008 Sngl MVL",
    QtyOrdered: 2,
    QtyReturned: 0
  },
  {
    Customer: "Blossom Entertainment",
    Supplier: "MICROSOFT - Select Orders",
    Team: "LGOV",
    SalesOrderNumber: "SO280366",
    PurchaseOrderNumber: "PORD2080049",
    OrderDetailId: "146322",
    PartCode: "3372795",
    Description: "SQL CAL 2005 Sngl MVL Device CAL",
    QtyOrdered: 200,
    QtyReturned: 0
  },
  {
    Customer: "Blossom Entertainment",
    Supplier: "MICROSOFT - Select Orders",
    Team: "LGOV",
    SalesOrderNumber: "SO280366",
    PurchaseOrderNumber: "PORD2080049",
    OrderDetailId: "146324",
    PartCode: "3579645",
    Description: "Windows Server CAL 2008 Sngl MVL Device CAL",
    QtyOrdered: 200,
    QtyReturned: 0
  },
  {
    Customer: "Blossom Entertainment",
    Supplier: "Ingram Micro (uk) Ltd",
    Team: "LGOV",
    SalesOrderNumber: "SO280366",
    PurchaseOrderNumber: "PORD2078960",
    OrderDetailId: "146327",
    PartCode: "2237068",
    Description: "SQL Svr Standard Edtn 2005 Win32 English Disk Kit MVL CD/DVD",
    QtyOrdered: 1,
    QtyReturned: 0
  },
  {
    Customer: "Blossom Entertainment",
    Supplier: "Ingram Micro (uk) Ltd",
    Team: "LGOV",
    SalesOrderNumber: "SO280366",
    PurchaseOrderNumber: "PORD2078960",
    OrderDetailId: "146330",
    PartCode: "2369438",
    Description: "Windows Svr Ent 2003 R2 32-bit/x64 English Disk Kit MVL CD",
    QtyOrdered: 1,
    QtyReturned: 0
  },
  {
    Customer: "Blossom Entertainment",
    Supplier: "MICROSOFT - Select Orders",
    Team: "LGOV",
    SalesOrderNumber: "SO280366",
    PurchaseOrderNumber: "PORD2080049",
    OrderDetailId: "146326",
    PartCode: "3457160",
    Description: "Config Mgr Clnt Mgmt Lic 2007 All Lng MVL STUDENT ONLY Per User",
    QtyOrdered: 1000,
    QtyReturned: 0
  },
  {
    Customer: "Blossom Entertainment",
    Supplier: "MICROSOFT - Select Orders",
    Team: "LGOV",
    SalesOrderNumber: "SO280366",
    PurchaseOrderNumber: "PORD2080049",
    OrderDetailId: "146325",
    PartCode: "3367746",
    Description: "Config Mgr Svr w/SQL 2007 Sngl MVL",
    QtyOrdered: 1,
    QtyReturned: 0
  },
  {
    Customer: "Blossom Entertainment",
    Supplier: "Ingram Micro (uk) Ltd",
    Team: "LGOV",
    SalesOrderNumber: "SO280366",
    PurchaseOrderNumber: "PORD2078960",
    OrderDetailId: "146329",
    PartCode: "3405287",
    Description: "Config Mgr Svr w/SQL 2007 English Disk Kit MVL DVD",
    QtyOrdered: 1,
    QtyReturned: 0
  }

 

I add this data to a collection called NewReturnData, and then, to create the OuterGallery, I use the following data shaping:

AddColumns(
    GroupBy(
        NewReturnData, 
        "Supplier", 
        "PurchaseOrderNumber", 
        "ItemLines"
    ), 
    "Id", GUID(),
    "SalesOrderNumber", First(NewReturnData).SalesOrderNumber
)

 

I use the OuterGallery ItemLines sub-collection for the InnerGallery, using the following data shaping:

AddColumns(
    ThisItem.ItemLines, 
    "Id", GUID(), 
    "ParentId", ThisItem.Id,
    "QtyAvailableForReturn", QtyOrdered - QtyReturned, 
    "QtyReturning", ""
)

 

Ungroup Suggestion

I looked at your suggestion to use Ungroup with great interest, but I think that the text-box is located on each line record rather than the header record, and then I became a little confused.

 

I'd just like to thank you again for taking the time to put together such a detailed response, I can't tell you how much I appreciate that.

 

- Kaine

 

 

 

 

Hi @Kaine :

I did a similar test and would like to suggest you to try my method:

The point is first handle the folw, then handle Gallery:

Step1:add a btton and set it's OnSelect Property to:

 

ClearCollect(
    Var,             /*My custom collection var*/
    AddColumns(
        Ungroup(
            AddColumns(
                GroupBy(
                    NewReturnData,
                    "Supplier",
                    "PurchaseOrderNumber",
                    "ItemLines"
                ),
                "ParentId",
                GUID()
            ),
            "ItemLines"
        ),
        "Id",
        GUID(),
        "QtyAvailableForReturn",
        QtyOrdered - QtyReturned,
        "QtyReturning",
        ""
    )
);
ForAll(
    Var,
    'MS Flow to save line records (on-premise Stored Procedure)'.Run(
            Id,
            ParentId,
            PartCode,
            Description,
            QtyOrdered,
            Value(Textbox_QtyReturned.Text)
)
)

 

 Step2:

OuterGallery:

Items:

 

 GroupBy(
        Var, 
        "Supplier", 
        "PurchaseOrderNumber", 
        "ItemLines"
    )

 

InnerGallery:

Items:

 

ThisItem.ItemLines

 

1.png

Best Regards,

Bof

 

Hi @v-bofeng-msft ,

Thanks again for taking the time to respond to me, I really appreciate it.

One thing that I'm not sure that I'm communicating clearly is that the text-boxes are at line level:

 

Textboxes are at line levelTextboxes are at line levelTextboxes are at line levelTextboxes are at line level

 

Data Shaping

In my previous response, the data shaping acted as the data source for both the OuterGallery and InnerGallery, I'm sorry if I wasn't clear.

 

- Kaine

Hi @Kaine :

Do you consider using only one gallery?

1\Add a button

OnSelect:

 

ClearCollect(
    Var,
    AddColumns(
        Ungroup(
            AddColumns(
                GroupBy(
                    NewReturnData,
                    "Supplier",
                    "PurchaseOrderNumber",
                    "ItemLines"
                ),
                "ParentId",
                GUID()
            ),
            "ItemLines"
        ),
        "Id",
        GUID(),
        "QtyAvailableForReturn",
        QtyOrdered - QtyReturned,
        "QtyReturning",
        ""
    )
);

 

2\add a gallery(Gallery1😞

items:

 

Var

 

3\add a button:

OnSelect:

 

ForAll(
    Gallery1.AllItems,
    'MS Flow to save line records (on-premise Stored Procedure)'.Run(
            Id,
            ParentId,
            PartCode,
            Description,
            QtyOrdered,
            Value(Textbox_QtyReturned.Text)
)
)

 

11.PNG

Best Regards,

Bof

 

Hi @v-bofeng-msft ,

Thanks again for taking the time to answer my questions. It's really valuable and, again, I really appreciate it.

My fallback position is to have a single gallery and have the business user choose the supplier/purchase order upfront before the screen is loaded instead of listing them all together.

It's a pity that this doesn't seem to be possible, especially since this is what our business wants to do.

Again, I appreciate your time and advice.

 

- Kaine

Hi @Kaine :

I think maybe using a gallery can also achieve the function you describe, just nest the filter function outside the formula.

 Best Regards,

Bof

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.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (1,078)