filemaker : joint table problem

Discussion created by jenny610138 on Nov 10, 2016
Latest reply on Nov 13, 2016 by David Moyer

I'm writing a call sheet for movie shooting  and have some problems can't be solved ><


the simple idea is

each shooting date have multiple scene to shoot (one to many )  &

every scene have multiple actor and every actor have multiple scene to shoot  ( many to many)


we hope that when we finish importing scene and date data,  and the corresponding scene-ShootingDate  relationship

the sheet  will automatically  import the scene that should be shoot today

and every actor that appear in the scene

(there will be an actor list to choose anyway)


call sheel example:

Date 2016/1/1


scene to shoot
scene- time -  context------------------------------------Actor

S01 ---day ---peter go back home-------------------------Peter

S02 ----night--Sam comes in and said hi to Peter------Sam,Peter


actor   -    scene----------call time

Peter   -     S01, S02------6:00(edit box)

Sam    -     S02------------6:00



the problem is that when the same actor appeared in both scene

the result will be


actor   -    scene

Peter   -     S01

Peter   -     S02

Sam    -    S02


it's fine if there's few actor in that day , but when there're tens of scene shooting that day

the list will be soooooo long


is it possible to convert into the form below?

Peter   -     S01, S02

Sam    -     S02



or is there any solution to list the correspoing scene for actor at specific day


my structure is below

actor ::actor  = actor-scene:: actor  AND   actor-scene::scene = scene

Shootingdate ::date =  scene::date


I'm thinking of using the script

  Substitute ( List(actor-scene::scene) ; "¶" ; ", ") & "."

in the actor-scene portal  at the table of Shootingdate

but  I have no idea where to put this ><


hope there's a solution

thanks a lot!