CCK Field: Data-row reference

Take a look at data_node module (ships with Data). You can use data_node to reference single records in a data table from a node. data_node comes with a views field that provides the interface (add record/remove record from node) and a views data definition that declares the join between the node and the data handler.

It is not yet very well documented, if you wrap your mind around it, I would appreciate some documentation as contribution : )

The current functionality is a little different than what the OP intended I think. Currently you can 'hard-code' a connection between a node and a data row, but it does not allow, for example, a user to select a data-row on the node-editing page (as far as I can see).

I would suggest the following, which should be rolled out as a new module, but might be packaged in with the Data module tar.gz

1. A new cck field type: data_row_reference
2. A field widget that allows you to select a row in a similar fashion to hierarchical select. For myself, we will have a data table with about 160,000 rows. I would recommend the following widget:
   • In the widget settings, the admin should select some columns to include in the widget and order them.
   • The widget would then display a select element with all distinct values from the first column. (SELECT DISTINCT column1 FROM table).
   • Once the user has select from the first column, the second column appears with distinct options defined by (SELECT DISTINCT column2 FROM table WHERE column1 = whattheyselected).
   • So on and so forth until the user has drilled down to a single row
3. The formatter might just be the admin selecting a row, and then having it themed by the Filter handler in the Data Table options
4. Views integration i'm not sure about. Suggestions?

Geomemes is willing to code this up, and will be submitting patches here as we go along, but we would love some input from the Data module maintainers for any suggestions, ideas, and/or code review.

Thanks!

subscribe

Just an update: We are almost done a first release of this which we will post here for peer-review in the next couple of days.

We would be very interested in helping testing.

Okay - here is a first cut. Peer review is welcome.

Some issues we ran into:
1. To properly reference a data-record, there needs to be an ID column in the table to uniquely identify which data-record we are referencing. We allow the user to select any field that looks unique. It would be handy if users had an interface for adding an auto-increment column for data tables that don't have a unique identifier
2. Some JavaScript was needed on the field settings page to make sure that the fields available to select are in sync with the table being selected.

Some things we haven't yet done:
1. Good formatter options. Right now we are just displaying the value of the reference field
2. Views integration. We basically have the interface to uniquely reference a data record, some work needs to be done so that this can be used as a view relationship

You'll note that we did alot of work to get a cool hierarchical select widget - this was because of the use case we need this for. A normal select widget is also included - more widgets might also be useful.

It would be great if the module maintainers were to have a conversation and decide whether they want to host this module as part of the data module package, or if I should start a separate project.

Cheers,

Patrick

subscribe

really wanting this functionality. Similarly a link user to data row but hopefully this and content profile can handle that.

jumentous - it should work with content profile - please download and give it a whirl and provide some feedback on any changes you would like to see.

Ok, had a quick play (haven't gotten to views yet).

Quick remarks:
- There is no None option even when required has not been set
- I don't like the "Add another" multiple selection style, emulating node_reference with a multi-select is much nicer
- When i set the option of 'User Select Field' it works fine, however when i go back and configure the field it doesn't reload my old value and so i have to keep re-selecting the field i want to show
- CCK Referential Integrity integration would be nice when you have time

Mind you i definetly think you have something useful here. I'm sure i don't understand data_node but its a pain in the arse and impossible to work with.

Thanks for the review jumentous, I try to squish those bugs. I personally like the 'add another' functionality, perhaps we can put an option in to allow for both. Note that no views integration has been done yet.

P

I added the autocomplete functionality.

Needs some polish.

There is no access control on the callback.

count me in!

Here's an update that correct a few bugs:

1. Limits to 10 records on the autocomplete - I have a table with ~160,000 records - it was bad news
2. Added support for strings as referencing fields

As far as I know this is what needs to happen to this thing before it's ready for prime time:

1. Fix the JS on the admin page. The javascript basically takes the 'referencing field' and then filters client-side the available fields on that table. To be even more confusing, the 'reference field' is in the global settings, which sits BELOW the field-specific settings - which is confusing. Perhaps we should hook-form-alter it so that, in this case, global setting sit above node-specific settings. Or someone else needs to think up a better way of handling this. :-)

2. There is no None option even when required has not been set. Select widget.

3. Better formatters. We are just using the reference-field. Yuk

4. Views integration so we can actually add a relationship to the data-table.

5. Access Control check on the autocomplete widget.

I implemented an access check for the autocomplete widget.

Using module data_cck.0.4 gives me errors when configuring the cck field:

# user warning: Table 'beta.feeds_data_feed_fast' doesn't exist query: select count(1) from feeds_data_feed_fast in /sites/all/modules/data/data_cck/data_cck.module on line 132.
# user warning: Table 'beta.feeds_data_feed_fast' doesn't exist query: select count(1) FROM (select count(1) FROM feeds_data_feed_fast GROUP BY guid) as testtable in /sites/all/modules/data/data_cck/data_cck.module on line 133.
# user warning: Table 'beta.data_table_x' doesn't exist query: show columns from data_table_x in /sites/all/modules/data/data_cck/data_cck.module on line 525.
# warning: in_array() expects parameter 2 to be array, null given in /sites/all/modules/data/data_cck/data_cck.module on line 533.
# Selected field(s) does not exist in selected table.

The first two warnings occur for every table I have that is managed by the Data module. I think this is related to the fact that I use a table prefix but, alas, my lot in life is that of a lowly tester not a programmer.

> I think this is related to the fact that I use a table prefix

Sounds likely. AFAIK data module doesn't handle table prefixes well anyway.

Loads of functions are missing docblocks.

Has any of this made it into the D7 version of Data/Data Node?

Nope, but given that on D7 you can treat your data rows as entities, you can refer to a data row with Entity Reference module.