how to design tables for dynamic queries

[kenndot]

Hi all,

I've got an application where some of the criterion in the queries are hard coded and I am trying to remove all of that hard coding and use tables to make the app dynamic for the user. My question is one of design. I am looking for ideas as to how to design the tables in a way that I can query those tables to make my dynamic select statement each time the user runs the app. They will have a 'default' setting, where I will default the criteria to what is hardcoded now.

Please let me know if my question doesn't make sense or if you need more info.

Thanks a bunch.

 

[darrellblackhawk]

More info.

It's easy to create dynamic queries.

And yes, it's an issue of design.

Darrell

 

[kenndot]

Just looking for some ideas on the design of the tables that hold the criteria that the user has selected, like the field names and table names.... the logic I will deal with, I'm just looking for design

For instance does this make sense?

tblbuildQuery

cSelect - holds the field name values to query
cSelectfrom - holds the table to query
cWhere - holds the where clause

and so on... is that how you would do it?

if no, then how would it make sense to design tables to populate on the fly so it would be easy to query those table/s with a final result of the query string itself?

 

[darrellblackhawk]

Still a little unclear on the UI.

How is the information entered that will be used
to create the queries?

How is the information to be displayed after the query?

You don't need to use a table to hold the values that
will be used to create the queries, but it can be helpful.

The structure you posted makes sense, but a little more
info is needed to determine the best design strategy.

Darrell

 

[baltman]

I have a single record table that I store in the users' local temp directory that has one field for each variable I want remembered.

You could easily use a multi record table with a username fields and seek that record.

Once you have that table designed it becomes a matter of building the query and executing it.

Brian

 

[Mike Lewis]

Kenndot,

Who is responsible for building and maintaining this table? If it is the programmer, your structure seems reasonable, but why not go the whole hog and have a single field to hold the entire SELECT?

If it's the user who must maintain it, it is probably too technical. The user won't know what a WHERE clause is, or even what a field is.

You asked:
is that how you would do it?

It wouldn't occur to me to do this at all, but if you feel you have a good reason, then it seems a valid approach.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[kenndot]

I was hoping to clarify what I am doing here so it makes more sense, because I'm getting the feeling I haven't done a great job of explaining my real question.

