User Name Relationship Question

[kseidule]

Let's say I have the following...

Table: PROCEDURES

Field 1: PROCEDURE_NAME (Text)

Field 2: PROCEDURE_DESC (Text)

Field 3: PROCEDURE_USER (Text)

When a record is created, the account name is automatically put in PROCEDURE_USER.

So when I log in as "Admin" and create a record and I make...

Field 1: "Procedure 1"

Field 2: "Procedure 1 Description"

Field 3: "Admin" (automatic)

Now I log in as "Kevin" and create a new record...

Field 1: "Procedure 2"

Field 2: "Procedure 2 Description"

Field 3: "Kevin" (automatic)

Now I log in as "Joe" and create a new record...

Field 1: "Procedure 3"

Field 2: "Procedure 3 Description"

Field 3: "Joe" (automatic)

Now I see a list of records...

RECORD 1: "Procedure 1" "Procedure 1 Description" "Admin"

RECORD 2: "Procedure 2" "Procedure 2 Description" "Kevin"

RECORD 3: "Procedure 3" "Procedure 3 Description" "Joe"

Now I want to do the following.

1.) Be able to show all records to the logged in user. Easy as it already does this.

RECORD 1: "Procedure 1" "Procedure 1 Description" "Admin"

RECORD 2: "Procedure 2" "Procedure 2 Description" "Kevin"

RECORD 3: "Procedure 3" "Procedure 3 Description" "Joe"

2.) Show ONLY those records belonging to the logged in user (will be in a different layout).

If logged in as "Admin", I want to ONLY see...

RECORD 1: "Procedure 1" "Procedure 1 Description" "Admin"

...as "Kevin" ONLY see...

RECORD 2: "Procedure 2" "Procedure 2 Description" "Kevin"

...as "Joe" ONLY see...

RECORD 3: "Procedure 3" "Procedure 3 Description" "Joe"

I know I need to make a copy of the table with a relationship, but I can't seem to get it to do anything except show ALL records for all users and can't figure out this relationship.

Apologize for the "newbie-ness" of this question. Thanks very much in advance.

Kevin

[Fenton]

Create a unstored calculation field, text result, = Get ( AccountName )

This will recalculate to the login Account name.

Create a table occurrence, with the relationship (self-relationship) from the above field (in the main table) out to the User field.

Go To Related Record ["relationship", Show only related]

[kseidule]

Oh Man! Thanks Fenton!

I almost got there. Had everything except the "Show only related".

And as always, my grateful thanks to the experts here who take the time to answer these questions. This forum is what makes Filemaker "work".

Kevin

[kseidule]

OK, I jumped the gun. Let me further explain since I must still be doing something dumb-headed,

------------------------

Table: PROCEDURES

Field 1: PROCEDURE_NAME (Text)

Field 2: PROCEDURE_DESC (Text)

Field 3: PROCEDURE_USER (Text - automatic Account Name entered)

Field 4: PROCEDURE_CURR_USER (Calc - unstored - Get(AccountName))

Table: PROCEDURES 2 (COPY OF PROCEDURES)

Relation: PROCEDURE_USER = PROCEDURE_CURR_USER

Field 1: PROCEDURE_NAME

Field 2: PROCEDURE_DESC

Field 3: PROCEDURE_USER

Field 4: PROCEDURE_CURR_USER

-------------------------

Now I have two "tabs". One is a layout called "Yours" and another is a layout called "Everyones". These two different layouts each show a LIST of records.

When I go to layout "Yours", I want the user to see JUST the records that match the current user (the logged in AccountName).

When I go the "Everyone's" I want to show all records created (this already works like one would expect).

Now, I ASSUME the layout "Yours" records are based on table "PROCEDURES 2" and the fields shown are also based on "PROCEDURES 2 ", and the layout "Everyone's" shows records based on table "PROCEDURES" and the fields shown are also based on "PROCEDURES".

There is no Go To Related Record option, as I am displaying a list. I just Go to the layout. For instance, click on tab "Yours" and the list of records shown should be just the one's that match the current user.

Is this clear? I hope so. Again, your help is appreciated.

Kevin

[-Queue-]

When you click on "Yours", use Go to Related Record with Show only, and specify the list layout.

[kseidule]

I did the above, but no go. I think the problem is that there is no related record at this point. I just have a layout showing a bunch of other records from something else, but when I click on the tab now set as Goto Related Record I don't think there is a related record.

Yes, it is quite possible I am being a dufus.

Thanks again,

Kevin

[-Queue-]

Ah, add a test before the GTRR step.

If [not IsEmpty(relationship::serial)]

Go to Related Record [show only related, "yourrelationship"]

Else

Show Custom Dialog ["You have created no records."]

End If

[Fenton]

Table: PROCEDURES 2 (COPY OF PROCEDURES)

Relation: PROCEDURE_USER = PROCEDURE_CURR_USER

The above is backwards. Should be: PROCEDURE_CURR_USER = PROCEDURE_USER

First, using the word "Table" for Table Occurrences is confusing. TO is good (and short).

It is natural to want to create the relationship with the "real" field as the source of a relationship, and the "weird calculation or global field" as the target. But, just like in 6, you can create a relationship to a global (and 7 won't complain at all), but it will not work as expected; it's the wrong direction.

It doesn't much matter when you have a self-relationship on the same indexed field (obviously, since both sides are exactly the same). But otherwise it does matter, especially with an unstored field on one side.

Since you are filtering by the calculation, you want it as the source of the relationship, IN the main Table Occurrence, and the "real" field in a TO off the side, as the target of the relationship. Because this "side" TO is from the same "base table" (Procedure in this case), it can access any record in Procedure which matches the relationship; that's what you want.

Also, don't use Procedures 2 as the name, unless it's so unimportent that you don't care if you know what it is. I would use something like:

PROCEDURE_CurrUser

or

self_CurrUser

We all have our own little naming conventions, but make it recognizable.

[kseidule]

No, the layout I am currently on shows the records from "BLAH" table.

So If I create the button "Yours" and have it show related records from "PROCEDURES 2" shown using the Layout "Yours", there are no related records to the "BLAH" table. Hence nothing happens even though I know they are there and were created.

I obviously still don't get it, although thanks for the above handling when there are no related records.

Kevin

[Fenton]

Well, I can't see why it wouldn't work. I've done it many many times. Is the CurrUser field Unstored (should be)? I thought this was a self-relationship, ie., the same "base table," Procedures.

If you've got 2 TABLES, well, from what you said, you shouldn't have. You have 2 Table Occurrences; very different.

[kseidule]

OK, I am officially dufus boy.

The layout I was going from wasn't even related to anything, so how the heck can you go to a related record! DUH!

Thanks for all you help (and advices) on this.

Many Regards,

Kevin