How to make combobox with values from different table

[mati_gouda]

Hello, I'm super new to power apps and still sometimes struggling : D 

So here's the thing, I have an app that is integrated into power bi. It allows users to make an input into the table so essentially the app is sending a form. The form is looking up values from power bi and submitting form to sharepoint lists. 

The table I'm using is very simple, it has column with every possible combination of person name and month & year in given time + three columns with possible input values. Two numerical, one text. The text value is code for currency. 

LookUp(Agent_Targets_Table, Owner = Owner_combobox.Selected.Value
    && Month_year = MonthYear_combobox.Selected.Value)

SubmitForm(Agent_Target_Form)

But here's the thing. 

When combobox takes data from the same table that form sends values to - Agent_Target_Table, then it works. like on the below example. 

But it does not work when combobox takes values from different table and tries to apply this value in form to this sharepoint list

Agents is from Agent_Target_Table, Month & Year the same, Adult Target and YL Target are in the agent_Target_Table and are input numerical columns. Exam Target should be from different table [Currency_Table] and should be able to select possible currency. I tried different things and all what I get is blank. I click on dropdown and see nothing. It's happening as well when I change for example Agents value from Agent_Target_Table to [Agents_Table] (so essentially attribute table with agents). Always blank. 

How to do this how to fix this?  

[v-jefferni]

Hi @mati_gouda ,

Could you please share more details? Is Owner_combobox the name of Agents Combo box?

If this is the case, when you change Items of this Combo box, the following formula will not work as expected:

LookUp(Agent_Targets_Table, Owner = Owner_combobox.Selected.Value && Month_year = MonthYear_combobox.Selected.Value)

What Items would you exactly like to populate in the Combo box for users to select from?

Best regards,

[mati_gouda]

Hello, yes precisely, owner_combobox is the name of combobox with agents name. 

Agent can stay, as well as month & year, but I would like to have another combobox with items from different table -> Currency table, not agent_target_table. 

[v-jefferni]

Hi @mati_gouda ,

I don't understand. You already have an Agents Combo box for users to select agents, now you want to have another Combo box with items from Currency table, to do what? Form's Item property is now not related to other combo boxes.

Best regards,

[mati_gouda]

Hi @v-jefferni 

I want to have third combobox to select items from currency table, to allow users to select in what currency they want to input data. Once again but maybe more clearly:

1. I have a form that inputs data into the Agent Target Table. Table has combinations of agents and dates + two additional empty columns in number format (for the input from users)
2. Users select agent and data (from two comboboxes) and put the numerical input 
   
   #### everything above works flawlessly  ####
   
   
3. I want to add third combobox so the users could select currency code (like USD or GBP) but currency codes are stored in another table which only has currency info. I don't want to let users type the currency. I want them to select it. 
   
   
4. SUMMARY - I don't know how to "connect" third table to this form. it works when I'm using one table like in Agent Target Table. but when I want to include third table in the form, nothing happens (I'm including this column in visual in Power BI)

What I want to do: I have currency code table with currency codes like USD or GBP (Currency_Table) and I want to have combo box that takes the values from this table and allows users to select whatever currency code they want and write back this value to Agent_Target_Table

[v-jefferni · Accepted solution]

Hi @mati_gouda ,

OK, so you have a form in Item property has below formula:

LookUp(Agent_Targets_Table, Owner = Owner_combobox.Selected.Value
    && Month_year = MonthYear_combobox.Selected.Value)

In Agent_Targets_Table there is a Text column name Exam_Target. There is another table Currency includes currency code. Now you want to make the default Exam_Target Text input to be Combo box and allow users to select any currency code. 

If this is the case, then Item of the Form don't need to change. In the form, you need to unlock the Exam_Target Data card, delete Exam_Target Text Input box, add a Combo box and use the CurrencyCode column of Currency table as its data source.

Currency.CurrencyCode

After that, in Update property of Exam_Target Data card, use below formula:

CurrencyComboBox.Selected.CurrencyCode

 DefaultSelectedItems of Currency Combo box:

LookUp(Currency, CurrencyCode = ThisItem.Exam_Target)

Best regards,

[mati_gouda]

Thanks! With some tweaks here and there I was able to achieve what I wanted. Accepting as a solutions. All the best!