5 Replies Latest reply on Nov 22, 2010 2:08 PM by philmodjunk

    Do I Need a Script or Calculation to populate a related field in parent table???

    MarieT

      Title

      Do I Need a Script or Calculation to populate a related field in parent table???

      Post

      The help I need is knowing if I need a script or a calculation and then how to do it?

      I have 4 tables:  Workers, Registrations, Jobs, Shifts in that order.

      On the parent table Workers I have a portal that includes information from the other 3 tables.  Basically the portal tells what job a person is registered for, where, and at what time (shift).  Simple eh.

      When I create a new job for Worker A in a certain shift, I want to automatically populate a unique shift field (there are 8) in the parent table with a "YES".  I've tried "IF" scripts, auto populate, calculations, but can't seem to get it to work properly.  What don't I know or what am I doing wrong?

      I've included a screenshot and am trying to poplulate the shifts outlined with red box with the info from the portal.

      Thanks in advance for your assistance.

      WorkerDB.png

        • 1. Re: Do I Need a Script or Calculation to populate a related field in parent table???
          philmodjunk

          There's more than one way to do this. They all rely on relationships and/or filter portals to accomplish.

          What version of FileMaker are you using?

          With FileMaker 11, you could place a series of one row portals in your header with different portal filters for each shift category.

          The first might be: Shifts::Day n shift = "Sat AM"

          You could just place "Yes" inside the portal row as layout text. If there is a record for the Sat AM shift in the portal, you'll see the word "yes". If there isn't, it'll be empty.

          • 2. Re: Do I Need a Script or Calculation to populate a related field in parent table???
            MarieT

            Hi Phil,

            WOW, thanks for answering!  I was reading all of your posts yesterday trying to figure this out and then I sent you that THANK YOU on behalf of all of us beginners.  I never expected to hear from YOU!

            Unfortunately I am using FMP 10.  I read how it could be done with portal filtering in 11.  Sounds slick. 

            Can I do it with 10? 

            I've tried to include an screen shot of the relationship.  Not sure if it will send.

            Any ideas you can provide will be greatly appreciated.  I've been working on this one problem for 3 days!

            Thanks,

            Marie

            relationship screen shot

            • 3. Re: Do I Need a Script or Calculation to populate a related field in parent table???
              philmodjunk

              Let's define an extra table to serve as the "bridge" to your different shift categories.

              Assuming your layout is based on Workers, we could establish this relationship:

              Workers::WorkerID = ShiftTypes::WorkerID
              ShiftTypes::ShiftType = ShiftsByCategory::Day n shift

              ShiftTypes would have these fields:
              WorkerID (Number)
              ShiftType (text)
              cShiftFlag ( Calculation: If ( Not IsEmpty ( ShiftsByCategory ) ; "Yes" ; "" ) )

              ShiftsByCategory is a new table occurrence of Shifts (or whatever TO represents the portal in your screen shot.) You create it by clicking Shifts, then clicking the button with two green plus signs to make a copy of it.

              Now you need to load ShiftTypes with records for each possible shift type for each worker. You can set up a script, so that each time you create a new worker record, your script also creates the related shift category records.

              You can place a portal (or several) to ShiftCategory on your layout to get the results you've specified.

              • 4. Re: Do I Need a Script or Calculation to populate a related field in parent table???
                MarieT

                I've set up the TOs, fields, and loaded as suggested.

                What does the script look like that accomplishes this?

                You can set up a script, so that each time you create a new worker  record, your script also creates the related shift category records.

                Thanks for answering.

                marie

                • 5. Re: Do I Need a Script or Calculation to populate a related field in parent table???
                  philmodjunk

                  Put a button on your layout for creating new worker records to run this script: (Or in FileMaker Advanced, modify the layout's menu to run it.)

                  New Record/Request
                  Set Variable [$WorkerID ; Value: Worker::WorkerID ]
                  Freeze Window
                  Go To Layout [ ShiftTypes ]
                  New Record/Request
                  Set Field [ShiftTypes::WorkderID ; $WorkerID ]
                  Set Field [ShiftTypes::ShiftType ; "Sat AM"]
                  New Record/Request
                  Set Field [ShiftTypes::WorkderID ; $WorkerID ]
                  Set Field [ShiftTypes::ShiftType ; "Sat PM"]
                  //continue this for each shift type
                  Go To Layout [original layout]

                  For your existing records, you can run this script minus the first line for each existing worker record and you'll get the needed shift type records for them as well.

                  PS. It's possible, with a little creativity to create the ShiftTypes records in a loop where the ShiftType names are loaded in a list prior to the records being created.