Depselect list customization

A place for users and developers of the Xataface to discuss and receive support.

Depselect list customization

Postby thedesignedge » Wed Sep 26, 2012 11:31 am

I am trying to allow us to add invoiceitems to an invoice using user friendly selections from a list. Invoice items will be added via populating an "invoiceitems" table consisting of: InvoiceID, InvoiceItemID, JobID, ItemID. We will populate the invoiceitems table through a grid widget setup in the invoices table in xataface so the InvoiceId is preselected. The InvoiceItemID is an auto entry via a trigger in MySQL leaving only a JobID and associated ItemID to be entered manually.

Not sure if it matters, but a jobitem's PK is a compound key of JobID and ItemID. A job's PK is simply the JobID. Tables involved in one way or another are customers, jobs, jobitems, invoices, invoiceitems.

I have gotten depselect up and running and got it to select a [Job]ItemID based on a JobID. But having a list of JobID's and ItemID's to select from is a bit cryptic.

I have played around and gotten a select widget to display "Customer name - Job Name" and select the associated JobID by configuring as follows:

fields.ini =
[JobID]
widget:label = "Job"
widget:table = jobs
widget:type = depselect
vocabulary=jobs

[ItemID]
widget:label = "Job Item"
widget:type = depselect
widget:table = jobitems
widget:filters:JobID="$JobID"
widget:labelcol=ItemID
widget:filters:ItemCompleted="1"
widget:filters:ItemInvoiced="0"

valueslists.ini =
[jobs]
__sql__="SELECT JobID, CONCAT((SELECT NamePri FROM customers WHERE customers.CustomerID=jobs.CustomerID),' - ',JobTitle) FROM jobs ORDER BY JobTitle"

The list looks great using the above configuration and is very user friendly but I cannot for the life of me figure out how to get the same list display using depselect. This leaves us trying to select a JobID that is cryptic. (In the end, I'll need to also need to get the Item Id to show the ItemName and ItemID as well, but one step at a time.) I have scoured xataface's site and forums for hours and tried many different combinations of solutions, sometimes just stabbing in the dark but none work thus far using depselect.

I am new (1+ month) to LAMP, Xataface, and Javascript, with no PHP or Javascript experience (yet), thus, my past solutions have not delve into delegate classes, or php/java scripting solutions. I also have yet to figure out how to implement a grafted field for the simple sake of example to myself. Point being, I have been trying to solve this using only the ini files, and am fully prepared to take the plunge into other solutions if this is not possible.

Could someone point me in the right direction for a solution? Can this even be done using the ini files?

I am using xataface 1.9.xx and running PHP 5.3.x.

Thanks for even reading this mess!
thedesignedge
 
Posts: 4
Joined: Wed Sep 26, 2012 10:27 am

Re: Depselect list customization

Postby shannah » Wed Sep 26, 2012 12:35 pm

Add the customer name to the jobs table as a grafted field. Then use the widget:labelcol directive to specify that you want to use this grafted field as your label column for the JobID field's depselect.

-Steve
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: Depselect list customization

Postby thedesignedge » Thu Sep 27, 2012 2:22 am

Thanks steve. Knowing a direction to go really helped alot!

So, progress, although not much in the scheme of things.

I moved to a grafted field via a delegate class and got it to work (mostly) using:

Code: Select all
<?
class tables_invoiceitems{
   function __sql__(){
      return "SELECT invoiceitems.*, jobs.JobTitle FROM invoiceitems LEFT JOIN jobs ON invoiceitems.JobID=jobs.JobID";
      return "SELECT invoiceitems.*, jobitems.ItemName FROM invoiceitems LEFT JOIN jobitems ON invoiceitems.ItemID=jobitems.ItemID";
   }
}
?>


With a fields.ini:
[JobID]
widget:label = "JobID"
widget:table = jobs
widget:type = depselect
widget:labelcol=JobTitle

[ItemID]
widget:label = "ItemID"
widget:type = depselect
widget:table = jobitems
widget:filters:JobID="$JobID"
widget:labelcol=ItemName
widget:filters:ItemCompleted="1"

The first grafted field works as expected; it's joined in the list view and provides a jobtitle selection using a depselect widget, but the second grafted field is not joined in list view, and I cannot get it to save the record with the selected the ItemID.

Also, I cannot access the delegate class query in the fields.ini via an assigned alias; although maybe this is purposeful. That is, using widget:labelcol=job, and adding "as job" after jobs.JobTitle just doesn't work like I thought it would.

Finally, I was really hoping to join and show something a bit more complex in the list (simpler to the user though). Right now it is a list of every job simply by a title. We really need to both filter it by a name and display the customer name along with the job title.

Tables as follows:
Customers:
CustomerID
NamePri


jobs
CustomerID
JobID


invoices
InvoiceID
CustomerID
InvoiceItemID


invoiceitems
InvoiceID
InvoiceItemID
JobID
ItemID


I run into where I spend time trying to get queries to simply work and return results in MYSQL workbench, only to find most will not work in the application; usually when I CONCAT, or nest SELECTS or JOINS. Or, like before, a query worked with a vocabulary and select list, but not a delegate class and depselect.

For example,
I got the following query to work in MYSQL Workbench and it JOIN's with a new column named "job" with an entry of "CustomerName - JobTitle" in MYSQL:

Code: Select all
SELECT invoiceitems.*,
      CONCAT((SELECT NamePri FROM customers WHERE customers.CustomerID=jobs.CustomerID),' - ',JobTitle) as job
FROM invoiceitems
LEFT JOIN jobs
on invoiceitems.JobID=jobs.JobID


But when I put this into the delegate class, and try to set the widget:labelcol to "job" the invoiceitems table throws an error page:

Fatal error: Failed parsing SQL query on select: SELECT invoiceitems.*, CONCAT((SELECT NamePri FROM customers WHERE customers.CustomerID=jobs.CustomerID),' - ',JobTitle) as job FROM invoiceitems LEFT JOIN jobs on invoiceitems.JobID=jobs.JobID . The Error was Parse error: Unexpected token "as" on line 1 SELECT invoiceitems.*, CONCAT((SELECT NamePri FROM customers WHERE customers.CustomerID=jobs.CustomerID),' - ',JobTitle) as job FROM invoiceitems LEFT JOIN jobs on invoiceitems.JobID=jobs.JobID ^ found: "as" in /var/www/dataface/lib/SQL/Parser.php on line 1765

I thought moving to a delegate class would open up for a more complex query like this and I guess it doesn't. Or maybe I am missing something simple (usually the case as I learn this stuff)? Can this stuff be done through queries and I just gotta know the rules? Or am I asking to much from the queries in the box of the application?

Being so new, I trying to familiarize myself with what the possibilities even are so I know where to look for answers in the future, e.g.modules, widgets, queries, php, javascript, even html. The delegate class took long enough to figure out and that was super simple! :) Granted a nice concatonated left join query didn't make things easy either.
thedesignedge
 
Posts: 4
Joined: Wed Sep 26, 2012 10:27 am


Return to Xataface Users

Who is online

Users browsing this forum: No registered users and 25 guests

Powered by Dataface
© 2005-2007 Steve Hannah All rights reserved