Seperate Multi selected field

[bsouthuk]

Hi

I want to be able to select an option in a scrolling field then click a button which will then display the selections in my datagrid. For example, I am using the following code that works perfectly for me:

Code: Select all

      put the selectedText of field "CompanyType" into theRec
      put revQueryDatabase( theConnectionID, "select * from Customer where Status LIKE '" & (theRec) & "%'") into theCursor

If I select 'Opportunity' in my scrolling field then the datagrid willl only display customers that statuses are 'Opportunity'. However, I want to be able to select more than one status in my scrolling field, for instance, 'Opportunity' and 'Cancelled' and then for my datagrid to display my customers with these statuses.

However, when I make more than one selected in my scrolling field my code falls short.

Can anyone help with this?

[dunbarx]

You are using a scrolling field to select these options? Are there that many?

If you use a list field with the ability to select discontiguous lines, then you can have more than one criterion. I don't use the query function, but perhaps with two passes you can filter the data you need?

[bsouthuk]

I'm using a list field where you can select multiple lines yes. Sometimes users will select maybe 6 lines but I have no idea where to start with the code. Surely this should be easy enough to filter the data I need no?

[BvG]

try to look at your data, when stuff doesn't work.

for example: look at what the sql query looks like.

Code: Select all

      put the selectedText of field "CompanyType" into theRec
      put "select * from Customer where Status LIKE '" & (theRec) & "%'" into theSQL
      put theSQL
      ---put revQueryDatabase( theConnectionID, theSQL) into theCursor

[bsouthuk]

Thanks but its not that my script doesn't work as it does when selecting just one list from the field. I'm just not sure how to code it so that it can filter by more than one selection from the field. Hope that makes sense.

[BvG]

look at the data that this line produces for the multiple selection field:

Code: Select all

      put the selectedText of field "CompanyType"

[bsouthuk]

Just did the 'answer the result' when highlighing more than one line and the result is correct. The pop up message shows one line under the other.

Also did the Answer the result under the Query line and it came back 116 so there are no errors. Am looking to filter for example all statuses that are 'Opportunity' and all that are 'Qualified'. If I highlight just 'Opportunity' in the field, the datagrid filters all customers in that status. But when i select both 'opp...' and and 'Qualified' the datagrid is empty even though there are lots of records that are in those statuses.

[dunbarx]

Why not let LC do the filtering. Query the database separately with all your various criteria. Put the results into successive variables, like "opportunity" and "cancelled".

Then filter out all entries that do not appear in all variables. This way LC does the multiple filtering work, not the query function.

Should be very straightforward.

Craig Newman

[bsouthuk]

Hi Craig

that seems like a much better solution, however I am a novice here - is there a lesson or tutorial on how to code this when filtering a datagrid? Or do you have sample codes?

the key thing for me here is to be able to filter a column with more than 1 criterion highlighting lines in a list field. Such as, I need to display all records where the status column is either 'opportunity' or 'cancelled'.

thanks again.

Daniel

[dunbarx]

A datagrid is nothing more than a composite field object that contains both columns and lines. It has considerable display power.

I would do all my filtering in a script, and simply load the results into the datagrid.

Go to:

http://lessons.runrev.com/spaces/lesson ... Data-Grid-

In the meantime you need to read about the "filter" command, and ALL the "see also" stuff in that entry, like "matchText. et. al. I suggest you fool around with some play data for a bit, until you are comfortable filtering and weeding information.

You are starting out with a fairly daunting project for a beginner. Don't stop; keep asking.

Craig Newman

[BvG]

Right, the multi line selection in a field returns several lines. Now what will that do to your sql statement? As you seem to not think about these things, here's a hint:

Code: Select all

select * from Customer where Status LIKE 'Opportunity
Cancelled%'

[bangkok]

Right, the multi line selection in a field returns several lines. Now what will that do to your sql statement? As you seem to not think about these things, here's a hint:
Cancelled%'[/code]

Indeed. You have to change the SQL query in order to handle the "or" situation (records that contains 'Opportunity' or 'Cancelled'), because actually you want to get both, right ?

So something like this :

Code: Select all

put the selectedText of field "CompanyType" into theRec
if the number of lines of theRec = 1 then
put revQueryDatabase( theConnectionID, "select * from Customer where Status LIKE '" & (theRec) & "%'") into theCursor
else
put "select * from customer where "  into theQuery
repeat with = 1 to the number of lines of theRec
put "status LIKE '"&line i of theRec&"%' or " after theQuery
end repeat
delete last word of theQuery
put revQueryDatabase( theConnectionID, theQuery) into theCursor
end if

[bsouthuk]

Aaa! Thats brilliant, the code works great apart from one thing:

How do I filter all records where the status is empty. When the field is blank, all the records in my database show when it should only display the ones where the status is blank.

Any ideas on this one?

[BvG]

i suggest learning how sql works a bit better. Pending that, you should ditch the % in the sql query, because you want the match to be exactly what you supply, in this case empty respectively nothing.

[bsouthuk]

Thats great, i appreciate your help