Hide certain related records

[madman411]

Hi all.

I am attempting to filter a portal in a few different ways.

I have a daily time sheet displayed in a portal that is department specific. Table: Day Portal Table: DailyTimes (Day::ID = DailyTimes::DayID) Each work day the user opens the current day record and can open a card window and be shown another portal with applicable crew who are in the system that could be working that day. Table: Day Portal Table: PR_Crew (another instance of the "Crew" table). Not every crew member works every day. Currently I have it scripted where the user selects a checkbox next to the names and the script loops through and adds the crew to the daily time sheet for that day. This works correctly. The PR_Crew portal is currently filtered by department "sDepartment" pop-up menu. Most users generally cannot change this as it's dependent on which department they work in, but for the sake of this post the department can be changed using the popup menu.

 

Case ( 

IsEmpty ( Days::sDepartment ) ; PR_CREW::Department <> Days::sDepartment ;
PR_CREW::Department = Days::sDepartment

)

 

I need to retain this popup filter and filter out any crew that's already been added to the DailyTimes table for that day so a duplicate entry cannot be made for the same person. I have created an additional field in the Crew table to list which days they already have records in, but I'm going in circles when it comes to calculating the filter. I'm either getting all crew member names listed in PR_Crew or none.

[Fitch]

I'm not sure I fully understand the setup here, but one alternate approach you could try is to script a Find for the crew records you want to display, then grab their IDs and put them in a global field, then use that global field to drive the portal's relationship. You wouldn't need to apply a filter at that point.

[madman411]

2 minutes ago, Fitch said:

I'm not sure I fully understand the setup here, but one alternate approach you could try is to script a Find for the crew records you want to display, then grab their IDs and put them in a global field, then use that global field to drive the portal's relationship. You wouldn't need to apply a filter at that point.

@Fitch - I managed to find a solution that is working by using the following calculation

IsEmpty ( FilterValues ( List ( Days::sCrewOccurence ) ; PR_CREW::PrimaryKey ) )

I do understand that filtering within the portal isn't the best method, but for now at least I have a working model. I will go back and try to recreate your method in another dummy file and amend as needed.

 

[madman411]

Okay, another question.

I am filtering my portal via a relationship using a popup menu to filter by departments (no direct filter on portal). This works fantastic so far, however I would like to be able to include an option to "show all" for select users. I've tried modifying the department table to include a "show all" record that features a "*" in the primary key field, but an asterisk doesn't appear to work like it does in Find Mode to show all records.

Is this just not possible using a Popup Menu? I may have to resort to filtering directly in the portal but I wanted to ask first.

Thanks

[comment]

This is very confusing. If there is no filter on the portal, then you are not filtering the portal. I presume you are filtering the relationship, but you're not telling us exactly how (or even what the relationship is to begin with, before the filtering).

Assuming that you have a Parent table and a Child table, and that there is a Department field in the Child table and a global gDepartment field in the Parent table, and that the relationship is filtered by matching these two fields, you can define a calculation field in the Child table (result is Text) =

List ( Department ; "All" )

and use this field instead of the Department field in both the value list and the relationship definitions.

 

[madman411]

Thank you, comment. That worked.