Filter Gallery From Multiple Sources

[kevinsray]

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.

[tchin-nin · Accepted solution]

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 

[kevinsray]

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.

[tchin-nin]

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

[kevinsray]

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)<>

[kevinsray]

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.

[kevinsray]

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?

[tchin-nin]

How many items do you have in your tables ?

[kevinsray]

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.

[kevinsray]

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)