Hi @tusharmehta ,
Ok, so just to make sure I understand - you're saying your Sharepoint column types for profit_cent_name and businessunits_name in the ledger_ac_transaction list are already Lookup column types to those lists?
If so, then filtering the comboBoxes with the sources directly disregards the lookup function of the SharePoint Column - which is why trying to save a result from that is blank.
My opinion: When it comes to building Power Apps from SharePoint lists, lookup columns are just headaches waiting to happen, so always go for simple column types and rely on Power Apps to control the user experience. If you're stuck with them however, then hopefully this still helps you.
I should add, that unless you actually need the columns to be lookup columns in SharePoint, you can make your life a whole lot easier by removing them and instead recreating these two columns to be plain text columns. It will save you a lot of headaches. If for some reason you absolutely need them to be lookups, then forge ahead. I'll answer in two parts - one for lookup columns, one for plain text columns.
I'd also suggest, before you continue, that you create a new form so we can start everything again from default and not have to worry about previous changes.
#Part1: Keeping your lookup columns
So, if we're using lookup columns, we must use the results of a Choices() function to ensure what we save back to SharePoint matches up with what SharePoint expects for that column type. This is why our Filter() function direct on the source is coming up Blank when we submit it. It's not the bu_name we need to store, it's actually a reference to a record (usually just the ID of the record) that contains the value and SharePoint is just displaying the value from a specific column in that record based on your column configuration. The Choices() function allows us to pick a record from a list and the control ensures the correct reference is stored back in SharePoint. Therefore, we're stuck with it.
It is possible however to Filter those choices - but if our filter depends on another column, (like profit_cent_id), then this is a problem. By default the Choices() from a lookup column only contain the actual column values, not the entire record - meaning profit_cent_id is not available to us to go perform filters on. Initially, our Choices() table coming from
Choices([@ledger_ac_transaction].businessunits_name)
looks like this;
We want to filter that based on the profit_cent_id associated with each bu - but it's not there for us to filter. Luckily, if our bu_names are unique, we can use these values to go and fetch their corresponding profit_center_id values using AddColumns() and our own LookUp() function in PowerApps.
This is very inefficient, as not only is SharePoint fetching the choices initially, but we're now also performing our own lookup for each Value to the same source list which makes every call redundant - so - highly inefficient, but doable.
So first, let's look at how we add the columns we need [just for reading, you don't need to set this anywhere just yet];
AddColumns(
Choices([@ledger_ac_transaction].businessunits_name),
"pc_id", LookUp(businessunits_index, bu_name = Value, profit_center_id)
)
This should give us the following list for our bu_name Combo box
Note: This will only work properly if your bu_names are unique in businessunits_index list.
Now that we have some way to filter our choices, we can wrap the above function in a Filter function. Set the buCombo Items: property to this;
Filter(
AddColumns(
Choices([@ledger_ac_transaction].businessunits_name),
"pc_id", LookUp(businessunits_index, bu_name = Value, profit_center_id)
),
pc_id = pcCombo.Selected.ID
)
If you created a new form like I suggested up front, then the defaults for the card Update: property should remain. Now if you submit the form, it should work?
#OPTIONAL Part2: using plain text columns
If you'd like to clean things up a bit, and if you expect people to only use your Power App to capture data into your list, you may want to use plain text columns to store the data instead of lookup columns.
You can't convert lookup columns, so you have to create new columns to hold the data.
To be safe, I'd rather add two plain text columns with slightly different names, and once everything is working fine and you're happy with the results, then you can remove the lookup columns...so for now, let's assume your ledger_ac_transaction list now has two additional columns that are plain text - pc_textname and bu_textname.
With the new columns added, refresh your ledger_ac_transaction source in PowerApps by selecting data sources and then clicking the ... menu next to ledger_ac_transaction and click Refresh.
Then, add an Edit form, connect it to ledger_ac_transaction and set it's default mode to New.
Because they are plain text columns, the pc_textname and bu_textname DataCards will default to Text Input controls, and the card's Update: property will point to each Text Input's .Text output by default. There are few ways to change this to suit our combo needs, but I'll opt for the cleanest - which means removing the Text Inputs, adding Combo's and changing the DataCard Update: values to point to the Combo's instead.
pc_textname DataCard
Select the Card, right click and select "Unlock"
Select the Text Input and copy it's name - we're going to reuse it
Delete the Text Input - ignore any errors for now. Insert > Input > Combo box.
Ignore the "Select Data Source" popup and edit the Items: property in the formula bar to;
profit_center_index
Rename the ComboBox and paste the name of the TextInput (it should be something like DataCardValue with a number eg: DataCardValue23). For reference in my formula here I'm going to use "pcCombo" as the name, just so it's easier to reference - wherever you see pcCombo in my formula just replace it with the control name you copied for the profit center combo box.
In the advanced properties of pcCombo, set the following fields;
- DisplayFields: ["profit_cent_name"]
- SearchFields: ["profit_cent_name"]
Select the data card, set the Update: property to;
pcCombo.Selected.profit_cent_name
If you copied the original name of the Text Input and renamed the combo then you shouldn't see any other errors.
If you still see errors on the card, they are likely references to the TextInput control that was there. For example, the error text usually hangs underneath the Text Input with a Y: property of DataCardValueNumber.Y + DataCardValueNumber.Height. If your text input is gone and your combo isn't called DataCardValueNumber, then you'll get errors.
To fix these, either go and update the reference DataCardValueNumber to whatever you called your combo, or just name combo DataCardValueNumber and the errors will be fixed.
bu_textname DataCard
Select the Card, right click and select "Unlock"
Select the Text Input, copy it's name, then delete the control - ignore any errors for now.
Insert > Input > Combo box.
Ignore the "Select Data Source" popup and edit the Items: property in the formula bar to;
Filter(
businessunits_index,
bu_profit_cent_id = pcCombo.Selected.profit_cent_id
)
Rename the ComboBox and paste in your copied name from the Text Input - Mine will be called "buCombo" for reference, just replace this with your name wherever you see it.
In the advanced properties of pcCombo, set the following fields;
- DisplayFields: ["bu_name"]
- SearchFields: ["bu_name"]
Select the data card, set the Update: property to;
buCombo.Selected.bu_name
That's it. Hope this helps you!
RT
