Filtering/hiding/deleting child records displayed in portal

[Joe_Schmo]

I have a portal set up to display related records based on a field value in the main layout. When the value selected in the "Equipment" field is changed, the records shown in the portal from the "Isolation Points" table change based on a relationship.

The "Isolation Points" table has a "Type" field that can only contain one value, either "Mechanical" or "Electrical". But on the layout pictured (Safety Clearance) there is a field with check boxes that will allow either or both types to be selected.

I would like to find a way to filter the records shown in the portal based on which isolation type(s) are selected on the "Safety Clearance" layout. If both are checked, then it should show all related records as it does now. But with only one checked, it should hide the type that is not checked.

Here's the tricky part- I don't want the relationship between the Equipment table and Isolation Points table to be altered. That relationship is set up with a joining table (Equip_IsoPnts) and should not ever be modified by the user. The isolation points that go with a given piece of equipment must stay intact, yet still be able to be hidden for that instance of the portal in the current record.

I also need to maintain a record of which child records (isolation points) were selected or hidden for each record of the main layout, i.e. which isolation points were used on a given safety clearance #. As it is now, I can easily look up which points were used. I just want to make sure that whatever method is used to hide certain isolation points from the table maintains the ability to see which ones were/were not selected as opposed to showing all related records.

For a more detailed description of the database, please see my "about me' section and screen shots in the gallery.

Thanks in advance for your help! You guys have been great so far. This forum has been a huge help!

Tagouts Copy.fp7.zip

[Joe_Schmo]

Forgot to mention-

As an alternative to filtering the results of the portal on the current layout, I could also create a copy of the Safety Clearances layout that has all drop downs linked to the isolation points table and let the user choose the points they want to use. I'd prefer to have it all in one layout by filtering the portal but if that's not possible then the 2nd layout would be acceptable.

[LaRetta]

Hi Joe,

What you don't show is your relational graph. What you don't tell us is the names of the fields or the field types. Views of your data in browse mode doesn't help, we need to see table names and field names and parts and things like that. You say, "a field value in the main layout." but that means nothing. Fields are not in layouts - they are in tables (specifically table occurrence). It is important that you pick up this terminology so we can help you. We need information such as:

I have global field in Equipment gType or I have text field in Equipment which holds the type ... and it displays as checkbox.

(or)

Layout based upon Equipment with portal based upon Isolation Points and the relationship is:

Equipment::EquipmentID = Isolation Points::EquipmentID

As is, I feel like I'm still fishing a bit to get us there but here's a shot - If your relationship is based up my guess indicated in grey AND if you have that checkbox field set up as global field, then you can create a calculation called cFilterType (result is text) and it will be:

Case ( IsEmpty ( gType ) ; ValueListItems ( Get ( FileName ) ; "Type" ) ; gType )

Be sure to uncheck below in the calc box 'do not evaluate if all referenced fields are empty'. And then modify your existing relationship by adding this connection:

Equipment::cFilterType = Isolation Points::Type

And since I don't know your value list name nor field names (my reason for pointing it all out above, smile), you will have to adjust this calculation or my response to fit your needs. If your setup is NOT as I have specified, speak up.

I hadn't read your 'forgot to mention'. "As an alternative to filtering the results of the portal on the current layout,"

What do you mean by 'current layout'?? Portals are based upon a relationship between two table occurrences; modify the relationship and you modify the portal. In vs. 11, you can choose to filter further within the existing relationship but not in vs. 10. In 10, you need to use relationships for it all.

[Joe_Schmo]

Getting ready to go to lunch but I wanted to get you the relationship graph first. I'll be back on in a few to provide the extra info you asked about. THANKS AGAIN!

[LaRetta]

Good. Now when you get back, go to the layout with the checkbox and go into layout mode.

1. Go to Layouts > Layout Setup and what does it say on 'show records from'? That is the table occurrence this layout is based upon. It is your POV (point of view).

2. Tell us the name and data type of the checkbox (double-click it and give the full proper name (always) as tableOccurrenceName::fieldName.

3. Tell us the name of the table occurrence of the portal (on the bottom row of the portal)

We will then have all three pieces. Take those pieces 1) the table occurrence of the layout (POV), 2) the table occurrence of any fields on the layout and 3) the table occurrence of any portal and look at the graph and find those connection points. It's a game of connect-the-dots ...

[Joe_Schmo]

Good. Now when you get back, go to the layout with the checkbox and go into layout mode.

1. Go to Layouts > Layout Setup and what does it say on 'show records from'? That is the table occurrence this layout is based upon. It is your POV (point of view).

2. Tell us the name and data type of the checkbox (double-click it and give the full proper name (always) as tableOccurrenceName::fieldName.

3. Tell us the name of the table occurrence of the portal (on the bottom row of the portal)

We will then have all three pieces. Take those pieces 1) the table occurrence of the layout (POV), 2) the table occurrence of any fields on the layout and 3) the table occurrence of any portal and look at the graph and find those connection points. It's a game of connect-the-dots ...

