Relationship Help

[mark s reynard]

HI please see attached file.

I have 3 tables 1 that stores users, 1 that stores books, and 1 that stores books read.

What i am trying to achieve

In the Users layout i want to show a portal showing all available books from the books table, but i want to highlight using conditonal formatting all the books read by the user.

Please help as i am seriously stuck on this

Thanks

mark

test.fp7.zip

[efen]

Like the attached - you need to change your table key fields to serial numbers rather than use user names/book names as links

test1.zip

[efen]

Sorry - unfinished file/wrong relationship in previous post - use this one

test1.zip

[mark s reynard]

Hi, an Thanks for your help.

However the Date Read shown on the users page for all books doesn't work. It shows the same values for all users.

Sorry

Thanks again.

[comment]

Try it this way?

test2.zip

[bruceR]

Try this.

test.fp7.zip

[comment]

Why these?

[bruceR]

Leftover from original (message 3).

A few improvements now added:

test.fp7.zip

[mark s reynard]

Hi thanks for all your help on this, i'm really grateful, however, these solutions don't address my original dilemma.

I was wanting to show 1 portal showing all the books available, then by using a calculation field if the book had been read by this user, bring the read date from the books read table.

I was then going to use conditional formatting to highlight any of the books that has been read.

User Mark Reynard

Books Available Read Date

Filemaker 10 Beginners 24/01/2011

Filemaker 11 Beginners 25/01/2011

Filemaker 11 Server

User Dan Hepworth

Filemaker 10 Beginners 01/10/2010

Filemaker 11 beginners

Filemaker 11 Server 24/03/2011

I hope this makes sense. I know they'll be a way to do it, just cant get my head around it, Thanks again for all your help.

mark

[bruceR]

No, it is not as easy or clean as you think to put them in the all-books portal.

test.fp7.zip

[comment]

I was wanting to show 1 portal showing all the books available, then by using a calculation field if the book had been read by this user, bring the read date from the books read table.

I was then going to use conditional formatting to highlight any of the books that has been read.

It is quite easy to highlight the books read by the current user. It is not so easy to include the date read in the AllBooks portal. The date read is not an attribute of a book - the same book has different dates read, by different users. The book record does not know which user record you are currently viewing.

The solution for this is to have a global field in the Books table indicating the currently viewed user record (populated by a script triggered OnRecordLoad in the Users layout). Then you can have a relationship between AllBooks and BooksRread matching on BOTH BookID and gUserID and place the date read from the matching record in the portal.

[mark s reynard]

Hi, could you explain the easy method to highlight the books read by the current user, as this is effectively all i need to achieve.

Thanks

mark.

[bruceR]

Hi, could you explain the easy method to highlight the books read by the current user, as this is effectively all i need to achieve.

And that already exists in the example I posted.

[comment]

Look at the conditional formatting applied to the button in Bruce's latest file.

[mark s reynard]

Thanks everyone for all your help.

It works,

See attached, many thanks

test 8.fp7.zip