Subset of records in current table in portal

[benmort81]

Hello,

I have a table holding records of members of staff.  Another table holding details about Events ("CPD") that the staff can attend.  I have a linking table "CPD Attendance" where I hold records of which members of staff have attended which events. Screenshot attached.

I want a portal of all records from the "CPD Attendance" table where the 'CPD Type' (a field in the CPD table) is of a certain type.  I know I could create a layout sorted by sub-summary type to see this, but I'd really like this data in a portal for ease-of use for the end-user.

What's the best way to achieve this please?

Very grateful...

Ben

 

Edited July 10, 2015 by benmort81

[dwdata]

What table do you want you PORTAL to live in?

If it is in a layout where the Table Occurrence (TO) CPD, ten you already have the  relationship established (CPD Attendance). You can tunnel staff data in because of the relationship from CPD Attendance --> Staff Records. So your portal can use these fields:

Staff Record::First Name
Staff Record::Last Name
CPD Attendance::CPO Confirmed
CPD Attendance::Notes
CPD Attendance::Points Confirmed

This seems straight forward, but if there is something else need or you need the portal in another table, let us know.

Good luck!

[eos]

One approach:

• create a value list with your existing types by selecting the option “from field” with CPD Attendance::type.
• create a new TO of the table where you want the portal to display
• add a text field to that table
• create a relationship as NewTO::newTextField = CPD Attendance::type
• create a layout based on the new TO 
• add the new field to the layout
• format is as checkbox or radio buttons with the new value list (a checkbox would allow you to filter by several types)
• place a portal into CPD Attendance onto the layout, displaying fields from any of your three existing tables

Adding the new TO to the existing structure has the benefit that you don't need to rebuild that structure. 

Another approach:

Formalize your types into their own table; consequently, use a foreign id_type in the join table and add a TO of Type to the join table (which then is a 3-way join). 

Find the desired Type record and show a portal into the join table.

This seems straight forward

… but what has it to do with the original question? 

Edited July 10, 2015 by eos

[Fitch]

A filtered portal might work for you. Have a look at the attached file. I assumed you wanted to show the portal on a Staff layout.

The file adds these elements:

• a global field to select the type (field can be in any table)
• a value list of types (you may already have this)
• a script to refresh the portal

The refresh portal script step requires that you give your portal a name using the Inspector, and the script is attached to the global field using an OnObjectModify trigger.

 FMForumsExample710.zip

[dwdata]

 

… but what has it to do with the original question? 

Sorry - EOS

Sheesh. I was totally off on my solution - I need to read the post more carefully next time and probably when I am more awake and less distracted. I'll try to do better ;-)

Edited July 10, 2015 by dwdata

[benmort81]

Thanks, as always, everyone for your invaluable help.  I'm looking forward to experimenting this morning and I'll post back later.

Don,  I got a notification of a post with a file in it to promote some out of the box thinking -  hierarchical_vls.zip but It's not available any longer - did I miss my chance?

Thanks again!

[benmort81]

One approach:

• create a value list with your existing types by selecting the option “from field” with CPD Attendance::type.
• create a new TO of the table where you want the portal to display
• add a text field to that table
• create a relationship as NewTO::newTextField = CPD Attendance::type
• create a layout based on the new TO 
• add the new field to the layout
• format is as checkbox or radio buttons with the new value list (a checkbox would allow you to filter by several types)
• place a portal into CPD Attendance onto the layout, displaying fields from any of your three existing tables

Adding the new TO to the existing structure has the benefit that you don't need to rebuild that structure. 

Another approach:

Formalize your types into their own table; consequently, use a foreign id_type in the join table and add a TO of Type to the join table (which then is a 3-way join). 

Find the desired Type record and show a portal into the join table.

… but what has it to do with the original question? 

Hmm,

This is a layout based on my new TO of the table "CDP".  The staff ID and code pull through as expected, but the CPD ID date and description are showing the first record of that type in the table as opposed the related CPD Attendance record.

Is this because the field "CPD Type" lives in the "CPD" table and not the "CPD Attendance" table?

 

Edited July 13, 2015 by benmort81
Clarification

[benmort81]

A filtered portal might work for you. Have a look at the attached file. I assumed you wanted to show the portal on a Staff layout.

The file adds these elements:

• a global field to select the type (field can be in any table)
• a value list of types (you may already have this)
• a script to refresh the portal

The refresh portal script step requires that you give your portal a name using the Inspector, and the script is attached to the global field using an OnObjectModify trigger.

 FMForumsExample710.zip

Thanks Fitch,

But what I need is a portal that shows me all staff in the portal, and the CPD events that they have attended (the CPD IDs will be unique for each member of staff as they actually create these for themselves), so for example:

Staff ID    CPD ID    CPD Type

1                 3            Workarounds

2                 4            Workarounds

3                 5            Workarounds

 

Edited July 13, 2015 by benmort81

[eos]

Is this because the field "CPD Type" lives in the "CPD" table and not the "CPD Attendance" table?

You're probably displaying CPD fields from the wrong TO. Be aware that in a relationship chain you cannot “look back”, or you lose the correct context.

