grid where clause change?

Scriptcase 7 Discussion
#1

hi guys,

assume you have a table with 100+ records, about 10 fields

one of the fields is collection_id, values 1,2,3,4 or 5

I want to display each collection alone… so what I’ve done, I crated 5 grid applications and added in SQL where collection_id=1, and the other one=2 then the third one=3…etc. that is ok

Is there a way that i can combine all that in one grid?

Something like grid opens by default to have one collection e.g. (collection_id=1) or even with all collections without WHERE initially… then after that, user clicks on a button up in the grid so it will change the SQL where to collection_id=2 so user can see the records of (collection_id=2)… an so on…

Or, even better than the buttons, perhaps a drop-down menu can be added to the grid header it will be great… user can select the collection=x from that drop down and directly the gird display records based on the collection_id selected…

What you think the best idea will be? I’m almost sure there will be more than one idea… after-all creating 5 applications or more is not so needed right?

ideas?

#2

Here’s my take on it.
You can create a form with 5 blocks configured as tabs and link your grids as details to it.

I guess you are on SC8, so why not make use of a button group and use only one grid.
Create 6 PHP buttons (btn_all, btn_grp1, btn_grp2, …) for the grid and combine them in a group.
Code for the respective buttons:
btn_all: sc_redir(yourgrid,vcoll = ‘’);
btn_coll1: sc_redir(yourgrid,vcoll = 1);
and so on.

Change the WHERE clause of the grid’s SQL to:

WHERE [vwhere]

there is no equal sign.

onScriptInit:


if(!isset([vcoll]) || empty([vcoll]))
{
	[vwhere] = '1 = 1';
        [vcoll] = 'All';
}
else
{
	[vwhere] = 'collection_id = '.[vcoll];
}
[vheader] = 'Listing Collection '.[vcoll];

Layout -> Header & Footer -> Header -> LIN1_COL1 -> Value [vheader]

Don’t forget to set the variables [vcoll], [vwhere] and [vheader] to Out.

jsb

#3

jsb, i am just trying to apply in different scenarios… especially that i’m back to SC7 permanently, will never go to SC8 unless you and Albert tell me that it is stable now (basically, a hibernate) :slight_smile:

please just don’t think i’m ignoring your reply… there is much code above to analyze and apply in cross with other ideas to see what happens and chose the best practice :slight_smile:

thanks and cheers

#4

Well Mike, we are working in SC8 as aducom as well as the university. However our needs are differently than yours so it depens on where the bugs are. We have had already a few and could work-around. But we haven’t migrated all to 8 so currently we work on both sides. But I know that SC is in contact with you regarding your issues. i hope you will work things out.

#5

yes they requested screenshots, i sent them, and invited them to access my development environment if they want… however, even if this summary issue was solved, i gave up because had/have many many issues repeatedly, and by keeping an eye on the forum, i see other users have different issues, especially when old bugs comes out, this makes me feel crazy… shortcut, can’t rely on sc8 for production, so i am back to 7 now, with monitoring the v8 “road map”, do they have one, anyway!? :slight_smile:

#6

okay… nothing seems to work with me on sc7 if i want to use only one grid and make the select dynamic by changing a drop down menu or clicking a button… also seems complicated…

next is to keep with my old plan, create grid for each collection… now trying to find a solution to make a drop-down menu in the header so it will switch and opens the other grids… is it difficult!? just a small drop-down that has “collection 1”, “collection 2”, “collection 3” and “collection 4”… once the first grid is opened, then from this drop-down will switch/link to the other grid… so user can change the grids by selecting from the drop-down… instead of the button…

did anybody try that?

#7

OK, here is another one, but don’t start banging your head on the wall. :slight_smile:

Create your grid with the SQL WHERE clause as outlined above.

onScriptInit


if(!isset([vcoll]) || [vcoll] == 0 || empty([vcoll]))
{
	[vwhere] = '1 = 1';
        $collection = 'All';
}
else
{
	[vwhere] = 'collection_id = '.[vcoll];
	$collection = [vcoll];
}
[vheader] = 'Listed Collection: '.$collection; //put the [vheader] in Layout -> Header & Footer -> Header -> Value

Global variables: [vwhere] -> Out, [vcoll] -> In

Create a form based on a table of your choice, doesn’t matter at all and remove all fields from the form.
Create a custom select field with your collections. Use Title: Yes, Titel: All, Internal Value: 0, Reload form when value has changed: Yes.
Create a second Block. Don’t display block title (both).
Put your select field in the second block.

Create a Master/Detail link and choose the your grid and set variable vcoll to your select field.

There you have it.

jsb

#8

[QUOTE=jsbinca;27638]

Create a Master/Detail link and choose the your grid and set variable vcoll to your select field.

jsb[/QUOTE]

well, first wanted to hit a nearby wall as always, then decided to go step by step :slight_smile:

Finished all as instructed then stuck here, the master/detail link should be from the form… then when grid is excuted, the vcoll is set to “in” and hence will request a value… actually didn’t understand all of it, this part only… i have the collection_id field set as {collection_id} in the code given… feeling like something is wrong

#9

Ok, probably expressed it not quite right.

I ment: Put the select field in the first block.

Here is the tutorial for master detail: http://www.scriptcase.net/scriptcase-samples/tutoriais/form/form38.php

Generate the source code for the grid and then create the master detail link to the grid.
If you have changed the grid as explained in the earlier post you will be asked for the value to be passed as vcoll. Here select Field and collection_id.
When finished place the grid in the second block and you are done.

jsb

#10

hmmmm, i’m starting to understand :smiley: do you mean i have the put the form in the menu for end user? so it will load the grid based on the [vwhere] sql mercury invention that comes from the form with the select field!? hope this is correct otherwise i’ll be lost again :slight_smile: still trying to make this work, will update you soon…

#11

okay, i assumed that is correct, that the form is the main point to add to menu for end users, so it will load the grid… it doesn’t load it… it is empty grid

in grid, i did exactly all instructed, added the codes, and the [vcoll] is put to In…

  • if i open the grid alone, it asks for a value (expected)
  • if i type manually 0,1,2,3… it gives correct records

in the form, master/detail… i linked the newly created field {selectcol} to [vcoll] but when form opens, no grid is loaded… looks empty area…
i tried putting the [vcoll] as static field = 2 to test, also the grid was not loaded in the form at all, i think the error is in the form, not in the grid

p.s. it asked me to link the [vwhere] and the [vheader] as well, i put them “empty”

details seem logic, but don’t know whats wrong! tried to read a lot, but this master/detail thing didn’t work… second block that supposed to show the gird is totally blank, just like empty block, no fields… no errors, nothing… variables ok, select field ok (in first block) with its values as instructed… don’t know

#12

as this is going different direction than the title and first started, i created new thread here
http://www.scriptcase.net/forum/showthread.php?7125-Master-Detail-form-issue&p=27827#post27827