Relationship/Portal Sort help

[chikanyc]

Hi Everyone,

I'm stuck. I don't know if I'm setting the proper relationships or if this is a portal sorting issue

I have four tables.

(1) Users

- UID

- Fullname calculation (from First Name, Last Name fields)

(2) Screening Sheet

- UID

- each invoice has portals to two other tables. one to show an itemized list of songs used. another to show who the Producer(s), Editor(s), and Production Associate(s) were.

(3) Screening Sheet Line Items

- UID

- Song IDs (populated from a separate Song database)

- fk_UID - copied over screening sheet UID

- these line items are generated from a portal on the Screening Sheet table/layout.

(4) User/Screening Sheet Line Items

- UID

- fk_UID - copied over screening sheet UID

- fk_UserID - populated by UIDs from the first table

- these User Line Items are generated from a portal n the Screening Sheet table--there is a layout for Entering Producer Info, Entering Editor Info & Entering Prod Assoc Info.

- UserRole - I set this field up as a calculation that depending on the layout in which you are creating the portal record, the field is automatically set to "Producer", "Editor", "Production Associate"

- also, there can be multiple producers, editors, production associate for a give Screening Sheet UID.

- My problem is this: Let's say that you create a portal record (a new record in User/Screening Sheet Line Items) in the "Entering Producer Info" layout by entering a fk_UserID., e.g. ID#1122. The UserRole field is automatically calculated to "Producer". Now when you go to the "Entering Editor Info" layout, the portal table already shows User ID #1122 as an existing record. I don't want that to appear but show only other UserRole="Editor" portal records.

Is there a way to do this? Thanks so much for reading. I'm so confused.

[bcooney]

"- UserRole - I set this field up as a calculation that depending on the layout in which you are creating the portal record, the field is automatically set to "Producer", "Editor", "Production Associate"

This seems the culprit. If you want a portal that only shows records of a certain role, then you need three relationships from Screening Sheet to SS Line Items. Create three "constants" in Screening Sheet table-calc, result text = "Producer"; calc, result text ="Editor", etc. Then your portal to Producers would use the relationship that has _fk_UID and "Producer"=Role.

That said, I hate such hard-coded stuff. What if you add another role? How about one portal for all people and a popup for their role? You could add a filter to the portal to see only people of certain roles.

Some more advice:

Name your key fields to indicate the entity to which they belong. UID isn't very descriptive, and you have the same field name in all tables. How about, __kP_SongID? __kP_ScreenSheetID? Check out the FMI White Paper on Development Conventions.

[chikanyc]

Thanks for the input...I was just attempting to do the constants, but was still running into trouble. Do you know how I can filter the portal instead? Thanks:)

[bcooney]

To create a filtered portal from Screening Sheet to SSLineItems, add a global text field to Screening Sheet. Assign a value list to the global field that contains your roles. Format it as a popup menu.

The relationship from SS to SSLineItems would be by ScreeningSheetID AND gRole = Role.