Best Way to Filter or best way to design tables in Dataverse

[rprahalad]

Hello All,

I have  two Tables in Dataverse: 'Queue' and 'QueueDetails'. It's a 1-Many relationship between them and so I have a LookUp QueueId column in the QueueDetails table. Each table also has its own separate set of other columns.

I have a 3rd transactional table called Tasks where I have a LookUp column of QueueId pointing to the Queue table,

Now, I have a free text search page where a user can enter a text or a Date and I have to search on a bunch of columns in each of the 3 tables. I have to tee off of the Tasks table because that's the main table the users are searching on and a record "has" to exist on this table to be included in the Search Results. 

What's the best way to write the Filter statement to Search on all these 3 tables efficiently?

I started off with

ClearCollect(

colTaskSearchResults,

Filter(Tasks, txtSearch.Text in new_keywords || txtSearch.Text  in new_queueid.queuetitle) 

);

and it gave me a Delegation warning for using the OR operator on a LookUp column. Is the preferred way to denormalize the Tasks table and store additional redundant data from the other 2 tables as well - despite having the lookup column, so that I can find it all in one table to aid my Search screen? 

Basically its a 3 table left join and  I am trying to fire this SQL Server equivalent of

SELECT t.col1, t.col2, t.col3

FROM tasks t

LEFT JOIN Queue Q on t.QueueId = Q.ID

LEFT JOIN QueueDetails qd ON qd.QueueID = q.ID

WHERE ((t.col1 like '%txtSearch.Text%') OR (Q.Title like ''%txtSearch.Text%')  OR (QD.Description like '%txtSearch.Text%' ))

Thanks!

-Ram

[rprahalad]

I think I will just create a View in Dataverse and get on with it. The lesson I have learnt with PowerApps is, anything complicated data tables wise, just create a View. Or If  you are stuck wondering how to do complicated joins , shop around for a View  or denormalize the table and live happily ever after. 

[HSheild]

Hi @rprahalad , looks like you’ve given yourself some good advice. You want to avoid the delegation warnings in the Canvas app and the best way to do this is to create views in Dataverse and let it do all the hard work, rather than in the Canvas app.

[rprahalad]

But I was just curious @HSheild , how does one work around this delegation issue? Like in my case, I am using DataVerse and the ONLY non delegable function I am using here is the "in" function. 

Filter(Tasks, txtSearch.Text in new_keywords || txtSearch.Text  in new_queueid.queuetitle) 

So, If I have a simple one-many tables and if I have to search for a text in table1.col1 and its related table2.col2 columns, how does one go about it? I am just very curious. I am still very new to PowerApps. 

I was thinking I do a ClearCollect on Table 1 (One side of One-Many) first based on the search string entered  and another ClearCollect on Table 2 (Many side of One-Many) next with the same search string. Now I have to show table 1's data from table 2's search results and eventually merge the two results. How does one do it? Thanks!

[rprahalad]

I am just trying to learn how others do it.  Like, I am begining to think that these LookUp columns are a pain! Instead I can just store the ID value and not have a link to the related table. That way I can do an IN with a collection. 

Is that a good practice? Should we always have to think LookUp or can we think in terms of storing scalar ID values?  Yeah, it is more  coding if the user decides to delete a row in the master table. But sure 2 lines of extra code won' do any harm. 

Like, I have resolved this particular problem on this thread by creating the view. But I am trying to get a grasp of overall "hacks" and best practices.

Thanks 

[rprahalad · Accepted solution]

I created a Views  and denormalized columns and stored a comma delimited string on the base table itself. That way I avoided everything and just had to deal with either a view or a single base table.