Generating or adding to a list of dates in a table

[Hyperbytes]

Many applications need date lists such as calendars or lists to form the basis of custom date pickers

In this simple tutorial i will show you how to generate a list of all dates within a specified date range

While there are undoubtedly more efficient ways to do this using custom queries i have stayed with the inbuilt Wappler visual tools

This routine will check for the presence of each date and will only add if missing, This allows the routine to be run on a regular basis or even as a scheduled task/ CRON job to keep the list populated
Here is the server action in full before we start

Firstly I have created s simple table, in this case called “datelist”, it has one field called ‘mydate’ data type Date. This is set as a key field to prevent possible duplicates

So we start with declaring two session variables, we could use variables but I prefer using sessions as this prevents any global/ local scope issues. It is the way i like to work but variables can be used just as easily

So we declare two session variables, ‘dateStart’ and ‘dateEnd’ and set their data type to Date (very important!)

We then give them values. In this example i am going to create all dates from the current date ({{NOW()}}) to a date two years ahead ({{NOW.dateAdd(“years”, 2)}})

So we use set session from Core Actions

We firstly set the value of dateStart using the picker to set it to NOW()

So enter the session name “dateStart”

Select the picker icon

Set the value to NOW()

We then repeat this but set the value of dateEnd to {{NOW.dateAdd(“years”, 2)}}

So again select core Actions => Set Session and add the session name dateEnd

Select the picker and pick NOW and then click the magic wand in the bottom right hand corner of ther dialogue box as highlighted

Right click the NOW entry

Select Date and Time => Date Add

We are now going to add two years years so in the interval property select ‘Years’

and in the number property add two (as i want the range to be two years)

Click Select then Select again

Your entry should appear like this

add you database connection as we will be running queries, I guess i don’t need to tell you how to do that

Now we simply loop through all the dates in this range, check if they exist in the table and if not add them

Do we go to core actions => While

Select the lightning bolt

Select $_SESSION.dateStart

Click the lightening bolt, right click on the entry and select Operation

Set the operation to less than or equal to

The set the value to $_SESSION.dateEnd

Now inside this we just repeat the actions, select the date and if not found add it

So in the steps entry we start with a Database Single Query

we select the date field we want to test

and a condition

To ensure the formats of the date field and the session match we apply formatdate() to the session value in the condition to set it to ISO format (YYYY-MM-DD)

The condition should be set to {{$_SESSION.dateStart.formatDate(“yyyy-MM-dd”)}} (case is important!) to test for the value within the table field datelist.mydate

You can do this manually or use the magic wand to create the condition via Date and Tome => Format Date

Now we test for the presence of the record using an condition from Core Actions => Condition

and use the presence of the date record from the query to test for the presence of that date in the table

So our condition is:

The condition then offers a ‘then’ and ‘else’ section

If the record has been found we don’t actually need anything to happen. As something must be input into the “when” i add a comment which acts as a great reminder for if we ever come back to this

In the ‘else’ we need to use an Insert Query to insert the current date being checked

So select a database Insert Query

and insert $_SESSION.dateStart into the date field.

NOTE. By default Wappler does not auto add Primary Key fields to the insert action when selecting a data table so it must be selected via the “+” icon

So lastly we repeat the loop with then next date by incrementing $_SESSION.dateStart by 1 day using the dateAdd function used previously i.e. {{$_SESSION.dateStart.dateAdd(“days”, 1)}}

Ensure this in inside the while loop but outside the conditional as it needs to execute with each cycle

That’s it, you can call this in a server action or run it directly from the server connect panel using “Open in Browser”

Next I will deal with filtering the list with a data view

Filtering a date list using a Dataview to show only a selected day of the week in the list

This guide follows on from the previous guide: Before we can use the previously created table we need to make a basic query server action So we create a server action, in this case i called it ‘getdate’ we add database connection [image] and a Database query I have not added any conditions,. the query returns all records. To hiode expired entries simple add a condition to ensure the date is => NOW following the method shown in the previous tutorial [image] That’s all which is needed f…