Dynamic check box items based on selections from drop down or combo box

[aymanhassanmct]

Hello All,

Using PowerApps,  I need to create the following entry (Update) form) and also create the ideal Excel table on OneDrive to store data entered to this form.

The form should look like this:

Each product has its corresponding countries from total 20 options, and the data that needs to be manually updated is a date for each country. So when a product item selected, the list of countries change to fit the selected product. A user needs to first select a product, then check some or all countries, and enter dates in front of selected countries, and submit this to an Excel table (To be created). Then the process repeats with the same product and Lift ID as the dates are not always available for all countries at once, or with different product or Lift ID.

The products and selected countries currently looks like this:

Where 1 means this is supported country for that specific SKU or Product. The list of checkboxes in the PowerApps form should dynamically change to the selected countries that have the value of 1 for the selected Product.

Is this possible with PowerApps? How to create the form and the table that stores the data submitted by it?

Thanks,

Ayman

[v-bofeng-msft]

Hi @aymanhassanmct :

I made a demo and don't know if it meets your needs.If you have any questions, please point out.

I have two tables:

Table1:

Column1Country1Counrty2Counrty3

SKU1	1	0	1
SKU2	0	0	1
SKU3	0	1	0
SKU4	1	1	1
SKU5	1	0	0
SKU6	0	1	0
SKU7	0	1	1
SKU8	1	0	1
SKU9	1	0	0
SKU10	0	0	0

Table2:

PropductCountryLiftIDDateCheckBox

Propduct	Country	LiftID	Date	CheckBox

Step1:Add adrop down control(Dropdown3)

Item:

Table1.Column1

Step2:Add a gallery(Gallery7)

Item:

Filter(Split(
       If(LookUp(Table1,Column1 = Dropdown3.SelectedText.Value).Country1 = "1","Country1##") &         
       If(LookUp(Table1,Column1 = Dropdown3.SelectedText.Value).Counrty2 = "1","Counrty2##") & 
       If(LookUp(Table1,Column1 = Dropdown3.SelectedText.Value).Counrty3 = "1","Counrty3##"),
       "##"
       ),
!IsBlank(Result))

Step3:Add a Checkbox into this gallery(Checkbox1)

Step4:Add a DatePicker control into this gallery(DatePicker1)

Step5:Add a button

OnSelect:

ForAll(
    Gallery7.AllItems,
    Patch(
        Table2,
        Defaults(Table2),
        {
            CheckBox: Checkbox1.Value,
            Propduct: Dropdown3.SelectedText.Value,
            Date: DatePicker1.SelectedDate,
            Country: Result
        }
    )
)

When the user presses this button, new records will be added to Table2 one by one.

Best Regards,

Bof

[aymanhassanmct]

Looks good. I am testing it.

This will not post any data about unchecked countries. Right?

[v-bofeng-msft]

Hi @aymanhassanmct :

If so,you shoud set the button's OnSelect property to:

ForAll(
    Filter(Gallery7.AllItems,Checkbox1.Value),
    Patch(
        Table2,
        Defaults(Table2),
        {
            CheckBox: Checkbox1.Value,
            Propduct: Dropdown3.SelectedText.Value,
            Date: DatePicker1.SelectedDate,
            Country: Result
        }
    )
)

Best Regards,

Bof

[aymanhassanmct]

Thank you again Bof, I found out that I have more than 20 countries in the actual data.

Is there any simplified code instead of using If in the items filter in the gallery?

Thank you again.

Ayman

[v-bofeng-msft]

Hi @aymanhassanmct :

I am afraid there is no way to simplify the code. Because "country" is a field name, but there is no way to get the field name through a formula.

Although the formula is a bit ugly, it is easier to operate without extensive modification.

Bet Regards,

Bof

[aymanhassanmct]

The items code looks fine but the country option does not appear with each checkbox. I think I missed something critical. Please help.

[v-bofeng-msft]

Hi @aymanhassanmct :

Please add a label control into this gallery and set it's text proeprty to:

dropdown.SelectedText.Value

Best Regards,

Bof

[aymanhassanmct]

Thank you sir,

This code populated the selected value from the above box.

I need to shoe the country name which is the column header in this table.

Results now are (Option), I need tha labels of the checkboxes in the gallery reflect the countries in this table with the values of 1 for the selected SKU

Regards,

[aymanhassanmct]

Up Up