Reports and Multiple select

[Jberry]

Hello everyone,

I created a dialog with 3 fields, all set to Multiple Select.

I find it complicated to construct the filter for the report but I managed to come up with this (and I'm sure there's a better/easier way)

theyear = ChosenYear.dump()
theyear = remove_blank_lines(theyear)
theyear = *for_each(x,"Yr_Rec = " + x,theyear)

psycho = ChosenPsycho.dump()
psycho = remove_blank_lines(psycho)
psycho = *for_each(p,"Psych_ID = " + quote(p),psycho)

vschool = ChosenSchool.dump()
vschool = remove_blank_lines(vschool)
vschool = *for_each(s,"School_Id = " + quote(s),vschool)

filter = (stritran(alltrim(invnum),crlf()," .or. "))
filter = "(" + filter + ")"
filter2 = (stritran(alltrim(psycho),crlf()," .or. "))
filter2 = "(" + filter2 + ")"
filter3 = (stritran(alltrim(vschool),crlf()," .or. "))
filter3 = "(" + filter3 + ")"

filter = filter + " .and. " + filter2 + " .and. " + filter3

So my question is (other than a better way of doing it) how do I allow the user to choose ALL of the schools, or years, or psychologists?

If I choose, say, a few of the schools by doing [click shift-click] then it works and I get multiple schools, if I choose all the schools I get an error of "incorrect data type". There are approx. 200 schools. I have tried choosing schools in groups of a-c, d-f etc to see if any of them were a problem but each group works fine, it's just when I multi-choose all of them.

So, how do I allow a user to choose ALL?

oh, I tried using an wildcard "*" but still to no avail.

Thanks

-John

[Stan Mathews]

Re: Reports and Multiple select

if I choose all the schools I get an error of "incorrect data type".

At the point that the filter string exceeds 1024 characters Alpha converts the string into a blob data type not suitable to the current endeavor. You will need to decide whether it is necessary to be able to select enough schools/years/psychos to exceed the string limit, or not, and act accordingly.

IOW, if all schools are selected or enough to exceed the character limit, given the other constraints, warn the user and remove that portion of the filter.

[Jberry]

Re: Reports and Multiple select

Thanks Stan. I'll try to work around it.

-John

[Jberry]

Re: Reports and Multiple select

Reposting from this thread (http://msgboard.alphasoftware.com/al...6&goto=newpost
) as I was in the wrong area:

Hi Mike,

I was thinking along the same lines. Looks like yours is for the desktop whereas mine is web. Anyway, here's what I was thinking of doing. Each of my tables will have a Selectrpt field and the Report criteria will be something like P_Psychos->Selectrpt="Y".AND.P_Schools->Selectrpt="Y". I would display a dialog where the user can pick the records they want to see in the report and once they click [Submit] I'd like for code to update the the Selectrpt field in each of the tables; that's where I'm stuck. I don't know how to do that and I just don't understand how the *For_Each() works no matter how many times I read the help file. If I use the *For_Each() how/where do I put the tbl.change code? I'm embarrassed to say how long I've been working on this one report. :o

I hope someone can put me out of my misery on this.

[Jberry]

Re: Reports and Multiple select

Still no joy on this although I have completed an ugly work around for now. I've added some code to the AfterValidate that checks if the user chose any psychologists, if not I add a filter that is sure to return ALL the psychologists (Psych_Id = -45 for instance). I did the same with the schools. So for now they can choose all or 1. I'm still stuck on how to allow them to choose say, 150 schools, 150 psychologists and years 2000 - 2009. Does anyone have an example they can share with me? I can't have them go through picking one by one :)

Thanks

-John

[Jberry]

Re: Reports and Multiple select

Okay, I guess this can't be done or no one has attempted it. If anyone has done this can you let me know how you did it?

Thanks

-John

[Steve Wood]

Re: Reports and Multiple select

Add selectable options in your dropdown for:

- ALL -
- ALL SCHOOLS -
- TOP 150 SCHOOLS by SALES -
- RANDOM 150 SCHOOLS -

or whatever

and then include code in your dialog to create a filter matching that option. Tell them to 'select one of these top four, or as many of schools as you like', or similar.

Or if you want them to be able to select 150 or so specific schools, abandon your dropdown and shift to a page with checkboxes where they can select and "add" schools to the report or. Another way is to list all schools in a Grid along with any Search ability and include an AJAX callback button next to each school. The button would "add" a school to the print list. This is similar to how a shopping cart works. You click to add and build the list in another table. Then the print routine would reference that table. Make sure you cut off their ability to add once the number of selections is just under what the filter will allow due to length.

[Jberry]

Re: Reports and Multiple select

Thanks Steve. I'm going to give it a shot with a grid and see what happens.

-John