Consider the following:
I have four tables, A, B, C, D
A has two columns: A.ID (primary key), A.BID (foreign key from B)
B has two columns: B.ID (primary key), B.CID (foreign key from C)
C has two columns: C.ID (primary key), C.DID (foreign key from D)
D has one column: D.ID (primary key)
I wish to create a report that displays all columns of the records where the relationship A->B->C->D exists (assume, for the purposes of this discussion, that B, C, and D also have columns that contain useful data and need to be included in the report)
In bone-basic T-SQL (the only kind of T-SQL I'm capable of, ha ha), this is something like:
select * from A
inner join B
on A.BID = B.ID
inner join C
on B.CID = C.ID
inner join D
on C.DID = D.ID
At present, according to Zoho Creator Support, this is not possible without first creating lookup fields in A that create relationships A->C and A->D, with scripts that make sure that A->C and A->D remain in line with B->C and C->D.
This is absurd. To permit Reports to be built that relate multiple tables across multiple relationships, I suggest that the "Select Related Form" element in the "Column Properties" section of the report be made recursive. That is, if I create a report based on A, and I click "select related form", I'll select B and it will show me B's columns. Then, "select related form" will appear at the bottom again, and I'll be able to select C and see C's columns, and so on.
Thank you for your time.
Edited to add: Similarly, it would be extremely useful if Display Fields for Lookups could themselves look inside Lookups. Current workarounds for this issue are more scripts and more bypassing legitimate relationships in favor of badly-designed pseudo-relationships. The existing system encourages kludges and band-aids, and discourages good database design practices.