1. 'show records from' Safety Clearances
   
2. Check box on layout 'Display values from' IsoType (same value list used for the 'Type' field in the isolation points table that the portal is based on)
   
3. Isolation Points
   

So are you saying that I should create relationships (by drawing lines on the table relationship graph) from 1 to 2 to 3? If so, that would be Safety Clearance::IsoType-->Isolation Points::Type?

FYI- I don't have the Safety Clearance::IsoType or Isolation Points::Type fields set to global but they both use the same value list "IsoType"

[Joe_Schmo]

I hadn't read your 'forgot to mention'. "As an alternative to filtering the results of the portal on the current layout,"

What do you mean by 'current layout'?? Portals are based upon a relationship between two table occurrences; modify the relationship and you modify the portal. In vs. 11, you can choose to filter further within the existing relationship but not in vs. 10. In 10, you need to use relationships for it all.

What I meant by 'current layout' was, instead of trying to filter the records displayed on the Isolation Points portal that's in the Safety Clearances layout (current layout), that I could make a copy of that layout without a portal and use all dropdowns arranged to look the way the table does now. The user would then have to choose an isolation point from the first dropdown in the row and the related fields to that record would then fill in. This would allow the user to customize the isolation points for that safety clearance.

From what you said about relationships, I take it that means I would not only have to make a copy of the Safety Clearances layout, but also make a separate TO for the Safety Clearance's table and the Isolation Points table and relate them as well.

I'd still rather keep it all in one layout if possible but I was trying to explain what I saw as another option if I made two layouts; one layout that auto fills all the isolation points using a portal (which is what I already have now), and one layout that allows the user to choose all isolation points.

[LaRetta]

if I made two layouts; one layout that auto fills all the isolation points using a portal (which is what I already have now), and one layout that allows the user to choose all isolation points.

No. Do anything to avoid duplicating a layout just because of elements on it. It will double your work every time you need to make a change. If you want all ISO points in a pop-up just create and attach a different value list to the ISO ID in Safety Clearances - one based upon all values from Isolation Points.

I take it that means I would not only have to make a copy of the Safety Clearances layout, but also make a separate TO for the Safety Clearance's table and the Isolation Points table and relate them as well.

I never suggested such a thing. If you DID have to create another occurrence, it would only be Isolation Points but we don't need to go this direction. You don't need to filter a portal using vs. 10, you need a value list. :)

[Joe_Schmo]

Ok, so no second Safety Clearance layout or TO then. I was just throwing ideas out there for another approach in case filtering the results of the isolation points portal on the safety clearances layout wasn't viable.

But back to your calculation in post #3- Can I use something like that to show/hide records in the Isolation Points portal based on the values checked/unchecked on the Safety Clearance:IsoType field?

Currently, the fields involved are setup as:

Isolation Points::Type [shows values from value list "IsoType", calculation to allow only one value, either "Mechanical" or "Electrical"

Safety Clearance::IsoType shows values from value list "IsoType", either, both or none can be checked]

Isolation Points Portal on Safety Clearance Layout, Type field

Isolation Points::Type

[displays values from Isolation Points::Type]

There are no relationships set up between any of them.

I don't have a global Isolation Point field yet. I think I have a rough idea how to start setting this up based on the filtered personnel fields I did a few days ago that used a global field. But I'd rather get some expert advice before I start messing with things.

[LaRetta]

Okay, back to the portal (filtered via relationship instead of portal filter since this is vs. 10) . So the User wants to use the checkbox to filter the portal to Mechanical, Electrical or all Isolation Points. Is this correct? Then I assume there are more than a handful of ISO points to choose from.

Then you need a gType (global text) in Safety Clearances and attach your checkbox of ISO Types to that field. Your User can select one or the other or both. Now go to your graph ... Select Isolation Points table occurrence and duplicate it (++). Name it All ISOs (or something). Set it over next to Safety Clearances. Join as:

Safety Clearancances::gType = All ISOs::Type

Base your portal on AllISOs. If Equipment should be included in this filter (so only ISOs for this piece of equipment) are possible to appear in the portal, include your equipment ID (joined on both sides using =). :laugh2:

[Joe_Schmo]

So the User wants to use the checkbox to filter the portal to Mechanical, Electrical or all Isolation Points. Is this correct? Then I assume there are more than a handful of ISO points to choose from.

Yes, correct on both accounts.

Sounds easy enough but I haven't had much time to work on it today. I'll let you know once I get a chance to try it. Thanks again and have a great weekend!

[Joe_Schmo]

