Page 1 of 1
Seperate Multi selected field
Posted: Sun Jan 30, 2011 6:07 pm
by 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?
Re: Seperate Multi selected field
Posted: Sun Jan 30, 2011 7:58 pm
by 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?
Re: Seperate Multi selected field
Posted: Sun Jan 30, 2011 10:08 pm
by 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?
Re: Seperate Multi selected field
Posted: Sun Jan 30, 2011 10:33 pm
by 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
Re: Seperate Multi selected field
Posted: Sun Jan 30, 2011 11:06 pm
by 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.
Re: Seperate Multi selected field
Posted: Sun Jan 30, 2011 11:13 pm
by BvG
look at the data that this line produces for the multiple selection field:
Code: Select all
put the selectedText of field "CompanyType"
Re: Seperate Multi selected field
Posted: Sun Jan 30, 2011 11:33 pm
by 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.
Re: Seperate Multi selected field
Posted: Sun Jan 30, 2011 11:42 pm
by 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
Re: Seperate Multi selected field
Posted: Sun Jan 30, 2011 11:47 pm
by 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
Re: Seperate Multi selected field
Posted: Mon Jan 31, 2011 1:56 am
by 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
Re: Seperate Multi selected field
Posted: Mon Jan 31, 2011 7:41 am
by 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%'
Re: Seperate Multi selected field
Posted: Mon Jan 31, 2011 8:41 am
by bangkok
BvG wrote: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
Re: Seperate Multi selected field
Posted: Mon Jan 31, 2011 10:04 am
by 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?
Re: Seperate Multi selected field
Posted: Mon Jan 31, 2011 12:37 pm
by 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.
Re: Seperate Multi selected field
Posted: Mon Jan 31, 2011 12:54 pm
by bsouthuk
Thats great, i appreciate your help