how can one implement an if-clause in a portal?

[imz]

Hi,

It would be absolutely great if someone could help me with a really tricky matter. Let me explain first of all that I am bound to the database structure I will explain, because there is an existing mysql-database which should be administrated by my filemaker-solution.

The tables of interest are:

FILMS

PERSONS

PERSON_TYPES

PERSONS and PERSON_TYPES are linked to FILMS by an n:m-relation (X_Films_Persons, with the fields ID, Film_ID, Person_ID, PersonType_ID).

Now I want to display all the persons and their personType belonging to type 1,2,3 in one portal in FILMS, all persons belonging to 4 in another one and the rest (5,6,7,8) in the last portal.

I tried to do this by a relation between FILMS and X_Films_Persons with two conditions, but I encountered the problem that for this I had to use a calculation field in X_Films_Persons to evaluate the personType. As I learned this doesn't work because like that the calculation field cannot be indexed which is necessary to build a valid relation.

I would be really glad if somebody knows a proper solution for that task!

Thank you,

Bernhard

[transpower]

You can use an auto-enter calculation with the Evaluate function. You can then index that field and use it in a relationship.

[Reed]

Here's something I threw together with one portal that will show people of whatever type specified in the checkbox field for the given film. You could easily make three separate portals if you wish. Does the structure here match the structure you specified? It was a little unclear to me if you actually have a join table for the three base tables.... (Although I don't see how you could do this without one.)

Let me know whether this helps,

Dana

movieStar.fp7.zip

[crpatter]

You could create three relationships:

The first relationship would be based on Persontype < 4

The second relationship would be Persontype = 4

The third relationship would be Persontype > 4

[imz]

Hi folks,

Thanks a lot for the replies and sorry for my delay in answering - I became daddy two weaks ago and had a break with my work due to this.

Anyway: Transpower, I do not really understand your point, perhaps because I am working with the german version of filemaker. Do you mean using a look-up? This doesn't work for me, because in case of a change in the perstypes, I need the dynamics of a relationship. I would be glad if you could become a little bit more detailled with your proposal!

to Reed: Thank you for your exemple that is indeed similar to my solution. Yet it doesn't solve my problem of separating the data into three portals of which each displays only certain data following the person type.

to crpatter: That

[imz]

hm... I know that it is rather a tricky one that I have here...

Yet I am wondering if there is really nobody with some sort of a solution.

If this should be the case, could the "clever ones" in here, like transpower, tell me so? Then I would have to start with a completely different approach (which would be, without doubt, the second best solution ;-)

Hope to hear from you soon,

Bernhard

[imz]

Why is nobody answering? I am a bit confused - perhaps this is because I am new to this forum; but have I offended someone by something I was saying or doing?

[RalphL]

The 2 weeks off might be the reason. I do not understand what you are trying to do. Perhaps a sample file would help. New to see the database structure and relationships.

[comment]

I am not one of the "clever ones" in here. I am making my baby steps in v.7, and this seemed like a good opportunity to learn something.

See if this does what you expect (thanks to Reed for providing the basic idea).

movieStarS.zip

[comment]

OK, that won't work, at least not all the way.

You want to display "all the persons and their personType belonging to type 1,2,3 in one portal".

I think the maximum you can expect is to display all the persons (without the Type) that meet the criteria. There'e simply not enough information there. I can answer the question "Who was either writer or director on this film?' and say "The people in this group". If you then ask me "What did each one of them do?', my answer will be "Either writer or director."

Furthermore, if there's a person who meets the criteria more than once (e.g. he wrote the movie AND directed it), his name will only be displayed once.

The new attached file has the upper portal and the upper part of the relationship table fixed. The red color represents IMHO the end of the tunnel.

Perhaps performing a Find would be a better approach.

movieStarS2.zip

[RalphL]

I took Comment's first file and reworked it. I used a Star Join. In the attachment look at the relationship graph. The right part is the basic E-R diagram. On the left are 3 sets of relationship. The top one is much like the first of Comment's. The middle one is used to define an actor portal. The bottom one is used to define the writer & director portal. This last one is a little tricky, FMP provides AND relationships but does not provide OR relationships. I had to make field to use as a key that used both the key for writers and the key for directors. The 2 keys need a return between then. This meant I had to change all key fields to text.

I went a step beyond and put a portal on the people layout to show what they films they were in and what they did.

I think this should meet all of the needs

[comment]

Well, I'll be... I could have sworn I have tried exactly that (what you did in the top part) and it didn't work for me. By now I thought it wasn't possible: how can I show 2 Oliver Stones in a portal, when there is only one "Oliver Stone" in the entire file? My hat is off to you!

A question:

What is so "tricky" about the Writers & Directors portal? If the User's Choice portal can show this combination, then surely if you hardwire the choice in a calc field it will still work, with no additional fields needed.

[RalphL]

Wouldn't the calc field be an additional field? What I need was a relationship between Films and WriterDirector Film ID AND (Writer ID OR Director ID). The new field simulates the OR part.

[comment]

The User Choice portal is just as "tricky": if 2 or more boxes are checked, that's your OR. What I meant to say is that once you've managed to get the User Choice portal to work, you've solved it for all possible implementations.

[RalphL]

I agree that they are very similar, but that is not obvious to everyone.

[imz]

Thank you very much for all your good suggestions! Now I even do understand what transpower meant in the beginning... I was a little bit locked up in my own mind so I did not manage to step out of it.

Now everything works perfectly - hurray!