cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Microsoft
Microsoft

Create collection with variable columns based on User input data

I am building an app for filling out a form. The form contains 50+ fields with most not required (data privacy reasons). I'm storing any fields actually filled out by User into a Collection to send up as JSON to a flow. I don't want to send out all 50+ fields, only the ones that actually get populated based on controls such as radio button selection or textbox entries.

 

How can I achieve the end result? Thanks!

Example: 
User A -- Fills out first three questions so collection would be:
Collect(colMyForm,{col1: "Some Text", col2: "Some text", col3: "some text"})

User B -- Fills out questions 1, 2, and 5 (skipping over 3 and 4)
Collect(colMyForm,{col1: "Some Text", col2: "Some text", col5: "some text"})

6 REPLIES 6
Highlighted
Super User III
Super User III

Hi @MAV_CB ,

Try this

ClearCollect(
   colMyForm,
   If(
      !IsBlank(col1ControlName),
      {col1:col1ControlName.Text}
   ),
    If(
      !IsBlank(col2ControlName),
       {col2:col1ControlName.Text}
   ),
   If(
      !IsBlank(col3ControlName),
      {col3:col1ControlName.Text}
   )
)

 

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.

Highlighted

Thanks @WarrenBelz . Unfortunately that formula creates three rows of data, instead of just 1 row of data with three columns. I need to have the collection only create a single row of data but have varying number of columns based on user input. 

Ideally this would work where one row of data's columns vary on the IF statements. Unfortunately it looks like PowerApps doesn't support this so trying other means to iterate through the input controls to identify which to bring in. 

 

ClearCollect(
   colMyForm,
   If({
      !IsBlank(col1ControlName),
      col1:col1ControlName.Text
   ),
    If(
      !IsBlank(col2ControlName),
       col2:col1ControlName.Text
   ),
   If(
      !IsBlank(col3ControlName),
      col3:col1ControlName.Text
   )}
)

 

Highlighted

Thanks @WarrenBelz . Unfortunately that just creates three rows within the collection. I need to create one row of data, but with those three specific columns that are dynamic based on user input. 

Highlighted

Hi @MAV_CB ,

I have been around in circles with the below, either dropping all columns or dropping none, but strangely the If  logic is valid and works with other things. This may give you the direction I think you need to take. I have tried four collections with the same result.

ClearCollect(
    colTestFill,
    {
        col1: Test1.Text,
        col2: Test2.Text,
        col3: Test3.Text
    }
);
If(
    IsBlank(First(colTestFill).col1),
    ClearCollect(
        colTestFill,
        DropColumns(
            colTestFill,
            "col1"
        )
    )
);
If(
    IsBlank(First(colTestFill).col2),
    ClearCollect(
        colTestFill,
        DropColumns(
            colTestFill,
            "col2"
        )
    )
);
If(
    IsBlank(First(colTestFill).col3),
    ClearCollect(
        colTestFill,
        DropColumns(
            colTestFill,
            "col3"
        )
    )
)

 

Highlighted

Thanks @WarrenBelz . Unfortunately that logic did not work either as I have 50 attributes. I just ended up collecting all the data and setting NULL where I didn't have values. 

Highlighted

Glad you got it working @MAV_CB ,

A long way around, but that makes sense.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

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

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (13,074)