Dropdown

[PowerAppNewbi3]

Hi Everyone! 

Anyone can help me with the formula for the dropdown if i want specific items to be shown only after choosing a country.

Country (dropdown) [was using distinct(Table name, country) ]

Philippines

Australia

New Zealand

Code (dropdown) [don't know what formula to use]

10001 (i want this to be shown if Philippines was chosen on the upper dropdown) 

10002 (i want this to be shown if Philippines was chosen on the upper dropdown)

10003 (i want this to be shown if Philippines was chosen on the upper dropdown)

20001 (i want this to be shown if Australia was chosen on the upper dropdown)

20002 (i want this to be shown if Australia was chosen on the upper dropdown)

20003 (i want this to be shown if Australia was chosen on the upper dropdown)

30001 (i want this to be shown if New Zealand was chosen on the upper dropdown)

30002 (i want this to be shown if New Zealand was chosen on the upper dropdown)

30003 (i want this to be shown if New zealand was chosen on the upper dropdown)

Code 2 (text input) [when choosing the code 1, this part will be automated on the designated codes using excel database, i used the connectors]

Code 3 (text input) [when choosing the code 1, this part will be automated on the designated codes using excel database, i used the connectors]

Code 4 (text input) [when choosing the code 1, this part will be automated on the designated codes using excel database, i used the connectors]

I hope you can understand what im trying to do, if not i can clear it to you using the reply box. It's just, i've been working for thisnover a month now and still can't find the right formula. 

Thank you in advance ❤

[ManishSolanki · Accepted solution]

Hi @PowerAppNewbi3 

For Code dropdown, put the below formula in the 'Items' property:

Filter(Table({Code:"10001",Country:"Philippines"},{Code:"10002",Country:"Philippines"},{Code:"10003",Country:"Philippines"},{Code:"20001",Country:"Australia"},{Code:"20002",Country:"Australia"},{Code:"20003",Country:"Australia"},{Code:"30001",Country:"New Zealand"},{Code:"30002",Country:"New Zealand"},{Code:"30003",Country:"New Zealand"}),Country=CountryDropdown.Selected.Value)

Use the below formula in the 'Default' property of Code2 textbox:

LookUp(<ExcelDateSource>,Code=CodeDropDown.Selected.Code).Code2

Similarly, use the following formula in the 'Default' property of Code3 & Code4 textbox:

LookUp(<ExcelDateSource>,Code=CodeDropDown.Selected.Code).Code3

LookUp(<ExcelDateSource>,Code=CodeDropDown.Selected.Code).Code4

Plese modify the name for exceldatasource and code columns as per your need & let me know if you face any problem.

Please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[PowerAppNewbi3]

Hi Manish, 

Thank you for the assistance. The first part was a success, thankfully. Yet the second part where it needs to auto populate the codes, it's highlighting the code dropdown in red. 

Let me share you the names of it and show the formula i am using. 

Excel Name: Table 1

Code Name: Finance Code

Code Dropdown: Finance Code1

Code 2: Business Unit

So what i did under default was, 

Lookup(Table1, 'Finance Code'='Finance Code1'. Selected. Finance Code1). Business Unit

The error was on after the equal sign, it is highlighted in red as it says it can't be read. Also questioning why 'Finance Code1' is the only available choices to inout whereas in your notes, it says 'code' after the Selected which refers to Finance Code right?.

Anyways, if you could help me thia one, i would really appreciate it!. 

Thank you in advance 🙂🙂

[ManishSolanki]

Hi @PowerAppNewbi3 

Pls modify the formula as mentioned below:

Lookup(Table1, 'Finance Code'='Finance Code1'. Selected. Code).Business Unit

To answer your query on Selected formula, I have used a collection of records with two columns 'Code' & 'Country' in the 'Items' property of 'Finance Code1' drop down so used 'Code' column name to retrieve selected the selected value. 

Pls let me know if you still face any problem & remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[PowerAppNewbi3]

Hi Manish, 

It was my error on the code part, it seems i use the wrong name when inputting the dropdowns. I used 'Finance Code1' rather than 'Fianance Code' which explains why the 'Finance Code 1' is the only visible. Yet still having trouble with the after the equal sign, it is still in red and highlighted. 

Lookup(Table1, 'Finance Code'='Finance Code1'. Selected. 'Finance Code'). 'Business Unit'

[ManishSolanki]

Hi @PowerAppNewbi3 

I would request you to please share the code in 'Items' property of 'Finance Code 1' dropdown.

Thanks

[PowerAppNewbi3]

Hi Manish, 

I don't think it is allowed as i tried replying many times but it is getting deleted. Basically what I've done is:

Filter(Table({Finance Code: "10001", Country: "Australia"}), Country=Country1.Selectrd.Value)