portal filtering question

[human]

I have two tables: jobs and tracks. A track can be either "pending" or "not pending" on multiple jobs. I set up a TO where the relationship is to match trackIDs between the job and tracks tables, but ignore the current job, so that for each track selected in a left side portal, a right side portal shows only other jobs (not the job on the left) on which the track is pending, using a filter on the right side portal.

Now I would like to toggle the left side portal between showing all tracks on a job (whether pending or not pending), or showing only tracks which are pending on other jobs. Either that, or better still, a conditional format on the tracks on the left so that any track that is pending in other jobs is highlighted. Is that possible?

Edited October 28, 2024 by human

[comment]

I got utterly lost in your description.

I presume that if you only have two tables, then you have a one-to-many relationship between them. Which is the one and which the many? If they are matched by TrackID then Tracks would be the parent table. But you say the portal is "showing all tracks on a job" so that doesn't fit.

Also doesn't fit:

59 minutes ago, human said:

A track can be either "pending" or "not pending" on multiple jobs.

That would mean a track can belong to more than one job, IOW a many-to-many relationship. And if this is the case, then you should have a third table to join the two.

 

[human]

There is no join table. Unfortunately, I didn't design it, and can't change the basic structure. But I can create TO's. To visualize it:

Job table, pk=Job#      Track table, fk=Job#

Job#1                            job#1 , track#1 pending

                                      job#1, track#2, not pending

Job#2                           job#2, track#1, not pending

                                      job#2, track#3, pending

etc.                               "pending" means its been sent to a client and we are waiting for a possible purchase.

                                      "not pending" means that it was sent out on other jobs but has been cleared to be sent out again.

The tables are related via the job number. I created a TO which is related via track#, by assigning a track ID to a global variable in the jobs table. The left side portal displays all tracks and their status for the current job. For each track on the left that a user selects, the right side portal displays every job for which that track is assigned, but which excludes whichever job you are currently looking at, and only shows other jobs' tracks, and a filter on the right side portal which can be turned on and off to view all tracks in that portal (whether pending or not pending), or only pending tracks. Now I would like to somehow summarize those, so that one or other of the portals displays a list of every track which is assigned to the current job but has pending status on any *other* job. The intent is so that users can easily visualize whether they are about to send out tracks for a job which may be already pending on one or more other jobs. Does that make sense?

The more I think about it the more I think it can't be done, because in order to get a relationship between the job and the tracks TO, I first have to assign a trackID to a global variable, so there is no way to summarize that process other than to script it to some sort of pop-up message.

 

Edited October 29, 2024 by human

[comment]

31 minutes ago, human said:

There is no join table.

Actually, there is. The table you call the Track table is a join table. The table you are missing is the real Tracks table, where each track would be a unique record.

 

34 minutes ago, human said:

The more I think about it the more I think it can't be done

I believe it can - at least this part:

19 hours ago, human said:

or better still, a conditional format on the tracks on the left so that any track that is pending in other jobs is highlighted

You need to construct a self-join of the (not) Track table as:

Track::Track# = Track2::Track#
AND
Track::Job# ≠ Track2::Job#

This allows each track belonging to the current job to look at its "siblings" that belong to other jobs and ask: are any of you pending? The exact method to do that depends on what type of field is Pending and how it is populated (ideally, it would be a Number field with the value of 1 if true, 0 or empty for false).

Scripting is also a possibility, and it doesn't limit you to "some sort of pop-up message". You can populate a global variable or a field when loading a job record. But I am not sure you need to do this, at least not for the highlighting part (I haven't really thought about the 2nd portal option).

 

 

[human]

What is "the (not) track" table? is it a TO?

[comment]

56 minutes ago, human said:

What is "the (not) track" table?

It is the table which you call the Track table (and of which I said it's not the real Tracks table).

 

[human]

I'm still not understanding which table is Tracks1 and which is Tracks2. Since I have only one (not) Tracks table, I assume the left side is that, and the right side is a TO of that? When I try that with the relationship you define, then clicking on a track on the left side just shows the right side being populated with many many ocurrences of the same track

[comment]

2 minutes ago, human said:

I assume the left side is that, and the right side is a TO of that?

Yes. That is what "self-join" means: both sides of the relationship are TOs of the same base table.

 

3 minutes ago, human said:

clicking on a track on the left side just shows the right side being populated with many many ocurrences of the same track

I am afraid I don't understand what you mean by that. You are supposed to use this relationship in the formula that conditionally formats some object in your "left" portal.

 

[LaRetta]

23 hours ago, comment said:

You need to construct a self-join of the (not) Track table as:

Track::Track# = Track2::Track#
AND
Track::Job# ≠ Track2::Job#

Hey Human, it sounds like you haven't included the Job# in the same relationship.  Check again what was shown that you need to create in the simple connection. Make special note of the NOT EQUAL in the Job# line. 

[comment]

Here's a basic demo of what I meant:

PendingElsewhere.fmp12

[human]

Thanks for this, I will study it.

[human]

In your example, the portal is pulling from the "Child" table, so it is still showing records from the current "ParentID" record, not from others. I don't see how the self join comes into play here. I made a version with names that correspond more to what I'm doing. If I set up a portal that is pulling records from the "Child 2" table, it simply displays the track that is selected in the Child portal repeated however many records there are.

 

 

track_demo.fmp12

[comment]

In my demo file, the portal displays child records that are related to the currently viewed parent. If there exists a child record with the same Name that is related to another parent and that child record is pending, then the displayed child record will be conditionally formatted.

That is how I understood your request:

On 10/28/2024 at 9:03 PM, human said:

or better still, a conditional format on the tracks on the left so that any track that is pending in other jobs is highlighted. 

If that's not what you meant, then please clarify.

I don't understand what you are trying to accomplish in the file you posted.

 

[LaRetta]

Hi Human, I too am confused by your request but ... the first thing you need to do is fix your example file you posted.  In your portal ( tracks 2 ), the fields within that portal are improperly pointing to tracks.  Select each field in that portal and change them to point to tracks 2.

That is a common mistake that I've fell into many times over the years.  Once you change the fields, see what displays for you and then maybe we can pin down your needs.  Hang in there; we'll help you through it.  😁

[human]

Doh!

I got a bit confused. The attached is working - the left portal shows all tracks for the current job, and the right portal shows only the current job's tracks if they are pending on any other jobs. And the left portal's tracks are highlighted if they are pending on other jobs. However, why is there a highlight on an extra blank row?

Also, why, in the right hand portal, for each track there is a parentID, which is correct, but  the parent name next to it only shows the name of the current job, not the parentID's name for that track. Would there need to be another join table for that?

Thank you so much for your kind help with this!

 

 

 

PendingElsewhereV2.fmp12

Edited October 31, 2024 by human
I was confused

[LaRetta]

I'll take a look later when I have more time (most likely tomorrow) but, on the conditional formatting on the pending field in the left portal, you need to include the child ID in the calc so it only fires if there is a related child record.  It would look similar to (adjust to match your field/table names):

not IsEmpty ( Child::ChildID )
and
Child::Pending = Child::Flag

 

[LaRetta]

On displaying the parent name in the Child 2 portal ... in FileMaker, perspective is everything so you can handle it a few ways.  The simplest, I suspect, would be to create a value list based upon the Jobs table ParentID and also display second field 'name' and check 'only display second field'. 

It seems you've already started one that can be modified.  Format it as pop-up menu, uncheck display arrow and turn off entry to field.  You can change its style to match your other regular non-entry fields so Users know that it is not enterable. Let us know how this works out for you, Human!  😀

[human]

I got it all working in my solution. Thanks again for all the help!