How to calculate the number of possibilities

[alisaif]

Hi,

I have four combo box of categories in a form:

1. Main Category (CboMainCat)
2. Category (CboCategory)
3. Sub Category1 (CboSubCat1)
4. Sub Category2 (cboSubCat2)

In a kategory table I defined all the category and the structure is as follows:

1. Icode
2. Full_desc
3. MainCat
4. Category
5. Subcat1
6. Subcat2

In combo box option is open, means user can select anything as follows and based on that I have to generate the query:

mMainCat = This.parent.cboMainCat.DisplayValue
mCategory = This.parent.CboCategory.DisplayValue
mSubCat1 = This.parent.CboSubCat1.DisplayValue
mSubCat2 = This.parent.cboSubCat2.DisplayValue

DO CASE
CASE ! EMPTY(mMainCat) AND ! EMPTY(mCategory) AND ! EMPTY(mSubCat1) AND ! EMPTY(mSubCat2)
CASE ! EMPTY(mMainCat) AND ! EMPTY(mCategory) AND ! EMPTY(mSubCat1) AND EMPTY(mSubCat2)
CASE ! EMPTY(mMainCat) AND ! EMPTY(mCategory) AND EMPTY(mSubCat1) AND EMPTY(mSubCat2)
CASE ! EMPTY(mMainCat) AND EMPTY(mCategory) AND EMPTY(mSubCat1) AND EMPTY(mSubCat2)
CASE EMPTY(mMainCat) AND EMPTY(mCategory) AND EMPTY(mSubCat1) AND EMPTY(mSubCat2)

CASE EMPTY(mMainCat) AND ! EMPTY(mCategory) AND ! EMPTY(mSubCat1) AND ! EMPTY(mSubCat2)
CASE EMPTY(mMainCat) AND EMPTY(mCategory) AND ! EMPTY(mSubCat1) AND ! EMPTY(mSubCat2)
CASE EMPTY(mMainCat) AND EMPTY(mCategory) AND EMPTY(mSubCat1) AND ! EMPTY(mSubCat2)

CASE ! EMPTY(mMainCat) AND EMPTY(mCategory) AND ! EMPTY(mSubCat1) AND ! EMPTY(mSubCat2)
CASE ! EMPTY(mMainCat) AND ! EMPTY(mCategory) AND EMPTY(mSubCat1) AND ! EMPTY(mSubCat2)

ENDCASE

But I doubt that it is very long possibilities! Is there any other way to determined the result.

Thanks

Saif

 

[vgulielmus]

You have 16 combinations, because each one of the four variables can be Empty() or !Empty(), and the total number is 2 x 2 x 2 x 2 = 16

You can use an integer variable or a four elements array to generate the combinations, if you like :

lnVers = 0
If ! EMPTY(m.mMainCat)
  lnVers = BitSet(m.lnVers,0)
EndIf
If ! EMPTY(m.mCategory)
  lnVers = BitSet(m.lnVers,1)
EndIf
If ! EMPTY(m.mSubCat1)
  lnVers = BitSet(m.lnVers,2)
EndIf
If ! EMPTY(m.mSubCat2)
  lnVers = BitSet(m.lnVers,3)
EndIf

DO CASE
CASE m.lnVers = 15 && ! EMPTY(mMainCat) AND ! EMPTY(mCategory) AND ! EMPTY(mSubCat1) AND ! EMPTY(mSubCat2)
CASE m.lnVers = 7 && ! EMPTY(mMainCat) AND ! EMPTY(mCategory) AND ! EMPTY(mSubCat1) AND EMPTY(mSubCat2)
CASE m.lnVers = 3 && ! EMPTY(mMainCat) AND ! EMPTY(mCategory) AND EMPTY(mSubCat1) AND EMPTY(mSubCat2)
CASE m.lnVers = 1 && ! EMPTY(mMainCat) AND EMPTY(mCategory) AND EMPTY(mSubCat1) AND EMPTY(mSubCat2)
CASE m.lnVers = 0 && EMPTY(mMainCat) AND EMPTY(mCategory) AND EMPTY(mSubCat1) AND EMPTY(mSubCat2)

CASE m.lnVers = 14 && EMPTY(mMainCat) AND ! EMPTY(mCategory) AND ! EMPTY(mSubCat1) AND ! EMPTY(mSubCat2)
CASE m.lnVers = 12 && EMPTY(mMainCat) AND EMPTY(mCategory) AND ! EMPTY(mSubCat1) AND ! EMPTY(mSubCat2)
CASE m.lnVers = 8 && EMPTY(mMainCat) AND EMPTY(mCategory) AND EMPTY(mSubCat1) AND ! EMPTY(mSubCat2)

CASE m.lnVers = 13 && ! EMPTY(mMainCat) AND EMPTY(mCategory) AND ! EMPTY(mSubCat1) AND ! EMPTY(mSubCat2)
CASE m.lnVers = 11 && ! EMPTY(mMainCat) AND ! EMPTY(mCategory) AND EMPTY(mSubCat1) AND ! EMPTY(mSubCat2)

ENDCASE

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[alisaif]

Thanks Mr.Vilhelm.

Saif

 

[Olaf Doschke]

For what would you need the 16 cases?
You can handle each combo and combine a query into a strign, finally use macro substitution.
Besides that approach a single query with four parameters might be all you need.

m.field1value = value choosen in combo1
m.field2value = value choosen in combo2
m.field3value = value choosen in combo3
m.field4value = value choosen in combo4

Select * from table where field1=?m.field1value and field2=?m.field2value and field3=?m.field3value and field4=?m.field4value

optional choices mean you set those variables empty string and then fieldx='' always is true, if you keep SET ANSI OFF, which is the default. Earlier versions of VFP not having ANSI setting will handle that query in the same way, too.

Bye, Olaf.

 

[alisaif]

Thanks

Suppose if the value is not chosen in combo2 or combo3 then? Or it is mandatory to choose value from all combo?

Saif

 

[Olaf Doschke]

I said "optional choices mean you set those variables empty string and then fieldx='' always is true"

Try this:
SET ANSI OFF && default setting, only needed, if you set it ON
SELECT * FROM browser where id=''

It'll show all records. So make no choice an empty string, and that won't filter anything out of the result. It's a major mechanism of VFP to only need one parameterized view and still be able to filter via none, some or all parameters. This does NOT work for other field types, though. But you can then set a parameter variable NULL and write WHERE NVL(fieldx=m.para,.T.), because something=.NULL. results in .NULL. and NVL(.NULL.,.T.) is .T., so that clause always results in .T. just like fieldx='' always results in .T.

So in short: My solution includes optional choices and I already explained. What is your problem in your special case?
Could you come up with concrete data and combobox choice that gives you a problem?

Bye, Olaf.