How do relationships work?

[pwg]

I'm using DF 0613 but I can't get the 'relationships.ini' file to create the relationships that I expect. I have two tables that each have an integer primary key (auto-increment) named 'id'. The keys should relate one table to another. I want the relationship "Table_2.id = '$id'" to create a relationship between the two tables (named 'foobar', say) such that when I click on the 'foobar' tab in the details screen for a record in Table_1, it will show the fields of the related record in Table_2.

But all I get is a message to the effect that no records match the query.

I've tried using an equivalent '__sql__' SELECT statement to no effect.

What might I be doing wrong?

Thank you,

Peter

[shannah]

Without a little extra info it's tough to say what is causing the problem, but here a few things:

1. Relationships are defined in one direction only... if you want it to be 2-ways, then you need to define it twice - once for each table.

2. Suppose you want the foobar tab to show up for Table_1 records.Ê Then your relationships.ini file should be located in:
tables/Table_1/relationships.ini
and it will look something like:

[foobar]
Table_2.id="$id"

3. The message "No records match the query", means that the relationship is probably set up correctly.Ê (i.e. the foobar tab shows up ok??).Ê If it says that no records matched the query that just means that for the current record you are viewing in Table_1, there are no records in Table_2 that fit the requirements of the relationship (i.e. Given the current record X in Table_1,Ê there are no records Y in Table_2 such that X.id=Y.id).

If you are sure that there should be at least one record meeting these criteria, then it's hard to say what is going on for sure ... let me know - I may have to take a look at your schema/ini files to tell you for sure what is going on in that case.

Hope this helps a little.

-Steve

[pwg]

Thank you, Steve. My setup is as you suggest in (2) above. I can certainly see the 'foobar' tab.

Table_1 (the source) and Table_2 (the destination) are linked only through the primary keys 'Id'. There is an identical number of records in each table and, therefore, a simple one-to-one relationship between them. Table_2 contains only two fields per record: an 'Id' field and a varchar field containing a long 'url' which defines the web-location of documents identified in the records of Table_1.

Like this:

Table_1.Id, Table_1.document_name ...(5 other fields)
Table_2.Id, Table_2.document_url

The fields.ini in ./tables/Table_1 contains only formatting widgets. The relationships.ini in ./tables/Table_1 contains precisely the entry you specify in (2) above.

The .ini files in ./tables/Table_2 are empty

I have no difficulty obtaining a full listing of the Table_2.document_url fields using an SQL query run outside DataFace such as:

SELECT document_url FROM Table_2, Table_1 WHERE Table_2.Id = Table_1.Id

This same query run as an "__sql__" query in the relationships.ini file produces an error unless I substitute the '$Id' variable for 'Table_1.Id'. When I make this substitution, the __sql__ query inside DF produces the same 'no matching records' result as the shorthand version.

I'm completely puzzled. It's got to be somethign really simple, but I can't see what (I'm running MYSQL 5.0.15 locally on OS X using DF 0613. The two tables appear normally in the standard DF setup. There seems to be nothing wrong with the DF setup).

[shannah]

I agree, it must be something very simple.

One possibility, is case sensitivity.Ê MySQL is not case sensitive - but Dataface is in many cases.Ê i.e. make sure that the Id field is named 'Id' and not 'id' - or at least that it is named correctly in all references in Dataface.

[pwg]

Fixed. You were right. A check of the field names in PHPMyAdmin showed that one of them had a lower-case "id" rather than "Id".

i hate it when that happens.