jacem
Posts: 87
|
| Posted: 06/12/2007, 11:45 PM |
|
I am wondering if you can reference a control in one grid (master) from another grid's (child) table select WHERE statement? (Typical Master/Child table arrangement).
The need is as follows: as the user traverses each master record (only one master is displayed at a time, no inserts allowed) the associated children are displayed in a second editable grid as the select statement for the child retrives only those records that match (linking field) the single master row displayed in the upper grid. (I am doing this in PHP CC)
If I can do initial linking then if the user adds a child for a particular master then setting the linking field in the child record during the insert to the currently displayed master record should also be straight forward. As the page refreshes after a submit of the child grid then the new child record is displayed with its siblings with another blank record standing by to allow another addition as part of the overall page refresh. This should also leave the same master record displayed.
Looking at the example pack it seems to be all url variable based. I don't see this as the way forward for me as the user comes into the page for the first time there would be no URL for the linking field generated for the child grid to select from nor would there be as the user skips along to other master rows (1 at a time) using the arrow icons.
Alternatively can I create a URL variable as part of an initial event on the page once the master records linking field is known so that by the time the child grid starts displaying it can use the newly created URL value to define its select statement?
I'm not a newbie but this is only my forth CC db (in increasing complexity) so I am still pretty 'fresh' so please be gentle. I have only done very simple PHP custom code to CC in the past.
|
 |
 |
