3 Replies Latest reply on Aug 31, 2011 11:47 AM by LaRetta_1

    Pulling a list of values from one table into another...

    ijontichy

      Title

      Pulling a list of values from one table into another...

      Post

      I'm on a film project and every day we're generating several shots.

       

      So on August 30th for example we might have 40 shots.

      24A take 1

      24A take 2

      24A take 3

      24b take 1

      etc.

       

      All the way through...let's call it 24cc take 9

       

       

      This information comes to me via a very field dense export from telecine.   So while 24A is a unique field and take 1 is a unique field, they are both in the same record with the date, the lab roll, etc.

       

      For the day to day, this is fine because what we really care about most is the individual shot, and we hang a lot of the data off of that.

       

      But there are times when I'd like to very easily display just the unique slates for any given day.

       

      I know I can create a get(valuelist) function that will show me all the slates with no repeats for a given day--a friend showed me how to do that, but...

       

      But I'd like to create a separate table that is essentially a calendar.   If I create a new record for August 30th, I'd love to see a list of just the unique slates (and not the takes) that were shot on that day.

       

      08/30/11:  24a, 24b, 24c, 24d...24cc

      with no repeats.

       

      Yes I could find for unique dates and get the same thing, but there has to be a way to harvest these sorts of lists and relate them to different tables and that's where I'm stymied.

       

      Thanks in advance for any and all suggestions.

       

       

        • 1. Re: Pulling a list of values from one table into another...
          LaRetta_1

          I do not know how the slates are assigned.  Ideally they would be in their own table.  It is easy to import only unique of each number - just set the SlateID to type text and set the options in validation tab to be unique and validate always.  Then show all Shots, go to your Slates table and import the Slate.  Any duplicate Slates will be excluded from the import.

          Once you have finished creating the Parent (Slates) then relate Slates table to your Shots table ( File > Manage > Database > Relationships tab ) as:

          Slates::SlateID = Shots::SlateID

          Now that you have a good, valid relationship, you can view the resulting Slates in may various formats.  From a layout based upon Slates, Insert > Field and select your Shots::Date field and place it directly onto Slates.  Perform a find for the date you want then switch to list view.  This can all be easily scripted.  Also, Insert > Portal and select Shots so when viewing each Slate, you can see all shots associated.  You can even assign button to portal rows to bring up that row's detail information.

          • 2. Re: Pulling a list of values from one table into another...
            ijontichy

            This works.   Thanks!  Can I assume going forward that if I add a slate to the shots table it will be added to the slates table, provided it's a unique value?   I have the relationship set up to allow the creation of new records.

            • 3. Re: Pulling a list of values from one table into another...
              LaRetta_1

              It will not automatically be added but it can be handled in the same script that imports your data.  Here is how I would handle it...

              Go to your relationship and also turn on 'Allow Creation' on the Slate side.  You haven't indicated what fields you want in Slates but I would suggest that you have a CreateDate (type date); do not set this to auto-enter date created - you want to handle that from the imported Takes table. 

              With 'Allow Creation' on, you can set any parent field (in this case we will set the CreateDate) and it will automatically create a new ParentID if required. 

              So script might look like (continuing after your import while you still have your found set):

              ... your regular import process and then ...

              Go To Record/Request/Page [ first ]
              Loop
              If [ IsEmpty ( Slates::SlateID ) ]
              Set Field [ Slates::CreateDate ; Get ( CurrentDate ) ]
              End If
              Go to Record/Request/Page [ next; exit after last ]
              End Loop

              If there is other information you wish to complete in Slates (such as typing to a film?) then just use global fields to capture your input and set any other fields in Slates right within this same script.