Hi,
I’m not sure if someone had encountered this before but I couldn’t find any answers.
I have a search form for the users. There are 9 fields in it. The users must fill in at least one field.
The problem I have is that the combos of what the users can key is different. They can fill in from 1 to 9 fields. These fields check different things in the database.
The number of combos that I have is (2 to power 9) - 1 = 511. I can’t write 511 possible combos that the users may key in.
Is there a way I can loop through the form and get the combos instead?
The If-Else statements are not that feasible as I have to remember what are the combos. If I have to use the If-else, what is the way to write it so that it’s not so long and still cover all the combos?
Oh the data is stored in MS-SQL.
Subject: Try this formula:
V1 := @Trim(YourField1 : Your2 : Your3 : Y4 : Y5 : Y6 : Y7 : Y8 : Y9);@If(V1 = “”; @Failure(“You must fill out at least one field”); @Success)
Subject: RE: Try this formula:
Thanks but I forgot to mention that I’m using LotusScript.
Subject: Loop Non-Empty Fields
I can think of some ways I’d approach this, but I think I’d need to know more about what you need to get at the end. What, exactly, are you doing with all this data? Are you ultimately trying to construct a SQL statement using the contents of the fields as WHERE conditions?
Subject: RE: Loop Non-Empty Fields
Oh, What I have is tons of If -else statementsIf fld1<>“” then
query = “country = '”+fld1+“'”
else
…
Those are where conditions.
I’m using LotusScript btw. (forgot to mention it).
Subject: RE: Loop Non-Empty Fields
I’m not after what you’ve got so much as what you’re trying to get at the end. However, it sounds to me like all you need to be doing is progressively building your condition. You don’t have to do it all at once; rather, you can do it a bit at a time. This is a clumsy and incomplete rendition, but it should give you the idea:
query = "Select foo, bar from table where "
If fld1<>“” then
query = query + “country = '”+fld1+"’ and "
end if
If fld2<>“” then
query = query + “city = '”+fld2+"’ and "
end if
If fld3<>“” then
query = query + “postcode = '”+fld3+"’ and "
end if
…and so on. Using this particular approach, you’ll need to remove the trailing " and " from the query string.
Subject: RE: Loop Non-Empty Fields
Oh!
I’ll have to figure out how to stop adding the trailing “and”.
Thanks!
Subject: RE: Loop Non-Empty Fields
You could use a dynamic array to store all your search criteria and then use join on the elements. Just size the array initially to 8 (since you have nine fields), increment a counter (that you initialize to -1 of course) each time you add a value (which you will only do if the field’s value is not an empty string).
After you’ve cycled through each field you can check to make sure the counter is greater than -1 and, if so, redim preserve the array to the counter value and call Join using " and " as your concatnation string. The check to see if the counter is greater than -1 will also give you your check to make sure at least one field has been given a value.
Also, if you name the fields something like “SearchCriteria_0”, “SearchCriteria_1”, etc. and you can use GetItemValue with a for loop and only have to write one routine to retrieve all your values.