Error while filtering data between two data card (Combobox)

[tusharmehta]

Hello Everyone, 

I am creating a power app using SharePoint list and I have two shares point lists: 

Profit_center_index

Profit_cent_id, 

Profit_cent_name - Display in the first data card 

Busineessunits_index

bu_profit_cent_id

bu_id,

bu_name - Display in the second data card 

The first data card (Combobox) allows the user to select the profit center name and base on the selection Profit_cent_id  must store as a value/variable to filter the second data card. 

In the second data card bu_profit_cent_id = Profit_cent_id  will be mapped and all the bu_name which are matching must be displayed on combobox. 

To store profit_cent_id as the value I have done a couple of tests: 

Filter in businessunits data card (items) but I got error near = sign

Error:

incompatible type for comparison. These types can't be compared: Number, Table 

Filter(
businessunits_index,
bu_profit_cent_id = profitcenter_index.profit_cent_id
).bu_name

The second option I used with the first data card onchange option 

Set(pid,
Filter(
profitcenter_index,
proft_cent_name = DataCardValue2.Selected.proft_cent_name
).profit_cent_id
)

Error:

incompatible type for comparison. These types can't be compared: Number, Table 

In both above options, I am getting the same error 

So can I know where is the problem and what I am doing wrong? 

Thanks, 

[RusselThomas]

Hi @tusharmehta ,

It looks like you're trying to be explicit in showing only the specific field you want when you actually just need the record sets matched through the field lookup - the control will allow you to select the field you want to display with the result.  

I'm not sure you need to set a variable for this, so maybe start without it and only set it if you need it for something else?  Also, are you wanting to show filtered results from the first table or the second - because your formulas above do both...?

Assuming you want to show results from "Businessunits index" filtered by bu_profit_cent_id which matches a corresponding Profit_cent_id lookup on "Profit center index", then on your second dropdown/combo Items: property;

Filter(
businessunits_index,
bu_profit_cent_id = DataCardValue2.Selected.profit_cent_id
)

With the records filtered, you can then set the Value of the field to display on the combo properties instead of trying to specify it in the formula.

Hope this helps,

RT

[tusharmehta]

Thanks, RusselThomas, 

I had done and I was able to filter the data and it is working as expected.  

While submitting the form profit center value is getting saved but in the second value in which we used to filter, the SharePoint list value is blank. 

The difference which I have found: 

Before writing filter on items paramters (data is getting saved)

Choices([@ledger_ac_transaction].businessunits_name)

After we replace choices with filter (Data is not getting saved and value is blank).

Does this because we replace filters and that had converted value and it is not getting saved? 

Please let me know if that is correct than what could be the correct statement for filter data as well save data to Sharepoint List as well. 

[RusselThomas]

Hi @tusharmehta ,

So we initially had two lists - Profit_center_index and Busineessunits_index

The control however seems to have been getting it's list of Items: from a choice column on another list - ledger_ac_transaction.

Choices([@ledger_ac_transaction].businessunits_name)

This means that in SharePoint there is a choice column which uses a SharePoint defined list of values to capture data into that column.  The Items: list is therefore coming from those items defined for that choice column.

Is that your intent?  I mean, is there a problem with the list of choices in that column that you want to filter from somewhere else?

Or do you want to filter the list of choices that are there?

If so, is there a link between ledger_ac_transaction and Profit_center_index, like there is  between Profit_center_index and Busineessunits_index?

At the end of the day, the Update: property of the card determines what get's saved back to your source, so if you were using a dropdown with a single selection for the second list of items then it should read something like this;

secondDropdown.Selected

If you are managing the list of items using the choice column in SharePoint, then you may want to rather filter that.  If you're getting your list data from another source and try and save something that doesn't currently exist in the Choices of the SharePoint column - you will get an error.

Kind regards,

RT

[tusharmehta]

Hello Russel, 

In profit_center_index I have following fields: 

• profit_cent_id
• profit_cent_name
• profit_cent_currency 

In businessunits_index I have the following fields

• bu_id,
• bu_name,
• bu_profit_cent_id,      (one profit center has more than one business units) 
• bu_region 

Above mentioned both lists is having a common field (bu_profit_cent_id - profit_cent_id)

Third ledger_ac_transaction I have the following fields: 

• account_description
• date
• profit_cent_name (lookup) (This will be populated when the user enter a new entry)
• businessunits_name (lookup) (This will be populated when the user enter a new entry)
• there are more fields like approver status, approver name, approver date, etc. 

The requirements: 

When the user start a new entry he will select the profit center from the first Combobox and base on the selection second Combobox (business units list will be populated and the user will select one from the list and submtit the entry. 

While submit form: 

in ledger_ac_transaction I want to store all the values and in this list profit_cent_name and business_name will be from two Combobox. 

I may be taking more than require time because I am new to SharePoint and getting used to it.

[RusselThomas · Accepted solution]

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

[tusharmehta]

Highly Appreciated Russel Thomas for providing an explanation with options. 

Especially I am new so it will be this will motivate me and this notes will be useful for future referance as well. 

After reading the details it looks that this will work for me but let me go slowly and read the instruction and perform the test.

Once again, Russel, I am very happy to read the responses they are motivating and pushing me to work harder and build one working application demo and recommend products to use in our daily office activity.   

Hopefully, after following step-by-step instructions I will be able to build a demo without any issues, and if anything I will get back to you with my query. 

God Bless you, Russel. 

[tusharmehta · Accepted solution]

Hello Russel, 

I am back with the result. 

Option one was not displaying the second field on Combobox was not displaying (pc_id) even it was not an error so I had tried in the new screen same thing but had no success so finally I had tried option 2. 

Couple of things I had noticed while testing option 2: 

• With original lookup, fields were with new single text field data was not saving so I had removed all the lookup fields and submitted the data it starts working with option 2. I don't know is it normal or maybe because of some system setting it is happening. 
• I am getting a yellow warning sign with both Combobox (delegation warning the search part of this formula might not work correctly on large data sets)
  Other than the above notes everything is working fine. 

Thank you very much. 

[RusselThomas]

Hi @tusharmehta ,

Delegation warnings appear when we use functions in our filters that the source can't understand.  In these instances, the query cannot be delegated to SharePoint - so SharePoint will instead just return the first 500 rows of data, then PowerApps will apply the query on that set of data.

If your tables have more than 500 rows, then this can be a problem. 

If your tables don't have more than 500 rows (and if they won't ever have more than 500 rows) then you can ignore the warnings.

If they do have more than 500 rows, or you suspect they may grow beyond 500 sometime in the future, then you should see if you can reconstruct your filters in such a way that they support delegation.

That said - you will always get warnings with Combo boxes connecting to SharePoint without adding any fancy filter functions because combo boxes have a built-in "Find" feature that actually uses the Search() function in the background.  The Search() function is not delegable with SharePoint (or anything else at this point) - so as soon as you connect a combo to SharePoint you'll see the yellow warning pop up.

Because it's built in, you either have to switch IsSearchable: off to remove the warning, use a collection instead of a source like SharePoint, or just ignore it.

May I ask, how many rows exist in your profit_center and business_unit lists?

Kind regards,

RT

[tusharmehta]

the number is less than 500. I will stop the search option.