cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Kaine
Advocate I
Advocate 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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,027)