Can't convert text to a data entity (filter gallery on formula or collection)

Like (0) Share

Report

Posted on 19 Apr 2021 19:05:03 by RMDNA

100

I have a formula that filters a table and returns a single column of it, all text. However, when I try to put that block of code (or a collection that returns the same data) into a filter statement to populate a gallery, it gives me the error in the title: "can't convert text to a data entity." All I'm trying to do is filter a gallery by whether one of its fields is in the list of returned data.

If(IsBlank(drpDropdown),
Filter(gallery code, field_1 in [a list of text values returned by the formula]),
Filter(gallery code, field_1 in drpDropdown.Selected.Result)
)

The second half of the formula, filtering by the dropdown selection, works fine. It's throwing an error on the second line. Field_1 is a text field, the returned data should also be text.

Update: if I use "field_1 in ColCollection.field_1", I get "invalid schema: expected a one-column table." Can I transform the one-column collection into a one-column table to use as a reference?

Categories:

General Questions

All responses (5)

Answers (0)

RMDNA

100 on 29 Apr 2021 at 15:38:46

Like (0)

Report

Re: Can't convert text to a data entity (filter gallery on formula or collection)

Hi @v-qiaqi-msft. This will be a bit long, but I'm trying to include as much information as possible.

Table 'Project Team Members' and the relevant fields:

Display Name	Backend Name	Data Type	Notes
Bookable Resource	msdyn_bookableresourceid	Lookup	Use 'Bookable Resource'.Name
Project Approver	msdyn_projectapprover	Yes/No
project	msdyn_project	Lookup	Use project.'Project Name'

From this, I want to get a list of all projects a user is approved on, where 'Bookable Resource'.Name = User ( ). That part is working fine, using a view where Approver is set to "Yes".

ClearCollect(
ColUserApprovals,
 Filter(
 'Project Team Members',
 'Project Team Members (Views)'.ApprovedProjects,
 'Bookable Resource'.Name = VarCurrentUser.FullName
 )
)

The real issue is filtering my gallery by those results. The gallery is built off a different table, ProjectName is a field in that table, also a lookup. When I try:

Filter(
 [gallery code],
 Project.'ProjectName' in ColUserApprovals.project.'Project Name'
)

everything disappears.

This is so confusing to me, because I've gotten each of the components to work separately. Using data tables for example:

Unfiltered list of all projects in the gallery code:

List of projects I'm approved on: (these projects exist in the unfiltered list above):

When I try using the filter I just wrote:

I've got to be missing something. There's no reason this shouldn't work.

v-qiaqi@microsoft.com on 29 Apr 2021 at 07:01:36

Like (0)

Report
Re: Can't convert text to a data entity (filter gallery on formula or collection)

Hi @RMDNA,

Sorry for the late reply.

Could you please share more about your Dataverse table, field type of 'Bookable Resource'?

In addition, if you want to filter the Yes/No field, please refer to the following formula:

Filter('Project Team Members','Project Approver'='Project Approver (Project Team Members)'.Yes)

Please check if it could solve your problem.

Regards,

Qi
RMDNA 100 on 26 Apr 2021 at 15:48:46

Like (0)

Report

Re: Can't convert text to a data entity (filter gallery on formula or collection)

@v-qiaqi-msft just following up - any assistance on this?
RMDNA 100 on 20 Apr 2021 at 15:33:22

Like (0)

Report
Re: Can't convert text to a data entity (filter gallery on formula or collection)

Hi @v-qiaqi-msft - the basic table filter is below:

Filter('Project Team Members', 'Bookable Resource'.Name = VarCurrentUser.FullName && 'Project Approver' [=/in] "Yes" )

Then I need to make a list of field_1 from the resulting filtered table to compare against.

The issue I run into is that the 'Project Approver' column in Dataverse is typed as a "Yes/No", which I've read PowerApps doesn't play nice with. If I use "=", it's an incompatible type, and if I use "in", it returns no data.

If I can that field_1 list, I can drop it in the original formula below:

If(IsBlank(drpDropdown), Filter(gallery code, field_1 in [the list of field_1 outputs above], Filter(gallery code, field_1 in drpDropdown.Selected.Result) )
v-qiaqi@microsoft.com on 20 Apr 2021 at 07:31:10

Like (0)

Report
Re: Can't convert text to a data entity (filter gallery on formula or collection)

Hi @RMDNA,

Could you please share your formula that filters a table and returns a single column of it?

Actually, the returned result from the filter formula is a Table rather than a single column data.

I want to confirm with you that if this field_1 is the returned data from your filter formula?

If so, please just refer to the result from the filter formula:

If(IsBlank(drpDropdown), [a list of text values returned by the formula].field_1 , Filter(gallery code, field_1 in drpDropdown.Selected.Result) )

If my understanding is incorrect, please correct me and provide more details.

Regards,

Qi