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
Last updated: