Choose between two date ranges based on a check box

["... you mean these fans?"]

Hi,

 

The following SQL Select works great  And it is where I started … 

 

ExecuteSQL ( " 

 

Select ToDo_Name_Calc, ToDo_Name

 

From ToDo  

 

WHERE ToDo_Alert_Date = ? and ToDo_Ck_Bx Is Null

 

Order By ToDo_Alert_Date Asc " ; " - " ; "" ;

 

cDateOfFirstPortal  )

 

Based on this new field "ToDo_Ck_Bx_Plus =1 ( checked )" i want to use the following date range inside my "Where" clause ….

 

WHERE ( ? Between ToDo_Date and ToDo_Alert_Date ) 

 

And if the new field "ToDo_Ck_Bx_Plus =  "" ( Not Checked )" i want to use the following date range inside my "Where" clause ….

 

WHERE ( ? Between ToDo_Alert_Date and ToDo_Date ) 

 

The "OR" didn't work meaning it only used the "FIRST" argument ...

 

My "OR" … WHERE (( ? Between ToDo_Date and ToDo_Alert_Date ) or ( ? Between ToDo_Alert_Date and ToDo_Date ))

 

So I tried to use a "Case" statement from within the "Where" clause but was unable to find enough tutorials to actualize my wanting :-( Kept getting "?"  

 

Where ( 

 

Case

When ToDo_Ck_Bk_Plus = 1 Then ? Between ToDo_Date and ToDo_Alert_Date

Else ? Between ToDo_Alert_Date and ToDo_Date

End 

 

)

 

Any assistance in this matter I would be grateful.

 

Thank you.

 

Tom

[email protected]

 

[eos]

You can only use the ? placeholder to inject values, not field references; i.e.

" … WHERE myField = ? " ; "" ; "" ; MyTable::myField )

is OK, but

" … WHERE ? = 'someValue' " ; "" ; "" ; MyTable::myField )

is not.

 

Note that it's good practice – at least if something doesn't work – to inspect the calculated SELECT string; often enough you'll spot the error right away, which in this case should be a missing or malformed field reference.  

 

What are those two BETWEEN queries doing? It seems you're looking to find records in a date range determined by the same end points, only once “looking” forward, then backward? (unless alertDate and/or toDoDate change their values according to one of those mysterious check boxes – but I don't see you mentioning that anywhere …)

 

Speaking of which: you need to specify

 

" … WHERE theDate BETWEEN earlierDate AND laterDate"

 

The form "…BETWEEN laterDate AND earlierDate" is syntactically correct, but won't return anything, since it seems the statement is internally translated into 

 

" … WHERE theDate >=  date1 AND theDate <= date2"

 

(which form btw. is said to work quite a bit faster than BETWEEN)

 

Of course, the two dates cover the same range in either direction – as noted above – so what's the point?

["... you mean these fans?"]

Hi Eos,

 

I didn't want to confuse the issue, so maybe a visual will be better.  I will post the SQL Statement next.

 

Please see attached example.pdf

 

Thank you for you time.

 

Tom

[email protected]

 

 

Example.pdf

["... you mean these fans?"]

Eos, please don't confuse the "ToDo_Ck_Bx" Is Null part; this if checked, keep the To Do OFF the calendar.

 

ExecuteSQL ( " 

 

Select ToDo_Name_Calc, ToDo_Name

 

From ToDo  

 

WHERE ( ? Between ToDo_Date and ToDo_Alert_Date ) and ToDo_Ck_Bx Is Null

 

Order By ToDo_Alert_Date Asc " ; " - " ; "" ;

 

cDateOfFirstPortal  )

Eos,

 

As long as the "Plus" check box is check, the above SQL Select works perfect.  No complaints.

 

Only when the customer selects the "Minus" check box on the "To Do" records that cause my dates to "FLIP"  and is causing me to now select two "Date" ranges based on the "MINUS" or "PLUS" check box.   

 

Again, it doesn't matter.  If "PLUS CK Bx" = 1 I need to use the 1st "Where" clause.  If "PLUS Ck Bx" is not not checked I need to use the 2nd "Where" clause.

 

Thank you again.

 

Tom :-)

[email protected]

[eos]

Well, the simplest solution is probably

Let
  ~sql = " SELECT … etc. " ;
    Case (
      checkBox ;
       ExecuteSQL ( ~sql ; "" ; "" ; date1 ; date2 ) ;
       ExecuteSQL ( ~sql ; "" ; "" ; date2 ; date1 ) 
)

or – if you inject the same parameter(s) in both cases – build two different queries and use one function.

 

Let (
  ~sql =
    Case (
      checkBox ;
      " SELECT version1 " ;
      " SELECT version2 " 
      ) ;
  ExecuteSQL ( ~sql ; "" ; "" ; param ) 
)

["... you mean these fans?"]

Hi Eos,

 

I need to look at each record's check box individually within the SQL Select and then choose which date range;

 

If I am understanding the "LET" function correctly, the "Case" statement "checkbox" is not referencing each record but rather "A" singular field somewhere to decide which "SQL SELECT" statement to use within the "CASE" statement across "ALL" records.

 

Unless I'm missing your examples completely ...

 

I was thinking along the lines of placing a SQL CASE statement within my "Where" clause ...

 

Sql ( " 

Select Todo_Name

From ToDo

Where ( ? Between

Case

When ToDo_CkBx IS NULL

Then Date_2 and Date_1

Else Date_1 and Date_2

End

 )" ; "" ; "" ; CalendarDate )

 

Is this possible … ?

 

Thank you

 

Tom

["... you mean these fans?"]

Hi Eos,

 

Me again.  I've attached a pic ...

 

If you could provided some insight I would be grateful.

 

The second part of the "OR" seems to be ignored in part.  Meaning I get incomplete results for some strange reason.  But when the "Date Range" is within the 1st part of the "OR" my results are correct.

 

Thank you.

 

Tom

[eos]

I need to look at each record's check box individually within the SQL Select and then choose which date range;

 

You could use the native tools of FM, i.e. …

ExecuteSQL ( " 
  SELECT Todo_Name
  FROM ToDo
  WHERE CalendarDate BETWEEN ? AND ?
  " ; "" ; "" ; Case ( ToDo_CkBx ; Date_1 ; Date_2 ) ; Case ( ToDo_CkBx ; Date_2 ; Date_1 )
)

["... you mean these fans?"]

Hi Eos,

 

The record / fields I'm looking at are on the To Do table and the field I am referencing is on the Calendar ( Day ).

 

So wouldn't the "?" reference the Calendar Date ...

 

… Where ? Between ToDo_Date_1 and ToDo_Date_2 " ; "" ; "" ; CalendarDate )

Hi Eos,

 

For the "Case" not to error ( FM ) i have to build a relationship.  Since I need to look a many records, within the SQL Select, the "CASE" should be within the SQL Select … yes ??

 

Placing the "Case" in the "Parameter" seems reversed.

 

Tom

[eos]

Hi Eos,

 

The record / fields I'm looking at are on the To Do table and the field I am referencing is on the Calendar ( Day ).

 

So wouldn't the "?" reference the Calendar Date ...

 

Placing the "Case" in the "Parameter" seems reversed.

 

Well, maybe – I don't know your solution.

 

Why don't you just describe what exactly is is you want to find, how your tables are structured, and what those checkboxes are doing to your data or your query? Or even post a sample file?

["... you mean these fans?"]

Eos,

 

I will have to build the example file.

 

Thank you.

["... you mean these fans?"]

Eos,

 

For some reason I am not allowed to upload the file ??.  

 

I you could forward me an email address I will send it that way.

 

To see the issue, please navigate to the To Do, select either Family or SWorker and toggle between the "Plus and Minus" check boxes while observing the calendar ( You may have to refresh ) to see the issue.  Basically the "Second" date range within the SQL "Where" clause is being ignored.

 

The following original SQL Select before I had to take into consideration date ranges.  

 

Works Great!!!  No complaints ….

 

If ( Calendar Filter = "All" or IsEmpty ( Calendar Filter ) ;

 

ExecuteSQL ( " 

 

Select ToDo_Name_Calc, ToDo_Name

 

From ToDo  

 

WHERE ToDo_Alert_Date = ? and ToDo_Ck_Bx Is Null 

 

Order By ToDo_Alert_Date Asc " ; " - " ; "" ;

 

cDateOfFirstPortal +16  )

 

; // Else

 

ExecuteSQL ( " 

 

Select ToDo_Name_Calc, ToDo_Name

 

From ToDo  

 

WHERE ToDo_Alert_Date = ? and Category = ? and ToDo_Name_ID_Calc = ?  and ToDo_Ck_Bx Is Null

 

Order By ToDo_Alert_Date Asc " ; " - " ; "" ;

 

cDateOfFirstPortal +16 ; Calendar Filter ; Calendar Filter Name ID  )

 

) // End If

["... you mean these fans?"]

Eos,

 

Just curious ….

 

Why does the following not work ??

 

Where ( ? Between ToDo_Date and ToDo_Alert_Date ) or  ( ? Between ToDo_Alert_Date and ToDo_Date )

 

Case

When ToDo_Alert_Ck_Bx_Plus Is Null

Then Where ( ? Between ToDo_Alert_Date and ToDo_Date )

Else Where ( ? Between ToDo_Date and ToDo_Alert_Date )

End

[bruceR]

You  got the answer to that at message 2.

Why are you asking again?

["... you mean these fans?"]

BruceR,

 

No. 2 does not allow me to choose between two date ranges.

[eos]

For some reason I am not allowed to upload the file ??.  

 

zip the file before trying to upload it.

["... you mean these fans?"]

Hi Eos,

 

 

Thank you, file uploaded :-)

 

 

 

Example.zip

[bruceR]

Now tell us what we are supposed to be looking for.

[bruceR]

You asked why your script didn't work.

 

Reply 2 answered your question.

 

Your query CANNOT work because you are trying to specify the fields to be searched using the placeholder "?" for the fields to be searched.

 

Once you straighten out that part, you can proceed to the OTHER issues involved with fixing your query.

[bruceR]

Additionally, it appears that you are discussing a global calculation field. I don't know how you expect the calculation field to refresh.

 

Besides fixing the calc (as described by message 2) I think you may need to use a script to set the field; or define it as an unstored calc.

Or explain what the purpose of the field is.

An example of your calc is shown below:

 

SQL_Event_01

 

If ( Calendar Filter = "All" or IsEmpty ( Calendar Filter ) ;

 

ExecuteSQL ( " 

 

Select ToDo_Name_Calc, ToDo_Name

 

From ToDo  

 

WHERE ( ( ? Between ToDo_Date and ToDo_Alert_Date ) or ( ? Between ToDo_Alert_Date and ToDo_Date ) ) and ToDo_Ck_Bx Is Null

 

Order By ToDo_Alert_Date Asc " ; " - " ; "" ;

 

cDateOfFirstPortal  )

 

; // Else

 

ExecuteSQL ( " 

 

Select ToDo_Name_Calc, ToDo_Name

 

From ToDo  

 

WHERE ( ( ? Between ToDo_Date and ToDo_Alert_Date ) or ( ? Between ToDo_Alert_Date and ToDo_Date ) ) and Category = ? and ToDo_Name_ID_Calc = ?  and ToDo_Ck_Bx Is Null

 

Order By ToDo_Alert_Date Asc " ; " - " ; "" ;

 

cDateOfFirstPortal ; Calendar Filter ; Calendar Filter Name ID  )

 

) // End If

[eos]

Your query CANNOT work because you are trying to specify the fields to be searched using the placeholder "?" for the fields to be searched.

 

Bruce –

 

interestingly enough, this seems to work if you reference a global field (see screenshot); but it doesn't work to read in record data.

 

 

@ Tom

 

Looking at your file, I suggest: put the logic into the event, i.e. create two calc fields à la dateEventBegin and dateEventEnd, with Min ( dateToDo ; dateAlert ) and Max ( dateDo ; dateAlert ), respectively

 

Now any query can simply look for events that fall BETWEEN those two dates – and it doesn't care what means you use to calculate the alert date.

 

Speaking of simplifying: I'd get rid of those 42 SQLEvent fields and make it one (unstored) repeating field, similar to the repeating date field where you use Get ( CalculationRepetitionNumber ) to calculate a value per cell/repetition.

 

And learn how script parameters work – this will dramatically cut down on the number of scripts in your solution.

["... you mean these fans?"]

Eos,

 

Min and Max worked perfectly. Thank you.

 

Having trouble with the 42 repeating SQL Events … As instructed, I created a copy of one of my 42 SQL Events and selected "Unstored" and set the number of repetitions to 42.

 

Since the SQL is, in a sense, global; looking at all the records, I placed the "Extend" and the "Get ( CalculationRepetitionNumber )" only in the "Parameter" part of the "SQL" select.  Since I had a working example, I though I could just follow it but … only the first repeating SQL Field updates, all other repeating field do not change As explain in the FM Function Reference for "EXTEND".  But I've applied the "Extend" to where I thought would be the correct place in the Parameter

 

It seems like the "SQL Select" is also effected by the "Repeating Fields" but I don't know how to apply the "Extend" to the SQL itself, hmm ? 

 

See calculations, I've changed the color to "Blue" where I made the changes within the SQL.

 

If ( Calendar Filter = "All" or IsEmpty ( Calendar Filter ) ;

 

ExecuteSQL ( " 

 

Select ToDo_Name_Calc, ToDo_Name

 

From ToDo  

 

WHERE ( ? Between ToDoBegin and ToDoEnd ) and ToDo_Ck_Bx Is Null

 

Order By ToDo_Alert_Date Asc " ; " - " ; "" ;

 

Extend ( cDateOfFirstPortal ) + Get ( CalculationRepetitionNumber ) -1  )

 

; // Else

 

ExecuteSQL ( " 

 

Select ToDo_Name_Calc, ToDo_Name

 

From ToDo  

 

WHERE ( ? Between ToDoBegin and ToDoEnd ) and Category = ? and ToDo_Name_ID_Calc = ?  and ToDo_Ck_Bx Is Null

 

Order By ToDo_Alert_Date Asc " ; " - " ; "" ;

 

Extend ( cDateOfFirstPortal ) + Get ( CalculationRepetitionNumber ) -1  ; Calendar Filter ; Calendar Filter Name ID  )

 

) // End If

 

Again, any assistance here I would be grateful.

 

Tom :-)

Edited August 7, 2014 by Lee Smith
Change type from 8 to 14. unreadable.

["... you mean these fans?"]

Eos,

 

This may not be the proper place to ask this questions, but it is related in a way.

 

The reason I choose to use "Global" fields for the ToDo's and not for the "Date" was because the "CalendarDate" is being reference from a global date calculation.

 

Through research on other issues, I read that if you are in a multi-user deployment, you want to have globals so you can have multiple user on the calendar.

 

With your suggestion Eos to have 42 repeating SQL Selects, I am no longer in a global field on my To Do.

 

So my question is … do i need to be concerned if the owner decides to host the file where other user will be navigating to the calendar ?

 

Again, thank you.

 

Tom

[eos]

 

Eos,

 

Min and Max worked perfectly. Thank you.

 

Then why not use them? And you're still trying to use the eSQL() parameters to insert a field reference, instead of a value. As discussed, this seems to work under certain circumstances, but I really suggest you use the function as intended.

 

Speaking of repetitions – try to avoid repeating yourself    …

Let ( 
  [
  repDate =  Extend ( cDateOfFirstPortal ) + Get ( CalculationRepetitionNumber ) - 1 ;
  examineAll = Calendar Filter = "All" or IsEmpty ( Calendar Filter ) ;
  ~sqlForSpecific = Case ( not examineAll ; " and Category = ? and ToDo_Name_ID_Calc = ?" ) ;
  ~sql = 
    " SELECT ToDo_Name_Calc, ToDo_Name 
    FROM ToDo 
    WHERE dateMin <= ? AND dateMax >= ? and ToDo_Ck_Bx Is Null" &
    ~sqlForSpecific & 
    " Order By ToDo_Alert_Date" // ASC is default
  ] ;
  ExecuteSQL ( ~sql ; " – " ; "" ; repDate ; repDate ; Calendar Filter ; Calendar Filter Name ID )
)

This should give you an idea of a better (not necessarily optimal) approach to building such calculations.

 

Btw, for the name filter, why not use a number field and a value list with ID and name (showing second field only)? This saves you from having to reconstruct the ID, since you've already selected it.

 

If you are concerned about a multi-user scenario, another option would be to just use an ordinary 42-repetition global field, put the above logic into a looping script where in the calculation you replace the repetition number with an iteration counter, and set the individual field repetitions to the result of the matching iteration, as in

Loop
  Exit Loop If [ Let ( $i = $i + 1 ; $i > 42 ) ]
  Set Variable ( $result ; theSQLQueryAdaptedFor$i ]
  Set Field [ Calendar::gCalendar [ repetition: $i ] ; $result ]
End Loop

All the other fields for the query parameters need to be globals, too – and you must put each individual user onto their own record in whatever table you're implementing this calendar. A Users table may not be a bad idea …

["... you mean these fans?"]

Eos,

 

Thank you.

 

No response necessary unless you want to.

 

 

 

Speaking of repetitions – try to avoid repeating yourself     …

 

I am thinking the the the reader would not want to scroll back to review, so I often place into the answer previous text.  I am not repeating myself but rather thinking how can I assist those that are helping me.

 

 

 

Then why not use them? And you're still trying to use the eSQL() parameters to insert a field reference, instead of a value. As discussed, this seems to work under certain circumstances, but I really suggest you use the function as intended.

 

Can you please be more specific, Bruce R. commented similar but again, since my understanding is limited.  I try to research extensively before coming into the forums.

 

What do you guys mean by  "I am using the SQL () parameters to insert a field reference instead of a value" are you talking about the "?".  And then "Use the function as intended." 

 

I am only following example from other recommend solutions, maybe not on this forum but other similar forums.

 

Maybe a pointer, I do read the reference materials and try to understand and best I can and research extensively, but without a parent  / child relationship the interrupt is often missing, and I am not corrected properly.  Again not intentional.

 

 

 

– and you must put each individual user onto their own record in whatever table you're implementing this calendar. A Users table may not be a bad idea …

 

According to what I have read and implemented off examples, I use a one table calendar, with one record.  Just reading this sentence I can feel that it is extremely vague.  It is my lack of understanding.

 

I HAVE a User Table, though not implemented since the owner says they will not have users, but I am trying to anticipate and I will be building the "User" table soon. Am I missing the point with the user table ??

 

Why would I have a record for each user on the "Calendar Table?"  The calendar is referencing the To Do Table.  

 

So I navigate the user to the Calendar Table, perform a find for the user record on the calendar … now the global fields I would think, would not change correct.  I am on record "6" instead of record "1" that I've been using for everybody.

 

Thank you.

[eos]

Hi Tom –

 

Don't Repeat Yourself is a general programming principle (with its own acronym):

 

http://en.wikipedia.org/wiki/Don%27t_repeat_yourself

 

The opposite is e.g. “Write Every Time”  …

 

It was a comment on the way your code was written, not the fact that you quoted it.

 

As a very simple example:

Case (
  condition = true ;
  this and that and somethingElseVersion1 ;
  this and that and somethingElseVersion2
)

… is redundant and consequently, not easy to maintain, with potential sources of confusion; compare to the way your calculation uses the almost exact same SQL statement twice.

 

OTOH, in …

this
  and 
that
  and
Case (
  condition = true ;
  somethingElseVersion1 ;
  somethingElseVersion2
)

… everything is defined only once, and thus needs to modified in only a single place.

 

Consolidating almost identical fields and scripts into single instances that uses repetitions and parameters, respectively, are other examples for applying DRY (retroactively, as it were).

 

What do you guys mean by  "I am using the SQL () parameters to insert a field reference instead of a value" are you talking about the "?".  And then "Use the function as intended." 

I am only following example from other recommend solutions, maybe not on this forum but other similar forums.

 

Please refer to post #2 in this thread. The instances where injecting field references into the SQL statement seems to work are either artefacts, or involve global fields; you cannot use this method to query a field on a per-record-basis. 

 

I HAVE a User Table, though not implemented since the owner says they will not have users, but I am trying to anticipate and I will be building the "User" table soon. Am I missing the point with the user table ??

 

Why would I have a record for each user on the "Calendar Table?"  The calendar is referencing the To Do Table.

 

The problem is that you'll have field access conflicts if all users are working in the same record when defining their Calendar parameters.

 

Just make sure that each user is on their own record when working with the same fields, in whatever table that may happen. Since these fields are globals, there are no record-specific values to draw from, so it doesn't matter which record a user is on – as long as it's theirs exclusively.