Okay, back to the portal (filtered via relationship instead of portal filter since this is vs. 10) . So the User wants to use the checkbox to filter the portal to Mechanical, Electrical or all Isolation Points. Is this correct? Then I assume there are more than a handful of ISO points to choose from. Then you need a gType (global text) in Safety Clearances and attach your checkbox of ISO Types to that field. Your User can select one or the other or both. Now go to your graph ... Select Isolation Points table occurrence and duplicate it (++). Name it All ISOs (or something). Set it over next to Safety Clearances. Join as: Safety Clearancances::gType = All ISOs::Type Base your portal on AllISOs. If Equipment should be included in this filter (so only ISOs for this piece of equipment) are possible to appear in the portal, include your equipment ID (joined on both sides using =).

Ok, I've set this up as you described except for that last line. I DO want the portal to filter so that it only shows isolation points for that piece of equipment, as well as further filter for the type based on the user selection.

I'm not sure how to set up that last relationship you said for Equipment ID though. I tried a few things and so far, all I'm getting is a portal full of the same 1st Isolation Point. Do I need to add the g.Type field to the Workbench layout and enter "Mechanical<return>Electrical" similar to the personnel dropdown filters?

The screenshot is what I have right now for the relationships. It's not working at the moment but I figured I'd show you where I'm at right now anyway.

[Joe_Schmo]

Still no luck. Anyone else want to take a crack at it?

Tagouts Copy 2.fp7.zip

[LaRetta]

account and password?

[Joe_Schmo]

account and password?

Just sent it to you in a message. Thanks for checking it out.

[LaRetta]

Hi Joe,

The fields within your portal do not match the same table occurrence as the portal itself. Change the fields to Isolation Points 2; same as portal and see what you get. ;)

[Joe_Schmo]

ALMOST THERE! Only now the portal is showing ALL isolation Point from ALL pieces of equipment that match the checked isolation type (Mechanical or Electrical). I had came accross that earlier and couldn't figure out where to go from there so I undid the relation that got me there.

If I can get that filter (Mechanical/Electrical check box on Safety Clearance layout) plus the Equipment filter at the same time then it would be perfect!

[LaRetta]

Ah, I see. Then try attached. I deleted your prior TOG. The issue was that the relationship from Safety Clearances to Equipment_ISOpnts (the original one) is fine. It filters to the equipment level. But there is no Type in Isolation Points so we must 'rely' on the intermediate table (Equipment_ISOpnts).

But we don't need an entirely new TOG - use your existing Equipment_ISOpnts table occurrence to filter to the equipment level. Then add your gType (global type field) into your Equipment_ISOpnts table (and change your checkbox on Safety Clearances to use it).

Then from Equpment_ISOpnts, we filter to include or exclude according to your gType filter. Be sure to re-point the portal itself and also all fields within it.

Tagouts Copy 2REV.zip

[Joe_Schmo]

Ok, you lost me. When you said

... use your existing Equipment_ISOpnts table occurrence to filter to the equipment level. Then add your gType (global type field) into your Equipment_ISOpnts table (and change your checkbox on Safety Clearances to use it).

Then from Equpment_ISOpnts, we filter to include or exclude according to your gType filter. Be sure to re-point the portal itself and also all fields within it.

-are you explaining the changes you made in the version you sent me, or saying what I need to do once I open that version?

I may have done a poor job explaining how I want the filter to work, but the way it's set up in the file you sent back still isn't working right. I see you added the equipment name to the portal fields. And the check box IS filtering to only show the type of isolation point that is checked. But it's still showing ALL isolation points for ALL pieces of equipment (even though the portal is showing the same equipment name next to all the child records).

If you test it with the equipment AC1 it's easier to see since there are only a few isolation points. AC1 has two mechanical and one electrical isolation point. In the version you sent back, if I choose AC1 for the equipment, and check both mechanical and electrical, it should show only three isolation points, but instead I get all points in the database. It's still only filtering per the checkbox but the portal is putting the equipment name next to the records which makes it look like you are only seeing the ones you want, if you don't know how many should show up.

[LaRetta]

I was saying what I did in the REV version and explaining why. I don't understand what the components mean. Try including the Components in that new relationship as:

Isolation Points 2::Type = Equipment_ISOpoints::gType

AND

Isolation Points 2::Component = Equipment_ISOpoint::ISOpnt Component

[Joe_Schmo]

GOT IT! Adding that second relation (Isolation Points 2::Component = Equipment_ISOpoint::ISOpnt Component) did the trick! Now it filters BOTH the equipment and the type! YOU ROCK!

By the way, you said you didn't understand what the components mean- Each component is a valve, or breaker (for the most part) used to secure water flow or electricity going to a piece of equipment.

If you think about doing work on your car's alternator:

-the battery disconnect would be an electrical isolation point (Component) for the alternator (Equipment).

If you are working on a pump, then you have to secure the suction and discharge valves (mechanical isolaitons) as well as the breaker (electrical isolation) before it's safe to work on.

Thanks again for all your help! I'm almost done with the database now and about ready to start entering TONS of data :hmm: