Trouble setting up Relationship

[MaddHatter]

(I'm using Filemaker Advanced 15 on Mac OS X El Capitan)

 

I have some filtered portals that I need to turn into portals that are filtered by relationship. I can not figure out how to set this up properly.  


I'm trying make a portal that will show me a list of all of the photos that still need to be Touched-Up. When a photo is selected for touchup the customer record is linked with the Photo by a key.

Customer -> Photo

After the photo is emailed out for touchup "EmailSent" goes into Photo::EmailedStatus and if the touchup comes back and needs to be sent out then "Rejected" goes into Photo::ApprovalStatus

I tried to make a self-join relationship between Customers and CustomersJoin and make a cartesian join between the primary fields and then add the rest of the filters using Calculation Fields and Global fields but I can't figure it out because the calculation field needs to be on the left side. I'm not sure if I'm approaching this right can you help me out?

1) What Table should the layout be based on? I have it on Customers is that correct?

2) How can I filter this using this relationship? Am I approaching this right? I want the list to show ALL of the photos where 

Customers::Key = Photo:::Key

AND

Photo::EmailedStatus = "Not Emailed"
or
Photo::ApprovalStatus = "Rejected"

 

Edited December 4, 2016 by MaddHatter
Removed the font style carried over from the General Topic

[comment]

On 12/04/2016 at 5:17 AM, MaddHatter said:

1) What Table should the layout be based on? I have it on Customers is that correct?

If you want to show all photos of a specific customer that still need to be touched-up, then yes - you should place the portal on a layout of Customers.

 

On 12/04/2016 at 5:17 AM, MaddHatter said:

How can I filter this using this relationship?

This is a bit complicated because you want an OR relationship. It's even more complicated because the OR part refers to two different fields - so you cannot just use a multi-key field on the parent side. I believe the simplest solution would be to define a calculation field in the Photos table =

If ( EmailedStatus = "Not Emailed" or ApprovalStatus = "Rejected" ; Key )

and use it as the matchfield opposite Customers::Key (in a relationship using another occurrence of the Photos table).

 

BTW, I would suggest you rename the Key fields to CustomerID to make it clear who is the parent in the relationships.

Edited December 5, 2016 by comment

[MaddHatter]

I just want to make sure that I'm understanding this properly.  Are you saying that I should match 

Customers::Key <-> YourCalculation  in the second TO and also match Customers::Key <-> Photo:::Key in the the original TO

if so what TO portal do I put on the Customers layout?

 

Also will this method show All photos that have Customer::Key <-> Photo:Key or just the photos associated with this Customer:Key?  I want to show ALL photos for all customers that have a matching key in the Photo table (not all of them have one)  and then filter with those other criteria.

Edited December 4, 2016 by MaddHatter

[comment]

2 hours ago, MaddHatter said:

 I want to show ALL photos for all customers that have a matching key in the Photo table (not all of them have one)  and then filter with those other criteria.

Then it makes no sense to put the portal on a layout of Customers. In fact, it makes no sense to use a portal at all. A portal shows records from another table that are related to the current record in the table of the current layout. For your stated purpose, it would make much more sense to perform a find in the Photos table and show the found records there as a list or a table.

[MaddHatter]

I I need to setup a portal relationship so that I can access the list from PHP page using the API.  Is there a way to setup this relationship?

[webko]

Perform a second search in the sub-table. It's more reliable, more controllable...

[MaddHatter]

How would I set that search  up?  

[webko]

If there is just one Customer being found at a time, use their ID in the sub-table, plus any of your other criteria, to create another array of Photos and their attributes...

[comment]

In order to find all photos that have a customer key and their status is either "not emailed" or "rejected" do:

Enter Find Mode []
Go to Layout [Photos]
Set Field [Photos::Key; "*"]
Set Field [Photos::EmailedStatus; "Not Emailed"]
New Record/Request
Set Field [Photos::Key; "*"]
Set Field [Photos::ApprovalStatus; "Rejected"]
Perform Find []

 

Edited December 5, 2016 by comment

[MaddHatter]

Hi Webco.  I'm not sure that I'm following you.  How would I setup the relationship with what you're suggesting to do?  What table would the portal be put onto and what would the relationship match criteria be?

Again I'm trying to show ALL of the photos that have a Customer:Key and then further filter this list with those criteria.

8 hours ago, comment said:

In order to find all photos that have a customer key and their status is either "not emailed" or "rejected" do:

