I’m creating a table where I am writing down which social media post would be posted at what date and time.
Problem is, when specific date and time is selected for earlier post, it is possible (by mistake) to select same date and time in the table for a new post. I would have to memorize if any earlier post has already been scheduled for this particular day and time.
Ideal solution to that would be to not being able to select same time (greyed out options?) for any given date [one is date column and another is select list column where I have put up 9 AM, 3 PM and 6 PM as posting times]. Sharing the example table below.
Is this solution possible?
If not what could be the workaround?
Please have a look and tell me if this is ok for you ?
Using lookup custom filter is the best option I guess
In your case you should say that you don’t want to lookup already existing date
I tried your suggestion. Do you mean having a separate table where I would have each date and time combination as separate row? Is that easiest possible way?
OK Let’s do this @Piyush_Patil , lot of steps, I tried to be clear. Embed doc at the end but please read this to understand the process mate
BONUS 1
First of all, this is not sexy to have several times the same date in your lookup option, then I implemented this solution How to get Lookup columns to display only unique values?, so that you have unique value of date in your Pinterest table
We can indeed unauthorize you to select a time that has already been selected for the same day. Bonus 2 : if every hour of a given date has been selected, I wont let you select this day anymore (see below)
First step is to create a “date & time” column in your pinterest database, we’ll use that to compare in the “Date & Time” Table
Then, we want to know in the date & time table if the combination of date/time has already been taken in pinterest table, create a checkbox with this formula
Back to pinterest table, set the time as “lookup”, and customize it to filter only time of current date, and corresponding to combination not already taken
So, in November 2nd, I have 3 possibilities in date and time : 1,2 or 3 PM. As 1 PM is already selected, checkbox is true, then in Pinterest table you cant select 1 PM any more. That’s the job you want !
Bonus 2
If all possible time of a given date have been taken, you’d like not to be able to select this date in the pinterest table
Create a “AllDayTaken” in date and time table, With this formula. It will run through every row of this date, and check if the time as been taken. If all of them are, the checkbox goes true. It means that there is no other time available for this date
Conclusion
Yes, you have to deal with a second table of date/time, but this is I think the better way to proceed complex filter on date & time. But you can create easily this table as long as you want with button using ForEach/Sequence Button
Hi @Piyush_Patil, I’m happy to have helped you solve this issue. A lot of different point seen in this post about unique value in lookup and custom filter ! Good look with your doc
Then, OP has Date and Time he wants to fill with several date, and several times for each date. Then I created a TimeWithinDay Table, and also a creation table with :
start date
end date
some button and variables related to the while loop
As long as the difference between the “loopDate” and the EndDate is positive or equals to 0 I start a ForEach() loop on the TimeWithinDays table, I create as many rows in the master table, with the current time (CurrentValue) of TimeWithinDay table, and the current “LoopDate”. When I’m done, I change the value of loopdate to +1, and start the while loop again until LoopDate reaches end date.