Filtering a datasource based on multiple many to many relationships

[david00239]

I have a datasource timeline. Each entry may be related in a many to many relationship with book entity and writer entity. I have two comboboxes where the user can select the records. I want the related records to be displayed in a gallery. It slightly works using combobox.selected.timeline (timeline is also the name of the many to many relationship). But I can not figure out how to filter using both criteria. 

I tried something like Filter(timeline; ThisRecord in combobox1.Selected.timeline && ThisRecord in combobox2.Selected.timeline), but it does not retrieve the Records correctly.

Can someone help me?

[BCBuizer]

Hi @david00239,

What type of data source are you using (SharePoint / Dataverse / other?) and how many records does the timeline data source have?

The reason I'm asking is because this is most likely an issue cause by delegation, or lack thereof since the in operator is usually not delegable. As a result only the first 500 (by default, but this can be raised to 2000) records in the data source will be taken into account when executing the formula.

[david00239]

Dataverse. Right now timeline has 2 entries. 

[BCBuizer]

Hi @david00239 ,

Please try this formula for the Items property of the gallery then:

Filter(
    timeline; 
    timeline in combobox1.Selected.timeline && timeline in combobox2.Selected.timeline
)

[david00239]

I tried but the gallery does not recognize the data.

[BCBuizer]

Hi @david00239 ,

All right, then let's try this:

Filter(
    timeline; 
    combobox1.Selected.timeline in timeline && combobox2.Selected.timeline in timeline
)

If this doesn't work, can you please provide a little more context of how this is supposed to work? Is the formula for the Items property of a gallery, or for the items property of a nested gallery? A screenshot always helps.

[david00239]

It did not work, since it said it did not allowed tabular values in the context

combobox.Selected.timeline

However, the code you suggested gave me an idea that started to work.

Filter(

    timeline;

    First(combobox.Selected.timeline).timeline = ThisRecord.timeline ) 

 (the timeline that follows ThisRecord is the Primary column of the timeline table)

But combobox.Selected.timeline has many items, so I used an ForAll for this to work on every item.

Filter(

   timeline;

    true in ForAll(combobox.Selected.timeline As A; A.timeline=ThisRecord.timeline))

However, the in gives a delegation warning. The number of related records will never be higher than 300 I believe, so I think this will not be a problem. But I have another issue, I need this code to work consider combobox.SelectedItems. I tried to do a nested ForAll but this is not working.

The gallery will present data fed from Dataverse tables. Let's say I have a table timeline. Each entry may be related in a many to many relationship with book and writer tables.

[BCBuizer]

Hi @david00239 ,

Can you perhaps share a sample dataset of what it is you are working with to provide a clearer picture?

As a side note: the delegation limit is not related to the number of related records, but to the number of records in your data source. In case your formula is not delegable, it will not look past the first 2000 records in your data source. Having said that, there are some workarounds, but in order to say if those apply to your, first I need to understand how your data is structured.

[david00239]

I was able to achieve a code that works. However, for this to be perfect I needed this to work for all the selected Items, and not only for the current selected one. I need to find a way to iterate over all the records of the selected Items.

Sort(Filter([@TimeLine];

        (ThisRecord.timeline in  ShowColumns(Combobox1.Selected.timeline;new_timelineid) || IsEmpty(Combobox1.SelectedItems))) ;'Created On';SortOrder.Descending)

I do not have data yet. 

[BCBuizer]

Hi @david00239 ,

Without any context I'm just making wild guesses, so if you can give any, that would be very useful.

In any case, here's the last wild guess:

Sort(
	If(
		IsEmpty(Combobox1.SelectedItems),
		[@TimeLine];
		With(
			{ _Items: 
				Distinct(
					ForAll(
						Combobox1.SelectedItems As FilterItems;
						Filter(
							[@TimeLine];
							ThisRecord.timeline in  ShowColumns(FilterItems.timeline;new_timelineid)
						)
					);
					timeline
				).Value
			};
			Filter(
				[@TimeLine];
				ThisRecord.timeline in _Items
			)
		)
	);
	'Created On';
	SortOrder.Descending
)	

Basically this loops through all selected items in the combobox and retrieves the related records. Since this will cause duplicates, a Distinct function is used to remove any. Since the use of the distinct function will only return a single column, a second filter function is used to get a table with the full data.