Filtering a portal with case shows too many results

[justakid]

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?

[rwoods]

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!

newA.fmp12

Edited May 23, 2016 by rwoods

[justakid]

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!

[comment]

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

[justakid]

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.

[rwoods]

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?

[comment]

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!

[justakid]

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 May 29, 2016 by justakid

[comment]

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.