Search - Missing Dates from the Entered Data

[Kanha_PM_Office]

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.

[Aleksi]

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

[Kanha_PM_Office]

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.

[Aleksi]

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

[Kanha_PM_Office]

I will try this also.
Thank you @Aleksi.

[Aleksi]

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

[Kanha_PM_Office]

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

[GreenFlux]

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.

[Kanha_PM_Office]

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.

[GreenFlux]

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.

[Kanha_PM_Office]

Wow…this is appealing!
Thank you.

[Kanha_PM_Office]

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.

[Kanha_PM_Office]

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

[GreenFlux]

Yes, if you follow the steps that I outlined above, and add a new table with a row for each date you want to search.

I’m tied up with work but if you give me a few days I can throw together a sample app.

[Kanha_PM_Office]

Great!
please take your time.
Thank you.

[Kanha_PM_Office]

Hi @GreenFlux,
Just a gentle reminder to know whether you are able to extend your support on this?

[GreenFlux]

@Kanha_PM_Office, what’s your email address?

I started a demo app that may help you, but it’s nowhere near polished enough to post as a public sample. I’ll share it with you directly though.

[Kanha_PM_Office]

I am messaging to you personally.
Thank you so much for your effort & time in spite of your schedules.
I have also seen,this app
https://community.appsheet.com/t/re-calendar-view-using-interactive-dashboard-filter/23010/2

I was thinking, this view shall solve our purpose. Have requested @Dan_Longley for a preview or just this app_view to share.
(This is just for your idea)

Thank you!
Kudos to the community!

[Aleksi]

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

[Kanha_PM_Office]

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.

[Kanha_PM_Office]

Hi @Aleksi, @GreenFlux,
How do I select particular Columns to be shown in the results?

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

Thank you.

[Aleksi]

You would need to open the system generated inline view for that table. Then you can set your columns with the “Column order” option.

[Kanha_PM_Office]

Wow…! Thank you.

[Kanha_PM_Office]

Successfully, I have got the dates on which the entries are made.
Now, I need to know the dates when entries are empty!
For that, I need to follow @GreenFlux’s idea in the previous post?
Do you, @Aleksi have any more suggestions?

[Aleksi]

One option is if you create Dashboard View where you have month as a ref instead of a date table. Then you don’t need to save anything.

[Kanha_PM_Office]

Ok. i’ll try that.

Thank you.

[Kanha_PM_Office]

How can I make it just as a Search view without “Save” & “Cancel” ?
Which view type is to be used as in Form view, Detail view, I have “Save & Cancel”
I don’t need to save this. I just need to display the results!

I guess, I have already discussed this with @Steve, to have this feature.

EDIT : Adding the link.

Disable or Enable Save Button based on certain conditions Feature Requests

Dear community, Pls have an option to disable/enable the “Save” option in the Form_View if certain conditions are met. 2020-04-09_23-20-42|690x315 For example, The users should not be able to click on “Save” if this (above) message is shown. Thank you!

Thank you.

[Aleksi]

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

[Kanha_PM_Office]

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?

[Aleksi]

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

[Kanha_PM_Office]

Yeah. This would be great!