Jump to content

# Filtering a portal with case shows too many results

This topic is 2225 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

Hi All,

I am trying to filter a portal using case to display records matching only one condition (the first it match going down), but it looks like the filter is taking into consideration all the options and displaying related records that match any of the conditions.

EG:

Case (
newA = oldA ; 1 ;

newB = oldB ; 1 ;

newC = oldC ; 1 ;

newD = oldD ; 1 ;

0)

I thought the filter would work by only showing me the records that matched the first matched condition, but instead it's showing records that match any of the conditions.

EG: if I had a record that had 5 related records, 1 where newA = oldA and 2 where newC=oldC, the portal would show me those 3 records instead a of just the first one which matched the first condition newA = oldA.

Can anyone give me any suggestions as to how I can get it so that the portal only filter 'stops' checking the case condition once it's found a matched one?

##### Share on other sites

Hi Justakid

The Filter rule applies itself to each related record individually, it has no idea what the result was from the previous related record. So whether or not the first record was successfully shown or not has no effect on whether the next record gets filtered or not.

What you are trying to do is much more complex. Here's how I would do it.

In the related record table setup a calculation field for each of your match conditions. So you would need four based on your example.

Lets call them newAMatch, newBMatch, newCMatch and newDMatch. The calculation for each one is :-

`If ( newA = oldA ; 1 ; 0 )`

and so on for the other 3.

Then in your header table, create a calculation field that determines whether or not there are matches for each of the conditons

```matchField =

Case (

Sum ( myRelationship::newAMatch ) ; "A" ;
Sum ( myRelationship::newBMatch ) ; "B" ;
Sum ( myRelationship::newCMatch ) ; "C" ;
Sum ( myRelationship::newDMatch ) ; "D"

)```

You now have either an A, B, C or D in the header record calculation field (and you'll only have the one that is earliest in the alphabet because that is what Case statements do, they stop when they get a match). You can now filter your portal based on that result.

The Filter should be :-

```filterCalculation =

Case (

( myParentRecord::matchField = "A" ) and (newA = oldA) ; 1 ;
( myParentRecord::matchField = "B" ) and (newB = oldB) ; 1 ;
( myParentRecord::matchField = "C" ) and (newC = oldC) ; 1 ;
( myParentRecord::matchField = "D" ) and (newD = oldD) ; 1

)```

Sample file attached!

Edited by rwoods
• 1
##### Share on other sites

Wow, thanks for such a quick and thorough response rwoods. I didn't realise it was so complicated, but I'm pretty sure I understood what you wrote.

Thanks again!

##### Share on other sites

I am not sure it needs to be so complicated - but then I did not understand your original question.

##### Share on other sites

Sorry comment, hopefully this is more clear...

I have a layout with a portal. Within the portal there may be many related entries. What I want to do is filter that portal so that it only show the records where

newA = oldA, if there are no records matching this condition then it should show the records where newB = oldB, then  newC = oldC, then newD = oldD and if none of those conditions are true, it should show no records in the portal.

The issue I'm running into is I want it to stop checking if the it finds a condition that is matched, but FM doesn't seem to be doing that with the case logic I used.

##### Share on other sites

Hi comment.

There may well be an easier way to do this, but it is an unusual requirement.

Justakid, I wonder what real life scenario you have where this kind of outcome is needed? Hope the sample file made it clear?

##### Share on other sites

1 hour ago, rwoods said:

what real life scenario you have where this kind of outcome is needed?

I would ask the same question. I suspect this is really a by-product of inadequate data structure. As a result, a complex and inefficient method needs to be employed to achieve the required display. The usual problem with portal filtering is that every related record needs to be evaluated in real time in order to render the portal; here, in the worst case scenario, each related record needs to be evaluated 4 times!

##### Share on other sites

Sorry for the late response rwoods, comment.

I couldn't get the solution rwoods suggested to work, then my urgent need to get it to work was no longer urgent.

rwoods, your sample was helpful, but as comment suspected, my need for this is definitely due to an inadequate data structure. Now that I've got more time, I'll try to plan it better.

In terms of real world application, I was trying to create a database that would help me compare the contents of two EDLs (list of shots and timecodes that tells a system how to compile a timeline) and show me the differences.

Once I rebuild my DB with non-production information I'll post some more info.

Thanks again for all your help guys. I should learn that getting to 85% really quickly (without thought) can make the last 15% really hard.

BTW, if you're interested in knowing what an EDL looks like, here is a small snippit.

038   A111_C004_0711AI   V    C          19:20:23:07 19:20:24:03 01:03:37:05 01:03:38:01
039   A732_C008_071134   V    C          23:43:23:22 23:43:24:21 01:03:38:01 01:03:39:00
040   A231_C004_0711VD   V    C          21:51:26:01 21:51:27:03 01:03:39:00 01:03:40:02
041   A732_C008_071134   V    C          23:43:39:17 23:43:41:08 01:03:40:02 01:03:41:17
042   A111_C005_0711B7   V    C          19:25:46:18 19:25:48:14 01:03:41:17 01:03:43:13
043   A732_C004_071160   V    C          23:34:43:08 23:34:44:13 01:03:43:13 01:03:44:18

Edited by justakid
##### Share on other sites

As it happens, I do know what an EDL is - but I don't know what it means to "compare the contents of two EDLs". Or how this refers to the original problem - i.e. what are newA, oldA, newB, oldB, etc.

##### Share on other sites

This topic is 2225 days old. Please don't post here. Open a new topic instead.

## Create an account or sign in to comment

You need to be a member in order to leave a comment

## Create an account

Sign up for a new account in our community. It's easy!

Register a new account

## Sign in

Already have an account? Sign in here.

Sign In Now
• ### Similar Content

• #### Importing and Portal data

By Sinky,

• 0 replies
• 256 views
• #### How to structure, would this be Many-to-Many?

By Tony Diaz,

• 3 replies
• 1,236 views
• #### Self Portal not displayed in Webdirect

By stan111,

• 6 replies
• 632 views
• #### How to generate different Portal line numbers for different parent record

By Guy_Smith,

• 4 replies
• 1,757 views
• #### Filemaker go portal not showing data on iPhone 5

By Randy Bennett,

• 2 replies
• 1,799 views
×
×
• Create New...

## Important Information

By using this site, you agree to our Terms of Use.