Adding select to a grid (subform)

[barryrs]

Great product!! I've learned a lot from the wiki and forums!!

I used http://xataface.com/wiki/relationship to model adding multiple allocations associated with the original transaction.
However, I have 2 issues I can't seem to fix.

1. Defining category.name as a "select" widget in it's fields.ini looks great on the transaction.new form,
(as a grid with a select widget) but on the category.new form it should still be a "text" widget.

2. I can't seem to stop it from adding new categories when it posts a transaction, even though catagory is just a lookup.

Basically, I want to add multiple allocations to a transaction using a select for the category names, while still being able to add new categories...

I hope I've been clear enough, as this concept may be useful to others. Thanks so much!! -Barry

here's the table structure/ relevant info. (i hope)


[category]
id
name

[transaction]
id
amount
etc.

[allocation]
catid
transid
amount

(transaction.relationships)
[Allocation]
allocations.catID = categories.CategoryId
allocations.transID = "$TransactionId"


(transaction.fields.ini)
[Allocations]
widget:label = "Category Allocations"
transient=1
order=5
relationship=Allocation
vocabulary = Categories
widget:type=grid
widget:columns="Name, amount"

(transaction.valuelists.ini)
[Categories]
__sql__ = "SELECT CategoryId, Name FROM categories ORDER BY Name"

(category.fields.ini)
[Name]
widget:label = "Name"
widget:type = select

[shannah]

Your 2 problems are related. Grid widgets work best in a one-to-many relationship... In your current setup, when you add a row to the Allocations grid it is actually creating a new category.

The easiest way around this is to either change the relationship to a one-to-many, or create another dummy relationship that is just one-to-many.

E.g.

Change your Allocations relationship to just:

Code: Select all

[Allocations]
allocations.transID = "$TransactionId"


Then make the allocations.catID field into a select list using the Categories valuelist you defined. (But you'll need to either move the valuelists.ini into the root folder, or redefine this valuelist in the allocations table directory).


Then change the transactions.Allocations fields.ini section to look like:

Code: Select all

[Allocations]
widget:label = "Category Allocations"
transient=1
order=5
relationship=Allocations
widget:type=grid
widget:columns="catID, amount"


Note I removed the vocabulary directive (it is never needed in the grid widget), and I changed the widget:columns directive to include the catID field instead of the Name field. This should show up as a dropdown because you defined it that way in the allocations table. You are now free to use a text field widget for the Name field on the edit/new record form of categories.

-Steve

[barryrs]

Thanks so much! That worked perfectly! Exactly what I wanted! (and it smartened up a couple of other things along the way as well!!(Category names now show in the allocations table)

One last thing, how might I best access the transaction.allocation data in the beforeSave() trigger? (It needs to add up to the transaction value).

Thanks again, have a wonderful day!! -Barry

[shannah]

It will be stored as an data structure in the Allocations field (i.e. the name of the transient field).

To see the structure, you can just do:

Code: Select all

$val = $record->val('Allocations');
print_r($val);
exit;


In a trigger.