INNER JOIN - Multi to Multi Support

[RentProperty]

Hi Guys,

We have 2 tables for instance Properties & Tenants. A Property can have multiple Tenants And a tenant Can Be Linked To Multiple Properties. However I want to be able to display a Query that would show all the Tenants next to each one of the properties they are linked to.

We are able to write a nice SQL inner join that displays the correct results in MySQL CLI but when I try to use that query in in Aware it Says 'Cannot Find business Object property_ref'. We obviously need to use the Ref table to get what we want but since that is not an object in AwareIM I have no way to display that data set.

Is this a known Aware limitation? What is the point of having access to 'SQL Form' of the Query writer if you cannot display results of any SQL query that is not a direct Aware Object?

Here is the Query we run in the CLI which gives the exact result set we want to see:

SELECT bastestdomaincustomquerytest_tenant.TenantName AS Tenant, bastestdomaincustomquerytest_property.PropertyName AS Property FROM bastestdomaincustomquerytest_property_ref INNER JOIN bastestdomaincustomquerytest_tenant ON basdbtest.bastestdomaincustomquerytest_property_ref.RID=bastestdomaincustomquerytest_tenant.ID INNER JOIN bastestdomaincustomquerytest_property ON basdbtest.bastestdomaincustomquerytest_property_ref.ID=bastestdomaincustomquerytest_property.ID

+--------+------------+
| Tenant | Property |
+--------+------------+
| Johan | Chablis 10 |
| Barend | Chablis 10 |
| James | Chablis 10 |
| Nadia | Wylant 20 |
| Simon | Wylant 20 |
| Rick | Wylant 20 |
| Johan | Brink 50 |
| Barend | Brink 50 |
| Nadia | Brink 50 |
| Simon | Brink 50 |
| Rick | Brink 50 |
| James | Brink 50 |
| Johan | Smith 30 |
| Barend | Smith 30 |
| Simon | Smith 30 |
+--------+------------+

If this is a known limitation... Then Support, is this something you would ever be able to address?

Thnx in advance for any input

[pureist]

Isn't a Property/Tenant(s) scenario just like an Order/Order Line(s) scenario (where Lines contain an Attribute for the Product), and thus Lines are 'carriers' for Products?
An Order can have multiple Lines and thus Orders are associated with multiple Products , and Products can be in Lines of multiple Orders.

A Property can have one or more Tenant 'lines', and each Line contains an attribute for the Tenant itself, rather than the Tenant being a direct Reference Attribute of a Property.
I know you are far more experienced than I am, and the above is pretty basic, but it's easy to generate a listing of Products next to the Orders they appear in.
Thus it's easy to generate a listing of Tenants and the Properties they appear in.

[customaware]

Hein,

Not sure if this will help you but might be easier...

Create a Cross Reference BO for Property and Tenant using the Other literal.

I do this a lot for creating Currency Exchange Cross Rates....

Here is a doc on how to implement.....

HOWTO - Using Other in a Process.zip

HOWTO - Using Other in a Process

(32.38 KiB) Downloaded 439 times