Selective relationship

[amiga115]

Hi.

I have a self join relationship with 3 global fields, related with 3 different fields.

 

Global_name = Name and

Global_date

Global_status = status

 

This way i use a portal to see the data and it works the way I want. I use value lists and select a name, a date and a status.

My question is this: How can I see the data using only one criteria? For example using all names and all dates but a certain status? Obviously I can't use a different relation for each occasion. I tried to use wildcards, but it doesn't work.

Any suggestions?

Thanks

[sujat]

Hi.

I have a self join relationship with 3 global fields, related with 3 different fields.

 

Global_name = Name and

Global_date

Global_status = status

 

This way i use a portal to see the data and it works the way I want. I use value lists and select a name, a date and a status.

My question is this: How can I see the data using only one criteria? For example using all names and all dates but a certain status? Obviously I can't use a different relation for each occasion. I tried to use wildcards, but it doesn't work.

Any suggestions?

Thanks

 

To view data in a portal you do not need Global fields. try this -

 

Name - text

Date - Date

Status - Text / Number ( depends on what you want to use )

 

As you have done create a self joining relationship Status = Status

 

Put all the fields in the portal and you are done.

 

Sujat

[amiga115]

I use this selfjoin relationship: Global_status = status. The global field is needed in order to choose the data in the portal. For example the global_status field uses the same value list of the status field. So when I want to see in my portal all the records that have status: Delivered for example, I choose "Delivered" on the global field and the portal gives me only those records. If I made a self join status=status, I would get all the records in my portal.

The relationships, fields and core design of the db is fine. The way it works, it gives me the records when all the criteria is true. So, when I choose a name, a date and a status, I get only those records. But I need to be able to also use one or two criteria. For example: "Give me all the records that have name "Name1" and date after "1/11/07" and status "all"" or "Give me all the names and all the dates that have status "pending"". This "all" thing is what I'm missing. I tried to use a wirldcard but it used it as a criteria. It was searching for name "*". Of course that name doesn't exist.

This is a very nice way to search the data live. I could use a find script and it would be very easy, but it would be slower and ugly..

[Ender]

A relationship needs to match something, so you'll need to add calcs the tables to account for or "All" filter values.

 

The key on the parent side might be something like:

 

parent_key (calculation, text result) = case(isempty(filter);"zzzAll";filter)

 

and the key on the child side might look like:

 

child_key (calculation, text result) = field & "¶zzzAll"

 

 

In that example, when the filter is left blank, the calc populates with zzzAll, and that matches all records on the child side. I used "zzzAll" to avoid any unintensional matches with real data values.

 

 

The date range parent key is similar, but instead when the filter is blank, have the calc use a date prior to anything you might have in your data, like date(1;1;1900), or whatever. The child key can just be the original date field.

[amiga115]

Wow... It worked. I added a calculation field that copies the contents of the name field, adding "¶All" and I linked it to the global_name, instead of the actual name field. Then I created a button that sets the Global_name to "All" and everything works great... Then I did the same with the status field and I have all the combinations I needed.

Thanks a lot... Well, not just a lot. Countless..

 

By the way, why the return symbol "¶"? And why this solution won't work if it misses?

[Ender]

The pilcrow (¶) is used in calculation dialogs to designate a paragraph return. FileMaker will match on each line of the multi-line field, giving you an OR relationship.

[Jack Rodgers]

Here's a demo file for using a filtered popup to show a filtered portal based on the selection in two fields.

 

Parent

Child - shows filtered popup based on selection in parent

Grandchild - shows in portal to list all related grand child records.

[Ender]

Are you in the right thead, Jack? What's that got to do with amiga's question?

[Jack Rodgers]

Are you in the right thead, Jack? What's that got to do with amiga's question?

 

That would be a question for amiga to answer.

[amiga115]

Actually I cannot download the file to see what's on it. I quest, by the name, that it is an other way to display related records on a portal, using more than one relationship. I already knew how to do that. An "OR" relationship was my question and it has been answered.

Thanks...

You're great