Solved: Should I be Creating a Collection

Phineas · ‎10-04-2022

I have the need to allow a user to item multiple choice to
an order form in Power Apps. I've created a Power Apps form
that replicates the below table and other needs.

I have created a SP List and the below app based on that List.
Currently the list only has one column for each product need.
The client should be able to add up to '10' size options (or items)
per order form that is associate with that singular client.

I'm thinking I should be creating a collection of items based on
the items at the bottom, saving the each item to the collection,
then Patch the collection to the List?

If this is true can you provide insight into how to best achieve?

RandyHayes · ‎10-04-2022

@Phineas

Yes - you are best (even with a proof of concept) to have the two lists. This will make what you are doing in PowerApps *much* easier!!

So now I will give you full details - this is going to look long, but it is just detailed, beyond that it is super simple!

Okay, so your setup should be the following (I'm going to make up control names as well to reference in formulas):

Data Lists:

1 - Two lists - a Member List that is the Parent List and an Items List that is the Child list. MemberList and ItemsList

The ItemsList should have a number column for the MemberID

BrowseScreen:

1 - A Gallery (galMembers) in your app on the BrowseScreen that has an Items property of: MemberList

2 - An Add Button/Icon for adding new Member records. (icnAddMember)

On the Edit Screen:

1 - An Edit Form (frmMember) (just replace the one on the EditScreen) that has a Datasource of MemberList

2 - A Gallery (galItems) outside of the form on the EditScreen. IN the gallery template, you should have an appropriate control for each of your field types.

WindowNumber: TextInput - txtWindowNumber,
WindowWidth: Dropdown - Set Items property as you currently have, ddWindowWidth
WindowHeight: Dropdown - Set Items property as you currently have, ddWindowHeight
WindowQuantity: Dropdown - Set Items property as you currently have, ddWindowQuantity
WindowCovering: Dropdown - Set Items property as you currently have, ddWindowCovering
WindowFrequencyOrUse: Dropdown - Set Items property as you currently have, ddWindowFrequencyOrUse
WindowShape: Dropdown - Set Items property as you currently have, ddWindowShape

You should also have a trash can icon in the gallery for deleting the row. icnRemove

3 - A "add" button/icon outside of the Gallery and outside of the form as well (icnAddItem). Note...I chose an icon for this example.

4 - A "Submit" button or icon. btnSubmit

Now for some formulas!

On the Browse Screen:

1 - from the main gallery on the Browse Screen (galMembers), you would alter the Right Arrow Icon and change the OnSelect action formula to:

Set(glbRecord, ThisItem);
ClearCollect(colItems, AddColumns(Filter(ItemsList, MemberID= ThisItem.ID), "intID", GUID(), "rem", false));
Navigate(EditScreen1)

2 - On the "add" icon OnSelect aciton:

Set(glbRecord, Defaults(yourParentListNameHere);
Clear(colItems);
Navigate(EditScreen1)

On the EditScreen:

1 - Your Edit form (frmMember) should have a default mode of "Edit". Do not issue a NewForm function anywhere for this form at this point.

2 - The Item property of the Form (frmMember) should be set to gblRecord

3 - Your gallery (galItems) Items property should be set to: Filter(colItems, !rem)

4 - Set the Default properties of all the Controls in the Gallery to their corresponding column name.

Ex. for txtWindowNumber, set the Default property to: ThisItem.WindowNumber

Repeat for each control in the gallery template.

5 - For the icnRemove OnSelect action, set the formula to:

If(IsBlank(ThisItem.ID), 
    RemoveIf(colItems, intID=ThisItem.intID),
    UpdateIf(colItems, intID=ThisItem.intID, {rem: true})
)

6 - For the Add new Item icon (icnAddItem) set the OnSelect action formula to:

Collect(colItems, {intID: GUID(), rem:false})

NOTE: if you want to limit the number of entries to 10 as you mentioned, then set the DisplayMode of the icnAddItem icon to: If(CountRows(colItems)>9, Disabled, Edit)

7 - For the OnSuccess action formula of the form (frmMember), set the formula to:

Patch(ItemsList,
    ForAll(Filter(galItems.AllItems, !rem),
        {ID: ID,
         WindowNumber: txtWindowNumber.Text,
         WindowWidth: ddWindowWidth.Selected.Value,
         WindowHeight: ddWindowHeight.Selected.Value,
         WindowQuantity: ddWindowQuantity.Selected.Value,
         WindowCovering: ddWindowCovering.Selected.Value,
         WindowFrequencyOrUse: ddWindowFrequencyOrUse.Selected.Value,
         WindowShape: ddWindowShape.Selected.Value
         MemberID: Self.LastSubmit.ID
        }
    )
);

Remove(ItemsList, Filter(colItems, rem).ID);

Clear(colItems);
Set(glbRecord, Blank())

Back()

8 - Finally, the btnSubmit button OnSelect action:

SubmitForm(frmMember)

That is it!!

You should have a fully functioning parent child entry and storing app.

NOTE: all the above was typed by hand, so there is always a potential that I have a typo. Please check carefully if there are issues.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes
Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

RandyHayes · ‎10-04-2022

@Phineas

Are the items list that you are showing child records to the parent form record? Or are all of these columns in your list?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes
Really want to show your appreciation? Buy Me A Cup Of Coffee!

Phineas · ‎10-04-2022

Currently the blank SP List has a one to many relationship.

If the user has more than one product choice the user's name will appear multiple times in the SP List.

I believe this was your question.

I have created a 'UniqueID' column (content yet to be determined) in the SP List.

I have created a button and a collection that is currently populating all data from the form using the following.

Collect( ProductOrder,

{ FullName: DataCardValue10.Text,
LastName: DataCardValue11.Text,
FirstName: DataCardValue12.Text,
BuildingTye: DataCardValue17.SelectedText.Value,
PrimaryNoise: DataCardValue18.SelectedText.Value,
Address: DataCardValue13.Text,
SuiteApt: DataCardValue14.Text,
State: DataCardValue15.SelectedText.Value,
ZipCode: DataCardValue16.Text,
WindowNumber: DataCardValue25.Text,
WindowWidth: DataCardValue19.SelectedText.Value,
WindowHeight: DataCardValue20.SelectedText.Value,
WindowQuantity: DataCardValue21.SelectedText.Value,
WindowCovering: DataCardValue22.SelectedText.Value,
WindowFrequencyOrUse: DataCardValue23.SelectedText.Value,
WindowShape: DataCardValue24.SelectedText.Value,
OrderComments: DataCardValue26.Text});

RandyHayes · ‎10-04-2022

@Phineas

Yeah...you don't need to do all of that. In fact, typing all that in is a waste of your time!

Your gallery already has the information you need. You only need a base collection to provide the rows for your gallery.

Your Unique ID already exists in your SharePoint list...it is ID

So I assume that your red-circled item in your picture is the gallery? Please confirm.

Also, is your solution to only create new records or to edit existing records?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes
Really want to show your appreciation? Buy Me A Cup Of Coffee!

Phineas · ‎10-04-2022

Actually everything is in a 'Form' (DisplayMode.New), created with the 'Integrate Power App' link from SP after I finished making the List.

RandyHayes · ‎10-04-2022

@Phineas

You will need to put the gallery outside of the form. It provides no advantage to have it in the form, but yet it has a long known bug when used in a form.

While we're at it...what is the Item property of your form?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes
Really want to show your appreciation? Buy Me A Cup Of Coffee!

Phineas · ‎10-04-2022

SP autogenerated a multiple component app from the List with BrowseScreen, EditScreen (the form I've edited), and DetailScreen.

The Items property of the Edit Form was by default 'BrowseGallery1.Selected'.

RandyHayes · ‎10-04-2022

@Phineas

Okay...that is all fine, there will be a couple of changes to that.

But still, you will not want the Gallery to be in the form in a datacard. That should be outside of the form.

In re-looking at your formula you provided for the collect function (which again...you don't need), I notice you do not have a master ID column in the list. Perhaps this is what you meant by UniqueID column!? But, at any rate, your child list (many) should have a column to identify the parent record ID (one).

Now, what I am not seeing is "gallery control" in your screenshots.

Your original mockup shows rows of Size 1 to Size 10.

Your screenshot of the gallery shows nothing for adding or removing rows to your child list.

What is your thought there? Have you just not added it yet?

Will you be providing not only the ability to add rows, but also to remove rows?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes
Really want to show your appreciation? Buy Me A Cup Of Coffee!

Phineas · ‎10-04-2022

Currently there is no Gallery in the fillable form (shown below).

All fields in the form are datacards.

Everything above the red circle should be reflect across every line item on the SP List related the all the items from the fields in the red circle (which will remove and add back as a Gallery).

I changed the SP List 'Title' column to 'UniqueID' I just need to develop content the loads with the form/gallery data.

Yes, it would be most appropriate to have a single new item line with the ability for the user to add lines items, up to 10.

RandyHayes · ‎10-04-2022

@Phineas

You are stating that your controls in red circle are all in datacards in the form. Are you stating that your Parent list has columns for those items (i.e. Height, Width, Qty, etc.)?? I though you said you had a one-to-many relationship (Parent - Child)?

If so, then all of those should be in your child list.

Having the UniqueID in your parent record is not relevant. It already has a unique ID - the ID column.

It is the child list that needs to have a column to hold the ID column value of the Parent record. Title is a Text column. I would advise having a number column in your list to hold the Parent ID...as it is a number.

You mention that you "develop content the loads with the form/gallery data" - but yet you also said - "there is no Gallery"

So, the point I am making is that the red-circled items are all for your Child list...not the Parent list. So, all of that should be done in a Gallery that is outside of your form.

Getting data in and out of the gallery is very easy - don't worry about that at the moment. First get your "foundation" in place.

1. Numeric Parent ID column in Child List

2. Gallery outside of your form

3. All of the controls you have in the red-circle into your gallery template

4. An "Add" Icon or button to add new rows to your child list

5. An optional delete/trash icon in the gallery if you want to also be able to remove rows from the child list

Once you have that all in place, then the formulas will be very simple. I will give you those as well once you are ready.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes
Really want to show your appreciation? Buy Me A Cup Of Coffee!