multiple records calculation problem

[Valdrin]

Hi all,

Hopefully you can give me a hand with this problem.

I'm making a database in Developer7 that has the following tables and fields:

Table #1 (SetUp)

ProjectName

Locations

Dates

Table #2 (Panelists)

Name

Address

City

State

Country

ZipCode

Phone

Email

Notes

OnPanels (a list of all panels that the panelist is on, based on the Events table. )

Table #3 (Events)

EventName

Description

Location (value list from SetUp::Locations)

Date (value list from SetUp::Dates)

StartTime (value list)

Duration (value list)

Moderator (value list from Panelists::Name)

Panelist1 (value list from Panelists::Name)

Panelist2 (value list from Panelists::Name)

Panelist3 (value list from Panelists::Name)

Panelist4 (value list from Panelists::Name)

Panelist5 (value list from Panelists::Name)

Panelist6 (value list from Panelists::Name)

Panelist7 (value list from Panelists::Name)

Panelist8 (value list from Panelists::Name)

I'm having trouble figuring out how to calculate the OnPanels field in Table#2 (the Panelists table). What would be the best way to do this?

Any and all help will be greatly appreciated.

[transpower]

You need to create a join table: panelist_event, with two keys: panelistID and eventID. Then show a portal in Panelists from panelist_event. In the Events table you would have a portal (to panelist_event) where you would add the panelists.

[Søren Dyhr]

To me does it looks like table 3 should be structured further - all the panels in a new table, wouldn't you get direct access to it then, either by ValuelistItems( or...: Explain the reasoning behind!

--sd

[Valdrin]

You need to create a join table: panelist_event, with two keys: panelistID and eventID. Then show a portal in Panelists from panelist_event. In the Events table you would have a portal (to panelist_event) where you would add the panelists.

That sounds great, but I'm having a little trouble implementing it (I must be messing up relationships somehow).

[Valdrin]

To me does it looks like table 3 should be structured further - all the panels in a new table, wouldn't you get direct access to it then, either by ValuelistItems( or...: Explain the reasoning behind!

--sd

I'm trying to make it easy for the users to add and subtract panelists from each event/panel and move the events/panels around in both location and time. The schedule will be changed by the programming committee at least a hundred times before it is finalized.

I'm still very much a FMpro newbie and if I'm gong about this the wrong way please let me know. I will be more than happy to change things around if it will make things run smoother. What changes would you make to table 3's structure?

[Søren Dyhr]

Hi I've made a template as an inspiration to solve the issue you had with the calculation, which it isn't a calculation problem at all ....if you use relations this way - But please question my reasoning behind!!!

--sd

Valdrin.zip

[Valdrin]

Thanks S