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!