Limiting Data Blaze Results

Questions and Help
1

I have a snippet that requires the user to select a start date from a DB SELECT menu. Based upon their selection, I need to present the user with another DB SELECT menu that only gives them the option to select either of the next two start dates from their first selection. Currently I have this working just in a TB formula, but I have to manually provide a range of start dates to initially choose from and every couple months, I need to update the list. I really want my users to be able to do all of this via the DB SELECT menu rather than a dropdown formmenu. Any ideas?

2

Hi Brad,

Can you share the snippet that you are currently using here?

3

{note: preview=no; insert=no}
Variables:
{dates=["-","10/23","11/20","1/08","2/05","3/04"]}
{endnote}
Is this a Bump Only, Degree Change Only, or Bump & Degree Change case?: {formmenu: default=; Bump Only; Degree Change Only; Bump & Degree Change; name=option}{if: option =""}{error: Required; block=yes; show=validate}{endif}

{if: {=option}=""}{else}What is the students current start date?: {formmenu: values={=dates}; name=CurrentStartDate}

{if: contains(option,"Bump"); trim=right}What start date would the student like to Bump to?: {formmenu: default=-; name=newstartdate; values={=slice(dates, location(dates, CurrentStartDate) + 1, location(dates, CurrentStartDate) + 2)}}
{endif}{endif}

4

@Brad_Hedinger does this work?

  • Create a DBSelect for the start date (only read the date column)
  • Edit the query in bsql, adding GROUP BY
    This will provide only the unique start date values
  • Then you can create another DB select and use the selected start date as a filter (only show greater than), sort it by date and limit it to 2

Does that work?

5

Thank you, Dan. That got me going. Landed on the following code:

SELECT Start Date FROM Start Dates & Term Codes WHERE Start Date > {=current start date["start date"]} ORDER BY Start Date ASC LIMIT 2

Now need to figure out formatting everything in M/DD/YYYY format for the DB menus, and then, the variable output that actually gets inserted when the snippet runs to be in M/DD format! :face_with_spiral_eyes:

6

@Dan_Barak1 I am hitting a brick wall with the date formatting. I have tried both datetimeformat and datetimeparse, but am unable to get what I need. At the end of the day, I need the DB SELECT menus to show in M/DD/YYYY format, but, when the user selects a date, I need it to be inserted when the snippet runs as M/DD format. What am I missing?

7

How about this:

8

Not working for me. Need the additional columns info from the DB table to be visible, which is why I need the menus to be DB Select. Also, the user needs to select a date from the first menu. THe selection drives what is selectable in the 2nd menu....the 2 dates following what was selected.

9

Let's jump on a call to further discuss this. I think that this solution can be modified to your needs.

10

The solution has been found! This came from @Abdalla_Mahmoud.

Select the current start date:
{dbselect: SELECT datetimeformat(`Start Date`,"M/DD/YYYY") as `Start Date`, `Term Code` FROM `Start Dates & Term Codes` ORDER BY `Start Date` ASC; space=1LJX5P5HrXL7nhlfZgDxkF; menu=yes; name=current start date}

Select the new start date:
{dbselect: SELECT datetimeformat(`Start Date`,"M/DD/YYYY") as `Start Date`, `Term Code` FROM `Start Dates & Term Codes` WHERE `Start Date` > {=`current start date`["start date"]} ORDER BY `Start Date` ASC LIMIT 2; space=1LJX5P5HrXL7nhlfZgDxkF; menu=yes; name=new start date}

The M/DD format of the current start date is: {=datetimeformat(datetimeparse(`current start date`["start date"], "MM/DD/YYYY"),"M/DD")}

The M/DD format of the new start date is: {=datetimeformat(datetimeparse(`new start date`["start date"], "MM/DD/YYYY"),"M/DD")}

2 Likes