Left Outer Join functionality needed

[andyshearer]

Hi, first time posting on this forum so I offer my apologies if this isn't the right place or formatted quite right. I have been working on FileMaker Pro 14 for about the last 5 weeks, so am still fairly new to some of the lesser known bits in FileMaker.

 

I have a few tables that I want to create a relationship with, but after many hours, I cannot find an arrangement that has a single relational path through.

 

User Directory contains all of the user details that will be stored in the system. There is a one to one relationship here with Asset Survey - this is basically a list of all of the Assets that a User (eg. a school) has. Asset Survey has a one to many relationship with Asset Survey Item which contains details of the Asset, and each Asset Survey Item has a one to one relationship with Element; all records in Element are a standard set of things that could be in the Asset Survey Item (eg. Fire Extinguishers).

 

For each User in the User Directory, I want to have a layout which displays every record in the Element table, and if there is an Asset Survey Item in this User's Asset Survey that has a relationship with that Element, show some information from that Asset Survey Item. But, I also want to show the Element records that don't have any related Asset Survey Item.

 

So for User 1, it would look like this: (sorry for the formatting)

 

[u]Element Name        Asset Survey Item Quantity[/u]
Fire Door           5
Fire Extinguisher   2
Fire Blanket        
Security Camera     
Security Pad        5

 

and for User 2, it would look like this:

 

[u]Element Name        Asset Survey Item Quantity[/u]
Fire Door           
Fire Extinguisher   8
Fire Blanket        12
Security Camera     4
Security Pad        4

 

This strikes me as the same result of a Left Outer Join in SQL, showing all values from the left table Elements, regardless or not of whether there is a value in Asset Survey Item.

The main problem I was running into was being in the menu for a particular user in the User Directory, and getting to the screen for that particular user (couldn't go to related records in Elements as it would not show all.

 

Thanks in advance for any help/advice, I can clarify anything if needed, and sorry if this is way too detailed but didn't want to be vague!

[andyshearer]

 

Here is the relationships explained a bit better