Filter A Portal

[adyf]

I have a parent table called (Route Packs), on that table I have a portal from (Route Pack Sections) that shows the Route Pack Sections that belong to a (Route Pack). I have another layout that displays reords from (Route Packs). On this layout I have a portal that displays records from (Route Retention). This all displays correctly. On my attached diagram the bottom portal displays the correct records from (Route Retention) that are related to the relevant (Route Pack Sections) that belong to the relevant (Route Pack). I would however like the top portal to only display the name of the person who has records in the (Route Retention) table that match all the related (Route Pack Sections). In my example there are eight (Route Pack Sections) records. As you can see in the bottom portal Archer has only two records but appears in the top portal but shouldn't. Beri has nine records which is a complete match and should be in the top portal. I'm not sure if I can solve this by filtering the top portal with a calculation or if I need a new relationship.

[comment]

You haven't described your tables and their relationships. I seem to have a vague recollection of something like:

Routes -< Sections -< Certifications >- Drivers

If that's correct, you could add a calculation field cCertifiedSectionIDs to the Drivers table (result is Text) =

List ( Certifications::SectionID )

and then filter the portal from Routes to Drivers by (untested) =

Let ( [

sections = List ( Sections::SectionID )

] ;

sections & ¶ = FilterValues ( sections ; Drivers::cCertifiedSectionIDs )

)

[adyf]

You haven't described your tables and their relationships. I seem to have a vague recollection of something like:

Routes -< Sections -< Certifications >- Drivers

If that's correct, you could add a calculation field cCertifiedSectionIDs to the Drivers table (result is Text) =

List ( Certifications::SectionID )

and then filter the portal from Routes to Drivers by (untested) =

Let ( [

sections = List ( Sections::SectionID )

] ;

sections & ¶ = FilterValues ( sections ; Drivers::cCertifiedSectionIDs )

)

Hi Comment, the relationships are:

Route Packs -< Route Pack Sections -<Certifications

My Drivers table is actually related directly to Route Pack Sections so therefore has an implied relationship to Certifications. Is this still ok?

PS What does the (untested) mean? I'm guessing it means that this is what you envisage will work without actually testing it.

[comment]

My Drivers table is actually related directly to Route Pack Sections

How is that possible? Isn't a driver certified for more than one section? And doesn't a section have more than one driver certified to drive over it?

What does the (untested) mean? I'm guessing it means that this is what you envisage will work without actually testing it.

Yes, that is correct.

[adyf]

How is that possible? Isn't a driver certified for more than one section? And doesn't a section have more than one driver certified to drive over it?

You are correct, I imagine ideally I need a Drivers table, Certifications table and a join table inbetween. I inherited Certifications from a colleague. There is a name field as well as a route section field for each record and that's how it currently works. I have however added an EmployeeID field to the Certifications table and as I have a Drivers table within my other file that also has an EmployeeID field I can relate the two. If I create a certifications portal on the form view of the Drivers table that display the sections for each Driver can I still use your original solution?

[comment]

You are correct, I imagine ideally I need a Drivers table, Certifications table and a join table inbetween.

I believe you need a join table between Drivers and Sections - and that would be the Certifications table.

[adyf]

There is a name field as well as a route section field for each record and that's how it currently works. I have however added an EmployeeID field to the Certifications table and as I have a Drivers table within my other file that also has an EmployeeID field I can relate the two. If I create a certifications portal on the form view of the Drivers table that display the sections for each Driver can I still use your original solution?

I fully take your point about a restructure but I use this table everyday which leaves very little leeway to do it. Not only that but my skill level means that I work fairly slowly so it would take me some time. Do you think that what I have explained above would work in the interim using the calculation in your original reply?

[comment]

Do you think that what I have explained above would work in the interim using the calculation in your original reply?

Probably not. I am not at all clear on how you have the thing structured at the moment, so I cannot say for sure

[adyf]

Probably not. I am not at all clear on how you have the thing structured at the moment, so I cannot say for sure

Comment, I have managed to do this thanks to your suggestion and the portal now filters correctly. Is there a way (if there is I'm sure you'll know) of creating a summary calculation that only counts the number of filtered records? I've created a summary field but it displays the total number of records prior to filtering that it logically would.

[LaRetta]

Put the summary field in another (one-row) copy of the same filtered portal. :^)

[adyf]

Put the summary field in another (one-row) copy of the same filtered portal. :^)

