Linking 2 columns

[RvdHeijden]

I was wondering if it's possible to 

I've got an app where you can add items to a sharepoint list, nothing spectacular.

When adding a new item to the list there are a few fields you have to fill in, 2 of those items are choice columns

The easiest way to describe it that it's a Category column and a Subcategory column. When you choose a Category the choices in the Subcategory should be a short list based on the chosen Category. I know it's not possible to link both columns in Sharepoint.

But is it possible in Powerapps to somehow link the choices in the subcategory to the Category column when filling in a new row ?

Category                                                                                             Subcategory

A	A1
A	A2
B	B1
B	B2

so when I add a new item to my list and choose Category A the only items shown in Subcategory should be A1 and A2.

I've got it to work in a gallery because then I could use distinct based on items in a list but now I create a new row so it doesn't work

[WarrenBelz]

Hi @RvdHeijden ,

I think I am reading this correct - you just want a second drop-down showing A1 & A2 (in this case). Replace xxxx with the output of the first drop-down

Filter(
   SPListName,
   Category = YourCategoryDDName.Selected.xxxx
).Subcategory

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.

Visit my blog Practical Power Apps

[RvdHeijden]

@WarrenBelz I'm not sure but I don't think this is the solution but I'm not fully understanding your solution.

I have 2 columns; Mainproject and Subproject in my Sharepoint list (I called it Category and SubCategory)

When I select Mainproject A I want the other dropdown to only show Subproject A1 and A2 but there is no relationship between the columns especially when adding a new item to a list.

I think the only option is to hardcode the options in the items property of the column Subcategory.

So basically (my formula is wrong but you get the idea (i hope):

Item property of Subprojects

If(
ThisItem.Mainproject.Value="MainProject A";["Subproject A1","Subproject A2"];
ThisItem.Mainproject.Value="MainProject B";["Subproject B1","Subproject B2"];
"Unknown"
)

I need to generate a choice column based on the value in Mainproject. But that hardcoded value has to be saved back to SP in that column

[WarrenBelz]

Hi @RvdHeijden ,

Yes - if there is no relationship, you need to hard-code them, although you could have a list (or even a collection OnStart) with the two fields and all the combinations, then filter on that.

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.

Visit my blog Practical Power Apps

[RvdHeijden]

@WarrenBelz @How do I do that ?

[WarrenBelz]

@RvdHeijden ,

At App OnStart 

ClearCollect(
   colCategories,
   {Category: "A", Subcategory: "A1"},
   {Category: "A", Subcategory: "A2"},
   {Category: "B", Subcategory: "B1"},
   {Category: "B", Subcategory: "B2"}
)

First drop-down Items

Sort(
   Distinct(
      colCategories,
      Category
   ),
   Result
)

Second drop-down Items

Sort(
   Filter(
      colCategories,
      Category = ddName1.Selected.Result
   ).Subcategory,
   Subcategory
)

> 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.

Visit my blog Practical Power Apps

[RvdHeijden]

@WarrenBelz unfortunately this isn't working or I'm doing something wrong. 

Somehow it doesn't accept the collection in my formula

These items are part of a Form which is linked to a datasource so when I change the Items property it doesn't get the collection. It's referring to a datasource

But when I run the OnStart option in the App it doesn't collect the unique items in the column MainProject

When I press the Run OnStart it should generate the collection right ?

[WarrenBelz]

Hi @RvdHeijden ,

Can you please post the exact code you are using OnStart and the Items of the two combo boxes (in Text) - what I posted works on test examples.

[RvdHeijden]

@WarrenBelz This is the code OnStart

And the itemsproperty for Mainproject is Choices([@'FttH Schouwfouten rapportage'].'Mainproject')

And the itemsproperty for Subproject is Choices([@'FttH Schouwfouten rapportage'].''Subproject')

ClearCollect(
    ColMainproject;
    {Mainproject: "Project A"; SubProject: "A1"};
    {Mainproject: "Project B"; SubProject: "B1"};
    {Mainproject: "Project C"; SubProject: "C1"};
    {Mainproject: "Project C"; SubProject: "C2"};
    {Mainproject: "Project C"; SubProject: "C3"};
    {Mainproject: "Project C"; SubProject: "C4"};
    {Mainproject: "Project C"; SubProject: "C5"};
    {Mainproject: "Project C"; SubProject: "C6"};
    {Mainproject: "Project C"; SubProject: "C7"};
    {Mainproject: "Project C"; SubProject: "C8"};
    {Mainproject: "Project C"; SubProject: "C9"};
    {Mainproject: "Project C"; SubProject: "C10"};
    {Mainproject: "Project C"; SubProject: "C11"};
    {Mainproject: "Project C"; SubProject: "C12"};
    {Mainproject: "Project C"; SubProject: "C13"};
    {Mainproject: "Project C"; SubProject: "C14"};
    {Mainproject: "Project C"; SubProject: "C15"};
    {Mainproject: "Project C"; SubProject: "C16"};
    {Mainproject: "Project C"; SubProject: "C17"};
    {Mainproject: "Project D"; SubProject: "D1"};
    {Mainproject: "Project D"; SubProject: "D2"};
    {Mainproject: "Project D"; SubProject: "D3"};
    {Mainproject: "Project D"; SubProject: "D4"};
    {Mainproject: "Project D"; SubProject: "D5"};
    {Mainproject: "Project D"; SubProject: "D6"};
    {Mainproject: "Project D"; SubProject: "D7"}
)

[WarrenBelz]

HI @RvdHeijden ,

First Combo Box Items would be

Sort(
   Distinct(
      colMainProject,
      MainProject
   ),
   Result
)

Second CB Items

Sort(
   Filter(
      colMainProject,
      MainProject = FirstComboName.Selected.Result
   ).SubProject,
   SubProject
)

Update of first Data Card

FirstComboName.Selected.Result

Update of the second Data Card

SecondComboName.Selected.SubProject

DefaultSelectedItems of first (I have assumed your list field names here as being the same as your collection)

{Result: ThisItem.MainProject}

DefaultSelectedItems of second

{SubProject: ThisItem.SubProject}

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.

Visit my blog Practical Power Apps