help with design

[compudude86]

actually, one last question on here, from now on ill start new threads, but here goes:
i have a few fields, which when a formula is applied, gives me a percentage. now, in mysql, i use a view to do this, but when i point dataface to the view, it shows zeros. i want my dataface to have a column, "GPM", to display the otuput, like so:

(SRP-NETBTL) / SRP

and i want it to have a rounded number, with a following percent sign. any help would be appreciated

[shannah]

Hi Joe,

This feature is not well tested but you can give it a shot.
http://framework.weblite.ca/documentation/how-to/how-to-add-calculated-fields-to-your-list-and-details-view

It is also possible to add calculated fields in the delegate class, but these wouldn't automatically show up on the details or list view. They would just be available to you at an API level.

[compudude86]

ok, so how would i go about doing it? i read it and im just really confused on it

[shannah]

You would add a line to the beginning of your fields.ini file as follows:

Code: Select all

__sql__ = "select *, (SRP-NETBTL)/SRP as GPM from tablename"


As I said, I haven't tested very thoroughly, but it should add a GPM column to your list view and details view.

Best regards

Steve

[compudude86]

ok, its fine, i guess ill be the test subject then, is there a way to make the column a (19,0) like you would in sql? and how would you add a percent sign to the end of it?

[shannah]

Use the mysql FORMAT function to display the number to the correct decimal places, and use the CONCAT function to prepend a dollar sign.

e.g.
__sql__ = "select *, CONCAT('$',FORMAT((SRP-NETBTL)/SRP),2) as GPM from tablename"

Or some variation on that... Check the mysql string functions manual page for more info about FORMAT and other functions that would be useful in this regard.

Hope this helps.

-Steve

[Aoirthoir]

Playing around with this, it seems, this is only for calculated totals? It cannot be used to just include a field from another table into your table?

For instance I have two tables. Customer. Return. Customer has an ID which is contained within Returns as CustID. In the Returns list and view screens, would I be able to display the Customer name by setting up a Customer.ID = Return.CustID ?

This is what I tried from the code posted above..but clearly I've got it wrong:

__sql__ = "select Customer.LName as Name from Customer where Customer.ID = Return.CustID"

This is in tables/Return/fields.ini

Thanks for considering this.

[compudude86]

ok, i tried the code and it brought down my dataface so i took it off. then i tried to switch to my mysql view, and when i did it was doing the thing of only showing the single items, so i made a column in my view to carry over the key, except it doesnt show as a key. does anyone know how id add a primary key to a view?

[Aoirthoir]

Ah not sure Joe. I will see if it works. I've desired a dataface interface to MySQL views for a while. I added it to the issue tracker. Don't know if it will be on the schedule anytime soon..but the primary key thing might solve it...

[shannah]

ok, i tried the code and it brought down my dataface so i took it off. then i tried to switch to my mysql view, and when i did it was doing the thing of only showing the single items, so i made a column in my view to carry over the key, except it doesnt show as a key. does anyone know how id add a primary key to a view?

OK.. if it brought down dataface that means there is just a problem with the SQL query. Try running the query directly in mysql or phpmyadmin and modify it until it works. My hunch is that it is complaining about using CONCAT() with a non-string value.. might have to do some conversions. What is the SQL query you used to define your view in MySQL. You can probably just use this same query in the __sql__ line of your fields.ini file.

As far as making Dataface work with Views, it may be a simple mod, but I am currently running only MySQL 4.1.x on my systems - I'll have to set up mysql 5 to begin developing for views ... ... So it may take some time.

Best regards

Steve

[compudude86]

ok, i tried using my query too, doesnt work. how much of a modification is needed to display views? all i can see is it is just getting past the primary key issue, it displays everything else just fine, except for it only showing a line at a time because of the key

[shannah]

In Dataface primary keys are extremely important because I need a way to uniquely specify a record using GET parameters. I suppose that the primary key could be specified in the fields.ini file to handle the case where it can't be retrieved from the database.

In fact, this would probably work in the current version of dataface. Give it a try.

In the fields.ini file for the view that you are trying to use add the following to any field that is part of the primary key:

Key = PRI

for example, suppose that the ID field should be treated as a primary key. Then your fields.ini file should contain:

[ID]
Key = PRI

Give it a shot and let me know how it goes.

-Steve

[compudude86]

hmmm, doesnt seem to work, in my fields.ini, i add KEY = PRI under the [ID] tag, right? that doesnt work though

[shannah]

Not KEY = PRI

Key = PRI

Case matters unfortunately.

-Steve

[shannah]

The other thing is that the [ID] tag was only an example assuming that the field that you wish to use as a primary key is named 'ID'. If you are using a field named 'Name' as the primary key, then it would appear under the [Name] section.

If you are using a multiple field primary key, then you would add 'Key=PRI' under each field section in the primary key.

If you still can't get it working, perhaps try posting relavent table and view definitions, and your fields.ini file and I can give more specific instructions for your situation.

Best regards

Steve