Hi LaRetta, I'll try that. Is there also a way of doing it using the calculation I was given to filter the portal in conjunction with the Get Summary function?

[adyf]

So far so good with the above suggestions. My portal from the (Train Drivers) table now only lists Drivers that have the (Certifications) for the correct (Sections) that make up the (Route), which is great.

Each Certification record has a field called Date Last Over which is the date the Driver last travelled over that Section. I would like to find a way of sorting the Drivers by the Date Last Over field for each certified section. So if two Drivers have the correct certified (Sections) for a (Route) and the (Route) has nine (Sections), if one of the Drivers hasn't been over five of those (Sections) which is the majority of the (Sections) more recently than the other Driver then I would like this Driver to appear first on my portal. I'm thinking another calculation field will be required that can evaluate this.

Hope I've explained myself in sufficient detail. This will allow more proactive planning/rostering by getting the Driver on the (Route) that will benefit the most by not running out of competence.

[adyf]

You haven't described your tables and their relationships. I seem to have a vague recollection of something like:

Routes -< Sections -< Certifications >- Drivers

Assuming these relationships, I have created a Competence Paperwork table that I have related to Routes via RoutesID as the match field. When I receive Competence Paperwork for a Route for a Driver I will create a record in the Competence Paperwork table which is ok.

As a result of creating this record I would like all the Certifications for the Sections that appear in that particular Route to have a field that automatically displays 'Yes'. It will be a useful feature for me because I can then see what paperwork I should have in my competence filing cabinet.

I do have a Certifications portal on the Routes layout and I can manually add 'Yes' to every relevant record. I'd just like to find a way of doing it automatically when I enter a date in a field in the Competence Paperwork table. Competence Paperwork has a RouteID field and:

A Route knows which Sections belong to it and a Route knows which Drivers have Certification on those Sections.

I was hoping I wouldn't have to have a record for each Driver for each Section in the Competence Paperwork table and that one record for each Driver for each Route would be suffice.

[adyf]

On the attached jpeg, the bottom portal displays historical records of each occasion I have recorded a Train Driver travelling over route sections that relate to the parent record.

At the moment I have filtered the portal as follows:

Routes learned::EmployeeID = Route Retention Master::EmployeeID

This filters the portal correctly and only displays records for David Crichton. As said above the portal displays a record for each occasion David Crichton has travelled over any of the route sections that relate to the parent record. Would it be possible to expand my portal filter calculation to do the following:

Display only the most recent occasion that David Crichton has travelled over each route section?

[LaRetta]

Please post a simple new file showing the critical relationship, fields and exiting portal. The FM file will need to be zipped first. :^)

[adyf]

Please post a simple new file showing the critical relationship, fields and exiting portal. The FM file will need to be zipped first. :^)

I'm struggling with the upload, 9mb zipped shouldn't be a problem should it?

[Lee Smith]

If you tried to attach a file, it didn't make it. The process is rather easy, if you follow these steps.

Step 1

Find the file on your hard drive, and zip it.

Step 2

Do not use the Quick Reply, BUT instead, choose the More Reply Option

Step 3

Click on Attached Files and this will take you to your hard drive to locate your file that you want to attach and that you previously zipped. Select the file, and then click Attach This File, and then click Add Reply.

If you should have a problem write me a private me

[adyf]

If you tried to attach a file, it didn't make it. The process is rather easy, if you follow these steps.

Step 1

Find the file on your hard drive, and zip it.

Step 2

Do not use the Quick Reply, BUT instead, choose the More Reply Option

Step 3

Click on Attached Files and this will take you to your hard drive to locate your file that you want to attach and that you previously zipped. Select the file, and then click Attach This File, and then click Add Reply.

If you should have a problem write me a private me

Lee, I have uploaded previously without issue. Is there an upload restriction with respect to zipped file size?

[Lee Smith]

Are you getting a rejection notice from you IP carrier? or a notice from the FM Forum?

Please give me more information of what you are seeing.

Lee

[adyf]

Are you getting a rejection notice from you IP carrier? or a notice from the FM Forum?

Please give me more information of what you are seeing.

Lee

The error message is FM side, 'No file selected for upload'. This is after going through the upload process and watching the progress bar reach the end.

[Lee Smith]

Send the ZIPPED file to me via my email address shown in my profile, and I'll see what I can do.

[adyf]

Send the ZIPPED file to me via my email address shown in my profile, and I'll see what I can do.

Lee, I've already had an offer to help via PM that I would like to progress first but thanks very much for your offer of help.