Look into the attached file to see how this is done.

 FilterByType_eos.fmp12.zip

[benmort81]

There's only so many ways to say this... Thanks Eos!

[benmort81]

You're probably displaying CPD fields from the wrong TO. Be aware that in a relationship chain you cannot “look back”, or you lose the correct context.

Look into the attached file to see how this is done.

 FilterByType_eos.fmp12.zip

Works beautifully.  Is this an extension of the first approach you suggested, or is this a 3-way join?

How do you sort on a field from a related table in a portal as in your example file?

[dwdata]

Thanks, as always, everyone for your invaluable help.  I'm looking forward to experimenting this morning and I'll post back later.

Don,  I got a notification of a post with a file in it to promote some out of the box thinking -  hierarchical_vls.zip but It's not available any longer - did I miss my chance?

Thanks again!

Yeah - I did not read your original post correctly (fatigue and distracted). What EOS recommended is excellent and quite a common UI schema used if FileMaker solutions for filtering list.

The 'hierarchical_vls.zip" that I uploaded was more for data entry efficiency when defining a type to an entity. It allows you to drill down through unlimited levels of categories to a base category, all from one popup menu.

I uploaded it, then read other responses and realized I was off, so I deleted it so I would not throw you off from getting to your solution. Sorry and I am glad you for an answer to your issue.

I did check out EOS sample file - very efficient. It is the definitely the right way to go. I did make an little additional feature to his elegant sample to allow for a SHOW ALL, in case you need that. I hope he does not mind ;o)

Sorry for the confusion.
 

FilterByType_eos2.fmp12.zip

For a rapid answer regarding sorting - just change the portal TO to the table that has the sort field(s), select your sort options, then change it back to the proper TO.

You would figure FileMaker would allow you the same sort options (choosing the TO) that it gives at the record level. It's funky, but it is, what it is).

[eos]

Is this an extension of the first approach you suggested

Not really; I did think that type was an attribute of StaffInEvent, thus my first set of instructions; since it is actually an attribute of Event, you add a TO of Event as an intermediate filter.

or is this a 3-way join?

No; these are utility TOs; as mentioned, you'd have a 3-way join if, say, “Type” was a table in its own right and you'd use a foreign Type ID in StaffInEvent, instead of a text field – so you'd have id_staff, id_event, and id_type.

While in your case an Event has a Type and it is reasonable to store that datum in Event, an Attendant could “play” a Role … another 3-way-join … but I wouldn't focus too much on these terms.

How do you sort on a field from a related table in a portal as in your example file?

Switch “Show related records from” to the desired TO, select the desired field(s) for the sort, then switch back.

Be sure it ”makes sense“ to sort the related set on the selected field; just because a TO is related doesn't mean that this is so. Selecting the wrong TO here can grind you solution to a halt, while FM tries to resolve the relationships chain to read the field data …

[benmort81]

Sorry for the confusion.
 

 

Thanks Don.  Absolutely no need for any apologies - I'm gob smacked by the level of help and support guys like you and Mr Eos gift on people like me!  You have a great website btw, loads for me to get my teeth into there.

 

While in your case an Event has a Type and it is reasonable to store that datum in Event, an Attendant could “play” a Role … another 3-way-join … but I wouldn't focus too much on these terms.

 Ok, good advice.  Learning so much here, I'd better give the old brain some time to cool down!

Edited July 13, 2015 by benmort81

[eos]

Not really; but is manipulating the back-end not sooo 20th Century? 

Since we already have a potential multi-key field, I would change the UI to checkbox, and show all related records with

Set Field [ ValueListitems ( "" ; "WhatWasTheNameOfThatValueListAgain?" ) ]

Delete the two new fields and set the relationship back to gTypeFilter = type. Add another button to show “None”, so you can start over selecting from a clean slate.

[benmort81]

Just changing the UI to checkbox works to show multiple types in the portal - I haven't had to do anything with 

Set Field [ ValueListitems ( "" ; "WhatWasTheNameOfThatValueListAgain?" ) ]

I'm I missing something? 

[eos]

Just changing the UI to checkbox works to show multiple types in the portal

That command lets you select all types with one click.

[benmort81]

Hello,

Earlier in this post I was shown how to create context TOs to be able to filter on a attribute within a table (It works great, thanks EOS).

In my solution the layout is CPD_FilterContext and the portal shows related records from CPD_Attendance, you can see this in the Relationship screenshot attached.

I added another portal on the same layout (CPD_FilterContext) showing related rows from CPD (Same Table, different TO).  This portal shows me all the CPD records as I require.  I wanted to be able to add new CPD events straight from this portal so went and edited the relationship between 'CPD Attendance' and 'CPD' so that I could create records in CPD via this relationship.

In the portal I get the blank row ready for new record entry - but when I try to enter data into any of the fields in the portal I get the error:  "This operation cannot be performed because one or more required related records are not available and cannot be created."

I can script something so the end-user can add and edit CPD events easily from this layout - but it would be simpler if they could do it straight in the portal.

I'm not 'getting' this level of complexity yet with the relationships, but I'm learning!  Is this a problem with the relationship structure?

Thanks you for any advice!

Edited August 6, 2015 by benmort81