I have an application that pulls some data and when it was built, the user had specific business rules as to what the criteria was in determining if a certain record is added (to a final table to be displayed) or not added. Quite often, over the course of a couple years now, those basic business rules (that's the only way I know how to explain that) have changed. My goal is to give the user the option to select fields from the tables that are already in use in the queries and allow them to modify the criteria used in the queries themselves.

So, the user will be in charge of modifying the 'where' clause (although they aren't aware that's what they are doing) of the query is what I am trying to do, Mike I think that answers one of your questions. They will have a list of fields from each table that they can choose and then they choose an option from the following:

Must be equal to
Must NOT be equal to
Must be >
Must be <

and so on...

and then they stick their own criteria in the criteria box, which yes I will validate the data type and all that, but my question is, based on what the user chooses here, how is the best way to dynamically build that SQL String?

I will be basically setting what is referred to as the 'default' criteria in the program for the user from the beginning. But, those criteria need to be modifiable to the user so they don't need to bother us anymore when their needs change weekly, does that make sense?

The user won't know that they are essentially building another SQL String every time they use it, they just know the data and what needs to equal what to get to the final result that they need and I need to provide them a way to do that with a pretty little interface. I was just looking for maybe some ideas on what has worked for other people as far as the GUI design and the table design - how to store the information to build that SQL string dynamically. My thought process was to store the SQL string somewhere in a table either broken out the way I mentioned before or in one big string, but in one big string that would be hard to modify just the WHERE clause (I realize I could parse it), but I just thought it sounded easier to concatenate fields rather than parse them out to change a clause.

 

[Mike Lewis]

Kenndot,

That makes it much clearer. On that basis, I think your storage mechanism is perfectly sound.

A much more important area will be the user interface. It is notoriously difficult to get users to express their thoughts in terms of relational operators, and even AND and OR cause trouble to some users. Given that your queries are relatively simple, you should be able to design an interface that lets users specify the business rules, but do test it carefully before unleashing it.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[kenndot]

I was thinking about what you mentioned, the problem getting a user to understand the AND/OR situation. That made me think of yet another question. What about those times where you need to include brackets, for instance, in an OR statement. What is a good way to handle that?

 

[MarciaAkins]

Hello KenDot.

>> I've got an application where some of the criterion in the queries are hard coded and I am trying to remove all of that hard coding and use tables to make the app dynamic for the user. <<

If you need a UI to allow the user to build their own queries, here is some code that might help uou get started:

**************************************************
*-- Class:        buildfilter (d:\tipsbook\ch11\ch11.vcx)
*-- ParentClass:  form
*-- BaseClass:    form
*-- Time Stamp:   04/09/04 11:59:03 AM
*
DEFINE CLASS buildfilter AS form


	DataSession = 2
	Height = 242
	Width = 630
	DoCreate = .T.
	BufferMode = 2
	AutoCenter = .T.
	Caption = "Build Filter Condition"
	Closable = .F.
	WindowType = 1
	AlwaysOnTop = .T.
	*-- Alias used to build the filter condition(s)
	calias = ""
	*-- Holds the filter condition as it is being built
	cfilter = ""
	Name = "frmBuildFilter"

	*-- Array to hold the field captions if the alias is part of a dbc and has captions set . Otherwise, holds the field names
	DIMENSION afieldnames[1,1]


	ADD OBJECT cbofieldnames AS combobox WITH ;
		BoundColumn = 2, ;
		RowSourceType = 5, ;
		RowSource = "Thisform.aFieldNames", ;
		Height = 24, ;
		Left = 7, ;
		TabIndex = 1, ;
		Top = 26, ;
		Width = 257, ;
		Name = "cboFieldNames"


	ADD OBJECT cboconditions AS combobox WITH ;
		RowSourceType = 1, ;
		RowSource = "", ;
		Height = 24, ;
		Left = 268, ;
		TabIndex = 2, ;
		Top = 26, ;
		Width = 100, ;
		Name = "cboConditions"


	ADD OBJECT label1 AS label WITH ;
		FontBold = .T., ;
		Caption = "Field", ;
		Height = 17, ;
		Left = 9, ;
		Top = 51, ;
		Width = 60, ;
		TabIndex = 8, ;
		Name = "Label1"


	ADD OBJECT label2 AS label WITH ;
		FontBold = .T., ;
		Caption = "Condition", ;
		Height = 17, ;
		Left = 270, ;
		Top = 51, ;
		Width = 72, ;
		TabIndex = 9, ;
		Name = "Label2"


	ADD OBJECT label3 AS label WITH ;
		FontBold = .T., ;
		Caption = "Value", ;
		Height = 17, ;
		Left = 375, ;
		Top = 50, ;
		Width = 40, ;
		TabIndex = 10, ;
		Name = "Label3"


	ADD OBJECT cmdok AS commandbutton WITH ;
		Top = 209, ;
		Left = 317, ;
		Height = 27, ;
		Width = 84, ;
		Caption = "OK", ;
		TabIndex = 6, ;
		Name = "cmdOK"


	ADD OBJECT cmdcancel AS commandbutton WITH ;
		Top = 209, ;
		Left = 406, ;
		Height = 27, ;
		Width = 84, ;
		Cancel = .T., ;
		Caption = "Cancel", ;
		TabIndex = 7, ;
		Name = "CmdCancel"


	ADD OBJECT cmdclearall AS commandbutton WITH ;
		Top = 209, ;
		Left = 228, ;
		Height = 27, ;
		Width = 84, ;
		Caption = "\<Clear All", ;
		TabIndex = 5, ;
		Name = "cmdClearAll"


	ADD OBJECT cmdaddcondition AS commandbutton WITH ;
		Top = 209, ;
		Left = 139, ;
		Height = 27, ;
		Width = 84, ;
		Caption = "\<Add Condition", ;
		TabIndex = 4, ;
		Name = "cmdAddCondition"


	ADD OBJECT edtfilter AS editbox WITH ;
		Height = 84, ;
		Left = 7, ;
		ReadOnly = .T., ;
		TabIndex = 11, ;
		Top = 84, ;
		Width = 619, ;
		ControlSource = "Thisform.cFilter", ;
		Name = "edtFilter"


	ADD OBJECT label4 AS label WITH ;
		FontBold = .T., ;
		Caption = "Filter Condition", ;
		Height = 17, ;
		Left = 9, ;
		Top = 169, ;
		Width = 90, ;
		TabIndex = 12, ;
		Name = "Label4"


	ADD OBJECT txtvalues AS textbox WITH ;
		Format = "!", ;
		Height = 23, ;
		Left = 376, ;
		TabIndex = 3, ;
		Top = 26, ;
		Width = 250, ;
		Name = "txtValues"


	*-- Builds the filter condition
	PROCEDURE buildfilter
		LOCAL lcCondition

		WITH Thisform
			IF TYPE(.cAlias+'.'+ALLTRIM(.cboFieldNames.Value)) == 'C'
				lcCondition = 'UPPER('+ALLTRIM(.cboFieldNames.Value) + ') ' + ALLTRIM(Thisform.cboConditions.Value) + ' '
			ELSE
				lcCondition = ALLTRIM(.cboFieldNames.Value) + ' ' + ALLTRIM(Thisform.cboConditions.Value) + ' '
			ENDIF
			*** Add the quotation marks if the field type is character
			IF TYPE(.cAlias+'.'+ALLTRIM(.cboFieldNames.Value)) == 'C'
			   lcCondition = lcCondition + CHR(34) + UPPER(ALLTRIM(.txtValueS.Value)) + CHR(34)
			ELSE
			   lcCondition = lcCondition + ALLTRIM(.txtValues.Value)
			ENDIF   
			*** If there are multiple conditions and them together
			.cFilter = IIF(EMPTY(.cFilter), lcCondition, .cFilter + ' AND ' + lcCondition) 
		ENDWITH

		Thisform.edtFilter.Refresh()
	ENDPROC


	*-- Create array of field names (or, hopefully, captions) to use in dropdown list
	PROCEDURE setform
		LOCAL lnFieldCnt, laFields[1], lnCnt, lcCaption, lnArrayLen

		WITH Thisform
			*** Make sure alias is available
			IF !USED( .cAlias )
				USE ( .cAlias ) IN 0
			ENDIF

			*** Get all the field names in the passed alias
			lnFieldCnt = AFIELDS( laFields, .cAlias )

			*** Don't include memo fields in the field list
			lnArrayLen = lnFieldCnt
			lnCnt = 1
			DO WHILE lnCnt <= lnArrayLen
				IF lnCnt > lnFieldCnt
					EXIT
				ENDIF
				IF TYPE( .cAlias + "." + laFields[ lnCnt, 1 ] ) = "M"
					=ADEL( laFields,lnCnt )
					lnFieldCnt = lnFieldCnt - 1
				ELSE
				    lnCnt = lnCnt + 1
				ENDIF
			ENDDO

			*** Create a two-dimensional array of captions (if available) and field names
			DIMENSION .aFieldNames[ lnFieldCnt,2 ]
			FOR lnCnt = 1 TO lnFieldCnt
				lcCaption = ""
				IF !EMPTY( DBC() ) AND ( INDBC( .cAlias, 'TABLE' ) OR INDBC( .cAlias, 'VIEW' ) )
				   lcCaption = PADR( DBGetProp( .cAlias + "." + laFields[ lnCnt, 1 ], 'FIELD', 'CAPTION' ), 40 )
				ENDIF   
				IF EMPTY( lcCaption )
				    lcCaption = PADR( laFields[ lnCnt, 1 ], 40 )
				ENDIF
				.aFieldNames[ lnCnt, 1 ] = lcCaption
				.aFieldNames[ lnCnt, 2 ] = PADR( laFields[ lnCnt, 1 ], 40 )
			ENDFOR
			.cboFieldNames.Requery()
			.cboFieldNames.ListIndex = 1
		ENDWITH
	ENDPROC


	PROCEDURE Init
		LPARAMETERS tcALias
		WITH ThisForm
			*** Save alias so it can be used by the entire form
			.cAlias = tcAlias
			.SetForm()
		ENDWITH

	ENDPROC


	PROCEDURE Unload
		RETURN ThisForm.cFilter
	ENDPROC


	PROCEDURE Load
		DoSets()
	ENDPROC


	PROCEDURE cboconditions.Init
		WITH This
			.AddItem( " = " )
			.AddItem( " > " )
			.AddItem( " < " )
			.AddItem( " # " )
		ENDWITH
	ENDPROC


	PROCEDURE cmdok.Click
		Thisform.Release()
	ENDPROC


	PROCEDURE cmdcancel.Click
		Thisform.cFilter = ""
		Thisform.Release()
	ENDPROC


	PROCEDURE cmdclearall.Click
		Thisform.cFilter = ""
		Thisform.EdtFilter.Refresh()
	ENDPROC


	PROCEDURE cmdaddcondition.Click
		Thisform.BuildFilter()
	ENDPROC


ENDDEFINE
*
*-- EndDefine: buildfilter
**************************************************

Marcia G. Akins

http://www.TightlineComputers.com

 

[rgbean]

Marcia,
To get this to work, I needed to make a few changes:
The cboFieldNames.Value references in PROCEDURE buildfilter needed to be cboFieldNames.DisplayValue.

And since you didn't provide DoSets(), I commented it out in the LOAD().

Rick

 

[MarciaAkins]

Hi Rick.

>> The cboFieldNames.Value references in PROCEDURE buildfilter needed to be cboFieldNames.DisplayValue. <<

I don't think so. I copied and pasted that code from a form that I have running as is in a production app. So either you are mistaken about that or my production app is running by way of black magic ;-)

Here is the code for DoSet()

**********************************************************************
* Program....: DoSets
* Compiler...: Visual FoxPro 06.00.8492.00 for Windows
* Abstract...: Quickie function to set up the environment
* ...........: Called from BeforeOPenTables method of DE so forms with private
* ...........: datasession have everything set up properly before opening views
* ...........: can be called from Form.Load() when not using the DE
**********************************************************************
FUNCTION DoSets

SET SYSFORMATS ON
SET SAFETY OFF
SET MEMOWIDTH TO 120
SET TALK OFF
SET CENTURY ON
SET CENTURY TO 19 ROLLOVER 30
SET MULTILOCKS ON               && For table buffering
SET DELETED ON
SET EXCLUSIVE OFF
SET NOTIFY OFF
SET BELL OFF
SET NEAR OFF
SET EXACT OFF
SET INTENSITY OFF
SET CONFIRM ON
SET STATUS BAR OFF
SET NULLDISPLAY TO 'None'

ENDFUNC

Marcia G. Akins

http://www.TightlineComputers.com

 

[rgbean]

OK Marcia,
I know I may have suffered some brain damage in Redmond earlier this week, so this is what I'm doing and what's causing problems.
I took your original code and put it into a .PRG file "testfilter". Then I ran the following:

SET PROCEDURE to testfilter
ox = CREATEOBJECT("buildfilter","junk")
ox.Show()

(Note: The table is a "junk" DBF with one field for every field type and they were creatively called FLD1, FLD2, etc.)

When I choose FLD3 (the logical) with = and .T., and click on 'Add Condition' I get:

Program Error

   Function argument value, type, or count is invalid.

Cancel  Suspend  Ignore  Help

The debugger shows .cboFieldNames.Value = 3 - not "FLD3" for the ALLTRIM().
I tried this in 6.0 SP5 ->, and unless I change the references to .cboFieldNames.DisplayValue, I get this error on all field types.

Rick

 

[darrellblackhawk]

Marcia:

In addition to Rgbean's post, you also need to perform
type validation on the value("txtvalues") argument based
the type of the table fields the user is creating a filter for.

Probably the easiest is way, is to change the
inputmask and value of "txtvalues" to the correct
type when "cbofieldnames" changes. The change is of
course based on the field type in ".cAlias".

btw/ Good start!

Darrell

 

[Toman]

Sorry, I'm using VFP 5 and I don't know what a notation ".cAlias" does mean (outside of WITH ENDWITH). Will someone explain it to me?
Thank you Marcia.
Tom

 

[MarciaAkins]

Hi Rick.

>> The debugger shows .cboFieldNames.Value = 3 - not "FLD3" for the ALLTRIM().
I tried this in 6.0 SP5 ->, and unless I change the references to .cboFieldNames.DisplayValue, I get this error on all field types. <<

I just tested this in version 8 and I do indeed see what you are seeing. This is very odd and I really can't explain it. What do you think?

Originally, the code I posted was an scx based form. In order to post the code, I saved it as a class and used the View Code option of the class browser to post it. The scx works just fine without any errors. But the vcx based version of the form does, indeed, behave badly.

When I stepped though in the debugger, this is what I discovered:

Thisform.cboFieldNames.ListIndex = 2
Thisform.cboFieldNames.List[Thisform.cboFieldNames.ListIndex, 2 ] = 'COMPANY'
Thisform.cboFieldNames.Value = 2
Thisform.cboFieldNames.BoundColumn = 2

Now the interesting thing here is that the combo is behaving as if it is bound to a numeric data source and BoundTo is set to .F. However, when I change BoundTo to .T. in the class, the debugger shows the Value as 0!

As I have said before, this works perfectly in the scx, so I am at a loss. Do you have any idea of what is going on here?

Anyway, the way the code is set up, DisplayValue will not work if the table has captions set in the DBC because the display calue in this case will be the field's Caption and not its name ;-)




Marcia G. Akins

http://www.TightlineComputers.com

 

[MarciaAkins]

Hi Darrell.

>> In addition to Rgbean's post, you also need to perform
type validation on the value("txtvalues") argument based
the type of the table fields the user is creating a filter for. <<

Yes, you could certainly add some validation easily enough ;-)

