laneoc
Posts: 154
|
| Posted: 01/04/2018, 4:38 PM |
|
I understand why an insert cannot be automatically built for an editable grid when the query involves multiple tables.
My editable grid has a working automatically built insert. But when I switch from Table to SQL for my grid query, CCS no longer can build the insert - even though I'm only using one, very simple table.
Under what conditions would CCS fail to build an insert?
_________________
Lane |
 |
 |
pbarkley
Posts: 37
|
| Posted: 04/16/2018, 2:48 PM |
|
This is an old post now, but I just saw it today. Normally an editable grid is handled through the Visual Query Builder, and this works great if your grid only includes one table. I often have more than 1 table because I need to sort the entire grid on a field that is not in my core table. For example, I might have a table of traffic values by zone, and that traffic table has a ZoneID in it. But I need the whole thing sorted by Zone Name, which is only in the separate zone table. I could look up the zone name easily enough and display it before the row is shown, but because that Zone Name is NOT in the underlying table that the grid is based on, I can't sort it that way. Even the sorter in the header won't work on that value.
So sometimes you have to use multiple tables to get your data in the grid to display correctly. I often have 3 or 4 tables. As soon as you have more than one table, though, you MUST use the custom insert and update and delete at the bottom of your properties window. It is relatively easy to map a control to a field--they're probably already mapped when you designed the grid elements and named them the same, so you just have to select the control name and the field name that match in the custom table dialog.
HOWEVER, I have recently found that there is a serious design flaw in CCS IMO, because the custom update, as an example, will fire BEFORE the many other events where you might want to put code. The Before Build Update is where I often update hidden fields and of course it fires before the actual update when you're using the regular Visual Query Builder approach. However, it does NOT fire before the actual update when you use the custom update. I found this incredibly vexing, and it wasted a huge amount of my time. I tried inserting diagnostic code in every event I could think of that might fire before the actual update to the database, and none of them work. Of course recently I've had trouble getting even buttons to fire correctly, so either I've started doing something wrong, or CCS is starting to get very unreliable for the application I'm currently working on, which is fairly large. It's worrisome either way. 
For anyone reading this, the solution I found was to go into the Before Show Row event, and update the label I wanted (in this case the last column which sums the previous 4 columns and stores the data redundantly so it doesn't have to be calculated on the fly when needed to order data). Now this will only update the label showing the sum, it doesn't actually update the database field that the label is based on. But I was able to add code to specifically go out to the database and update the database during this event. I consider this incredibly kludgy, but it solves the problem. Obviously you can test the update on the screen and then look at the actual table and see it's not updating the data until you add code. Here's the code in the Before Show Row event:
// Update the calculated total field.
// So, after updating the label so it displays correctly, also update the database directly.
$tc = $Container->DataSource->SequenceNumberRoadNetwork->GetValue();
$tc = $tc + $Container->DataSource->SequenceNumberUtility->GetValue();
$tc = $tc + $Container->DataSource->SequenceNumberAdjacentGrowth->GetValue();
$tc = $tc + $Container->DataSource->SequenceNumberOther->GetValue();
// Set the label on the screen.
$zonesequence_scenario_zon->TotalCalc->SetValue($tc);
// Now force the database to update as well.
$zid = $zonesequence_scenario_zon->ZoneID->GetValue();
$sid = CCGetParam("s_ScenarioID","");
$db = new clsDBConnection1();
$SQL = "UPDATE zonesequence SET TotalSequenceNumberCalc = ".$tc."
WHERE ScenarioID = ".$sid. " AND ZoneID = ".$zid;
$db->query($SQL);
$db->close();
So, if this isn't clear, you're working in the custom insert and update area, and flipping back and forth between table (which is how I usually map my updates) to SQL. So any time you have a multi-table editable grid, you're going to have to use the custom update and you're going to have the problems I discussed above. Hope this helps someone.
|
|
|
|
MichaelMcDonald
Posts: 640
|
| Posted: 04/21/2018, 3:49 AM |
|
Maybe not in context exactly but this editable grid talk jogged my memory.
I don't bother with an insert in the grid. I just put an insert record above it and make the html look like it's all the one form, even better if it's all in an update panel. Then if you want to created linked keys, you can do a mysql_insert_id() after the insert and insert that value into another table and so on .... and if it is in an update panel you could set the mysql_insert_id() as a session variable and use the session variable in the before show or before show row, and then clear it in the on submit or after update. There is nothing I have ever not been able to do with this method.
_________________
Central Coast, NSW, Australia.
|
|
|
|
|