mamboBROWN
Posts: 1713
|
| Posted: 06/14/2007, 10:50 PM |
|
jacem
Did I read this right?? You are using CC and not CCS?? Just wondering...
|
|
|
|
jacem
Posts: 87
|
| Posted: 06/16/2007, 5:16 PM |
|
Well spotted: Yes CCS - I am using CodeCharge outputing to PHP. Dropped off the S in my brain for some reason.
|
|
|
|
mamboBROWN
Posts: 1713
|
| Posted: 06/16/2007, 6:50 PM |
|
jacem
You should be able to create a URL variable as you suggested above. Maybe this link will assist you: http://docs.codecharge.com/studio31/html/ProgrammingTec...Parameters.html
|
|
|
|
jacem
Posts: 87
|
| Posted: 06/16/2007, 7:03 PM |
|
Thanks Mabobrown for your interest.
I have been successful in doing master/child displays using URL params to pass the link field after a page reload however I am just wondering if it is possible to directly reference a control in another form on the same page?
Should I assume from your answer I can't and your dymanic creation of a url variable as you pointed to in the previous post as the only way? So I would call this code as I initialise the upper master form to create the url for the page in the process of loading and before the lower child form is initialised (before the Child's SELECT is built & executed)?
I suppose this would achieve what I want to do in any event...
Thanks so much for your interest thus far.
|
|
|
|
wkempees
|
| Posted: 06/17/2007, 6:49 AM |
|
Grid one is displaying data from MASTER
id description
Grid two is displaying DETAIL
id, master_id, description
Master Grid is set to display only one record at the time, so it has
navigation button in bottom row.
Your main problem (from your post) is that probably there is no link to a
Form in the Master Grid,
The id field on the MASTER grid is probably a label.
The way to solve this fast and without hassle is:
Method 1:
in the before show event of the MASTER id, put
CCSetSession("id",$Component->GetValue();
This will set a session variable "id" equal to the id of the displayed
MASTER row.
2/ in the DETAIL grid DataSource Where parameter block
(select het Detail grid's properties DataSource and press [...], takes you
to VQB)
Table Parameter
Field Name : master_id, type integer
Condition: equals (=)
Parameter: id, Type: Session
OR
Method 2
In Design mode right-click the id label in MASTER grid and set it to HIDDEN
or Textbox (least desirable)
this will make sure the id is Posted
Now you can set the Detail grid DataSource:
Table Parameter
Field Name : master_id, type integer
Condition: equals (=)
Parameter: id, Type: Application
That's all.
Advantage, there is no URL to set, to get and it is not even displayed.
Walter
|
|
|
|
wkempees
|
| Posted: 06/17/2007, 6:53 AM |
|
BTW
If the detail grid is used to add new records within the Master scope
you definitily need to use method 2.
You then have a hidden field to use when adding new record to set the
details master_id.
Walter
|
|
|
|
jacem
Posts: 87
|
| Posted: 06/19/2007, 3:46 PM |
|
thanks wkempees. Setting the type of parameter to 'application' seems to be the trick to your suggestion. I am excited to see your answer in terms of 'this is how you do it' and not 'try this'.
I have made the changes as decribed in your previous posts however my application is displaying all the records in the detail/child table so I must have something simply wrong at my end.
Couple of clarifications with option 2:
In Detail grid DataSource, my 'Where' settings should be:
Table Parameter
Field Name: id, type integer
Condition: equals (=)
Parameter: master_id, Type: Application
1. Now this is slightly different to your example in the previous post as it said master_id for the Field name but shouldn't I be setting this to the field name to the field in the detail table - id and not referencing the master grid?
2. Following on from that shouldn't the Parameter be gridname_fieldname (ie: master_id) for the application parameter?
3. Looking at the code behind the scenes for the detail grid it seems to be getting a session variable named girdname_fieldname (ie: master_id) so can I assume that given the 'application' type that this session varible is being created / looked after by the master grid code?
Thanks so much thus far...
|
|
|
|
jacem
Posts: 87
|
| Posted: 06/19/2007, 10:30 PM |
|
Ok - it is getting interesting...I tried option 1 from the previous posts and it works - well sort of...
I set a session on the before show event of the master grid. Then in the child / detail grid I simply have the where clause of the datasource set to restrict the records from the table according to the sesson variable value. Bring up a master record and only its related child / detail records are display. Great!!!!
However if I advance the master grid to the next record or if I do a search on the master grid, while the next master record displays the previous set of related child / detail records remain. If I advance to the next record again, then the related child / detailed records for the previous master record appear. and so on. I am one step behind in detail records as I move around the master tables records - slightly out of sync. That is unless I do a refresh on the browser then it resyncs and the child / detail records for the displayed master record are displayed correctly.
It seems I need to set the session variable on a different trigger or somehow trigger the child / detail grid to re-datasoruce select the records for its grid using the up-to-date session variable...I am displaying the session variable in the child / detail grid as a visual check and it always displays the correct master ID but he records in the child / detail grid are selected using the previous master records' ID.
Clearly I am close to getting all I wanted...and it will work - I just have to get the events right...true?
Thanks so much so far...
|
|
|
|
Wkempees
|
| Posted: 06/20/2007, 5:20 AM |
|
Jacem,
Nice to have you triggered but the two last of your posts show me that
you are thinking way to deep.
Reread my previous post and take it to the letter.
In respect to your last post (route 1):
The setting of the session variable HAS to take place in The BeforeShow
of the Hidden field "ID".
It is only usefull if this is a one row grid with navigation, as you
stated it was.
Do literaly what I wrote as to route 1 and it will work.
What I mean by thinking to deep:
If I write
Table Parameter
Field Name : master_id, type integer
Condition: equals (=)
Parameter: id, Type: Application
I mean this to be in the WHERE part of the DETAIL grid.
master_id is then a reference to the MySQL table field master_id within
the detail table (which I assume to be present) and Parameter id, type
application is a way to tell CCS to go look for a variable named "id"
within the URL, GET and or POST.
I would like to respond more to your two posts but it is a bit much.
Go and try to get it this way, replacing my field names with your actual
field names.
Walter
|
|
|
|
jacem
Posts: 87
|
| Posted: 06/20/2007, 7:14 PM |
|
Thanks for your patience Walter.
I have literally gone back to your previous posts and confirmed I have all in place as per option 1 and I still get the 'lag' problem as I move from record to record in the Master editable grid (which is displaying only 1 row/record at a time). The variable is getting set from the master_id before_show trigger and I have confirmed this with an echo. I have also retrived the session variable in the row of the child / detail editable grid displaying in a label and displays as expected on each row. I have the where set as suggested to retrict the grid to the session variable and it works on refreshing the page perfectly - it just lags as the master grid records are transioned one by one.
I have put PHP echo's into the standard CCS code to track down the issue. Here is what I have found (yes I am thinking deep as it is not working for me as is...)
The variable 'id' is getting set as part of the upper master grid's before show for that field. I then went deeper to see what the variable was as various parts of CCS's back end triggers and I found that the problem can be traced back to the Prepare() function. See the standard CCS generated code below:
__________________________________________________
//Prepare Method @204-9FB03A59
function Prepare()
{
global $CCSLocales;
global $DefaultDateFormat;
$this->wp = new clsSQLParameters($this->ErrorBlock);
echo " Prepare method called.";
$this->wp->AddParameter("1", "sesProspectIDMaster", ccsInteger, "", "", $this->Parameters["sesProspectIDMaster"], "", false);
$this->AllParametersSet = $this->wp->AllParamsSet();
$this->wp->Criterion[1] = $this->wp->Operation(opEqual, "ProspectID", $this->wp->GetDBValue("1"), $this->ToSQL($this->wp->GetDBValue("1"), ccsInteger),false);
$this->Where =
$this->wp->Criterion[1];
echo " ProspectIDMaster:";
echo CCGetSession("ProspectIDMaster");
echo " Criterion[1] set to:";
echo $this->wp->Criterion[1];
}
//End Prepare Method
_________________________________________________
I have added the echo's to see what is going on. When the page is first loaded the echo's reveal that the Criterion[1] is set to the correct 'id' value (in my case the variable is called 'ProspectIDMaster' however move to the next record in the master editablegrid and the Criterion[1] is left at the last master_id value even though the 'id' (ProspectIDMaster in my application) has advanced to the next id number as per what is displayed in the master editgrid. Criterion[1] is then used to set the 'Where' in subsequent functions.
So prblem may well be in the code:
$this->wp->AddParameter("1", "sesProspectIDMaster", ccsInteger, "", "", $this->Parameters["sesProspectIDMaster"], "", false);
where CCS doesn't go get the current value of the master_id variable. CCS has prefixed my variable name 'ProspectIDMaster' with 'ses' to indicate to itself that this variable is a session variable but why and how this works is too deep for me. Maybe its the next line of code in CCS I am not sure - all I know is that the session variable has the right value during this event.
Delving into this problem has been a good learning experience however I still have the lag issue.
|
|
|
|
jacem
Posts: 87
|
| Posted: 06/20/2007, 8:37 PM |
|
Update: The select statement for the child/detail grid get built before both grids are displayed and therefore before the master_id session variable is updated as part of the before_show event for that field in the master editable grid. That's why the session variable is still on the last value when the select statement for the detail grid is being built but when the variable is displayed in a label in the child/detail grid it is after the master grid has been shown and therefore the session variable is updated.
The upshot is that updating the session variable as part of the before_show event for the field on the master grid is not early enough as the WHERE for the child/detail grid has already been built.
So I have to find another eariler place set the session variable after we now the new master_ID.
|
|
|
|
jacem
Posts: 87
|
| Posted: 06/20/2007, 11:50 PM |
|
Resolution!!!
I had to place some code on the trigger BeforeSelect for the child/detail editable grid:
$Container->DataSource->Where = "ID = " . CCGetSession("master_id");
The session variable is updated on the trigger BeforeShow of the id field on the master editable grid.
Leaving it to the ‘where’ setting in the datasource for the child/ detail grid puts you out of step with the session variable being updated, making your child/details records being for the master record you were previously displaying before you moved to the next record.
Take a bow Wkempees/Walter as you were the catalyst for resolving this.
To be honest I don’t know why everyone doesn’t use this approach for typical master/detail type applications. I just hope nobody comes back with "ah yes but this aproach will fail when this situ occurrs!". 
|
|
|
|
wkempees
|
| Posted: 06/21/2007, 2:49 AM |
|
Jacem,
Glad you solved it, good thinking, good debugging.
I was to busy and you were in a highly paced posting rythm, too fast too
furious (lol).
Anyway I have to reread you post later today and see if I can answer some
more of your questions.
Someone will definitly respond with some barriers as this method will only
work nicely with your single row Master table, as you heavily rely on the
selected rows id being posted.
If you had a multi row master grid and needed to do this while mouse
scrolling the rows, it would be a whole different ball-game alltogether.
The 'ses' prepending of the ProspectID happened to me also the first time I
build your example.
In itself it is not a problem as long as one notes this and changes the
where parameters for grid2.
Oh well it is working, and I might add some comments later on.
Now go and try method2, the one with the Hidden field and the appropriate
Where parameter settings.
Also please post here your table lay-out for this case, for me to have a
look at.
Good job,
Walter
|
|
|
|
jacem
Posts: 87
|
| Posted: 06/21/2007, 2:56 PM |
|
Hello from Australia,
To be honest Walter, the posting of the issues and further investigation was helpful in itself as it helped to ensure I got my facts straight so I wasn't being defeated by bad logic in my testing / debugging.
You re right - definitely a single master record solution. The user would have to *point* at a particular master record if multiple masters were being displayed in the master grid. A button to do this in each master row would just have to set the session variable master_id (using our variable name example) plus a call to refresh the page or even just the child / detail editable grid (not sure how to do a refresh of just one grid on a page without the page refreshing).
The application is a typical direct marketing database where prospective customers can be assigned to one or more lists (Segments).
Table def for the master in my application (prospects):
ProspectID Int Not Null Auto Inc Unsigned
FirstName Varchar(45) Not Null
Surname Varchar(45) Not Null
Blah blah blah
more customer / prospect fields used for marketing
Table def for child/ detail in my application (prospectsegments):
ProspectSegmentID Int Not Null Auto Inc Unsigned
ProspectID Int Not Null Unsigned
SegmentID Varchar(20) Not Null
Created DateTime Not Null
ByWho Varchar(20) Not Null
ProspectID is the link field back to the master table so it is a one (prospects) to many (prospectsegments) relationship. The SegmentID is just a key to a list of segments (table) the user can choose from.
Further testing success. By the way inserting records in the child/detail table is unaffected by this solution as expected so I have the related child/detail records listed for the particular master (prospect) record plus three blank spots for new segment assignments (new related child / detail records for the master record).
The other nice thing I have realised is there is nothing stopping me from having a second child/detail grid on this same page. I actually have a need for this in this application. So today I am going to clean up all the little detail grid functionality tid bits (default dates, who etc…) and then add a second grid so the user can also assign an unlimited number of *reminders/notes* for each prospect. Therefore, this page will have a master record display and two sets of related child/ detail records. Having fun!
Hope these posts help someone else as so many other posts have helped me in the past – good to finally give something worth while back to the CCS forums.
Thanks again Walter.
|
|
|
|
Wkempees
|
| Posted: 06/22/2007, 5:03 AM |
|
|
|
|
|
Damian Hupfeld
|
| Posted: 06/22/2007, 5:43 AM |
|
Not nearly as verbose as your usual responses!
"Wkempees" <kempe819@planet.nl> wrote in message
news:f5gdql$e5f$2@news.codecharge.com...
>
|
|
|
|
jacem
Posts: 87
|
| Posted: 06/22/2007, 2:45 PM |
|
Expansion of the original solution:
Seems you can have as many child/detail grids as you like. I have added a second and as expected it works perfectly.
(I'm trying to write less now too!) 
|
|
|
|
|