Since the final result must be a string in order to be macro-expanded later, I just allowed all the input to be character data.





Marcia G. Akins

http://www.TightlineComputers.com

 

[MarciaAkins]

Hi Tom.

>> Sorry, I'm using VFP 5 and I don't know what a notation ".cAlias" does mean (outside of WITH ENDWITH). Will someone explain it to me? <<

cAlias is a form property. You need to pass the name of the alias for which you are building the filter to the form as a parameter.

The form saves the parameter to the property in its init()



Marcia G. Akins

http://www.TightlineComputers.com

 

[MarciaAkins]

Hi Rick.

>> The debugger shows .cboFieldNames.Value = 3 - not "FLD3" for the ALLTRIM().
I tried this in 6.0 SP5 ->, and unless I change the references to .cboFieldNames.DisplayValue, I get this error on all field types. <<

OK. I got it sussed. You need to explicitly set the value of cboFieldNames to an empty string in its Init(). When you do, the problem in the class disappears. Don't know what causes it - but this fixes it ;-)




Marcia G. Akins

http://www.TightlineComputers.com

 

[rgbean]

Hi Marcia,
I thought of this too, about 15 minutes after I left the office Saturday, but I just got back to respond. (I had to crash after the brain overload of last week!)

It's amazing how many defaults just don't get set in the "View Code" generation!

Rick