Enter Find Mode []
Go to Layout [Photos]
Set Field [Photos::Key; "*"]
Set Field [Photos::EmailedStatus; "Not Emailed"]
New Record/Request
Set Field [Photos::Key; "*"]
Set Field [Photos::ApprovalStatus; "Rejected"]
Perform Find []

 

I'm trying to setup a relationship I could very easily have found these using a search.  Did you read the post?

14 hours ago, webko said:

If there is just one Customer being found at a time, use their ID in the sub-table, plus any of your other criteria, to create another array of Photos and their attributes...

 

Hi Webco.  I'm not sure that I'm following you.  How would I setup the relationship with what you're suggesting to do?  What table would the portal be put onto and what would the relationship match criteria be?

Again I'm trying to show ALL of the photos that have a Customer:Key and then further filter this list with those criteria.

Edited December 5, 2016 by MaddHatter
Update of content

[bruceR]

You're missing the point which has been stated clearly and repeated several times now.

The solution does NOT involve a portal or a relationship of any kind.

[MaddHatter]

I understand Bruce but, as stated above,  I need to set this filter up using a relationship so that I can use the portal on one of my Filemaker PHP api webpages.  I can create this list very easily with a search.   Did you read the previous comments??

[comment]

15 minutes ago, MaddHatter said:

 I need to set this filter up using a relationship so that I can use the portal on one of my Filemaker PHP api webpages.

That makes very little sense to me. A relationship does not know anything about a web page. If the web page is set up to show a Filemaker layout of a table other than Photos, and you want to show records from Photos on the same layout,  then you will need a relationship between that layout's table and the Photos table. I don't see why you wouldn't simply switch to a layout of Photos, but if you must, define a calculation field in the Photos table (result is Number) =

not IsEmpty ( Key ) and ( EmailedStatus = "Not Emailed" or ApprovalStatus = "Rejected" )

and use this as the matchfield in the relationship, opposite a calculation field that returns the value of 1 (or a global field that contains 1).

 

1 hour ago, MaddHatter said:

I'm trying to setup a relationship I could very easily have found these using a search.  Did you read the post?

Yes I did read your post. It said:

17 hours ago, MaddHatter said:

How would I set that search  up?  

and that's exactly what I answered. I suggest you tone the venom level down.

 

[bruceR]

"Did you read the previous comments??"

I did. Including the comments from highly competent and experienced people who have redirected you to a clear and easily executed solution.

You came here asking for the expert advice and you have received it.

[MaddHatter]

I finally figured out how to do this. When a Customer selects a photo it assigns it the Customers::Key. 

 

So here's what I setup

 

Parent Table                                                                                    Child Table

un-stored Calc field with value  "1" <---------------------------------------------> Stored and Indexed Calc field with value     Case (not IsEmpty (_fkCustomers) ; 1)

(The above shows all photos that have been selected instead of only the ones associated with the current customer)

un-stored Calc field with value "Not Emailed" & ¶ & "Rejected" <---------------------> Stored and Indexed Calc value of     Photo::EmailedStatus & ¶ &  Photo::ApprovalStatus

(I learned that setting up a relationship like this creates an OR relationship between the two keys it creates.  You can easily accomplish this with a Portal Filter but it's not as easy to do when creating relationships.. UNTIL NOW!)

 

Two important points for those coming here afterwards with the same problem.

1) The value on the right side of the relationship value can never be an un-stored calculation field or it will not work (for any relationship)  The field has to be indexed so it has to be stored.

2) The Paragraph returns (& ¶ &) create keys on both sides.  These correspond to each other so "Not Emailed" will relate to Photo::EmailedStatus  OR "Rejected" will relate to Photo::ApprovalStatus

 

 

 

On 12/5/2016 at 10:17 AM, BruceR said:

"Did you read the previous comments??"

I did. Including the comments from highly competent and experienced people who have redirected you to a clear and easily executed solution.

You came here asking for the expert advice and you have received it.

By the way BruceR you added nothing to this conversion at all.  You offered no advice at all about the subject at hand (even after I said that in needed to be done with a relationship)  So I didn't get any expert advice from you ole' Sport  

1)  This was posted in the RELATIONSHIP section of the help forum

2) I made it very clear How and Why I needed to set it up as a relationship.  If you read my original post you would have seen that.

Thanks for stopping by and half reading the post before you decided to talk down to me.  Maybe next time you could try to extend some sort of advice AFTER you read through EVERYTHING.  Thanks to everyone else who offered advise I tried to take everything (except BrucR's comments) into consideration while figuring this out.