Filtering difficulties with repeating groups

[Stevebenson223]

I'm building an app to record and display amateur theatre productions. I need to record basic details of every production - title, dates, artwork, synopsis - as well as people data such as cast and crew. It's a bit like a mini IMdB. Data is entered on the form, a list displays the productions and each production is displayed in the Detail view.

I have two main lists: Productions for all the productions and People, which is simply first name, last name and calc field for name. And I decided to use repeating groups for many of the fields, especially the people data. So I have repeating groups for:
Cast: 2 fields - Character Name and Actor Name (databasejoin to People)
Crew: 2 fields - Crew Role and Crew Member (databasejoin to People)
Company (where multiple people are grouped together as "Company" or similar): 2 fields - Company title and Actor Name (multiselect databasejoin to People)
Awards: 2 main fields - Award Name and Person (databasejoin to People)

This approach works perfectly for entering the data and displaying in the Detail view.

My problem is that I now want to be able to search on the people data. Ideally, I want to select a person's name and a list is displayed showing all the productions they were involved in, either as Actor, Crew Member or Company and any awards they may have won. I've tried various ways but I can't find a way to display details for more than one group.

Is there a way to do this or am I using the wrong approach?

 

[juuser]

I've tried various ways but I can't find a way to display details for more than one group.

Could you clarify this a bit. I assume you want to do the filtering in list view?

 

[Stevebenson223]

Yes, that seems the most straightforward way to do it. I envisaged using the "Require Filtering" = Yes option and having a single search/filter box with a dropdown showing all the names.
I have considered an alternative approach where there is a Detail page for every person, which is accessed either through a dropdown search/filter or maybe a link from any instance of their name. But this seems a bit less intuitive for users. (Also, I'm not sure how to achieve it!)

 

[juuser]

OK, I cannot think of a really good way to do it with Fabrik's out-of-the-box features.

Easiest way would be to use search all field, but this is an inputbox, not dropdown.

Second option would be to create a search form with only one dropdown/dbjoin element (not saving to database) which holds the persons:
https://fabrikar.com/forums/index.php?wiki/create-a-search-form/
Then open the form in a modal window from a link e.g. in list intro and on search (submit) button click, redirect to a url-filtered list like described in the link above.

Probably fanciest way would be to add a dropdown element with persons in the list header (create it with a query in custom template somewhere). Then, in list_x.js catch the change event of the dropdown and set-execute the filters which need to be included for persons search.

 

[Stevebenson223]

Thanks @juuser

I tried using the Search All field, but could not get the result I wanted. If I select only the relevant Elements from the Cast and Crew repeating groups, then type an Actor's (person) name in the Search All box, the list displays the correct productions and character for that person, but lists it three times each: one for each of the crew members for that production. Same vice versa: searching for a crew member name, will display that person's role multiplied by the number of characters in each production. Example below where the searched person played the Character shown in each Production. What I want to see is two rows: "Sugar, April 2019, (blank), Joe/Josephine" and "Scrooge, October 2019, (blank), Ebenezer Scrooge".


I'll try the Search form option next; the final option sounds great but is far beyond my abilities!

 

[juuser]

You can set "Merge rows and reduce data" for "Display mode" in your list's joins settings. That way your joined rows will no be repeated.

But you will still have 3 times of character's name in one cell as the same character has different roles.

If you want to get distinct rows by production, it's a bit trickier. Do you have always the same character for all roles within one production?

 

[Stevebenson223]

Yes, I tried the Merge rows options and it looks better but it's not right.

I don't think I've explained my example very well. The person searched for did not have any Roles in the production. The "Character" is from the Cast group which details each Actor and the Character they played. The "Roles" are from the Crew group which details the Director, Musical Director, Choreographer for each production (contained in dropdowns) all of which are undertaken by different people. It seems that the Search All box, having found a match in one of the columns, is then returning ALL the results in the other searched-for column. If this was an app for films, imagine searching for Johnny Depp and getting the following list:
Alice in Wonderland 2010 Director Mad Hatter
Alice in Wonderland 2010 Writer Mad Hatter
Alice in Wonderland 2010 Producer Mad Hatter
Alice in Wonderland 2010 Cinematographer Mad Hatter
etc

Johnny Depp did not fulfil any of those crew member roles. But if he had also been a Producer, what I would want to see is this:
Alice in Wonderland 2010 Mad Hatter
Alice in Wonderland 2010 Producer

I'm not sure whether this is related to the fact that I am using repeated groups or just because it's an unusual set-up, having the same information in different groups.

 

[juuser]

I think I get the point now. You basically want a bit like "pivot table" solution as you have repeat data in several repeat groups (several columns in list), but want the search results to be in one column.

Some options that come into my mind atm:

1) Change your setup so you would have only one repeat group basically with two fields:
- Role type (options: Character, Crew, Company)
- Person's name (who fills the role)

In that case you would have all the person names in one column of the list, so searching one way or another would be easier. For details view, you can have calc element which groups the roles types etc. and displays them however you like.

2) Try to create a MySQL view for the table to get the list structure you like. MySQL-views are not my strongest side, so I cannot give you exact tips just from the top of my head.

 

[Stevebenson223]

Thanks @juuser

I have considered option 1, but one problem is that I wanted different element types for the Roles: Character is a field, but Crew should ideally be a dropdown. And although Company is a field, the associated Person field needs to be a multiselect databasejoin. I suppose I could try with the Person field as multiselect for them all (thinking out loud now...).

I'll also have a go at the Search form option and see if that works better. I have hardly any experience with MySQL views so that would be a challenge for me.

Thanks again. You've (once again) given me plenty of food for thought!