shannah / xataface

Framework for building data-driven web applications in PHP and MySQL
http://xataface.com
GNU General Public License v2.0
138 stars 58 forks source link

Multiple calculated fields in fields.ini #115

Open viharm opened 4 years ago

viharm commented 4 years ago

Hi @shannah , I've always been a fan of Xataface and very much appreciate the work you have been doing for years. I very much enjoyed your guidance whilst creating the LDAP auth plugin.

I am creating a personal inventory application which has tables for items (tbl__Item) and categories (tbl__Category). I have established a many-to-many relationship between these two tables using relationships.ini.

I would like to count the number and the total cost of items in a category when I list the categories.

I have followed the guide at http://xataface.com/documentation/how-to/how-to-add-calculated-fields-to-your-list-and-details-view to add the count using a SQL statement in the beginning of fields.ini for the categories table (tbl__Category).

However when I add the second SQL statement for cost, the list view shows only the cost. Is there a way to have multiple calculated fields in a list and browse views?

LHammonds commented 4 years ago

If the result can only show the ID and another field, use the concat() function in the database to create the desired display as a single field using SQL. I have used concat() to modify a dropdown list that just showed a city location name to also show the State+City as if it were a single selection.

airdrummer commented 4 years ago

i'd also like to add a total to a list of related records, but the docs page referenced by OP says: What did this NOT do? So far this only helps us for the list tab and the View tab. This extra column will not show up in related records lists. Possibly in future versions, this support will be added. as this page is "as of 0.6.3" is there a way now?

airdrummer commented 4 years ago

i'd also like to add a total to a list of related records, but the docs page referenced by OP says: What did this NOT do? So far this only helps us for the list tab and the View tab. This extra column will not show up in related records lists. Possibly in future versions, this support will be added. as this page is "as of 0.6.3" is there a way now?

viharm commented 4 years ago

@LHammonds , thanks for your input, however wouldn't the concatenation logic apply to string fields? I was trying to include two calculated fields to show total count and total cost. Would your suggestion still be applicable?

LHammonds commented 3 years ago

@viharm , you should be able to concatenate it. The concat() function will convert any column input into a string before the operation. And of course, any output from the function will be of string type. You could make the result field end up looking something like "Count=##, Total=$##.##"

viharm commented 3 years ago

@LHammonds , thanks, I’ll try this out, but will need a separate test install for this, so will need some time.