Search - Missing Dates from the Entered Data

Hi all,
I am trying to create a search view (can be similar to Search multiple columns with different criteria) where I need to show the dates, between the two selected dates, where I have no data.

I have a table collecting daily attendance data. Based on the different criteria like Department, Manager, I would like to show the search result which has the dates on which there is no data!

Thank you.

Hi, do you have those records but without data in your table?

For that you would need another table with ALL dates that relates back to your main table. From there you can COUNT() the [Related Records] to your main table and find the dates with zero related records.

HI @Aleksi,
No. I have the dates only where data is entered.
The search just shows the dates (between the Start_Date & End_Date fields) where there is no data.

Thank you.

My question is, is there a way to just filter / list- / select the dates where there is no data between the selected dates?
If not, I just need to know a way where there is a list which shows all the dates between two dates, from where I shall subtract the dates which are not empty.

Thank you.

Then it could be something like…
SELECT(Table[KeyColumn],AND([DATE]>[_THISROW].[START],[DATE]<[_THISROW].[END],ISBLANK([Column])))

2 Likes

You can’t search for a row that doesn’t exist. But you can add a table with a new row for each date and search from that table. The feature you want can not be done without adding a new table, just like in my sample app you referenced.

  1. Add DateSearch table with one row for every date
  2. Add a Ref-Type virtual column to your main table, and relate it to the DateSearch table
  3. the Ref_Rows column will be generated automatically in your DateSearch table
  4. Add a virtual column in DateSearch to COUNT() the Ref_Rows

Another Approach:
If you don’t want to add a new table, another approach could be using a formatting rule to highlight entries that are more than a day away from the previous/next entry. This won’t give you a full list of dates, but it would draw attention to gaps.

3 Likes

Wow…this is appealing!
Thank you.

1 Like

I will try this also.
Thank you @Aleksi.

Actually, I misunderstood your reply. Then solution by @GreenFlux is the one you are looking for.

Okay. I think I need to make it clear for u to help me better.

I am looking to have a Search_View where the User searches by Name or Manager name,
The results would be the list of total no. of dates (days) where it is empty.

I have two tables,
Parent - Just to get the basic details of the Associates, like Department, Manager.
Child - The attendance data of the selected associate.

I am trying to fetch data from Child table.

If I am supposed to follow your suggestion, how can I get the result list showing Date, entries.

Thank you.

Ok.
But, is there way to just get the dates between two dates?

Do you only need to show dates as a list or like a table view?

Well, the reason why I asked this is to have the “Result” to show like,
Search by : (Name) or (Manager, Department)
Based on the search field data,
Date - Count of All Associates - Count of Available Data (or Missing Data)
26 Mar - 35 - 10
27 Mar - 35 - 35
28 Mar - 35 - 0
.
.
30 Apr - 35 - 5

This would save lot of time for the users in just searching for their details instead of looking in the whole table.

Thank you for your time & effort.

How many days is the difference between start and end date?

It would be a Maximum of 31 days.
May I know why it matters?
Is it matter that, this count is necessary?
Can it be done for more than that?

I’m thinking a detail with quick editable start date. Something like…

Yeah. This would be great!

Hi @GreenFlux, is it possible to have a result view,like what I have mentioned above,
similar to your Search App?

Hi @Aleksi, @GreenFlux,
How do I select particular Columns to be shown in the results?
15.04.2020_10.59.33_REC_GIF

I would be glad if I get the Date values shown instead of just name as shown on the screen record.

Thank you.