cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Filter Gallery From Multiple Sources

OK. I have been looking for a resolution but not able to find.
Hoping someone could provide some direction.

 

On my screen, I have 2 dropdowns and a gallery.
The Gallery displays records (TaskName) which are filtered by the 2 dropdowns, (Location and Frequency) from the CAMT table.
CAMT table has TaskID, TaskName,Location and Frequency columns.

When you click the right arrow, it Patches the Location, Frequency, TaskID and Datetime to the CAMR table. (note, different table)

What I would like to do is:
Once the record for this task has been patched on today`s date to the CAMR table, remove it from the gallery so it can`t be done twice in one day.

 

In essence, removing tasks from the list which have been done today.

If anyone is able to provide some suggestions it would be greatly appreciated.

Really having trouble figuring out how to filter with these 2 tables.

 

18 REPLIES 18

Yes you can use the Value() or Text() function, basically like this :

Value("1") = 1

Text(1) = "1"

 

I suggest you to convert your varchar column, so let say that it is the CAMR_Task column tha tis varchar, you should have something like this :

Text(LookUp('[dbo].[WHS_CAMRecords]',Value(CAMR_Task)= ...

 

Théo 

View solution in original post

Theo,

OK. some progress.

I added the value(), but it is still getting blue line to the left side of <>.

I added the 2 labels. first shows 11/29/2018 and second 12/5/2018.

Ok, it seems that the date are formatted the same way, meaning that they can be compared.

 

Do you have any record in the CAMR table that have today's date on the CAMR_Date column ?

 

Can you add OnVisible of your Screen : 

Set(varToday,Today())

And in the Filter formula replace Today() with varToday (it should remove the delegation warning)

(don't forget to go to another screen, and comeback to this one to perform the OnVisible actions) 

 

Théo

I have added the varToday variable, and changed the filter, but the blue lines still there under:

Text(LookUp('[dbo].[WHS_CAMRecords]',Value(CAMR_Task)=CAMTask_ID,CAMR_Date),DateTimeFormat.ShortDate)<>

The delegation warning says:
The "LookUp" part of this formual might not work correctly on large data sets. The data source might not be able to process the formula and might returan an incomplete data set. Your application might not return correct results or behave correctly if the data set is incomplete.

 

I refreshed the data sources again just to check, but no go.

Theo,

Thank you so much for your help.

I ended up fixing the DB side and rearranging my screens.

Now it is working as expected, just really slow.

This part of the Items property for the Gallery is still in blue though.

Text(LookUp('[dbo].[WHS_CAMRecords]',CAMR_Task_ID=CAMT_Task_ID,CAMR_Date),DateTimeFormat.ShortDate)<>

 

Is there any way to speed this up?

 

How many items do you have in your tables ?

Theo,

I was able to speed this up by pulling the data into a collection and then running the query on the collection.
works great.

Thank you for all your help.

Hi Theo,

I have just revisited this and found that if I only have 1 record for the task, being today, then it works fine.

However, if I have say one from yesterday and one today, it doesn`t remove the task from the gallery.

There is currently 4 records in the database.

It will grow in time, but normally by 2 or 3 a day.

The blue underline is still showing.

The Items property of the gallery looks like this:

Filter('[dbo].[WHS_CAMTasks]',
Text(LookUp(CAMR,CAMR_Task_ID = CAMT_Task_ID,DateAdd(CAMR_Date,-10,Hours)),DateTimeFormat.ShortDate) <> Text(varToday,DateTimeFormat.ShortDate) && CAMT_Location = CAM_LocationDD.Selected.Value && CAMT_Frequency = CAM_FrequencyDD.Selected.Value)

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (20,219)