Relational Tables

I'm having a similar issue. I have a View where I need to view up through a relational table to one of the linked tables. My situation is a little simpler as I only have 3 tables, but I cannot get it to work either. I have Owners, Pets and the relational table Owners/Pets. I'm trying to view Pets from a particular owner. Here's my code for the View element:

let myID := ID (selecting the current Owner's ID)

let opID := (select 'Owners/Pets' where myID = Owners).ID (selecting all records in Owners/Pets that contains the Owners ID)

select Pets where opID = 'Owners/Pets' (I thought this would select all Pet records where the Owners/Pet match from the line above)

Nothing returns. I can get it to return the records for the relational table 'Owners/Pets', but can't connect back into the Pets table. Not sure where I'm going wrong. 

Hopefully someone can help us both!

ch_ong, do these screenshots properly represent your situation? If so, do they help? If not, maybe they will assist someone else to help.

LeahH, I am not clear on why you need a third 'Owners/Pets' table. Does the above solve your problem?

LeahH, the following would work from a view in the Owners table, however, it is not necessary because the default Reference view from that table already provides the same information:

 

let key := this;
select Pets where Owner = key

In the above code "Owner" is the Reference that contains the foreign key. If you look in the Edit Fields view of the Pets table, you should see a Reference named "Owner" with a arrow that is pointing from left to right, per below:

 

When I first started using Ninox, I was confused about where the foreign key was stored that establishes the relationship between two tables. To confirm that the "Owner" reference (relation) in the above screenshot contains the foreign key, drag a formula field below "Owner". Then open the Formula field and enter "Owner" (without the quotes) as the formula. Then save and return to the detail view. You will see that the new formula field contains the foreign key that matches the Owner key in the related Owners table. That is why the above select statement works. The "where" in the statement is referring to the "Owner" reference in the Pets table (the related foreign key).

Thanks for your input Dean. I need the Owners/Pets table because one Owner could have many pets, and one Pet could have many owners. It's not a 1:many relationship - it's a many:many relationship. With that in mind is it possible to view through the relational table to the actual Pets table? At the end of the day I don't want to look at the relational (Owner/Pet) table, but the actual Pet table. I would think the code I wrote above would catch that. Thanks!

Okay, now I better understand the problem. I will think about it some more. In the meantime, maybe someone else will come up with the answer.

LeahH, Have you tried pasting your code into a new view in the Owners table?

Good idea Dean, but no luck. 

 

I was doing additional testing and found an interesting issue. I was trying to get a formula to return the array of ID's from the let opID := (select 'Owners/Pets' where myID = Owners).Id to see if it would return all of the records. It did not. So then I tried to pull at least the first record:

 

opID := first(select 'Owners/Pets' where myID = Owners).Id 

This did return the first record which made me think it's having an issue with the array. I tried to add the brackets [ ] to denote an array:

 

opID := [(select 'Owners/Pets' where myID = Owners).Id]

With this code I get an error "Expression returns multiple values"...which is odd because that's exactly what I'm trying to do! Any idea why this error is happening?

 

I did try this code with no luck as well:

let myID := ID

let opID := first(select 'Owners/Pets' where myID = Owners).ID 

select Pets where opID = 'Owners/Pets' 

 

I get no records, and a popup that says “Expression does not return multiple records” when editing the view columns.

 

I can access ‘Owners/Pets’ easily, just can’t get it to access the Pets table.

Try adding this to new view in 'Owners/Pets':

 

let key := Owners.Id;
select Pets where Owner = key

No luck with that since the Pets and Owners tables are not directly connected. So I get an error that Owners is not a field in Pets. I did try:

 

let myID := Owners.ID;

select Pets where 'Owner/Pets'.Owner = myID

 

No luck with this one either. 

 

Seems like I may just be stuck clicking through the relational table to get to Pets. Fortunately I can at least pick into Pets for the columns of the view, just can't click on the record and go directly to Pets (since it's the record for the relational table itself). 

 

Thanks a bunch for your help and if you have any other ideas, I'd appreciate it. 

It is working here. Did you type "Owners" instead of "Owner". The "Owner" is a reference field in the Pets table, as shown on the right side of the below screenshot.

 

My view in Owners/Pets that shows all the pets for the selected owner is shown below:

 

If I understand your question correctly, you want to build a view in the "Owners" table showing the pets belonging to that owner, so you can click on one of the rows to open the record of that pet.

So, in the formula to populate that view in the "Owners" table, you will need to select records from the "Pets" table, and add a condition to assess that the pet is belonging to the right owner. That condition will thus need to test, from the context of each record in the "Pets" table, the value of "Owners/Pets'.Owners". Here, you are navigating the concatenation of two relations, and one of them is "1:n". Which means that the value returned by the above expression will be an array. You can not compare a single value with an array (*), you need to check if the right value exists in the array. Unfortunately, there is no function in Ninox to perform such a test directly, so you will need to play tricks by converting the value and the array into strings. Code proposal:

let zOwnId := ", " + Id + ", ";
select Pets
where contains(---
, { concat('Owners/Pets'.Owners) },
---, zOwnId)

Please note the space at the end of the fourth line. You can of course do the same thing in the "Pets" table to show the owners.

(*) the function editor does not flag an error.

Dean - I do not have the Owners table tied to the Pets table directly....only via the relational table Owners/Pets. I hadn't thought of adding that connection, so I'll give it a try and let you know. It seems like that would work since the relationship is directly connecting Owners to Pets. I'm a little uncertain of how that will look when making the Pets to Owners or Owners to Pets connections with the Owners/Pets table, but I'm definitely interested to see what happens. 

 

Alain - Thanks for your input. My specific need is to be in the Owners table and view "through" the n:n table 'Owners/Pets' directly into the Pets table as you stated above. In my setup I don't have a the Owners and Pets table connected directly. I think I understand your code and the essential problem I'm having which is that there is no way for Ninox to compare a single value with an array. I'll give this a try and let you know.

 

Thanks to you both for your time and input - I really appreciate it!

Alain - your code worked very nicely. I can "see up" through the relational tables to the original table. Thanks!

Dean - yours worked, but it did require an extra table relationship so I'm going with Alains solution for simplicity. 

 

Thank you both so much for your input!! 

The formula I proposed is an application of a solution that is able to produce the required list about anywhere in a complex database. For simply showing the list in a record of the "Owners" table, an even simpler solution is possible for the formula defining the view:

'Owners/Pets'.Pets

Got it. That is SO much simpler. I kept thinking I had to identify the owner first and tell it to look up through the relational table. The other code is very helpful too for when I need to look at the data in a dashboard or other location. Thanks again for your help!