CodeCharge Studio
search Register Login  

Visual Web Reporting

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> PHP

 How to access fields for label with f() when using custom SQL

Print topic Send  topic

Author Message
pbarkley

Posts: 37
Posted: 02/01/2013, 4:55 PM

From time to time I need to set the value of a label in the BeforeShow event using values from multiple fields. For example, because of the hierarchical nature of some data (yes, it's necessary because of the data complexity of this project), I have three copies of the site table joined in various ways using the Visual Query Builder. So, I may need to populate the SiteName by looking at all three values and performing some tests to see which one I really want. Since I'm already on a given row, in the BeforeShow event I can access all three of these values easily like this:

$s0 = $Container->ds->f("site_SiteName");
$s1 = $Container->ds->f("site1_SiteName");
$s2 = $Container->ds->f("site2_SiteName");

After performing some tests, I can set the value of the label to, say, $s2, with no problem.

However, this doesn't seem to work when I need more complex SQL queries that I can't write in the VQB. (This is the only difference I can see between when this method works and when it doesn't.) I have to go into the BeforeExecuteSelect event for the grid and write my ds->SQL there. I can get the correct values for the table, but it seems that the f() function will no longer retrieve anything, so apparently I don't understand what's happening here. Since I know the data is correct (I can echo the SQL to the screen, then run it in phpMyAdmin, which populates the fields I need), why can't I access various fields using the f() function without getting a null? The exact syntax shown above does not throw an error, but it also does not pick up anything. This particular project is still in CCS 4.3 if that makes a difference to anyone.

I really don't want to run a CCDLookup in BeforeShow for the label because a) I already have the data I need in the row, and b) this is such a complex query that running another one inside would kill everything. I just want to access the data that I'm already returning.

Anyone have any idea what I'm doing wrong here? I'm sure it's a boneheaded conceptual thing I'm missing, but I don't see it.

Thanks!
View profile  Send private message
DataDoIT
Posted: 02/01/2013, 5:24 PM

Use an alias for all of your SELECT fields, and reference those aliases.
laneoc

Posts: 154
Posted: 02/01/2013, 10:13 PM

When it fails, do all field value retrievals fail? Or do some work and some fail?

If some work and some fail, maybe field names you call with ...->f("field2") don't match the field names outputted by the SQL statement?


_________________
Lane
View profile  Send private message
DataDoIT
Posted: 02/02/2013, 7:38 AM

Output the SQL also to see what you have.

echo $Container->ds->SQL . $Container->ds->Where . $Container->ds->OrderBy;
pbarkley

Posts: 37
Posted: 02/02/2013, 5:43 PM

@laneoc, that's a good suggestion because in fact I've burned myself several times that way, particularly by not watching capitalization. In fact, in my 500+ pages of notes I've made in the last couple of years working on CCS (enough material for a book for noobies showing pretty much how to screw up all parts of CCS!), I noted that the example that I put in my original post did not work until I capitalized ds->f("site_sitename") to ds->f("site_SiteName"). However, that example worked, when spelled correctly, with a form that used the VQB, and I've used it a number of times that way with no problem.

Although it's easy to make false assumptions like, "everything else I did is perfect, so it can only be X", this really appears to be a case of ds->f() not working with custom SQL for some reason. I was hoping that someone else had run into this and knew how to get it to work. Obviously, plenty of people are using custom SQL. I love the VQB, but of course it can't do everything.

@DataDoIT, I'm definitely using aliases because I'm joining the same table several times. I've tried referencing these fields using the real field name (first line below) and the aliased table, and by aliasing the field name itself (second line). Both return nulls. I also tried several other unique fields that do not require any aliases, and they fail as well.

$org3b = $Container->ds->f("Organizationp3b.OrganizationName");
$org3a = $Container->ds->f("OrganizationParentName3a");

@DataDoIT, I've definitely echoed the SQL to the screen in test mode, and I can copy that SQL to phpMyAdmin and execute it. I can also drop any of those fields into a label, so the SQL itself isn't the problem. I will mention that I still have the original VQB in place that I used for a year, but with the WHERE and ORDER BY ripped out; if I delete that CCS throws errors. So I left it alone and overwrote it in the BeforeBuildSelect.

I did one other "off" thing and that's building up the SQL by hand and adding the WHERE and ORDER BY right into the ds->SQL rather than using the ds->WHERE, etc. This way I have the entire SQL in one place. Since this particular report is intended for Excel (after it hits the screen first in typical CCS style), I don't need sorters to work off the ds->WHERE or anything.

In the past when I've been unable to get ds->f() to work with custom SQL, I've resorted to attaching all the fields to hidden controls and then referencing the hidden control. I don't want to do this because I actually have 5 potentially different organization names in a hierarchy, only 1 of which I want on any given row. So that's a lot of hidden junk just to work around this issue.

Since this is not an easy thing to search for in the forum, I was hoping that someone had already done this and knew why this technique of using ds->f() won't work with custom SQL, or how it can be made to work.

Thanks to everyone!
View profile  Send private message
DataDoIT
Posted: 02/02/2013, 7:13 PM

I can't say I've experienced this problem. Something else to try would
be to go ahead and wipe out the .php file, and let it regenerate. That
will regenerate all of the Custom SQL and accompanying objects.
pbarkley

Posts: 37
Posted: 02/02/2013, 8:06 PM

Interesting suggestion. Although I've never tried deleting the PHP file before to "reset" things, I suspect that it might have been the right answer when CCS, as opposed to me, was confused in the past. I'll keep it in mind for other problems as well.

However, I deleted the PHP files for both the page and the events, and was quite surprised to see all my custom code disappear! I'm guessing you didn't mean to delete the events PHP file. :-) Oh well, that's what backups are for, and no harm done.

Unfortunately, it didn't fix the problem, but thanks for the thought and a jolt of adrenaline when all my code disappeared!
View profile  Send private message

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

Internet Database

Visually create Web enabled database applications in minutes.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright © 2003-2004 by UltraApps.com  and YesSoftware, Inc.